0

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.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794

2 Answers2

0

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.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
0

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.

Jose Basilio
  • 50,714
  • 13
  • 121
  • 117
  • the error would still bubble up, it goes to the client connection directly, no matter the nesting level. Only 2k5 try/catch can change that. – Remus Rusanu Aug 07 '09 at 22:07