0

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.

TT.
  • 15,774
  • 6
  • 47
  • 88
behzad razzaqi
  • 3
  • 1
  • 2

2 Answers2

1

You are running BCP on the SQL Server, which cannot see drives mapped on the local PC. If you have the SQL Server client tools installed (which includes BCP) installed on your client, simply run the BCP command from a command line.

If you must run BCP via xp_cmdshell on the server, the account used must have permissions to the network share and underlying file system. That will be the SQL Server service account if you are a sysadmin role member, or the xp_cmdshell proxy account for non-sysadmin users. You should specify a UNC path rather than a mapped drive in that case.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
0

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.

TT.
  • 15,774
  • 6
  • 47
  • 88