asked on

Run batch file using stored procedure - access denied

Hello,

When I run this command through the stored procedure,

SET @PATH = 'D:\ExcelFilexfer.bat'

EXEC xp_cmdshell @PATH;

It returns:
Access is denied.
0 file(s) copied.
Could Not Find C:\WINDOWS\system32\*.xls

-------------------------------------
ExcelFilexfer.bat Batch file content, it has only one line (it resides on the database server)

copy D:\ ExcelFilexfer.xls \\otherservername\foldername\*.*
-------------------------------
When I run this batch file from the command prompt of the database server, it works just fine.

What am I doing wrong? Can anyone please help me?

Thanks,
Andris help

TempDBA

Can you check if office package is installed in your system? But still for copying you don't need excel. I am just confused with your error message:-

Could Not Find C:\WINDOWS\system32\*.xls

Can you try running the following from sql server query.

EXEC xp_cmdshell 'copy D:\ ExcelFilexfer.xls \\otherservername\foldername\*.*'

Again, \\otherservername\foldername\ should be sufficient. There is no need of *.* there.

andrishelp

ASKER

This is correct version of batch file because I made one mistake.

ExcelFilexfer.bat Batch file content, it has only one line (it resides on the database server)

copy D:\*.xls \\otherservername\foldername\*.*

I ran this query directly to stored procedure, but still getting access denied error.

EXEC xp_cmdshell 'copy D:\ *.xls \\otherservername\foldername\*.*'

TempDBA

Are you using windows authentication or sql server authentication to connect to the sql server?
The error signifies that the login doesn't have proper access to the \\otherservername\foldername\*.*. Its where the command is try to write.

andrishelp

ASKER

Right click the servername from mgmt studio and go to properties. Click the security tab and server authentication set to "SQL Server and Windows Authentication Mode".

How can we find which login that sql server is using?

TempDBA

While you open the SSMS, what authentication you are providing? I mean in the dialogue box, when you connect to an instance of the server, do you give userid and password or you just hit enter?

andrishelp

ASKER

"Windows Authentication"

VENKAT KOKULLA

Might the account which you are using doesn't have full rights (Admin) on the system. Please provide Admin rights if not already provided.

Try Below:

While Open SSMS, Right click on the SSMS Icon and select Run as Admin option then after try to run the SP which having the batch script.

It might resolve the issue let us know the status.

--Venkat

ASKER CERTIFIED SOLUTION

andrishelp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

andrishelp

ASKER

Nothing worked. So I created the SQL SERVER AGENT Job to copy files from one server to another server.
Thanks for all your help.