Today while working with a .bat file full of SQLCMDs with really verbose
output, I wanted just to capture error messages that were being sent to STDERR without changing the .sql files.
I know how to capture/redirect handles in a variety of programming languages,
but I haven’t tried to do this in a windows command prompt. A

quick search located a neat page on

Using Command Redirection Operators in Windows XP.

The most common redirect used is > which can write the output (STDOUT)
from a command to a file instead of displaying it onscreen. Place the number 2
before it ( 2> ) and you are now getting the errors (STDERR) handle to
write to a file.

So using cmd.exe in Windows you can redirect a command output from STDERR to
a file using

cmd.exe command-to-run 2> errors.txt

If you are trying to capture the STDERR with SQLCMD, you will need to

use the -r switch. Use -r0 to capture error messages of severity level 11 or
higher. Use -r1 to capture all error and PRINT messages.

Capture Severity 11 or Higher Error Messages to install-err.log

sqlcmd -E -i”install.sql” -r0 2> install-err.log

Capture all Error Messages and Print Messages to install-err.log

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

Capture STDOUT to install.log and Error Messages to install-err.log

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

 

Incidentally I found these pages while giving
SearchMash
a go. Apparently
its a

feedback site for interface changes at Google. It uses a different branding
to try to get honest feedback from users on search results. Interesting.

Tags: coding, Debugging, Microsoft SQL Server, MSSQL, MSSQL Performance, SQL Server, SQL Server Management Studio, SQLCMD, T-SQL, T-SQL Performance