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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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:
The following will execute the
CD %TEMP% SQLCMD -E -Q "print 1;" -r0 > NUL
But the following displays an error message as those are not being redirected to
CD %TEMP% SQLCMD -E -Q "print a;" -r0 > NUL
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.
The following will work:
sqlcmd -U user -P pass -S Server -Q "sp_who" -r0 1> test.log
The output from the query will be put into the log and nothing is printed on the screen.
More details at the MSDN article on sqlcmd.
Found this on SQLServerCentral
sqlcmd -E -i"install.sql" -r1 2> install-err.log 1> install.log