Getting SSMS to print messages before hell freezes over

by Robert Mills 27. December 2010 08:52

Scenario: You have a sproc that takes about 2 decades to run and you want to see where the problem section lies.
Problem: When you put print statements in your sproc to debug, ssms (and sqlcmd) cache them until after the sproc is done running before printing them out. This also affects RAISERROR.

Before we go any further, I want to say, who ever designed this needs to reevaluate their career choice.

Anyhow, The solution is to use RAISERROR, with any severity of 10 or less (SQL won't stop on a low severity error) and add WITH NOWAIT. This tells the query engine to print you message NOW DAMNIT.

Syntax: RAISERROR('Print my message NOW DAMNIT',0,1) WITH NOWAIT

Tags:

SQL

About the author

My name is Robert Mills and I am a .NET developer in Charlotte(ish), NC. While everyone is welcome to peruse, most of these posts won't actually provide any value, they are just a reminder to me so I won't forget.

Tag cloud

Month List