none
how to get error information back from sql server stored procedure using sql adapter

    Question

  •  

    Hi,

    In my application the entire logic is in stored procedure covering 10 to 15 tables. The problem iam facing is when ever an small error occurs in stored procedure(say line 5 of 150) its going to catch block, but iam unable to get that information back from sql adapter.

    when ever an error occurs the biztalk suspends the messge and when i look in to event viewer, its not showing the correct error information.

    example: when inserting a value into an identity column it goes to catch block and generates the messge "cannot insert value into identidy column", i need to back get this messge exactly from sql adapter as output value

    Plz help me.

    Friday, December 05, 2008 6:10 PM

Answers

  • Use the following code sample in your stored procedure:

     

    IF @@TRANCOUNT > 0
    BEGIN
     ROLLBACK TRANSACTION UpdateTrans
    END

     

    DECLARE @ErrorText VARCHAR(500)
    SET @ErrorText = ERROR_MESSAGE()
    RAISERROR (@ErrorText, 16, 1);

     

    Friday, December 05, 2008 6:46 PM

All replies

  • Are you using Try/Catch blocks in your SP code (this works on SQL 2005 & 2008 but not SQL 2000)?  If not, I suggest you rewrite your SP using Try/Catch blocks and utilize the RAISERROR function to throw custom alerts from the SP.  That should override the default alerts it looks like you're seeing from the SQL adaptor.
    Friday, December 05, 2008 6:38 PM
  • Use the following code sample in your stored procedure:

     

    IF @@TRANCOUNT > 0
    BEGIN
     ROLLBACK TRANSACTION UpdateTrans
    END

     

    DECLARE @ErrorText VARCHAR(500)
    SET @ErrorText = ERROR_MESSAGE()
    RAISERROR (@ErrorText, 16, 1);

     

    Friday, December 05, 2008 6:46 PM
  • Thanx for the reply, it was helpful for me, but if i want to write the error message returned from RAISERROR function to event log how i need to write this.

     

    Saturday, December 06, 2008 6:11 AM