SQL Server connect to MySQL
Step1: mysql testing db [demo], table [t];mysql server ip: [10.100.1.43]
[root@centos ~]# mysql -h 10.100.1.43 -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.1.67-log Sourcedistribution
Copyright (c) 2000, 2012, Oracle and/orits affiliates. All rights reserved.
Oracle is a registered trademark ofOracle Corporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use demo;
Reading table information for completionof table and column names
You can turn off this feature to get aquicker startup with -A
Database changed
mysql> select * from t;
+------+-------+
| idx | cname |
+------+-------+
| 1 | a |
| 2 | b |
+------+-------+
2 rows in set (0.00 sec)
mysql>
Step 2: install mysql ODBC driver , download the filefrom mysql website.
Once installedthe driver, from ODBC data source administrator can see this driver from list
Step 3:Create new data source for mysql , windows search editbox , type ODBC, willpopup ODBC data source administrator window, click new button, from the driverlist , choose [MySQL ODBC Driver], click [finish] button
Once click the [finish] button, will pop up a newwindow [MySQL Connector/ODBC Data SourceConfiguration],here you can set the data source name , mysql server IP andport, key in login user and password, choose testing database [demo], rememberto set the Character set to MySQL Character set, [ok] to save.
Step 4:Create Linkedserver from SQL Server
Open SSMS,new a query, here I use T-SQL to create the linkedserver,
Tested ok.