SQL Server connect to MySQL

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.