0

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
AshBash
  • 27
  • 6

1 Answers1

0

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.

TT.
  • 15,774
  • 6
  • 47
  • 88
  • Thanks for the pointers. I'm getting an error now in cmd.exe about BCP: unknown option d. This is using the code above on a single line just with our auth credentials in place instead of -t – AshBash Nov 16 '16 at 17:02
  • @AshBash `-d` is for specifying the database which the query should be run on. If omitted, you have to specify the database in the query (like `SELECT*FROM my_database.INFORMATION_SCHEMA.TABLES`). – TT. Nov 16 '16 at 17:04
  • Thats what I have also used before so not sure why I'm getting an error on that at present. I'll try specifying in the SQL query and work through a process of elimination... Will come back with feedback. – AshBash Nov 16 '16 at 17:08
  • Error is improving! I think it is permissions now: SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-f ile – AshBash Nov 16 '16 at 17:10
  • @AshBash What if you try the BCP from SSMS as is done [here](http://stackoverflow.com/a/40591155/243373) (substitute the `-T` with `-U xx -P yy`)? That is my go-to sanity-check script. Also make sure the account you're working with has write access in `C:\Temp` – TT. Nov 16 '16 at 17:21
  • @AshBash More input about the error you are now getting [here](http://stackoverflow.com/q/18619061/243373), [here](http://dba.stackexchange.com/q/128855/65699) and [here](http://dba.stackexchange.com/q/31202/65699). – TT. Nov 16 '16 at 17:24
  • I think thisw is my issue@tt: http://stackoverflow.com/questions/2016669/getting-execute-permission-to-xp-cmdshell – AshBash Nov 16 '16 at 19:04
  • @AshBash That sanity-script yeah, you need to enable `xp_cmdshell` first. Probably nothing to do with the problem in your question. – TT. Nov 16 '16 at 19:14
  • I have sorted it... The issue was to make sure it was a domain admin account executing the script then the scheduled task. I had to wait on one of our domain admins for the details but is running now. Thanks so much for all of the help and advice. I've learned a lot through this task. – AshBash Nov 18 '16 at 16:48