12

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.

bytedev
  • 8,252
  • 4
  • 48
  • 56

3 Answers3

28

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
Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • 2
    Thanks. MSDN mentions the -m and -V options: [link]http://msdn.microsoft.com/en-us/library/ms162773.aspx - but it says that severity level needs to be equal or less to the provided number. To me this seems very strange, for example why would someone want info messages but not the errors. I was hoping for a more log4net (for example) style severity filter (specify ERROR and get ERROR and FATAL messages only) – bytedev Jun 28 '12 at 13:36
  • 1
    Note: I will use -r0 then (to get only errors). Will just have to do without "warnings". – bytedev Jun 28 '12 at 14:20
1

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.

PabloInNZ
  • 515
  • 4
  • 14
0

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.

user3448451
  • 21
  • 1
  • 2