inserts within a trigger to a linked MySQL server not working

• adesouza

  SSC Veteran

  Points: 269

  More actions

  September 2, 2011 at 4:31 am

  #243309

  Hi,

  I'm trying to test inserts to a linked MySQL server using triggers, but am having a few problems.

  System-

  Windows 2003 Server running

  - SQL Server 2008 R2 Developer Edition

  - MySQL 5.5.15.0 Server

  - MySQL ODBC 5.1 driver

  I followed the instructions in this post - http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx to link the MySQL server to SQL server

  I can SELECT and INSERT rows in the linked server using the query window in SSMS.

  The problem arises when I try to do an INSERT to the linked server in a trigger

  Trigger

  USE [Test]

  GO

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER trigger [dbo].[update_trig] on [dbo.test]

  for insert

  as

  SET XACT_ABORT ON

  INSERT INTO OPENQUERY(MYSQL, 'select data from roh.test') values ('testing trigger')

  When a row is inserted into the SQL server the following error occurs

  [font="Courier New"]Msg 7391, Level 16, State 2, Procedure update_trig, Line 7

  The operation could not be performed because the OLE DB provider "MSDASQL" for linked server "MYSQL" was unable to begin a distributed transaction.[/font]

  After doing a bit of googling someone suggested adding a COMMIT statement to the trigger after SET XACT_ABORT ON, so the last part of the trigger looks like

  SET XACT_ABORT ON

  COMMIT

  INSERT INTO OPENQUERY(MYSQL, 'select data from roh.test') values ('testing trigger')

  This does do the insert in the linked MySQL server but gives the error -

  [font="Courier New"]

  Msg 3609, Level 16, State 1, Line 1

  The transaction ended in the trigger. The batch has been aborted.

  [/font]

  Someone also suggested doing the insert in a stored procedure and calling this from the trigger, but this gives the same errors.

  Am I missing something, or is this not possible? Any suggestions would be appreciated.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  September 2, 2011 at 6:18 am

  #1376397

  Triggers are executed inside an auto-generated transaction.

  You need to configure the MSDTC to allow two-phase commit.

  I wrote an article on a similar topic some months ago and you should find the information you're looking for.

  The article can be found here: http://www.sqlservercentral.com/articles/Linked+Servers/73794/

  Hope this helps

  Gianluca

  -- Gianluca Sartori

• adesouza

  SSC Veteran

  Points: 269

  More actions

  September 2, 2011 at 7:13 am

  #1376412

  Thanks for your reply. I applied the settings under the Distributed Transaction Coordinator section of your article but it is still the same, I'm logged in as a local Administrator so I guess I don't need to make any permissions changes??