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
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.
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 -
Msg 3609, Level 16, State 1, Line 1 The transaction ended in the trigger. The batch has been aborted.
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.
精彩评论