How can I save SQL Server BCP queryout result into the local network share folder?

Question

I want write query to run the BCP utility and save the query result into the network share folder.

For that purpose I mapped another pc's share folder in my local network to my drive. That drive name is z. In my explorer it shows me the mapped drive. Opening that drive works and I have full control over it, but when I run this query:

EXEC xp_cmdshell 'bcp "SELECT  "028",rtrim(ltrim(anumber)),rtrim(ltrim(bnumber)),rtrim(ltrim(duration))  FROM [myTestReport].[dbo].[CDR]"  queryout z:\beh.csv -S DESKTOP-A5CFJSH\MSSQLSERVER1 -Umybehzad -Pbeh1368421 -f "f:\myFORMAT.fmt"  '

I get this error:

unable to open BCP host data file

How can I solve that problem? Thanks.

Answer 1

The following mistake jumps out:

• Having double quotes (") unescaped in your query. You need to double those. In your query the "028" needs to become ""028""

A working example highlighting this (tested on my environment):

DECLARE @cmd VARCHAR(8000);
SET @cmd='BCP "SELECT ""028""" QUERYOUT "C:\Temp\test.txt" -c -T -S ' + @@SERVERNAME + ' -d ' + DB_NAME();
EXEC master.sys.xp_cmdshell @cmd;

Note that it is generally best to enclose the data_file in double quotes ("). The way your command is written, it would fail if the file name had spaces in it.