Suppressing SQL Server Management Studio's 'Messages' from output of SQLCMD via SQL Server Agent

Question

I've created a script that outputs data in an XML format, and was using SQL Server Agent (Job Step of Type 'Operating System (cmdexec)') to run a job monthly/quarterly to output the data to a specific file. However, the issue that we are running into is that the output from the SQLCMD script:

sqlcmd -d <Database> -i "<Package>\<SQLFile>.sql" -o "<OutputFilePath>\<FileName>.xml" -y 0

is outputting the 'Messages' as well as the 'Results'. Is there a way to only write the results via sqlcmd?

I've removed a few messages including:

"Warning: Null value is eliminated by an aggregate or other SET operation. "

"(1 row affected)"

By using the following Code:

SET NOCOUNT ON
SET ANSI_WARNINGS OFF
GO

Thanks for your suggestions!

Answer 1

A better approach is to switch over to a PowerShell job step, which is about 1000% more powerful than CMDEXEC. For instance in PowerShell writing an XML (or JSON) result to a file is as easy as:

invoke-sqlcmd 'select * from sys.objects for xml path' | % { $_[0] } | out-file 'c:\temp\results.xml'

Since PowerShell passes results as streams of objects, cmdlets can differentiate between data and messages, and only put data on the output pipeline. eg this

invoke-sqlcmd "print 'hello'; create table #T(id int); insert into #t values (12); select * from sys.objects for xml path" -Verbose | % { $_[0] } | out-file 'c:\temp\results.xml'

Still creates a valid XML file, as only the rows that result from the SELECT are sent through the pipeline to out-file.

By contrast sqlcmd.exe is a separate program and can only pass results to the calling program through Standard Output and Standard Error, which are just plain text streams. So it can't differentiate between errors, informational messages, resultsets, and rowcount messages.

Answer 2

I second using Powershell for executing queries, but as an answer for sqlcmd itself:

You can use -r1 parameter, which will redirect all output to the screen, rather than the file.

From the docs

-r[0 | 1]

Redirects the error message output to the screen (stderr). If you do not specify a parameter or if you specify 0, only error messages that have a severity level of 11 or higher are redirected. If you specify 1, all error message output including PRINT is redirected. Has no effect if you use -o. By default, messages are sent to stdout.

Because it is ignored if you use -o, you would need to redirect stdout instead

sqlcmd -d <Database> -i "<Package>\<SQLFile>.sql"  -y 0 -r1 > "<OutputFilePath>\<FileName>.xml"