Exporting results to any kind of file via SQL Agent without Job headers

Question

Exporting results to any kind of file via SQL Agent without Job headers

scarr030

SSCommitted

Points: 1699
More actions
February 21, 2019 at 12:43 pm

#377066

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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

scarr030 - Thursday, February 21, 2019 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?

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

Lowell SSC Guru Points: 323504 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

scarr030 SSCommitted Points: 1699 More actions · Answer 3

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.