How do I execute a batch file using xp_cmdshell?

[Former Member]

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

[justin_willey]

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.

[former_SQLA_member1694874]

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 🙂