First, rule out an xp_cmdshell issue by doing a simple 'dir c:*.*';
Check out my blog on using BCP to export files.
I had problems on my system in which I could not find the path to BCP.EXE.
Either change the PATH variable of hard code it.
Example below works with Adventure Works.
-- BCP - Export query, pipe delimited format, trusted security, character format
DECLARE @bcp_cmd4 VARCHAR(1000);
DECLARE @exe_path4 VARCHAR(200) =
' cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn\ & ';
SET @bcp_cmd4 = @exe_path4 +
' BCP.EXE "SELECT FirstName, LastName FROM AdventureWorks2008R2.Sales.vSalesPerson" queryout ' +
' "C:\TEST\PEOPLE.TXT" -T -c -q -t0x7c -r\n';
PRINT @bcp_cmd4;
EXEC master..xp_cmdshell @bcp_cmd4;
GO
Before changing the path to \110\ for SQL Server 2012 and the name of the database to [AdventureWorks2012], I received the following error.
After making the changes, the code works fine from SSMS. The service is running under NT AUTHORITY\Local Service. The SQL Server Agent is disabled. The output file was created.
I modified the code as you suggested by hard coding the path but I get the same error. Unable to open host data file.
Can you paste a screen shot of the actual error on the post? After the EXEC, can you PRINT @@ERROR to see if there is an error code?
Also, can you look at the permissions on the directory and files that you are using? Does the above example work in your environment.
Couple more suggestionss: 1 - change the name of the file. using a minus might be an issue. 2 - why is there no delimiters in the output? change "$#Y#$" to "," and the same for "" to see if that is an issue. It is either a permission or command issue. Can not tell w/o having the database on hand.
I got it! Since I was specifying the location of BCP.exe I thought I'd try moving the .fmt file to the same location I was executing BCP from "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\" and it worked. Thank you soo much.