question

pooja124 avatar image
pooja124 asked

Bcp error unable to open host file on network drive

I am working on a virtual machine with sql server 2008(R2). I am trying to use bcp command to export table data on network drive but its giving bacp cannot open host file error. the command is EXEC xp_cmdshell 'bcp "SELECT * FROM ayn.dbo.temp_tableforbcp " queryout "\\ clt.alexandercc.local\Userdata\HomeFolders\volpj01\data.xls" -c -S ALEXSQLP -U sa -P AYN1965 ' I think I have all necessary permission for sql server to access network drive but it is still giving me the error.pls tell me what permissions do I need to check
bcp
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
David Wimbush avatar image
David Wimbush answered
It looks like you're using the sa login for this. That's a SQL-only login so I doubt it has any access to the file system. Try replacing '-U sa -P AYN1965' with '-T'. That way, it will use your credentials when you run it yourself, and the SQL Server service's account when you run it in a SQL Agent job step. Of course, whichever way you do it, the account in question will need sufficient permissions on the files and folders involved.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.