Trapping SQL error in VB before ADO raises run-time error

Trapping SQL error in VB before ADO raises run-time error

Post by Francis Lalon » Sat, 09 Oct 1999 04:00:00



Using: VB5sp3, MSSQL6.5, ADO2.1, NT4.0wks

I want to execute a stored procedure performing multiple transactions
before setting a return value telling me how many transactions went
without errors.

The problem is, as soon as I get an error (for example, a duplicate
key) from one of the transactions, ADO raises a run-time error and
stops waiting for the output of the stored proc. The stored proc
continues executing alone (it is not killed), but the VB program just
never receives any more information after the error is raised.

I can catch the error with "On Error" but still, there is no way to
reconnect to the sp. I tried catching the error by setting up an
ADODB.Connection Infomessage event handler and returning
adStatusUnnwantedEvent, but the handler isn't even called before the
run-time error is raised.

Maybe I could inhibit SQL server from sending me those messages (since
I have my own server-side error logging mechanism anyway) but how?
There is no "exception trapping" frame in Transact-SQL 6.5 is there?

I really am about to kick the damn thing, there HAS to be a way of
doing this.

Any ideas?

Francis Lalonde

 
 
 

Trapping SQL error in VB before ADO raises run-time error

Post by Russell Field » Sat, 09 Oct 1999 04:00:00


Francis,

        I really do not know, but I have seen multi-rowset problems resolved
by:

                Set NoCount On

        This reduces the communication to the front-end and may be of help.

Russell Fields, SQL Server MVP


>Using: VB5sp3, MSSQL6.5, ADO2.1, NT4.0wks

>I want to execute a stored procedure performing multiple transactions
>before setting a return value telling me how many transactions went
>without errors.

>The problem is, as soon as I get an error (for example, a duplicate
>key) from one of the transactions, ADO raises a run-time error and
>stops waiting for the output of the stored proc. The stored proc
>continues executing alone (it is not killed), but the VB program just
>never receives any more information after the error is raised.

>I can catch the error with "On Error" but still, there is no way to
>reconnect to the sp. I tried catching the error by setting up an
>ADODB.Connection Infomessage event handler and returning
>adStatusUnnwantedEvent, but the handler isn't even called before the
>run-time error is raised.

>Maybe I could inhibit SQL server from sending me those messages (since
>I have my own server-side error logging mechanism anyway) but how?
>There is no "exception trapping" frame in Transact-SQL 6.5 is there?

>I really am about to kick the damn thing, there HAS to be a way of
>doing this.

>Any ideas?

>Francis Lalonde



 
 
 

1. Error Trapping - Can you trap a DTS Package error using SQLDMO running a job

I'm running a DTS pacakge by scheduling it through a job
in SQL DMO (I am doing it through DMO vs. DTS Object in
order to run the package on the server from a client pc).
I am having trouble finding info on how to retreive the
error messages if any of the packages fail.  I know I can
make an error log and look at that text file but for the
application purposes it would be a lot better to pull it
in through DMO, any suggestions on how to find the error
message?  I've looked a little into the Alert object but
don't fully understand how that might or might not help
me.  Thanks in advance,
Mike

2. Q: index only table for single unique field?

3. Trapping SQL server errors before ADO raises run-time error?

4. turn logging off

5. Error Trapping: MS Access errors not trapped in VB code

6. Australian Contract - SYS ADMIN MANAGER

7. ado errors not trapped by vb error object???

8. ADO/VB cannot detect errors raised from stored procedure of SQL 7

9. ADO error trapping in VB + SQL Server 2000

10. Trap run-time errors in a trigger

11. Can someone show me how to trap an run-time error