I have a table say Table1
on SQL Server 2014 and MySQL both.
Table1
ID INT,Code VARCHAR(100)
I created a linked server MyLinkedServer
in SQL Server using "Microsoft OLEDB Provider for ODBC".
**Linked Server **
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'MyLinkedServer', @provider=N'MSDASQL', @datasrc=N'MyLinkedServer'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='########'
Linked Server Settings
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'remote proc transaction promotion', @optvalue=N'true'
The linked server is created successfully and I am able to query Mysql Table in SQL Server.
Query
When I run
INSERT INTO MyLinkedServer...Table1(ID,Code) SELECT 1,'Code1'
The record is inserted. However when I start a transaction and run the INSERT
, I get an error:
BEGIN TRAN
INSERT INTO MyLinkedServer...Table1(ID,Code) SELECT 1,'Code1'
COMMIT
Error:
OLE DB provider "MSDASQL" for linked server "MyLinkedServer" returned message "[MySQL][ODBC 5.3(a) Driver]Optional feature not supported". Msg 7391, Level 16, State 2, Line 8 The operation could not be performed because OLE DB provider "MSDASQL" for linked server "MyLinkedServer" was unable to begin a distributed transaction.
What I have tried so far.
Enable XA Transactions in MSDTC
Enabled following setting in Linked Server Provider
- Nested queries
- Level zero only
- Allow inprocess
- Supports ‘Like’ Operator
I checked the following links and their suggestions however the error persists:
Distributed transactions between MySQL and MSSQL
SQL-Server and MySQL interoperability?
SQL Server and MySQL Syncing
EDIT
Additional Details:
MySQL is using InnoDB storage engine on Ubuntu machine.
I have already configured the ODBC connector and used it to configure a ODBC System Data Source which is used in the Linked Server