SQL Server access MySQL

Usage Environment: Operating system: WINDOW7 database: SQL Server2005, MySql5.01, installing MySQL ODBC driver on server with SQL Server installed: Download link: http://dev.mysql.com/ downloads/mirror.php?id=376346 points in the past will be linked to the following page, do not login, directly click on the "No thanks,just start my download" will pop up the download box.

　　

The above link is just window7 ODBC driver, the following link is more complete, you can download the appropriate driver as needed: http://dev.mysql.com/downloads/connector/odbc/5.1. The Htmlmysql ODBC driver installation process is all the way next, the default is the line, until Install,finish. 2. Create an ODBC data source: Click "Control Panel"->> "Administrative Tools"->> "Data Source (ODBC)", the system will pop up "ODBC data Source Administrator", select the "System DSN" tab, you can see the existing system data source information.

　　

Click the "Add" button and select the appropriate driver in the "Create New data source" box that pops up. I am connected to MySQL here, so choose MySQL ODBC program. MySQL ODBC provides two versions of ANSI and Unicode, what is the difference between them I do not know, but literally, Unicode should be able to support more character sets, and ANSI only for a limited range of character sets. I just use it for testing, just pick an ANSI driver.

　　

When you click the "Finish" button, the system will pop up the MySQL odbd data source Configuration box, the DataSource name: data source names, cannot be empty, can be arbitrarily filled, but to remember, later useful to Deion: Data source Description, can be empty, can also be filled with TCP/IP Server: The IP address of the MySQL-installed machine port: port number, MySQL default port number 3306User: Login mysql username password: login mysql password database: data source

　　

After filling out, you can click on the "test" testing, if the connection is correct pop-up box

　　

If the connection is unsuccessful, it pops up:

　　

When the test is successful, click the "OK" button and the data source you just added is in the ODBC data Source Manager. If you want to modify the configuration information you just completed, you can click the Configure button.

　　

Finally "OK". 3. Establish a linked server locate the "Linked Servers" node in SQL Server2005 and right-click to select "New Linked server".

　　

Linked server: Be sure to fill in, you can fill it, it will automatically turn the information entered into uppercase server type select "Other data Sources" provider: Select "Microsoft OLE DB Provider for ODBC Drivers" (The default is "Microsoft OLE DB Provider for SQL Server ", two quite like, don't mix it up." Product Name: Official explanation is to be added as a linked server OLE DB data source, also do not know what meaning, seemingly can fill the data source: is the above established ODBC data source, I here is Testmysql

　　

Then select the "Security" tab, the bottom link to choose the last "make a connection using this security context", enter the user name and password and click "OK".

　　

At this point, the linked server node will be more than the linked server that you just established.

　　

4. Simple test Execution SQL statement: SELECT * from OPENQUERY (testmysql, ' select * from CDR_EX ') where "Testmysql" is the linked server that was just established, CDR_ Ex is a table in the selected database when the data source is built. Later on the Internet can also use SQL statements directly built "linked server", free from the front of a lot of things, here with SQL to create a new link server named "Testmysql2", copy and modify the following sql:

1. IF EXISTS (SELECT srv.name from sys.servers srv WHERE srv.server_id! = 0 and Srv.name = N ' testmysql2 ')
2. exec sp_dropserver ' testmysql2 ', ' droplogins '
3. EXEC sp_addlinkedserver
4. @server = ' testmysql2 ',--this is ODBC inside the data source name
5. @srvproduct = ' testmysql2 ',--this is your own free
6. @provider = ' Msdasql ',--this is fixed, not blind, or dead.
7. @datasrc =null,
8. @location =null,
9. @provstr = ' Driver={mysql ODBC 5.2 ANSI DRIVER}; Server=192.168.0.119;database=asteriskcdrdb; Uid=root;pwd=root; port=3306; ',
10. --note that the driver name is the same as ODBC
11. @catalog = NULL
12. EXEC sp_addlinkedsrvlogin
13. @rmtsrvname = ' Testmysql2 ',
14. @useself = ' false ',
15. @rmtuser = ' root ',
16. @rmtpassword = ' root ';

Technology sharing: www.kaige123.com

SQL Server access MySQL