Hi, and thx in advance for any help.
Here is my problem:
I have a SP that is supposed to move data from one table in one DB to a table in another DB (see code below). Previously this has worked just fine. The databases was placed on two different physical servers. Then we needed to move the destination database to another physical server, and after that the SP will not do what it is supposed to (it says Running, but nothing happens). If I kill the process, it changes to KILLED/ROLLBACK, but it stays like that until we restart the server. If I remove the transaction handling from the SP, it works fine (but I really need the transaction handling).
The code is like this:
Code:
CREATE procedure PROC_Exportsettlementtoaxapta
AS
BEGIN
SET XACT_ABORT ON
IF @@TRANCOUNT<>0 ROLLBACK --Check for orphaned trans and rollback
DECLARE @errorcode int
set @errorcode=0
BEGIN TRAN
--Transfer the values
insert into Lnk_INDIA.[Axapta].[dbo].[BE_FORBRUKMVDB]([ANLEGGSID], [VOLUM], [DATOFRA], [DATOTIL], [NYKID], [UKE], [CMX_DATOIMPORTERT], [AAR], [DATAAREAID], [IMPORTERT])
SELECT [Anleggsid],[Volum], [FraDato], [TilDato], [Axaptakid], [Uke], [CMX_DatoImportert], [Aar],[DataAereaId], [Importert] FROM [AxaptaAvregning] where status=0
SET @errorcode=@@ERROR --check for errors
IF @errorcode<>0 BEGIN
RAISERROR('Error. Rolling back..',16,10)
IF @@TRANCOUNT<>0 ROLLBACK
SET XACT_ABORT OFF
RETURN(@errorcode)
END
--If everything OK, update status
Update AxaptaAvregning 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
END
As mentioned before, if I remove the transaction handling, it works fine:
Code:
CREATE procedure PROC_Exportsettlementtoaxapta
AS
BEGIN
--Overfør verdier
insert into Lnk_INDIA.[Axapta].[dbo].[BE_FORBRUKMVDB]([ANLEGGSID], [VOLUM], [DATOFRA], [DATOTIL], [NYKID], [UKE], [CMX_DATOIMPORTERT], [AAR], [DATAAREAID], [IMPORTERT])
SELECT [Anleggsid],[Volum], [FraDato], [TilDato], [Axaptakid], [Uke], [CMX_DatoImportert], [Aar],[DataAereaId], [Importert] FROM [AxaptaAvregning] where status=0
--Oppdater status
Update AxaptaAvregning set status=1 where status=0
END
I have not found any different settings between the old physical server and the new one. Nor have I found any differences in the settings of the Linked servers.
Any suggestions welcome.
Best regards
Gert
Linked Servers and Transactions
