You may get the following error while running a SQL Server command xp_cmd. Please refer to the previous post. In this example I have changed the destination to a different drive, the D: drive. What’s the problem? It worked on the C: drive but not on the D: drive. The directory exists under the D: drive.

sql string to be sent to xp_cmdshell to create output file: 
bcp "SELECT Output FROM myDatabase.dbo.AuthorsFormatted" queryout "D:\data\temp stuff\authors20170301.txt" -c -T -SDESKTOP-R8A1P4K\SQLEXPRESS
output
---------------------------------------------------------------------------------------------------------
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file
NULL

I searched for an answer and found a possible solution at StackOverflow: “This seems to be permission of the folder. Edit the Security Tab under Folder Properties and give Modify permission to the user account through which SQL Server services are running in the SQL Server. Normally this user account is named as MSSQLSERVER.”

Error Trapping

There is an article at sqlservercentral.com that discusses error trapping. The article is called Easy Error Trapping When Using xp_cmdshell.

At the bottom of the article the author, Sylvia Moestl Vasilik, states the following: “When using xp_cmdshell with bcp, keep in mind that it will NOT recognize any temporary tables that were created. If you need to use temporary tables, they must be global temporary tables, prefixed with ## instead of #.”

Bcp and Transactions

Running this code, xp_cmdshell with bcp can hang SQL Server. Why? bcp cannot be run inside of a transaction.