Trapping error from sp in another db

Question

Trapping error from sp in another db

rray 44280

Ten Centuries

Points: 1112
More actions
September 11, 2013 at 12:58 pm

#297726

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.

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/

rray 44280 Ten Centuries Points: 1112 More actions · Answer 2

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.

rray 44280 Ten Centuries Points: 1112 More actions · Answer 3

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?

mstjean Hall of Fame Points: 3310 More actions · Answer 4

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

rray 44280 Ten Centuries Points: 1112 More actions · Answer 5

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!