niceApp niceApp - 1 year ago 123
SQL Question

XP_CMDSHELL bcp error

I am executing the XP_CMDSHELL as below:

Exec XP_CMDSHELL 'bcp "Select OrderID, OrderDate, OrderDesc from DB.dbo.Order" queryout C:\Orderfile.txt -k -t \t -c -Slocalhost -T'

I got the following error:

"Unable to open BCP host datafile."

I looked at the c:\ drive and there is a file Orderfile.txt exist. ANd i have not opened the file or any other program doesn't not the file. But still I am getting this error.

Anyone have any idea why I am getting this error? Thank you in advance.

gbn gbn
Answer Source

One of two issues:

  • XP_CMDSHELL uses the SQL Server service account credentials for filesystem (and other) access. The service account won't have permissions on the root of C: and it doesn't matter if you can see it

  • SQL Server is on a server somewhere (that is, isn't a local install) and you are trying to access your local C: drive. SQL Server can't see this of course...

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download