SQL Server Agent – Do not show job step details and column headers in output file

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Method 2

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply