Most of the scheduled jobs on my SQL Servers write some sort of output to a log file. Most of the time, it’s just the output of a SQL statement. However, for some of my longer, more complicated routines, the procedures I write include PRINT statements to output various statuses or progress messages while the procedure is running.

When these are running on SQL Server Management Studio, they appear in the Message tab of the results pane. If the procedure is run as a scheduled job and the option to output the results to a file is enabled, the text output by the PRINT statement gets written to the text file. But it’s not pretty.

If you’ve ever tried this, you’ll quickly discover that SQL Server appends the text [SQLSTATE 01000] to the end of each line output by a PRINT statement. I find this really annoying. It renders the log file much harder to read.

There was a Microsoft Connect item created to change this, but Microsoft has stated they will not change this behavior. Apparently, their code cannot tell the difference between a PRINT statement and an error and SQLSTATE is a vital piece of information that needs to be included in error messages.

Enter Powershell

Because SQL Agent offers a PowerShell step type, it’s very easy to use PowerShell to remove these unwanted additions to your wondrous PRINT statement messages. For my jobs that output verbose log files populated by such messages, I just add an additional step at the end of the job that calls a PowerShell script to replace the string “[SQLSTATE 01000]” with a carriage return and line feed.

Here’s the script:

$file="FullPathAndFileNameHere.txt"
Get-Content $file | ForEach-Object { $_ -replace "\[SQLSTATE 01000\]", "`r`n" } | Set-Content ($file+".tmp")
Remove-Item $file
Rename-Item ($file+".tmp") $file

What the script does is export the contents of your file to a temp file, replacing the offending string with a carriage return-line feed as it does so, deletes the old file, and renames the temp file back to your original file name. The script needs to escape the brackets, as they are special characters in PowerShell. Additionally, the `r`n combinations are PowerShell special characters that represent a carriage return and a line feed.  The script works well if the filename the job writes to is always the same. If it changes, you’ll need to add logic to set the $file variable correctly.