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.

By default, SQLCMD sends all non-error messages and error messages to stdout. Hence, attempting to redirect output won't help.

The first thing you need to do in order to get only one or the other type of messages (error or non-error) is to tell SQLCMD to separate them by sending the error messages (severity level 11 or higher) to stderr. You do this by using the -r command-line switch, specifying 0 as the option for that switch (i.e. -r0). At this point there is no visible difference in terms of running SQLCMD and seeing messages of any type displayed on the screen.

The next part is to filter out the regular messages going to stdout. This can be done by redirecting the stdout messages to somewhere via >, but where to? You could do > file.txt, but I doubt you want a file of the messages that you didn't want to begin with. Fortunately, DOS has the NUL keyword (that is not a typeo: it has one L, not two) that works like /dev/null in Unix. Meaning you can use the following to redirect output to nowhere: > NUL.

The following will execute the PRINT command and show no output as no errors are generated, and no file is created that would contain the output of the PRINT command:

CD %TEMP%

SQLCMD -E -Q "print 1;" -r0 > NUL


But the following displays an error message as those are not being redirected to NUL:

CD %TEMP%

SQLCMD -E -Q "print a;" -r0 > NUL


Returns:

Msg 128, Level 15, State 1, Server DALI, Line 1
The name "a" is not permitted in this context. Valid expressions are constants,
constant expressions, and (in some contexts) variables. Column names are not permitted.


StackExchange Database Administrators Q#20096, answer score: 5