October 16, 2002 at 1:28 pm
Does anyone know of a way to trap errors and continue executing SQL instead of returning out of a proc within a distributed transaction? I am looping through a cursor and performing inserts (to a linked server within a distributed transaction) within the cursor. If one of the inserts fails (due to linked server down or other errors), can I continue to resume to the next record within the cursor. Please help!! Thanks.
Edited by - ddw2s on 10/16/2002 1:36:03 PM
October 16, 2002 at 2:45 pm
Have you tried to use @@ERROR
Something like....
insert into ......
if @@ERROR <> 0
print 'error in insert, continue'
Haven't tested this, but seems like it might work.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 16, 2002 at 2:59 pm
This would work fine if it were local and not accross linked servers. I am not able to capture any errors when I cross servers. The batch just ends reporting the error back.
I have a master server with numerous linked servers set up. I looping through a cursor with a distributed transaction nested inside for each linked server (record of my cursor). I want to be able to trap any errors and skip to the next record of my cursor if an error occurs. If SQL experiences an error, it jumps out of the stored procedure and rolls back appropriately. How do I trap the error and skip to next record to continue processing?
A part of my SQL looks likes like such:
set XACT_ABORT on
--BEGIN LOOP
...
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO SQL1.DB1.DBO.TBL1 (a, b)
Values (c,d)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
--END LOOP
...
I am unable to trap the error that occurs on the linked server. Does anyone know how I can trap this error and continue on?
Edited by - ddw2s on 10/16/2002 3:01:41 PM
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply