Search code examples
sql-serverbatch-filecmdbcp

BCP Syntax Issues (pt2)

I am trying to execute a really simple SQL select query to file as it is on a 3rd party suppliers DB hosted in SQL Express with no SSIS.

I have written the select query & tested this runs within SSMS without any issue at all.

I have had a nightmare however trying to get BCP to run in a command prompt. I have tried running from my local machine, running on a network drive, even adding the .bat file to the tools/binn dir of SQL server as advised elsewhere on line.

The error I get now flashes up very quickly so I had to grab a quick screenshot before it closed the cmd window as per below:

Command Line Issue

I have also tried running from a scheduled task on the server but this produced different error codes within the servers error logs.

This is the last piece in a wider project & I don't have much experience with .bat files so any suggestions would be greatly appreciated.

The bat file looks like the below (passwords replaced with XX - Excuse the long query. I need to create a file that has an exact number of sections for the import inot a 3rd party systerm to work so added numbers to easily count fields).

BCP "SET NOCOUNT ON; SELECT '|' + '|' + '|' + '|' + '|' + '|' + '|' + '|' + '|' + '|' + '|' + '|' + '|' + '|' + [sender_reference] + '|' + '|' + '|' + '|' + (REPLACE(CONVERT(CHAR(15), manifest_dt, 103),' ',' - ')) + '|'  + '|' + '|' + '|' + '|' + '|' + '|' + '|' + '|' + '|' + '|' + '|' + '|' + '|' + '|' + [airwaybill_num] + '|' + '|' + '|' + '|' + '|' + '|'FROM airwaybills_history WHERE manifest_dt between (SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()))) and (GETDATE()) + '23:59:59''" QUERYOUT \\carrier\test-file\test.OUT -S CARRIER\DHLEASYSHIP -U xx  -P xx -c
Solution

  • I just ran the following on my test platform without problems. Created a C:\Temp\queryout.bat with following contents (all on one line):

    BCP "SELECT*FROM INFORMATION_SCHEMA.TABLES" QUERYOUT "C:\temp\inf_schema.out" -S my_server\my_instance -T -c -d my_database

    It uses -T, for you that would be -U xx -P yy. Also the my_..., you know what to do with those.

    Does that work for you? If so, you could mod it to see if it works for you. If not, what are the errors?

    Also, if you want to actually be able to see the errors, run this from cmd.exe.