TRY CATCH with Linked Server in SQL Server 2005 Not Working

Hello, I am trying to catch sql error raised when I execute a stored procedure on a linked server. Both Servers are running SQL Server 2005.

To prove the issue I have created a stored procedure on the linked server called Raise error that executes the following code:

    RAISERROR('An error', 16, 1);

If I execute the stored procedure directly on the linked server using the following code I get a result set with 'An error', '16' as expected (ie the code enters the catch block):

    BEGIN TRY
        EXEC [dbo].[RaiseError];
    END TRY
    BEGIN CATCH
          DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int;
          SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY();
          SELECT @ErrMsg, @ErrSeverity;
    END CATCH

If I run the following code on my local server to execute the stored procedure on the linked server then SSMS gives me the message 'Query completed with errors', .Msg 50000, Level 16, State 1, Procedure RaiseError, Line 13
An error'

    BEGIN TRY
          EXEC [Server].[Catalog].[dbo].RaiseError
    END TRY
    BEGIN CATCH
          DECLARE @SPErrMsg nvarchar(4000), @SPErrSeverity int;
          SELECT @SPErrMsg = ERROR_MESSAGE(), @SPErrSeverity = ERROR_SEVERITY();
          SELECT @SPErrMsg, @SPErrSeverity;
    END CATCH

My Question is can I catch the error generated when the Linked server stored procedure executes?

Thanks in advance!
LVL 1
peddleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
In short, no, most of the time.  Read below.

Below is an excerpt from MSDN BOL:

RAISERROR and TRY…CATCH

Calling RAISERROR with severity less than 20 from inside a remote stored procedure causes a statement abort error on the remote server. A TRY…CATCH construct on the local server handles remote batch abort errors only. If a remote stored procedure calls RAISERROR with severity less than 20 and the remote stored procedure is scoped within a TRY block on the local server, RAISERROR does not cause control to pass to the CATCH block of the TRY…CATCH construct. However, RAISERROR with severity 20 or greater on the remote server breaks the connection, and execution on the local server passes to the CATCH block.


http://msdn.microsoft.com/en-us/library/ms191515.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peddleAuthor Commented:
Thanks for the quick and accurate reply.

The only problem is you need to be in the sysadmin role or users with ALTER TRACE permission, and use WITH LOG to raise an error with a severity of 20 through 25 (MSDN http://msdn.microsoft.com/en-us/library/ms178592.aspx states 19 through 25 although a quick test proved otherwise? ).

Guess I'm going to have to see what the linked servers DBA is going to let me get away with!
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Or just alter the procedure [Server].[Catalog].[dbo].RaiseError to return 1 as output in case of any failures and then validate the output here..
0
peddleAuthor Commented:
@rrjegan17: True, I am already doing that in the real world implementation... I just wanted to be able to log the exact error.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.