Question Re: How do I execute a batch file using xp_cmdshell? in Technology Q&A

How do I execute a batch file using xp_cmdshell?

Former Member — Wed, 06 Oct 2010 17:41:32 GMT

We are using ASA 11.0.1 build 2044. The database runs as a windows service using the local system account.

I need to copy data files from our web server to our database server before a stored procedure runs. The xopy command is in a batch file and it works fine if I double-click on it or if I execute it from Start>>Run. However, I need to call it as part of a procedure using xp_cmdsheell. When I try to run it this way, nothing happens.

Here is the simple script where 'XXXX' is a parameter I am passing to the batch file:

call xp_cmdshell('c:\\somefolder\\copy.bat' + ' XXXX', 'no_output');

The copy.bat has the following syntax:

xcopy T:\\somefolder\\%1\\history\\*.txt c:\\somefolder\\%1\\history /s /y

Again, this is being run on the database server and we are trying to get files from the web server. The web server is mapped to the database server in windows explorer as follows: c$ on 'server1' (T:). I've modified copy.bat in different ways to get it to work without success. For example, I've tried the following in an attempt to account for UNC mapping:

xcopy \\\\\\\\server1\\\\somefolder\\\\%1\\\\history\\\\*.txt c:\\somefolder\\%1\\history /s /y

What is the correct syntax for the xcopy command in this situation? Is this a rights/permissions issue on the folder I am trying to copy from? Thanks, Tom

Re: How do I execute a batch file using xp_cmdshell?

justin_willey — Wed, 06 Oct 2010 17:57:38 GMT

The problem is that the local system account has no network rights by default. You can either change that, or run the service under a user with the necessary rights.

Re: How do I execute a batch file using xp_cmdshell?

former_SQLA_member1694868 — Wed, 06 Oct 2010 21:02:51 GMT

If you do not want to change the service username, you can try using the Sysinternals tool PSEXEC. That will allow you to specify a username/password to use to run the command either as you specify or to run it on the remote system (web) back to the DB system.

It all depends on what control you have over the usernames and rights on each system.

PSEXEC Link

Re: How do I execute a batch file using xp_cmdshell?

Breck_Carter — Thu, 07 Oct 2010 05:58:55 GMT

@All: For those who don't know anything about http://sysinternals.com, not only has it always been a 100%-trustworthy website for downloading Windows utility programs, but it is now owned and operated by Microsoft itself.

Re: How do I execute a batch file using xp_cmdshell?

VolkerBarth — Thu, 07 Oct 2010 07:38:44 GMT

In addtion to Justin's correct answer:

A third (and in my mind better) solution is to leave the service under the local system account (and do not give that account network rights for reasons of security) but to make a temporary network connection as a specific domain user with the according rights, something like

xp_cmdshell("NET USE /USER:.......") -- do the desired network access xp_cmdshell("NET USE /DELETE ....")

When these connect/disconnect calls are put into separate procedures, you could even use ALTER PROCEDURE SET HIDDEN to hide any credentials.

We have used this method to copy backup files to a net share for years.

In addition, we typically use the 2> option with xp_cmdshell to pipe stderr messages to a file, something like

nRetCode = call xp_cmdshell('XCOPY C:\\*.* D: 2> C:\\MyError.txt', 'no_output');

and in case nRetCode is <>´0 (which shows an error condition), read any OS error messages with xp_readfile('C:\\MyError.txt').

Re: How do I execute a batch file using xp_cmdshell?

former_SQLA_member1694874 — Thu, 07 Oct 2010 08:10:55 GMT

This always catches me out when I'm trying to unload to a UNC path and the server has permissions to it but not sybase 🙂

Re: How do I execute a batch file using xp_cmdshell?

former_SQLA_member1694874 — Thu, 07 Oct 2010 08:24:57 GMT

I'm not sure but doesn't that mean any connection to the Sybase server can then execute sql to access the network share while you are between the use...delete ? It's probably not a problem for most people (if it is even the case), but some might not like that.

Re: How do I execute a batch file using xp_cmdshell?

VolkerBarth — Thu, 07 Oct 2010 09:21:13 GMT

@Daz: I agree. But the other alternatives (granting the database service network access) would allow network access permanently, whereas the solution shown does only allow that for a small timeframe - a clear improvement, methinks. - Besides that, xp_cmdshell, xp_writefile etc. require DBA permission, so not all users would have access.

Re: How do I execute a batch file using xp_cmdshell?

former_SQLA_member1694874 — Thu, 07 Oct 2010 12:36:15 GMT

@Volker It's a fine solution and I've already got somewhere I'm thinking of using it 🙂 I was just trying to make the only downside I could see a bit more obvious 🙂

Re: How do I execute a batch file using xp_cmdshell?

Former Member — Fri, 08 Oct 2010 16:23:43 GMT

First off, thanks to everyone who has replied. I like the idea of employing NET USE for this. However, I've never used it and I'm not sure what parameters to pass in for /USER and /DELETE. Assuming my username for the server is 'juser' would it be something like the following? Thanks

NET USE T: \\\\server1\\somefolder /USER: juser pwd

Re: How do I execute a batch file using xp_cmdshell?

Former Member — Fri, 08 Oct 2010 17:55:35 GMT

Volker - I tried this:

nRetCode = call xp_cmdshell('NET USE /USER:myuserid@production.local 2>' || strCmdErrorLog, 'no_output' );

The log file indicates that I am not using the correct syntax for NET USE -- it just lists what all the possible switches are. When I tried this:

nRetCode = call xp_cmdshell('NET USE M: \\servershare /USER:myuserid@production.local 2>' || strCmdErrorLog, 'no_output' );

I got the following error in the log file: "System error 67 has occurred. The network name cannot be found."

Re: How do I execute a batch file using xp_cmdshell?

VolkerBarth — Fri, 08 Oct 2010 21:16:12 GMT

@Tom: I generally use the "domainuser" syntax, i.e. "NET USE M: \\MyServerMyShare /User:MyDomainMyUser MyPwd 2> c:MyError.log" and "NET USE M: /DELETE". Can't commment on the syntax with "MyUser@MyDomain". Note, however, when you use these commands as SQL strings, you will have to mask all backslashes by doubling them, i.e. 'NET USE M: \\\\MyServer\\MyShare ...'.