How-To: MySQL as a linked server in MS SQL Server

Posted by Daniel B. Roy on 12 May 2005

Tag(s): Databases, Consultancy

This document describes how to link a Microsoft SQL Server and a MySQL server by using the 'linked server' feature in SQL Server. After linking, tables on both servers can be referenced in one SQL query or stored procedure, with the MySQL database behaving as if it was a local SQL Server database.

This technique can be useful for synchronizing MySQL-driven web pages with data held locally in a Microsoft SQL Server. The document contains an example which uses triggers to automatically propagate any changes to the content of a SQL Server table to a linked MySQL table.

A downloadable PDF version of this article is available at the bottom of theis page.

[UPDATE 2010: This article is now over five years old and refers to SQL Server 2000. For the newer versions SQL Server 2005 and 2008 most of the article is still applicable, but there is a slight change in the procedure when using the new SQL Server Management Studio.

You must now first create an ODBC System DSN that contains all information to connect the MySQL server and then refer to this DSN from the Linked Server dialog when you are creating the linked server. Also, Linked Servers are now located under 'Server Objects'->'Linked Servers' in the Management Studio. Finally, provider options are set on the provider level in the Management Studio under 'Linked Servers'->'Providers'->'MSDADQL']

 

1 Introduction

This document describes how to link a Microsoft SQL Server 7.0/2000 (MS SQL) and a MySQL 4.x server. After linking, the linked MySQL server behaves as if it is a local SQL Server. Having linked the two servers, examples of some things you could do are automatically synchronizing each Update/Insert/Delete on the MS SQL database immediately in the tables on the linked MySQL server or write a SQL query that uses tables and data on both servers simultaneously.

To perform this procedure, you require:

  • Microsoft SQL Server 2000 or 7.0.
  • MySQL 4.x. The server must have transaction support, which is included in versions 4.x and newer. The linking is possible against both InnoDB and MyISAM tables.
  • Communication between the MySQL server and the MS SQL Server on TCP port 3306. TCP port 3306 is the default port; you can use a different port if you want to.
  • MyODBC 3.51 installed on the MS SQL Server box. A separate DSN is not required. You can find the latest MyODBC release at http://dev.mysql.com/downloads/connector/odbc/3.51.html.

 

This How-To describes how to perform the linking process and was written by Daniel Roy (daniel.roy@infi.nl). Comments and additions to its content are more than welcome and can be mailed to Daniel. Check http://www.infi.nl/blog  for the latest version of this document.

2 Why do I want to do this?

Many companies run a mostly Microsoft based network locally and thus use Microsoft's SQL Server for storing their local databases. On the other hand, the Apache/MySQL combination is very popular for storing website databases on the Internet, especially in combination with PHP as a server-side scripting environment (the so-called LAMP configuration).

Now, often the need arises to link the contents of these two database environments, for instance to synchronize what's happening in the office to what people can see on the web or to combine website obtained data with local office information. Sometimes this linkage has to happen at set times or intervals, but it may also be triggered by a change in data.

An example of the above would be a shipping company that processes orders and stock changes locally and stores this data on their local SQL Server. Clients want to see this data on the web with the least possible delay: if something has been shipped or has come in stock, this has to be shown on the companies' web pages as soon as possible. The web pages, however, use a MySQL database for data storage. This situation requires a link between the local MS SQL Server and the MySQL server on the web, so that changes to information in the MS SQL Server can immediately be propagated to the web database.

One option to implement such a link is by using MS SQL's 'linked server' feature. This feature allows you to link any ODBC-accessible database server to the SQL Server. After having performed this link, the linked server can be accessed in MS SQL as if it was a local SQL Server.

Advantages of this approach are:

  • Centralized administration of the database synchronization. All components of the synchronization process can be administered from one location, i.e. the SQL Server Enterprise Manager.
  • Support for triggers. MySQL doesn't support triggers, but SQL Server does. After linking, this means synchronization between the two databases can be triggered by changes in the data on the SQL Server, allowing very tight synchronization of the two databases involved.
  • Better administrative tools. The SQL Server Enterprise Manager and Query Analyzer can be used to edit and administer the linked MySQL database. Of course, if you're an M$ basher, this is a disadvantage.

 

3 How?

To demonstrate the procedure we'll create two identical tables, one on the SQL Server and one on the MySQL server. After that we will link the SQL Server to the MySQL server and use a trigger to automatically propagate any changes made on the SQL Server table to the MySQL table. The most important step is step 3, the linking of the two servers. Once this link has been made, tables on the MySQL server can be accessed in virtually the same way you would access the local SQL Server tables.

3.1 Step 1: Create table in SQL Server

 

For this example we first create a table in SQL Server according to the above figure. (Please excuse the column names: this document was originally prepared in Dutch. 'Naam' translates to 'Name' and 'Leeftijd' translates to 'Age'). We will synchronize all changes to the contents of this table automatically to a MySQL Server, which could, for instance, be located on the Internet.

 

3.2 Step 2: Create an identical table in MySQL

We create the same table on the MySQL server. See the figure below.

 

3.3 Step 3: Create linked server in MS SQL Server

This is the core step in the process: the actual creation of the linked server. To do this, in the Enterprise Manager, go to the folder Security and open the context menu of the item 'Linked Servers'. In this menu, choose the option 'New Linked Server'.

 

The dialog 'New Linked Server' appears. In the dialog, enter the following values:

  • Linked server: A name to identify this linked server. You can choose this value as you wish.
  • Server type: Choose the option 'Other data source'.
  • Provider name: Choose the option 'Microsoft OLE DB Provider for ODBC Driver'.
  • Provider string: Enter the following text:
    Driver={MySQL ODBC 3.51 driver};Server=;Port=3306;
    Option=131072;Stmt=;Database=;Uid=;Pwd=
    

In this text, replace the placeholders , , , by the IP address of the MySQL server, the database name on the MySQL server, the login and the password on the MySQL server respectively.

 

Some tips:

  • Remember that Linux and MySQL are case-sensitive when it comes to user names, database names, etc.
  • We haven't tested this for spaces or other irregular characters in the password. If anyone has any experience with this, please let us know
  • IMPORTANT. The login and password for accessing the MySQL server are visible in plain text to anybody who can view the properties page for this linked server in the Enterprise Manager. We strongly advise to create a separate login and password for the linking between MySQL en SQL Server and to restrict the rights of this account as much as possible. Also, try to grant access to the Linked Server properties in the Enterprise Manager to as few people/logins as possible.

 

Don't click OK yet! First, click on the button 'Provider options' and set the options as shown below:

 

Click on OK to save the Provider options. Leave the default settings for the Security tab unchanged and set the options on the Server Options tab as shown to the right. Finally, click OK. The linked server has now been created.

 

3.4 Step 4: Create triggers on the SQL Server table

To now access data on the linked MySQL database, you use the SQL Server OPENQUERY function where you would normally use a table name. This function takes two arguments: the first one specifies the linked server containing the data and the second argument is a SQL query which should return the data on the linked server you want to access or modify. So, for accessing the table [RemoteTable] on the linked server [LinkedServer], use the syntax:

OPENQUERY ([LinkedServer], 'SELECT * FROM [RemoteTable]')

 

Anywhere you would normally enter a table name in your SQL code, you can now enter the above construct and the statement will behave as if [RemoteTable] is a local table on the SQL Server. You can access, edit and delete data in [RemoteTable] from your MS SQL Server stored procedures and other SQL code without being bothered by the fact that it actually lives on a different server and database.

To illustrate this, we'll add some triggers to our SQL Server example table which will synchronize any changes made to the contents of this table to the same table on the linked MySQL database.

Enter and execute the following SQL statements in the SQL Query Analyzer:

CREATE TRIGGER items_insert ON [dbo.items]
FOR INSERT
AS
SET XACT_ABORT ON
INSERT INTO OPENQUERY(WEBDB, 'SELECT * FROM items')
SELECT IDkolom, naam, leeftijd FROM INSERTED

GO

CREATE TRIGGER items_update ON [dbo].[items]
FOR UPDATE
AS
SET XACT_ABORT ON
DELETE FROM OPENQUERY(WEBDB, 'SELECT * FROM items')
WHERE IDkolom IN (SELECT IDkolom FROM DELETED)
INSERT INTO OPENQUERY(WEBDB, 'SELECT * FROM items')
SELECT IDkolom, naam, leeftijd FROM INSERTED

GO

CREATE TRIGGER items_delete ON [dbo].[items]
FOR DELETE
AS
SET XACT_ABORT ON
DELETE FROM OPENQUERY(WEBDB, 'SELECT * FROM items')
WHERE IDkolom IN (SELECT IDkolom FROM DELETED)

GO

 

If you find yourself using a certain table on a linked server often, you could consider creating a view on the local SQL Server which contains the appropriate OPENQUERY function. You could then use the View name in your SQL code instead of the OPENQUERY syntax, saving you some typing. Also, using this technique, if the table name on the linked server or the name of the linked server were ever to change, you would only have to modify your code in one place, the View. This could increase the maintainability of your code.

3.5 Step 5: Insert data into the SQL Server table

To test if everything is working properly, you can INSERT a row in the SQL Server table and check if the same INSERT has also been executed on the linked MySQL server. This should happen almost immediately. The link is now complete!

 

 

Dowload related files:

Comments:

Posted by Tom M. on 7 July 2010, 18:07:
Thanks. This is great info
Posted by tanguy on 8 July 2010, 14:07:
Thanks for this info, this really saved our ass!
Posted by Lenci on 18 September 2010, 23:09:
couldn't have asked for more. thanks a lot
Posted by Pirx Danford on 19 January 2011, 11:01:
I had real trouble getting this to work.
Apparently one needs a mysql odbc driver version before 3.51.23, with using 3.51.22 and rewritten triggers it works.
After AS and before the SET insert two new lines with "COMMIT" and "SET IMPLICIT_TRANSACTIONS OFF".

Also the UPDATE trigger might be more dynamic the way its written, but I prefer to use
"UPDATE OPENQUERY(WEBDB, 'SELECT * FROM items') SET naam=(SELECT naam FROM DELETED),leeftijd=(SELECT leeftijd FROM DELETED) WHERE IDkolom IN (SELECT IDkolom FROM DELETED)" INSTEAD of DELETE and INSERT.
Posted by Pirx Danford on 19 January 2011, 11:01:
Ouch!

Of course it should be
UPDATE OPENQUERY(WEBDB, 'SELECT * FROM items') SET naam=(SELECT naam FROM INSERTED),leeftijd=(SELECT leeftijd FROM INSERTED) WHERE IDkolom IN (SELECT IDkolom FROM INSERTED)

That is what I get for copypasting and rewriting from the example queries, without thinking...
Posted by Ryan on 9 February 2011, 04:02:
Hi, i face problem while installing this,
UPDATE OPENQUERY ([WEBDB], 'SELECT * FROM [items]')

CREATE TRIGGER items_insert ON [dbo.items]
FOR INSERT
AS
SET XACT_ABORT ON
INSERT INTO OPENQUERY(WEBDB, 'SELECT * FROM items')
SELECT user_idcolum, user_login, user_pass FROM INSERTED

GO

CREATE TRIGGER items_update ON [dbo].[items]
FOR UPDATE
AS
SET XACT_ABORT ON
DELETE FROM OPENQUERY(WEBDB, 'SELECT * FROM items')
WHERE user_idcolum IN (SELECT user_idcolum FROM DELETED)
INSERT INTO OPENQUERY(WEBDB, 'SELECT * FROM items')
SELECT user_idcolum, user_login, user_pass FROM INSERTED

GO

CREATE TRIGGER items_delete ON [dbo].[items]
FOR DELETE
AS
SET XACT_ABORT ON
DELETE FROM OPENQUERY(WEBDB, 'SELECT * FROM items')
WHERE user_idcolum IN (SELECT user_idcolum FROM DELETED)

GO



then it come out with the below error.....

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CREATE'.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE TRIGGER' must be the first statement in a query batch.
Server: Msg 7399, Level 16, State 1, Procedure items_update, Line 6
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [MySQL][ODBC 5.1 Driver]Access denied for user 'cupwater'@'60.50.182.118' (using password: YES)]
Server: Msg 7399, Level 16, State 1, Procedure items_delete, Line 6
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [MySQL][ODBC 5.1 Driver]Access denied for user 'cupwater'@'60.50.182.118' (using password: YES)]
Posted by Daniel on 9 February 2011, 20:02:
Hi Ryan,

This seems to be an error from mysql regarding incorrect logincredentials. Can you correctly login to the mysql server when using the command line mysql tool from the computer runing sql server?

Please note that with mysql you have to include the IP in the login account: make sure you have included 60.50.182.118 in the account definition in on mysql for the cupwater account.
Posted by ryan on 11 February 2011, 03:02:
Hi Daniel,

i cant login,
My website is www.cupwater.com & i notice i cant just type the IP 110.4.45.145 and get my website, is that is the main issue??

60.50.182.118 is my company IP address..
I shall include 110.4.45.145 in Server=; but not 60.50.182.118 right ?
thanks
Posted by Eric on 16 February 2011, 08:02:
Hi,

everything goes fine until the last part i face some error when triggering the database.

UPDATE OPENQUERY ([WEBDB], 'SELECT * FROM [items]')

CREATE TRIGGER items_insert ON [dbo].[items]
FOR INSERT AS SET XACT_ABORT ON
INSERT INTO OPENQUERY(WEBDB, 'SELECT * FROM items')
SELECT idcolum, login, pass FROM INSERTED
GO

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CREATE'.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE TRIGGER' must be the first statement in a query batch.

Posted by Ashley on 18 February 2011, 07:02:
Hi Daniel,

i have completed all the steps you guide and i can see the data changes in Link server Table once i change the data in MySQL, but no changes to my local Mssql table also.

And when i change the data in MssQl, there is nothing changes to the MysQl table too =((

or i need a Trigger to link them ??

I need them to synchronize together when either of them got changes, please help me =((
Posted by mbachu on 21 March 2011, 08:03:
using sql 2005. hv done all. from sql end i can get result but from mysql end no result. even the result i got from sql i use union to combine the two tables. help me
Posted by Javier on 13 April 2011, 13:04:
Hi, Daniel

I followed all the procedure, everything right but when I changed data in the MsSql table, an error occurs so the MySql table doesn't get changed. The error is:
"No rows updated.
Data on row nn were not confirmed.
Error source: .Net SqlClient Data Provider
Error message: Transaction completed in trigger. Batch aborted.

Correct the errors and try again or press ESC to cancel changes
Posted by Dinh Lam on 4 May 2011, 06:05:
Hi Daniel,
Does mySQL support to create Linked server?
I want to create LinkedServer from mySQL to SQL server, how can I do?

Thanks
DinhLam
Posted by Raphael K on 7 September 2011, 15:09:
I followed this great tuto, with MSSQL 2005 and Mysql5 ; unfortunately, I can t make it work ; I have this message :
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "XXXX" was unable to begin a distributed transaction.

Some people got the same error, I can t find a solution to solve it. If someone has the same environnement, and get this work, please tell me :)
Posted by Raphael K on 7 September 2011, 15:09:
I followed this great tuto, with MSSQL 2005 and Mysql5 ; unfortunately, I can t make it work ; I have this message :
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "XXXX" was unable to begin a distributed transaction.

Some people got the same error, I can t find a solution to solve it. If someone has the same environnement, and get this work, please tell me :)
Posted by korea on 23 September 2011, 06:09:
thanx, very godd info.
Posted by dGo on 4 April 2012, 16:04:
Hi Daniel,
Great article ! Thanks.
I might just add that with SQL2008, one has to use the ODBC name as "data source" when creating the Linked Server.

I do encounter the same issue as Raphael (above) though.
Everything works great when just making INSERT, UPDATE and DELETE requests... But when I try to do this through a trigger, I am getting :
Msg 7390, Level 16, State 2, Procedure items_insert, Line 5
The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "LINKEDMYSQL" does not support the required transaction interface.

I have tried many suggestions I found online, but nothing seems to help.
Have you got any suggestions ?

Thanks,
dGo
Posted by Mk on 1 May 2012, 10:05:
I want this from "mysql" to "MS Server". is it possible to connect. if it's pssoble, plz tell me how
Posted by Andreas on 5 October 2012, 12:10:
I'm facing the same error 7391 regarding distributed transactions as Raphael and dGo.
Would really appreciate if someone could share a fix.
Posted by winchotun on 25 February 2013, 06:02:
Hi, Daniel

I followed all the procedure, everything right but when I changed or insert data in the MsSql table, an error occurs so the MySql table doesn't get changed. The error is:
"No rows updated.
Data on row nn were not confirmed.
Error source: .Net SqlClient Data Provider
Error message: Transaction completed in trigger. Batch aborted.

Correct the errors and try again or press ESC to cancel changes
Posted by winchotun on 25 February 2013, 07:02:
Hi, Daniel

I followed all the procedure, everything right but when I changed or insert data in the MsSql table, an error occurs so the MySql table doesn't get changed. The error is:
"No rows updated.

Msg 8501, Level 16, State 3, Procedure items_insert, Line 5
MSDTC on server 'WinChoTun-PC\SQLEXPRESS' is unavailable.
Posted by Daniel Roy on 1 March 2013, 13:03:
@winchotun: The error you are seeing is caused by the fact that you are connecting to mySQL from within a MS SQL trigger. The MS SQL trigger starts an implicit transaction. When you then try to do something on the mySQL server from within the trigger, the MS SQL server tries to extend the transaction to the mySQL server. To do this, it uses the DTC. This is not supported by myODBC so the trigger query fails.

To clarify: the following happens:

1). MS SQL trigger fires and an implicit transaction begins on your MS SQL server.
2). Trigger wants to do something on the linked mySQL server. This means the mySQL server has to be included in the running transaction started in 1).
3). Since the transaction is to be extended to a different server, MS SQL calls in the MS DTC service.
4). The DTC contacts the mySQL server through the myODBC driver.
5). Since AFAIK the myODBC driver doesn't support distributed transactions, it returns an error and your trigger fails.

I don’t think there is a solution to this, other than not doing anything to the mySQL server from within a MS SQL trigger.

So I think the best approach would be to have the mySQL part of what you want to happen run separately from the trigger, i.e. when the trigger fires, it locally stores in a MSSQL table what it wants to happen to the mySQL server. A different non-trigger process on the MS SQL server (run every XX minutes or something) checks this local data and performs the requested operation on the mySQL server.
Easiest would probably be to construct the actual mySQL SQL statements in the trigger and then write them to the local table so they could subsequently be executed verbatim. Please note that what you do on the mySQL server can include 'normal' transactions that apply to the mySQL data manipulations, as long as they don't cross machine boundaries.

It's been a while since I've worked with linked servers, but I hope this helps,

Daniel.
Posted by Nugroho Budi on 20 September 2013, 03:09:
Very useful explanation, I am doing project to create Historical Data from Wonderware InTouch. I think impossible to linking wonderware to MYSQL server but with your article i am sure that can do it. Although I have to take advantage of SQL server first.

Regards
NB
controlmanuals.com

Post a comment:

Name:
E-mail*:
Comment:
*optional, will not be published.