MS SQL Server and MySQL through Linked Server
We have been stuck with the following issue for almost 2 Months.
We have MS SQL Server 2012 and MySQL 5.5, and we need to sync one table data between the two databases. So that whenever a record is inserted in the table "trainee" in MS SQL Server then it should directly copied to the table "trainee" in MySQL database.
We used the linked server, and it works fine for reading data from MySQL with the following driver:
MySQL ODBC 5.2 UNICODE Driver And then we added a Trigger on the table in MS SQL Server with the following code:
USE [case] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[Inserttms] ON [case].[dbo].[mdl_tms_trainee] AFTER INSERT AS Insert into dbo.tms (id,u_id,trainee_number) Values ('8','8','888'); Further more, we enable the DTC in the server, set True for RPC and RPC OUT in the LInked Server, and even created aview in the local database in MS SQL Server in order to use workaround, but these all never solve the below problem.
OLE DB provider "MSDASQL" for linked server "TMSCASEU" returned message "[MySQL][ODBC 5.2(w) Driver]Optional feature not supported". Msg 7391, Level 16, State 2, Procedure Inserttms, Line 5 The operation could not be performed because OLE DB provider "MSDASQL" for linked server "TMSCASEU" was unable to begin a distributed transaction. Please help!!!!
