Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

SQL Server Messages: fairies rearranging furniture.

SQL Server and Sybase are geared up to send information to applications over two distinct channels, the results in one channel that we all know and use that is dedicated to the processing of result sets, and both messages and errors in the other..

Messages were once used a lot for advising of scheduled down-time, or advising of potential problems. They can cause problems. I can remember once being in the production team for what was then one of the largest database systems in the world. The admin team was rectifying a deficiency in the schema that was causing performance issues, and decided to roll out the new version. It was, for some reason, something that would result in a down-time of six hours. A message went out, visible in a message area at the bottom of the screen, saying ‘Scheduled system down-time of 6 hours due to necessity for rearranging the tables’. An angry manager burst into my office, pink with emotion, complaining that he didn’t see why an international business should suffer just because some flaming fairies  in IT wanted to re-arrange their office furniture.

Messages and errors are very similar in SQL Server. A database can send anything from a purr to a squeak of pain, along a continuum. The only real difference is that messages have a severity level less than 11.  You will get messages from statements such as DBCC.  Any TSQL code can send a message, usually via RAISERROR, or PRINT. At the application level, the way that you handle messages and errors will be very different.

The Print statement in TSQL is a misunderstood creature, probably because of its name. It actually sends a message to the error/message-handling mechanism that then transfers it to the calling application.  PRINT is pretty dumb. You can only send 8000 characters (4000 unicode chars). You can send a literal string, a string variable (varchar or char) or a string expression. If you use RAISERROR, then you are limited to a string of just 2,044 characters.  However, it is much easier to use it to send information to the calling application since it calls a formatting function similar to the old printf in the standard C library. RAISERROR can also specify an error number, a severity, and a state code in addition to the text message, and it can also be used to return user-defined messages created using the sp_addmessage system stored procedure. You can also force the messages to be logged.

Your error-handling routines won’t be any good for receiving messages, despite messages and errors being so similar. The technique varies, of course, according to the actual way you connect to the database (OLBC, OLEDB etc). In order to receive and deal with messages from the SQL Server Database Engine, when you’re using System.Data.SQLClient, you’ll need to  create a SqlInfoMessageEventHandler delegate, identifying the method that handles the event,  to listen for the InfoMessage event on the SqlConnection class. You’ll find that  message-context information such as severity and state are passed as arguments to the callback, because from the system perspective, these messages are just like errors.

It is always a good idea to have a way of getting these messages in your application, even if you are just spooling to a file, because there is always going to be a use for them when you are trying to chase a really obscure problem. However, I can’t think I’d want the end users to ever see them unless you can reserve an informational level that displays stuff in the application.  


No comments.

Leave a Comment

Please register or log in to leave a comment.