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
asked Sep 30, 2015 at 05:23 AM in Default
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.
answered Sep 30, 2015 at 02:08 PM