September 2, 2011 at 4:31 am
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.
September 2, 2011 at 6:18 am
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
September 2, 2011 at 7:13 am
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??
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply