I have a SQL Server Agent job which runs a select statement on a schedule and appends results to a CSV file

(Right click the job --> properties --> advanced and then adding a file path to the output file location)

This is great except the output it generates displays

Job 'MyJobName' : Step1, 'Step Name' : Began executing YYYY-MM-DD HH-MM-SS

as well as the column headers throughout the file (it adds this with each append)

Is there a way to not output the Job name / step details and column headers?

up vote 1 down vote accepted

You can do this with SQLCMD

Query

remember to add nocount so it does not add the rows affected to the file

sqlcmd -Q"set nocount on; select * from sys.databases;"  -S localhost -D master -o  "c:\temp\test3.csv"

Your job step needs to be SQLCMD however enter image description here

The file

enter image description here

Part of the output enter image description here

Similar to @Randi's approach

If using POWERSHELL is an option, you can change the job type to POWERSHELL and use INVOKE-SQLCMD.

A possible downside to this approach is because ConvertTo-CSV automatically surrounds output columns with double quotes. The follow script handles the elimination of ALL double quotes and also removes the header from the results.

Invoke-Sqlcmd -ServerInstance "." -Query "Select name from sys.databases where Database_ID <=4" | ConvertTo-Csv -NoTypeInformation | % {$_ -replace '"',''} | Select-Object -Skip 1 | Out-File ("C:\Test\test.txt") -Append -Force -Encoding ascii

Running the SQL Agent job twice outputs the following for me due to using -Append

master
tempdb
model
msdb
master
tempdb
model
msdb
  • I like this aswell, but isn't the powershell job step kinda buggy? I mostly use cmd and call powershell from there. – Randi Vertongen Dec 11 at 10:31
  • @RandiVertongen - I personally haven't encountered any issues using the Powershell job type, but I would not say I have used it extensively. Testing would definitely be important. My answer was an attempt to show how to remove the column headings from the output. – Scott Hodgin Dec 11 at 10:41

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.