MS DTC has stopped this transaction

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

MS DTC has stopped this transaction

Author

Topic

irumman
Starting Member

1 Post

Posted - 2012-02-20 : 06:19:12

Hi all,

I am new with Sql Server and going to use Link Server where I have to connect with a Postgresql Database.
Everything is working fine.
All the following queries are working:


SELECT a.*
FROM openquery(PG,'select * from sports') as a

INSERT OPENQUERY(PG,'SELECT ID FROM TESTTAB')
VALUES(1)

UPDATE OPENQUERY (PG, 'SELECT ID FROM TESTTAB') 
SET id = 2;

But when I use this DML statement in a trigger for a table, I get error:

quote:

Msg 8522, Level 18, State 1, Line 1
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.

I enabled the "Enable XA Transaction" from googling.

Any help please.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 11:37:47

can you try this?
http://support.microsoft.com/kb/904774

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-02-21 : 01:44:51

1) After you have enabled XA transactions , and restarted the SQL Server, do you still get the problem?
2) Do you have anyting in your transaction list ?
3) Grab the guid and use sys.dm_tran_active_transactions to find more details about the transaction
4) Is MS DTC running?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

gnav
Starting Member

2 Posts

Posted - 2014-09-09 : 12:46:41

Hi everybody,

I get the same error :
Msg 8522, Niveau 18, État 1, Ligne 1
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.

when i use 'insert openquery' in trigger,
i use mssql 2008 and Postgres (9.3).
it urgent for me, Any help please.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-09-11 : 01:47:30

gnav
Starting Member

2 Posts

Posted - 2014-09-17 : 06:19:48

thanks M. Jackv,

Fisrt I apologize my English, and the delay.
1- yes i still have the same problem, same thing for Update;
2- transaction list: it's in sys.dm_tran_active_transactions?, wether i don't find a lot of information this's the sys.dm_tran_active_transactions table
140 worktable 2014-09-11 17:20:12.687 2 NULL 2 0 0 0 0 0 NULL
143 worktable 2014-09-11 17:20:12.717 2 NULL 2 0 0 0 0 0 NULL
145 worktable 2014-09-11 17:20:12.717 2 NULL 2 0 0 0 0 0 NULL
147 worktable 2014-09-11 17:20:12.717 2 NULL 2 0 0 0 0 0 NULL
150 worktable 2014-09-11 17:20:12.750 2 NULL 2 0 0 0 0 0 NULL
152 worktable 2014-09-11 17:20:12.750 2 NULL 2 0 0 0 0 0 NULL
16444735 UPDATE 2014-09-17 09:45:41.343 1 NULL 2 0 2 0 0 -1 NULL
16444737 LobStorageProviderSession 2014-09-17 09:45:41.357 2 NULL 2 0 0 0 0 0 NULL

3- see 2
4- MSDTC is running and I have already activated all checkboxes

this is what it look my trigger :

ALTER TRIGGER [dbo].[triggerFicheSynchroUpdate]
ON [DB_AWJ].[dbo].[FicheClient]
AFTER UPDATE
AS
BEGIN

declare @uqid int;
declare @TSQL varchar(8000);
declare @value varchar(255);

declare @inserted varchar(255);
declare @deleted varchar(255);

set @uqid=(select UQID FROM deleted);
set @value=(select num_1 from Inserted);

set @inserted=(select num_1 from inserted);
set @deleted=(select num_1 from deleted);

SET @TSQL = N'select * from OPENQUERY(PGSLAVE, ''SELECT * FROM "public"."ficheclient" WHERE "uqid" ='+CONVERT(varchar(10),@uqid)+ ' '' )';
EXEC(@TSQL);

select @inserted as inserted, @deleted as deleted,@value as value,@uqid as uqid;
if (@inserted!=@deleted) begin
select 'intern';
/*sELECT DTAT.*
FROM sys.dm_tran_active_transactions DTAT*/
SET @TSQL = N'UPDATE OPENQUERY(PGSLAVE, ''SELECT * FROM "public"."ficheclient" WHERE "uqid" ='+CONVERT(varchar(10),@uqid)+ ' '' ) SET num_1='+@value;
EXEC(@TSQL);
select 'fininterne';
end

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources