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.