February 21, 2019 at 12:43 pm
Hi all,
In short, I have a need to export some data from a client that's on their own server. We have a way for the information (which will be held in some kind of file - likely .txt, but.xls would be amazing) to be transmitted from server to server, but only one the information has been dumped to this file. It's been easy enough to dump the info into the file; that in itself isn't the issue. The problem is that along with the results, the job is also including the 'headers' for this job (job name, execution time, query being used, SQL State messages, and a whole lotta dashes. I can't figure out how to stop this information from being included. Any ideas?
February 21, 2019 at 4:30 pm
scarr030 - Thursday, February 21, 2019 12:43 PMHi all,In short, I have a need to export some data from a client that's on their own server. We have a way for the information (which will be held in some kind of file - likely .txt, but.xls would be amazing) to be transmitted from server to server, but only one the information has been dumped to this file. It's been easy enough to dump the info into the file; that in itself isn't the issue. The problem is that along with the results, the job is also including the 'headers' for this job (job name, execution time, query being used, SQL State messages, and a whole lotta dashes. I can't figure out how to stop this information from being included. Any ideas?
No idea what you are using in the job to export the data but from the description I would guess you might be just executing a t-sql command and then using an output file for the data. If that's the case, trying using a CmdExec step instead and execute sqlcmd to execute your query and output that to a file.
At the beginning of the query, you can use SET NOCOUNT ON if you don't want the number of rows listed at the end.
Sue
February 21, 2019 at 6:10 pm
here's a complete example like Sue_H is referencing:sqlcmd -S stormserver\SQL2016 -Q "SET NOCOUNT ON;select TOP 10 * from DBA.dbo.Algorithm" -o c:\data\myfile.csv -h-1 -s","
The parameter flags are case sensitive:
-S ServerName
-Q query and exit after execution
-H headers -1 is no headers
-s column seperator (comma)
Lowell
February 22, 2019 at 7:58 am
Sue,
My apologies for not being clear; your assumptions are exactly correct. I had been using NOCOUNT to keep the row count out of the output, but the headers were alluding me. Thank you very much for the suggestion, I'll try it today.
Lowell,
Thanks for the example. Should make things easier on me - not too familiar with scripting.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply