Is there any way to catch/handle an error from a remote procedure call?

Question

Is there any way to catch/handle an error from a remote procedure call?

TangoVictor

SSChampion

Points: 10969
More actions
June 3, 2019 at 5:09 pm

#3649848
For example if i run this:
```
BEGIN TRY
EXEC msdb.dbo.sp_help_job @job_name = 'foobar',@job_aspect = 'JOB'
END TRY
BEGIN CATCH
PRINT 'something went wrong'
END CATCH
```
Result:
something went wrong

But if i run this:
```
BEGIN TRY
EXEC [mylinkedserver].msdb.dbo.sp_help_job @job_name = 'foobar',@job_aspect = 'JOB'
END TRY
BEGIN CATCH
PRINT 'something went wrong'
END CATCH
```
The statement fails and never hits the catch block. Since the procedures i'm calling are msdb system procedures i can't modify them.
Any thoughts on how to trap this error?

Viewing 4 posts - 1 through 3 (of 3 total)

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

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

This has to do with how SQL Server processes errors on remote procedure calls. The error message bubbles up but not the actual exception, you could do something like this,

BEGIN TRY

DECLARE @output int 
EXEC @output = [mylinkedserver].msdb.dbo.sp_help_job @job_name = 'foobar',@job_aspect = 'JOB'
IF @output != 0
BEGIN
THROW @output, 'REMOTE CALLS FAILED', 1
END

END TRY
BEGIN CATCH
PRINT 'something went wrong'
END CATCH

rVadim Hall of Fame Points: 3969 More actions · Answer 2

In the last paragraph of the article below, it says:

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.

I bet there are few RAISERROR statements in that sp_help_job procedure.

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191515(v=sql.105)

--Vadim R.

TangoVictor SSChampion Points: 10969 More actions · Answer 3

ZZartin wrote:

This has to do with how SQL Server processes errors on remote procedure calls. The error message bubbles up but not the actual exception, you could do something like this,
BEGIN TRY

DECLARE @output int 
EXEC @output = [mylinkedserver].msdb.dbo.sp_help_job @job_name = 'foobar',@job_aspect = 'JOB'
IF @output != 0
BEGIN
THROW @output, 'REMOTE CALLS FAILED', 1
END

END TRY
BEGIN CATCH
PRINT 'something went wrong'
END CATCH

Thanks this did hit the catch block but also still shows the error. Also i'm implementing it like this:

    INSERT  INTO @jobInfo
            EXECUTE [mylinkedserver].msdb.dbo.sp_help_job @job_name = 'foobar',@job_aspect = 'JOB'

But i just realized i can simply query the sysjobs view and just check if it exists first. Sorry i don't know why i didn't think of that first.