trap error and EXECUTE

trap error and EXECUTE

Post by david russ » Thu, 18 Sep 1997 04:00:00



hello,

how to trap and error when i use the command EXECUTE in a stored
procedure. here an example :

DECLARE cursor_res_update CURSOR FOR SELECT id, champ, valeur FROM query

OPEN cursor_res_update


    BEGIN
    DEALLOCATE cursor_res_update
    RETURN
END



    BEGIN

/*===========================================================================================================*/

    /* ENG : if an error (triggers) occured on the update, the stored
procedure does not continue, that prevent  */
    /* to close the cursor or other action on the
error.                                                         */
    /* FRA : si une erreur (dclencheurs) a lieu sur l'update, la
procedure stockee ne continue plus             */
    /* ce qui empeche de fermer correctement le curseur ou de reagir
pour annuler les modification               */
    /*
precedente
*/

/*===========================================================================================================*/



    END

CLOSE cursor_res_update
DEALLOCATE cursor_res_update



error_fvp_res_update:
    /* it never comes if and error occured with EXECUTE */
    CLOSE cursor_res_update
    DEALLOCATE cursor_res_update

 
 
 

trap error and EXECUTE

Post by Ron Talma » Thu, 18 Sep 1997 04:00:00


David,

If the update trigger contains a rollback, it will terminate the
calling batch. Therefore your sproc terminates if the update trigger
is rolling back.

Ron



>hello,

>how to trap and error when i use the command EXECUTE in a stored
>procedure. here an example :

>DECLARE cursor_res_update CURSOR FOR SELECT id, champ, valeur FROM query

>OPEN cursor_res_update


>    BEGIN
>    DEALLOCATE cursor_res_update
>    RETURN
>END



>    BEGIN


>/*===========================================================================================================*/

>    /* ENG : if an error (triggers) occured on the update, the stored
>procedure does not continue, that prevent  */
>    /* to close the cursor or other action on the
>error.                                                         */
>    /* FRA : si une erreur (dclencheurs) a lieu sur l'update, la
>procedure stockee ne continue plus             */
>    /* ce qui empeche de fermer correctement le curseur ou de reagir
>pour annuler les modification               */
>    /*
>precedente
>*/

>/*===========================================================================================================*/



>    END

>CLOSE cursor_res_update
>DEALLOCATE cursor_res_update



>error_fvp_res_update:
>    /* it never comes if and error occured with EXECUTE */
>    CLOSE cursor_res_update
>    DEALLOCATE cursor_res_update



 
 
 

trap error and EXECUTE

Post by david russ » Fri, 19 Sep 1997 04:00:00


hi ron,

the stored procedure must cancel the update and close the cursor in this
two cases :

a foreign key doesn't exist
---------------------------
Msg 30003, Level 16, State 1
Code de "PROJECT" inconnu. Modification de "RESOURCES" interdite.
(french message)
-> yes, the trigger rollback my transaction and canceled the updates
:)))
-> but the cursor is not closed :(((

the update is incorrectly formatted (update a varchar with a int)
-----------------------------------------------------------------
Msg 260, Level 16, State 1
Disallowed implicit conversion from datatype 'int' to datatype 'varchar'

  Table: 'msftest.dbo.resources', Column: 'projectkey'
  Use the CONVERT function to run this query.

Msg 266, Level 16, State 1
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN
is missing. Previous count = 0, Current count = 1.

-> no, the trigger is not fired, and the rollback is not invoked :(((
-> and the cursor is not closed :(((

thanks
david russo
ti
technop?le
ch - 3960 sierre

 
 
 

trap error and EXECUTE

Post by david russ » Fri, 19 Sep 1997 04:00:00


hi ron,

the stored procedure must cancel the update and close the cursor in this
two cases :

a foreign key doesn't exist
---------------------------
Msg 30003, Level 16, State 1
Code de "PROJECT" inconnu. Modification de "RESOURCES" interdite.
(french message)
-> yes, the trigger rollback my transaction and canceled the updates
:)))
-> but the cursor is not closed :(((

the update is incorrectly formatted (update a varchar with a int)
-----------------------------------------------------------------
Msg 260, Level 16, State 1
Disallowed implicit conversion from datatype 'int' to datatype 'varchar'

  Table: 'msftest.dbo.resources', Column: 'projectkey'
  Use the CONVERT function to run this query.

Msg 266, Level 16, State 1
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN
is missing. Previous count = 0, Current count = 1.

-> no, the trigger is not fired, and the rollback is not invoked :(((
-> and the cursor is not closed :(((

thanks
david russo
ti
technop?le
ch - 3960 sierre

 
 
 

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. Scheduled Backups question

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

4. Database Log

5. Trapping for NO EXECUTE permissions on SP

6. NYC - Strong Powerbuilder w/Oracle or Sybase - PB, GUI Design (F/T and Contractors)

7. Error trapping within my ON ERROR routine

8. package variable

9. DAO Error Trapping & Most Common Errors

10. Deadlock errors & vb error trapping

11. error-scenarios that can not be trapped with @@error

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

13. Error not being trapped with @@error