Post reply

error: no update between databases when error occur

  • November 24, 2005 at 3:42 am

    #95946

    Problem.....

    I have found out the following issue:

    I run a procedure (A) in Database (A) that call another proc (B) in database (B).

    Proc B have several sql statement with handling error.

    I generate an error modifying an sql code in proc (B) statement just to observe if everything works.

    the handling error catch the error, call another proc (error), which is in database (B), which should update a table (log) in the database (A). This table is a log table on which I retrieve starts, ends of procs, success and fails.

    database (A) from database (B), looks like no communication between DBs.

    Do you have any suggestion, work-around about to retrieve errors when this issue happen.

    thank

  • November 24, 2005 at 6:58 am

    #605685

    For a full explanation on error handling, see Erland Sommarskog's articles "Error Handling in SQL Server – a Background" at http://www.sommarskog.se/error-handling-I.html

    and "Implementing Error Handling with Stored Procedures"

    at http://www.sommarskog.se/error-handling-II.html

    Here is the basic conclusion that I have reached:

    Error handling must be performed by the client as error detection can be performed in stored procedure BUT error handling cannot.

    The easiest coding solution is to set transaction abort on so that when an error occurs, control is immediately returned to the client. This leaves 2 case where error detection is needed: After the exection of a stored procedure from within a stored procedure and after the execution of dynamic SQL.

    To be consistent with the other error actions, I just return control to the client.

    Create procedure FOO

    as

    set nocount on

    set XACT_ABORT ON -- Return to client when an error occurs.

    declare @SPReturnCode integer

    exec @SPReturnCode = SomeOtherProcedure

    IF COALESCE(@SPReturnCode , @@ERROR) 0 RETURN +1

    exec ('SELECT * from ThisTableDoesNotExists')

    IF @@ERROR 0 RETURN +1

    RETURN 0

    go

    SQL = Scarcely Qualifies as a Language

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply