1

I am trying to capture failures for a remote (via linked server) stored procedure called within a local stored procedure. If the remote procedure fails (error connecting to server, etc) I want the return value or an indicator for this so I can capture this and log it. If it doesn't fail, I want the results which would be used within the local procedure and processed as a temp table.

DECLARE @myTable TABLE(id INT,
        TransDAte date,
        Description nvarchar(25))


INSERT INTO @myTable

select * from openquery(MyLinkedServer, 'EXEC   [MyDatabase].[dbo].[RemoteStoredProc]') 

The below link is on the right track but I have two differences. One, my table is multiple fields that I need to return (In the example, @RC is a list of INTs) and I am just selecting the data to use in the local stored procedure, I do not need to commit or rollback transactions.

Handling exceptions in stored procedures called using insert-exec blocks

I have tried adding catch/try statements to both the local and remote procedures but they don't capture the failures correctly.

This doesn't seem to work:

Create PROCEDURE thisTEST
AS

DECLARE @myTable TABLE(tableid INT,
        TransDAte date,
        Description nvarchar(25))

BEGIN TRY
    INSERT INTO @myTable
     select * from openquery(MyLinkedServer, 'EXEC  [MyDatabase].[dbo].[RemoteStoredProc]')
END TRY

BEGIN CATCH
    print 'Create entry in error log table'
END CATCH

-- This doesn't work because the remote procedure failure is not captured. When the procedure fails, the sproc attempts to insert the error message into the tableid field)

Create procedure RemoteStoredProc
AS

BEGIN TRY


Select transactionid, transactiondate, myDescription from SourceTable


END TRY


BEGIN CATCH
print 'Error with getting data'

END CATCH

I tried adding an output parameter but I don't know how to execute an Insert-Exec procedure with output and/or results

Create procedure RemoteStoredProcWParam(@sprocFailed INT OUT)

AS


BEGIN TRY


Select transactionid, transactiondate, myDescription from SourceTable

SET @sprocFailed = 0

END TRY


BEGIN CATCH
Set @sprocFailed = 1
print 'Error with getting data'

END CATCH

It looks like the best option is an output parameter with a cursor to store table results but I don't think that will work either. I am used to using SSIS but don't want to have to rewrite two entire (complicated) stored procedures. Any help would be appreciated. Thanks in advance

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.