September 15, 2006 at 12:43 pm
I'm trying to create a multi-part insert stored proc inside a tran so I can rollback if either insert fails. The 'meat' of the procedures, any of the select statements, inserts, and deletes each run individually in mere seconds however when I encapsulate them inside the transaction the procedure seems to hang....I've had the patience to let it run for 10 minutes before I kill it.
The text of the proc is as follows:
ALTER Proc dbo.procMergeLaneMessages as
set nocount on
Declare @er int, @recno int, @ertwo int
BEGIN TRAN
select @recno = max(msgrecordid) from messagessent
Insert into svr001.storedlaneinfo.dbo.messagessent (MsgRecordID,Collector,[Time],[Date],Plaza,Lane,
MsgID,Message,EntryBarcode,DateAdded)
select MsgRecordID,Collector,[Time],[Date],Plaza,Lane,MsgID,replace(Message,'''',''''''),EntryBarcode,
DateAdded from messagessent where msgrecordid <=@recno
select @er = @@error
IF (@er <> 0) GOTO PROBLEM
Insert into svr002.storedlaneinfo.dbo.messagessent (MsgRecordID,Collector,[Time],[Date],Plaza,Lane,
MsgID,Message,EntryBarcode,DateAdded)
select MsgRecordID,Collector,[Time],[Date],Plaza,Lane,MsgID,replace(Message,'''',''''''),EntryBarcode,
DateAdded from messagessent where msgrecordid <=@recno
select @ertwo = @@error
print @ertwo
IF (@ertwo <> 0) GOTO PROBLEM
COMMIT TRAN
PROBLEM:
IF @er <> 0 or @ertwo <> 0
ROLLBACK TRAN
Both destination locations are linked servers.
Am I overlooking/missing something in the structure of my TRAN?
September 15, 2006 at 1:59 pm
try with explicitly opening distributed transaction like this
BEGIN DISTRIBUTED TRANSACTION
-- see BOL for more details
September 15, 2006 at 2:09 pm
Thanks for the reply but no luck. It's my understanding that Distributed Trans is assumed when linked servers are involved. But I tried it just the same and the proc still hangs.
September 15, 2006 at 4:04 pm
Try this:
select @recno = max(msgrecordid) from messagessent
BEGIN TRAN
Insert into svr001.storedlaneinfo.dbo.messagessent (MsgRecordID,Collector,[Time],[Date],Plaza,Lane,
MsgID,Message,EntryBarcode,DateAdded)
select MsgRecordID,Collector,[Time],[Date],Plaza,Lane,MsgID,replace(Message,'''',''''''),EntryBarcode,
DateAdded from messagessent where msgrecordid <=@recno
select @er = @@error
IF (@er = 0)
Begin
Insert into svr002.storedlaneinfo.dbo.messagessent (MsgRecordID,Collector,[Time],[Date],Plaza,Lane,
MsgID,Message,EntryBarcode,DateAdded)
select MsgRecordID,Collector,[Time],[Date],Plaza,Lane,MsgID,replace(Message,'''',''''''),EntryBarcode,
DateAdded from messagessent where msgrecordid <=@recno
select @ertwo = @@error
print @ertwo
IF (@ertwo = 0)
Begin
COMMIT TRAN
End
Else
Begin
ROLLBACK TRAN
End
End
Else
Begin
ROLLBACK TRAN
End
Thanks
Sreejith
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply