Unable to open BCP host data-file

Below is an example of the BCP Statement. I’m not accustomed to using BCP so your help and candor is greatly appreciated

I am using it with a format file as well.

If I execute from CMD prompt it works fine but from SQL I get the error. The BCP statement is all on one line and the SQL Server Agent is running as Local System. The SQL server, and script are on the same system.

I ran exec master..xp_fixeddrives C,45589 E,423686

I’ve tried output to C and E with the same result

EXEC xp_cmdshell 'bcp "Select FILENAME, POLICYNUMBER, INSURED_DRAWER_100, POLICY_INFORMATION, DOCUMENTTYPE, DOCUMENTDATE, POLICYYEAR FROM data.dbo.max" queryout "E:StorageExportDatamax.idx" -fmax-c.fmt -SSERVERNAME -T

Here is the format file rmax-c.fmt

10.0

7

1      SQLCHAR             0       255     "$#Y#$"          1     FILENAME                               
2      SQLCHAR             0       40      ""               2     POLICYNUMBER                                 
3      SQLCHAR             0       40      ""               3     INSURED_DRAWER_100                           
4      SQLCHAR             0       40      ""               4     POLICY_INFORMATION                           
5      SQLCHAR             0       40      ""               5     DOCUMENTTYPE                                 
6      SQLCHAR             0       40      ""               6     DOCUMENTDATE                                 
7      SQLCHAR             0       8       "rn"           7     POLICYYEAR    

Due to formating in this post the last column of the format file is cut off but reads SQL_Latin1_General_CP1_CI_AS for each column other that documentdate.

Answer

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 FilesMicrosoft SQL Server100ToolsBinn & ';
SET @bcp_cmd4 =  @exe_path4 + 
    ' BCP.EXE "SELECT FirstName, LastName FROM AdventureWorks2008R2.Sales.vSalesPerson" queryout ' +
    ' "C:TESTPEOPLE.TXT" -T -c -q -t0x7c -rn';
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 AUTHORITYLocal Service. The SQL Server Agent is disabled. The output file was created.