11

I am using 

sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(sqlConnection_InfoMessage);

and ExecuteNonQuery() to execute a stored procedure.

I am using RAISERROR("Custom Message", 10 , 1) WITH NOWAIT in stored procedure to throw message.

I am getting all message thrown by sql server at end of query execution which i want at the time of process.

I could get all the message at real time by setting sqlConnection.FireInfoMessageEventOnUserErrors = true; but this cause the error with severity greater than 10 also be handled by SqlInfoMessageEventHandler which i want to be thrown as exception .

Is there any way to get my custom sql infomessage with severity 10 at real time in eventHandler but other as exception?

BipinBaglung
  • 494
  • 4
  • 20

1 Answers1

21

It seems like the issue is with ExecuteNonQuery().

In my case I used ExecuteScalar() and removed the line

sqlConnection.FireInfoMessageEventOnUserErrors = true;

Which cause the RAISERROR("Custom Message", 10 , 1) WITH NOWAIT to be caught by eventHandler and other as exceptions at real time.

BipinBaglung
  • 494
  • 4
  • 20
  • ExecuteScalar instead of ExecuteNonQuery did the trick for me, thank you ! – Agustin Garzon Mar 09 '17 at 13:18
  • +1 This also worked for me. However, one word of caution: From what I can tell, if your query returns any result sets, then `ExecuteScalar()` ignores everything after the first result set, INCLUDING any further messages. In this case, I had to go back to using `ExecuteNonQuery()` with `FireInfoMessageEventOnUserErrors = true`. – Scott M Apr 17 '18 at 19:03