Error handling of stored procedure having known errors being called from vb.net

Question

I have sql server2000 encypted stored procedure. I can not modify them. Typicaly all the procedures manipulate row by row different tables using cursors etc.

When the stored procedure is executed at the Query Analyser screen, I see error being thrown in between but the procedures continues till all the records have been processed. This behavior is acceptable to the client.

I now need to automate the process using VB.net 2002 windows application. I call the procedure from vb.net but the program throws runtime error on the 1st occurance of a error in the stored procedure.

Can any one guide me how to handle and progran error handling so as to continue with the processing till all the records are processed. I will highly appreciate your help.

Answer 1

I'm not sure how to do it in .Net 1.0.

In 2.0 and later I would set the FireInfoMessageEventOnUserErrors property of the connection to True and handle the `InfoMessage event. But a quick check of MSDN shows them as not available until .Net 2.0 and 1.1 respectively.

Answer 2

Since the Stored Procedure is encrypted and you can not modify it. One option would be to create another stored procedure to act as a wrapper for the encrypted one.

Example... pseudo-TSQL

CREATE PROCEDURE MyWrapperProc
(
   @MyParameters somedatatypes
)
EXEC MyEncrytedStoreProc @MyParameters

Then change your VB.NET call use the Wrapper proc. There's no TRY-CATCH in SQL 2000. I think this should allow the Encrypted proc to run without the error bubbling up to your VB.NET code unless you explicitly raise the error.