Need an Help on Xp_Cmd shell and BCP command

Question

Hi ,

My Query goes as shown below

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

Here I have my local severe with name  Santhoshh\SqlExpress and within that i am trying to acess my table T2 which has few columns from the database Test and trying to put into a txt file with "|" separated values. But whenever i am executing this i am getting an error as below

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 i dont know hw the bcp command wrks.

thanks,

Santhosh

---

Please have look on the comment

Answer 1

can you try to change the path of the output file to non system folder, say C:\TestFolder\Results.txt OR D:\Results.txt ?

regards

joon

Answer 2

Hi ,

It worked with my local path C:\Users\santhoshh\Desktop\Novels\Results.txt after adding the user and giveing read write permissions to it.But now i am trying to access a remote server \\ <>\Test\Result.Txt but this is now giving an error as mentioned before. So is thr any condition or anything that has to be added in remote serevr.

Please let me know as this crucial.

---

Please have look on the comment

Answer 3

Hi Below in BCP format;

EXEC master..xp_cmdshell 'bcp "YourDatabaseQuery" queryout "YouCSVFilePath" -c -t, -S "ServeName\InstanceName" -U bcpuser -P bcppassword -w'
EXEC master..xp_cmdshell 'bcp "SELECT EmployeeID,NationalIDNumber FROM AdventureWorks.HumanResources.Employee" queryout "E:\Test\Sample.csv" -c -t, -S SSSLT105\SQLEXPRESS08 -U sa -P 123456 -w'
EXEC master..xp_cmdshell 'type "YourInitailCSVFile" >> "YourAppendedCSVFile"'
EXEC master..xp_cmdshell 'type "E:\Test\Sample.csv" >> "E:\Test\SampleAll.csv"'

EXEC master..xp_cmdshell 'bcp "YourDatabaseQuery" queryout "YouXMLFilePath" -f formateFile-c..xml -T, -S "ServeName\InstanceName" -U bcpuser -P bcppassword -w'
EXEC master..xp_cmdshell 'bcp "SELECT EmployeeID,NationalIDNumber FROM AdventureWorks.HumanResources.Employee" queryout "E:\Test\Employee.xml" -f Employee-c..xml -T -S SSSLT105\SQLEXPRESS08 -U sa -P 123456 -w'

Please let me know if this doesn’t work for you. Hope I have answered you correctly.

Thanks,
Hasham

Answer 4

Tried by changing the path but did not wrk. So is there anything that need to set on remote server

---

Please have look on the comment

Answer 5

For this to work, the service account for SQL Server must have write permission to the location where you write the file.

However, there is reason to reconsider the strategy. xp_cmdshell is best disabled, and as you have seen there is some hassle when you write files to remote shares.

It may be better to write an application program that can run BCP directly and not from within SQL Server. This relieves you from the file-permission issues.

SSIS is also an option, but Express Edition does not include Integration Services.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

Hi,

Can u please explain me hw to set the service account of sql server to give a read write permission to destination remote location

---

Please have look on the comment

Answer 7

Right-click the file, select properties. Go to the Security tab and find your ways around. If you have more question, please try a forum for Windows security.

To change the service account for SQL Server, use SQL Server Configuration Manager. Right-click the server, select the Log On tab where you can set the service account. (You may need to do this if you are using NETWORK SERVICE.)

You should probably consult your Windows administrator for which account to use etc.

---

Erland Sommarskog, SQL Server MVP, [e-mail]