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.