To give some background detail, let's say there's a 'Person' table and a stored proc 'PersonPerformance' that returns the performance of a Person given a PersonId. Both of these objects reside on a linked sql server 2000 instance.
My goal is to run the PersonPerformance proc for each PersonId and store the results in a table that resides on a Sql Server 2008 instance. If an execution error occurs during the execution of the stored proc, I'd like to note that it 'failed' and send out an email listing all the failed PersonId's when the storage process is complete.
I've tried using a try..catch block and checking for @@error != 0 but neither have worked. I assuming it's because the stored proc continues executing after throwing the error and eventually returns results even though they aren't valid. The stored procedure itself is one long batch and has no error handling.
Here's the code I have so far:
{@ReturnTable is defined here}
declare cur cursor for
select PersonId from [linked-server-name].DB.dbo.Person
open cur
declare @pId int
fetch next from cur into @pId
while (@@FETCH_STATUS = 0)
begin
begin try
print(@pId)
insert into @ReturnTable exec [linked-server-name].DB.dbo.PersonPerformance @pId
end try
begin catch
print('store person that failed here')
end catch
fetch next from cur into @pId
end
close cur
deallocate cur
I think I understand why it's not working as I expected for the reason I mentioned earlier (stored proc continues) but I'm wondering if there's way to circumvent that issue and catch the execution error even if the proc continues.
Here's a snapshot of the output generated during execution:
101
The statement has been terminated.
Msg 2627, Level 14, State 1, Procedure OSPerfAttribution_ps, Line 626
Violation of PRIMARY KEY constraint 'PK__@SecMaster__2CD2DAF1'. Cannot insert duplicate key in object '#2BDEB6B8'.
The statement has been terminated.
Msg 515, Level 16, State 2, Procedure OSPerfAttribution_ps, Line 1047
Cannot insert the value NULL into column 'BasketBenchmarkId', table '@ReturnTable'; column does not allow nulls. INSERT fails.
(3 row(s) affected)
102
(36 row(s) affected)
106
(32 row(s) affected)
The fact that the error message is propagating from the stored proc to the output of the outer sql makes me believe there must be a way to catch it. I'm just having trouble finding out how so I'm hoping to get some help here.
Last resort I'll probably just have to save the whole output to a log file and search for errors. This isn't that bad of an option but I'd still like to know if there's a way to catch the errors during execution.
I would use SET XACT_ABORT ON if you want the whole batch rolled back.