Unable to open BCP host data-file

Question

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:\Storage\Export\Data\max.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       "\r\n"           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 1

Does the output path exist? BCP does not create the folder before trying to create the file.

Try this before your BCP call:

EXEC xp_cmdshell 'MKDIR "E:\Storage\Export\Data\"'