BCP Utility on Remote Server

Question

Hi ,

My Query goes as shown below

Exec xp_cmdshell  ' bcp "Select * from Test..T2" QueryOut "\\<ServerName\C:\Users\santhoshh\Desktop\Novels\Results.txt" -T -c -t"|" -S Santhoshh\SqlExpress'

Above Wrks with the local machine but getting an error as shown below whenever trying to access remote server

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
NULL

Please help me out in debugging this query .

thanks,

Santhosh

---

Please have look on the comment

Answer 1

hi Santosh

please check xp_readerrorlog is enable or not,

check the screen shot which i have done on my desktop

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 2

Hi Ramesh,

Its wrking on my local maschine if replace my address to remote server i.e say from C:\Test to \\Remoteserver\C:\ then again i get the same bcp error

---

Please have look on the comment

Answer 3

Hi,

It should be \\Remote\C$\... not \\Remote\C:

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 4

But even Then its not Working

---

Please have look on the comment

Answer 5

Hi,

Please, try the following query

Exec xp_cmdshell  'dir \\<ServerName\C$\Users\santhoshh\Desktop\Novels\Results.txt'

Does it work?

If it doesn't, then SQL Server account doesn't have the necessary privileges to access that remote folder, that would be the case when SQL Server runs with 

local credentials instead of domain ones.

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 6

Check for SQL Server Services is started with a Domain Account

please make sure that the destination server folder is shared with FULL Access properties

---

Ramesh Babu Vavilla MCTS,MSBI