Error Messages Versus Exceptions

I am using .NET v2.0 and SqlServer2005 SB in 90 compatibility mode.

From my brief experience it appears that if I try to subscribe to SB with incorrect SQL or wrong names .NET will immediately throw an exception. However, if my code is correct but there is an error on the server (for example incorrect options as per http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=883992&SiteID=1) then SB will immediately fire the notification message but with €œerror€? instead of €œupdate€? content. How do I detect this?

My code is based on the http://msdn2.microsoft.com/en-us/library/3ht3391b(vs.80).aspx example. (I had too much trouble with the SqlDependency class, including overflowing error logs. So unlike the post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=903713&SiteID=1 I am NOT receiving an SqlNotificationEventArgs object. I wish I were getting one!) I€™ve included my Listen() method below.

The debug portion of this code gives me message body that looks like the following when everything is good. What should it look like, if for example my SET OPTIONS (first link above) were wrong?

messageBody: <qn:QueryNotification xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification" id="129" type="change" source="data" info="update" database_id="23" sid="0xF4F50461FD43CA4191A1173B9350632A"><qn:Message>7fd415c7-2986-411d-abfd-615cbce0af28</qn:Message></qn:QueryNotification>

Thanks. Code follows:


private void Listen()
{
// infinite loop on this thread: keep listening even after
// receiving notification
while( true )
{
// connects to SqlServer queue and sleeps until a notification msg arrives
using( SqlConnection conn = new SqlConnection( _connect ) )
{
WriteMessage( "RequestNotification: Listen connection opened." );
using( SqlCommand cmd = conn.CreateCommand() )
{
int msgBodyId = InvalidSubscriptionId;
cmd.CommandText = waitFor;
cmd.CommandTimeout = 0; // _notificationTimeout + 100;
try
{
conn.Open();
// the thread is blocked until the waitfor event occurs.
SqlDataReader reader = cmd.ExecuteReader();
// the waitfor has triggered when we've reached here.
WriteMessage( "RequestNotification: Notification received." );
// there may be mulitple notifications waiting, but there seems only
// ever to be a single record to read, but we still use the while
// construct to ensure we get them all...
while( reader.Read() )
{
// the messageBody is actually XML with a GUID embedded as the msgId, eg:
// <qn:Message>82120744-f2bf-4c62-a17c-867cc6eb040e</qn:Message>
String messageBody = FixupMessageText( System.Text.ASCIIEncoding.ASCII.GetString( (byte[]) reader.GetValue( 13 ) ).ToString() );
msgBodyId = MessageBodyId( messageBody );
// doesn't work, sqlbinary...
// String messageBody = reader.GetSqlXml( 13 ).ToString();
#if __dbg
// used for debugging
// messageText = System.Text.ASCIIEncoding.ASCII.GetString( (byte[]) reader.GetValue( 13 ) ).ToString();
Byte status = reader.GetByte( 0 );
Byte priority = reader.GetByte( 1 );
Int64 queuingOrder = reader.GetInt64( 2 );
Guid conversationGroupId = reader.GetGuid( 3 );
Guid conversationHandle = reader.GetGuid( 4 );
Int64 messageSeqNo = reader.GetInt64( 5 );
String serviceName = reader.GetString( 6 );
Int32 serviceId = reader.GetInt32( 7 );
String serviceContractName = reader.GetString( 8 );
Int32 serviceContractId = reader.GetInt32( 9 );
String messageTypeName = reader.GetString( 10 );
Int32 messageTypeId = reader.GetInt32( 11 );
String validation = reader.GetString( 12 );
StringBuilder sb = new StringBuilder( "MESSAGE", 1024 );
sb.AppendLine();
sb.Append( " " );
sb.Append( BuildMessage( "status", status ) );
sb.Append( BuildMessage( "priority", priority ) );
sb.Append( BuildMessage( "queuingOrder", queuingOrder ) );
sb.Append( BuildMessage( "conversationGroupId", status ) );
sb.Append( BuildMessage( "conversationHandle", conversationHandle ) );
sb.Append( BuildMessage( "messageSeqNo", messageSeqNo ) );
sb.Append( BuildMessage( "serviceName", serviceName ) );
sb.Append( BuildMessage( "serviceId", serviceId ) );
sb.Append( BuildMessage( "serviceContractName", serviceContractName ) );
sb.Append( BuildMessage( "serviceContractId", serviceContractId ) );
sb.Append( BuildMessage( "messageTypeName", messageTypeName ) );
sb.Append( BuildMessage( "messageTypeId", messageTypeId ) );
sb.Append( BuildMessage( "validation", validation ) );
sb.AppendLine();
sb.Append( " " );
sb.Append( BuildMessage( "messageBody", messageBody ) );
WriteMessage( sb.ToString() );
#endif
}
reader.Close();
// and now we need to hand things back to the user's passed delegate.
// orphan notifications can be left behind from previous runs,
// determine if this notification is one we want to pass on
if( _subscriptionId == msgBodyId )
{
WriteMessage( "Message " + msgBodyId.ToString() + " calling delegate." );
// see http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx
SqlNotificationEventArgs e = new SqlNotificationEventArgs( SqlNotificationType.Change, SqlNotificationInfo.AlreadyChanged, SqlNotificationSource.Database );
_delegate( this, e );
}
else
{
WriteMessage( "Message " + msgBodyId.ToString() + " ignored." );
}
}
catch( SqlException )
{
// if the queue name doesn't match one in the db an exception will be thrown
throw;
}
finally
{
conn.Close();
}
}
}
WriteMessage( "RequestNotification: Listen connection closed." );
}
}