Link MySQL Server Into MSSQL Server
February 23rd, 2009 by Andrew Chen
I found a lot of posts on the internet regarding how to set up a MSSQL link server to MySQL server. I didn’t find any of them gave a complete picture. So I wrote my own post and hope it can help.
The first thing need to do
The second thing is to download and install MySQL ODBC driver onto the computer running MSSQL server. After that set up a ODBC DSN pointing to the MySQL server you want to link using the already tested host name, username and password. I want to emphasize these installations and tests should be done on the computer running MSSQL server.
When the above installation and tests are all successful we can proceed to set up the link server in MSSQL server as follow. Go to linked servers under the security folder in Enterprise Manager. Right click and select “New Linked Server…”, put in a name in the “Linked Server” box and choose Microsoft OLEDB provider for ODBC drivers in the “Other data source” drop down box. Put in something in “Product Name” box. It doesn’t matter. Put in the ODBC DSN you set up in previous step in the “Data source” box and leave provider string and location empty. Put in the MySQL database name in the “Catalog” box and click “OK”. It is done.
However MySQL link server tables can not be access using a three part name. It has to be accessed using openquery function like this:
select * From openquery (MySQL_Linked_Server_Name, ’select * from tablename’). Alternatively you can refer to the following document on How to set up MySQL as a linked server in MS SQL server
Related Posts:
(No Ratings Yet)
Loading ...
Hi Andrew,
I have recently setup a pair of linked MySQL servers, each on a different computer. Not being entirely familiar with MySQL, I noticed that you mentioned I cannot use 4part naming to access the tables. The problem I am having is that I need to run Insert into svr1.db1.schema1.tbl SELECT * from svr2.db1.schema1.tbl and I am not sure how to effect this. Would you be able to provide some assistance? I would really appreciate any suggestions to resolve this. As a side note, what I am attempting to do is create a stored procedure or some method to backup one MySQL database to another, and my knowledge of the MySQL interfaces is almost nonexistant.
Thanks,
Geoff Sutton
The syntex of insert statement to a MySQL linkedServer will be somthing like this:
insert into openquery(mysqlserver,’select * From tablename’) values (value1,value2….)
for your case it will be like:
insert into openquery(mysql_linked_server1,’select * From table1′) select * from openquery(mysql_linked_server2, ’select * from table2′)
Let us know if it works.
Thanks
all thes “tests” above worked for me except the important one, the “Link Server” connection test.
1. originally i had added a USER DSN. So i deleted the USER DSN and added a SYSTEM DSN with a simple name (no punctuation) like S008
2. i had to download and install the “Microsoft OLE DB Provider for ODBC” because it was not available on my w2003 x64 server. See ms
then it worked as described above.
thanks!
Here is the problem that I am facing. setup of the linked server is good but if I am running
INSERT INTO OPENQUERY (MYSQL, ‘SELECT * FROM MySQL_Table’)
SELECT * FROM MS_SQL_Server_Table
GO
The insert works fine except that any MONEY type will be multiplied by 10,000 in the MySQL table
the tables are identical except that it is a DECIMAL (19,4) type.
Has any one had this problem and fixed it