Trapping error from sp in another db

  • SQL Server 2008 R2

    Two computers: MachineOne and MachineTwo

    Two instances of SQL server,: MachineOne.ServerOne and MachineTwo.ServerTwo

    Two databases:MachineOne.ServerOne.DBOne, and Machine2.Server2.DBTwo.

    On [MachineTwo].[ServerTwo].[DBTwo] I write an sp that calls an sp from [MachineOne].[ServerOne].[DBOne]

    EXEC [MachineOne].[ServerOne].[DBOne].[dbo].[MySP]

    On ServerOne [MySP] does a bunch of validation and uses RAISEERROR to break when something fails. The code there that raises the flag looks like

    SET @i_ReturnMessage = dbo.udf_SystemMessage(100000,1)

    RAISERROR (@i_ReturnMessage, 16, 1)

    RETURN @@ERROR

    When everything is right as expected it works fine. I want to trap an error in MySP, though, so on ServerTwo I call

    BEGIN TRY

    EXEC @ErrorReturn = [MachineOne].[ServerOne].[DBOne].[dbo].[MySP]

    END TRY

    BEGIN CATCH

    @ErrorMessage = ERROR_MESSAGE(),

    @ErrorSeverity = ERROR_SEVERITY(),

    @ErrorState = ERROR_STATE(),

    @ErrorLocation = 'DoExistingEmployeeNewPasses',

    @ErrorDescription = @ErrorReturn+' - Adding Address Profile for '+@WFirstName+' '+@wLastName;

    EXEC [dbo].[LogError] @ErrorMessage, @ErrorSeverity, @ErrorLocation, @ErrorDescription ;

    END CATCH

    Where [LogError] is an sp on ServerTwo that should write the error to a log file.

    When I run it from SSMS connected to ServerTwo I get an error message as expected, but my TRY clause never seems to get triggered.

    How can I collect the error information on ServerTwo for logging? [MySP] on ServerOne is vendor code, so I can't do anything there to change how the flag is raised.

  • When I run it from SSMS connected to ServerTwo I get an error message as expected, but my TRY clause never seems to get triggered.

    What do you mean the TRY never gets triggered? In the code you posted you have only 1 line in the TRY. Unless you don't execute the code it is firing.

    The rough skeleton of procs you posted appears that it should be working (and trapping exceptions) correctly.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sorry, I meant the CATCH never gets triggered.

    @@ERROR does come back as 50000, but none of the other error info is available. I can trap for @@ERROR right after the sp is called and log the error from there, but it doesn't give me the complete error information as is raised by the sp on the other server and is displayed in SSMS after the call completes.

    That is, when I force an error on validating a session on serverOne SSMS gives me:

    Msg 50000, Level 16, State 1, Procedure proc_publicAddAddressProfile, Line 145

    100000: The User Session is not valid.

    But I can't seem to collect that info in the query.

  • Update after some testing:

    It works fine when I call the sp from a different db on the same server. The problem is when the db where the sp lives is on a different server. The sp gets called properly, but the details supplied by RAISEERROR never get back to me, although a value for @@ERROR does.

    Is this a limitation of SQL Server 2008? Maybe there are additional configuration options I need to set to get it to work?

  • Could it be that it fails because the code in your CATCH is missing a "SELECT" at the top? (or just a transcribing error?)

    Error is 50000 because it's a user defined error.

    They may have put it in the sys.messages table, but that won't exist on the other server. I don't know if the error info can get passed to a linked server... If you can put a small db on the app server you could capture the error info here-- and copy it over to server2 periodically if it needs to be there.

    --show user defined errors (for english language)

    select * from sys.messages where language_id = 1033 and message_id > 50000


    Cursors are useful if you don't know SQL

  • The good news is that I've got an answer. The bad news is that the answer is that it doesn't work. As I suspected, and as per Microsoft, the error on the remote server cannot be caught in a CATCH block on the calling server, it just doesn't work that way, and there's no workaround.

    Bummer, but at least I can stop trying to figure it out!

Viewing 6 posts - 1 through 5 (of 5 total)

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