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.
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