Take the 2-minute tour ×
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's 100% free, no registration required.

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.

share|improve this question
    
Hmm, in another app we could redirect error output with 2> Err.txt, but it looks like SQLCMD does not split its output. –  Jon of All Trades Jun 28 '12 at 23:26
1  
@JonofAllTrades By default yes, SQLCMD sends all output to stdout. However, you can instruct SQLCMD to send errors to stderr via the -r0 command-line switch, in which case they can be redirected to a file using 2>, or they can be displayed while regular messages are redirected via >. Please see my answer for details. –  srutzky Aug 12 at 1:35

3 Answers 3

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.
share|improve this answer

Found this on SQLServerCentral

sqlcmd -E -i"install.sql" -r1 2> install-err.log 1> install.log

http://www.sqlservercentral.com/Forums/Topic536968-146-1.aspx

share|improve this answer
    
This does not actually work according to the requested "I essentially dont want information based messages to be output" (my emphasis). The issue is that -r1 sends all messages to stderr, including PRINT and RAISERROR('', 10, 1) messages. You need to use -r0 to keep those regular info messages going to stdout such that they are redirected to install.log. –  srutzky Aug 12 at 1:32

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.

share|improve this answer
    
This does not actually work according to the requested "I essentially dont want information based messages to be output" (my emphasis). The issue is that -r1 sends all messages to stderr, including PRINT and RAISERROR('', 10, 1) messages. You need to use -r0 to keep those regular info messages going to stdout such that they are redirected to test.log. –  srutzky Aug 12 at 1:30
1  
@srutzky Thanks, modified my answer to reflect your comment. –  John M Aug 12 at 13:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.