• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 933
  • Last Modified:

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
0
andrishelp
Asked:
andrishelp
  • 5
  • 3
1 Solution
 
TempDBACommented:
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.

0
 
andrishelpAuthor Commented:

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\*.*'
0
 
TempDBACommented:
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.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
andrishelpAuthor Commented:

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?
0
 
TempDBACommented:
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?
0
 
andrishelpAuthor Commented:
"Windows Authentication"
0
 
VENKAT KOKULLASQL Server DBACommented:
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
0
 
andrishelpAuthor Commented:
Nothing worked. So I created the SQL SERVER AGENT Job to copy files from one server to another server.
Thanks for all your help.
0
 
andrishelpAuthor Commented:
Nothing worked. So I created the SQL SERVER AGENT Job to copy files from one server to another server.
Thanks for all your help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now