Vidhya Sagar – Blog

In this article we going to look into creating a heterogeneous linked server. i.e. We are going to create a linked server to MYSQL database server from SQL Server environment and then going to query data’s from MYSQL. First step in heterogeneous linked server is to create a ODBC connection to that RDBMS platform. For creating ODBC connection we need the ODBC drivers for that RDBMS platform. In this we need drivers for MYSQL, this is not included in windows you need to download it from MYSQL Website. Lets discuss on this step by step

Testing Environment

Windows Server 2008
SQL Server 2008
MySQL Version 5.0.51
MySQL ODBC Driver 5.1
MySQL DBName : sqlarticles

Implementation Steps

• Download MySQL ODBC drivers from the link “Get MySQL ODBC Driver”
• Install the MySQL drivers from the downloaded setup.
• Once installation completed go to Run—> Type odbcad32 and press enter
• In the ODBC admin window go to System tab as shown below

  

• When you click on Add button a list of ODBC drivers available in the system will be shown, select “MySQL ODBC XX.X Driver” (this will not be displayed if you haven’t installed MySQL Drivers, where XX is the version number) and then click finish button

• Now you will be shown with the ODBC driver configuration, all the fields in the window are self explanatory hence fill all the details required and click on Test button to test the ODBC connection as show below and click on Ok to close it.

• Now you have created the ODBC connection successfully, the next step will be to create a new linked server in SQL Server.
• Open Management studio and connect to the SQL Server Instance
• Expand “Server Objects” in object explorer and right click on “Linked Servers” folder and then click on “New Linked server” in the menu as shown below

• New Linked server window will be popup, fill all the details required. Provide the name for the Linked server, Select “Other Data Source” Option button, Select Provider as “Microsoft OLE DB Provider for ODBC Drivers” and provide the ODBC name in the field “Data Source”.

• Now click on the Server Options in the left pane to configure it as per your requirement. I’ve configured it as below, once this done click on OK button to create the linked server.
• Now we have created the linked server successfully however to retrieve the data’s correctly from MySQL we need to configure the OLE DB provider in SQL Server correctly (you can also modify this with respect to your requirement).
• Expand the folder Providers under Linked server directory in SSMS and go to properties of MSDASQL provider as shown below and configure it.

If the above provider is not configured properly you will thrown any of the error message below when querying through linked server hence don’t forget to configure the provider properly.

OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL".

Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "MYSQL".

Querying Data from MySQL Linked Server

Connect to SSMS and open a new query window and type the query to retrieve data’s from MySQL linked server.

SELECT news.link FROM mysql...news

In query above you can’t change the database name when you use four part identifier format since the database name is already selected while configuring the ODBC. To select data’s from different database using the same linked server can be accomplished by using OPENQUERY command. You can see from the below example that I’m selecting data’s from Information_Schema db and also from sqlarticles db using the same MySQL linked server

--Below is the catalog defined in ODBC

SELECT COUNT(*) FROM OPENQUERY

(MYSQL, 'SELECT * FROM sqlarticles.article')

--Using different catalog in the query

SELECT COUNT(*) FROM OPENQUERY

(MYSQL, 'SELECT * FROM INFORMATION_SCHEMA.TABLES')

That’s it you have created a linked server to MySQL Server from SQL Server. Now you can use this to retrieve data’s from MySQL Server.