Hanging process

Gert · April 4th, 2005, 04:08 AM

Hi all.

I have a SP that has worked for a long time, but now it has stopped working. In the SP I simply move data from one table on one server, to another table on another (physical) server. The trouble started when we moved the target database to a new physical server.

Now when the SP is started, it seems to be running OK, but it does no work at all. If I kill the process, it says it is rolling back, but it never completes. (KILL WITH STATUSONLY reports that it is 100% rolled back, but the process is hanging on...)

I use some manual transaction handling in the SP, and if I remove this it works fine. It puzzles me though why it worked on the old server. See code below. Any comments welcome.

Code in SP:

Code:

SET XACT_ABORT ON

IF @@TRANCOUNT<>0 ROLLBACK --Check for orphaned trans and rollback

DECLARE @errorcode int
set @errorcode=0

BEGIN TRAN 
--OverfÃ¸r verdier
insert into Lnk_TargetServer.[Axapta].[dbo].[TargetTable]([ANLEGGSID], [VOLUM], [DATOFRA], [DATOTIL], [NYKID], [UKE], [CMX_DATOIMPORTERT], [AAR], [DATAAREAID], [IMPORTERT]) 
SELECT [Anleggsid],[Volum], [FraDato], [TilDato], [Axaptakid], [Uke],  [CMX_DatoImportert], [Aar],[DataAereaId], [Importert] FROM [SourceTable] where status=0

SET @errorcode=@@ERROR
IF @errorcode<>0 BEGIN
    RAISERROR('Error. Rolling back..',16,10)
    IF @@TRANCOUNT<>0 ROLLBACK

    SET XACT_ABORT OFF
    RETURN(@errorcode)
END

--Oppdater status
Update SourceTable set status=1 where status=0 
SET @errorcode=@@ERROR
IF @errorcode<>0 BEGIN
    RAISERROR('Error. Rolling back..',16,10)
    IF @@TRANCOUNT<>0 ROLLBACK

    SET XACT_ABORT OFF
    RETURN(@errorcode)
END
COMMIT TRAN

SET XACT_ABORT OFF