CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2sqlcmd
bytedev
bytedev

Reputation: 9109

How to get SQLCMD to output errors and warnings only

How can you get SQLCMD, when executing a SQL script file, to just output any errors or warnings it encounters?

I essentially dont want information based messages to be output.

Upvotes: 13

Views: 36278

Answers (3)

user3448451
user3448451

Reputation: 31

It looks like print statements are sent to stderr with -r1 so you can use them to log separate from your output like so:

sqlcmd -Q "print 'hello logfile';select 'Ted' as bro"  -r1 1> C:\output.txt 2> C:\logfile.txt

This also works with -i inputfile like: sqlcmd -i helloTed.sql -r1 1> C:\output.txt 2> C:\logfile.txt

helloTed.sql:

print 'hello logfile';
select 'Ted' as bro

Probably you could use -Q and insert exec a stored proc that contains prints.

Upvotes: 0

PabloInNZ
PabloInNZ

Reputation: 555

Just as an addition to this, if you are sending errors out to file, I found this https://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/

which I have used. If you omit setting the OUT, then you only get an error log created. So you have a command like this :

sqlcmd -x -E -S MyServer -i C:\MySQLBatchToRun.sql

Then in MySQLBatchToRun.sql , something like this

USE MyDatabase
:Error C:\MyErrorLog.txt
:r C:\MySQLScript.sql
GO

In MySQLScript.sql you have the actual SQL to run. It's a bit convoluted, but works. The only issue I have is that it seems to create an empty error log file, even if there is not an error.

Upvotes: 1

Ed Harper
Ed Harper

Reputation: 21505

It's not possible to prevent SQLCMD returning non-error output messages by passing it a parameter.

However, what you can do is redirect error messages to STDERR, then direct all other messages to NUL.

This is done by passing the -r parameter. From books online:

-r[ 0 | 1] msgs to stderr

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.

Set -r depending on exactly which error messages you want to display, but to display all error message output, an example command would be:

sqlcmd -Q "select 1 as a; select 1/0 as b" -E -r1 1> NUL

Upvotes: 28

Related Questions