MySQL Linked Server on SQL Server 2008

Exclusive offer: get 50% off this eBook here
Learning SQL Server 2008 Reporting Services

Learning SQL Server 2008 Reporting Services — Save 50%

A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008

$29.99    $15.00
by Jayaram Krishnaswamy | August 2009 | .NET Microsoft

Linking servers provides an elegant solution when you are faced with running queries against databases on distributed servers or looking at your distributed assets on disparate databases.

This article by Dr. Jay Krishnaswamy explains how to set up a MySQL linked server on SQL Server 2008 Enterprise. Configuring a linked MySQL server as well as querying a table on the MySQL linked server is described. The reader would benefit reviewing the first article on this series on MySQL Servers.

Introduction

MS SQL servers always provided remote access to servers through RPC mechanisms, but they had the limitation of being confined to invoking procedures on remote locations. A linked server (a virtual server) may be considered a more flexible way of achieving the same thing, with the added benefits of remote table access and distributed queries. Microsoft manages the link mechanism via OLE DB technology. Specifically, an OLE DB datasource points to the specific database that can be accessed using OLEDB.

In this article we will be creating a MySQL linked server on SQL Server 2008 and querying a database [TestMove] table shown in the next listing . In reviewing the previous article it may be noticed that the Employees tables was moved to MySQL database TestMove. In running the commands from the mysql> prompt it is assumed that the MySQL Server has been started.

Listing 1: employees table in TestMove

mysql> show tables;
+--------------------+
| Tables_in_testmove |
+--------------------+
| employees |
+--------------------+
1 row in set (0.09 sec)

mysql>

Creating an ODBC DSN for MySQL

In the previous article on MySQL Servers cited earlier, a DSN was created for moving data. Essentially the same DSN can be used. Herein follows a brief review of the DSN MySQL_Link created along the same lines as in the previous referenced article. The ODBC driver used for creating this ODBC DSN is the one installed on the machine when the MySQL Server was installed as shown.

MySQL Linked Server on SQL Server 2008

The final interactive window where you may test the connectivity is shown in the next figure. You may notice that the database Testmove has been named in the ODBC DSN. The name MySQL_LINK is the ODBC DSN.

MySQL Linked Server on SQL Server 2008

When you close the window after clicking the OK button, a ODBC DSN item will be added to the System DSN tab of the ODBC wizard as shown.

MySQL Linked Server on SQL Server 2008

Steps to create a linked server from Management Studio

Right click the Linked Servers node to display a drop-down menu as shown in the next figure.

MySQL Linked Server on SQL Server 2008

Click on New Linked Server...item. This brings up the New Linked Server window as shown. The window is all empty except for a default Provider.

MySQL Linked Server on SQL Server 2008

The very first thing to do is to provide a name for this linked server. Herein it is LINKED_ MYSQL. We will be using one of the providers [Microsoft OLE DB Provider for ODBC] supported by SQL Server 2008. You may access the list of OLE DB Providers in the Providers sub-folder of the Linked Servers. Provide the other details as shown in the next figure. Make sure they are entered exactly as shown or according to how you have created the database on MySQL Server.

MySQL Linked Server on SQL Server 2008

Click on the Security list item under General in the left. This opens the 'Security' page of the New Linked Server wizard as shown. Change the login from the default "Be made without using a security context" to "Be made using this security context". Enter remote login. In this case it is "root" for the remote login and the password is the one used during the ODBC DSN (also the password for server authentication) creation.

MySQL Linked Server on SQL Server 2008

Make no changes to the Server Options page. Click OK. This creates a linked server Linked_MySQL as shown expanded in the Linked Server's node as shown. You may need to right click and refresh the Linked Servers' node to see the new linked server. As you can see in the figure, the 'User' tables are not displayed.

MySQL Linked Server on SQL Server 2008

 

 

Running Queries and reviewing results

Running system stored procedures can provide various levels of information and the database can be queried using the four part syntax and the openquery() method.

Information on the linked server

It is easy to find how the linked server is configured using system stored procedure sp_linkedsrvlogin on the SQL Server 2008. Open a Query window from File | New | Query Current Connection to open the query window and type in the following statement. The next figure shows the statement as well as the returned values. SQL Server 2008 querying has the intellisense report and this must be put to good use.

Exec sp_linkedsrvlogin

This shows all servers both local and remote as shown in the next figure.

MySQL Linked Server on SQL Server 2008

Information about the tables on the remote server can also be accessed by running a stored procedure. Executing the stored procedure sp_tables_ex as shown in the next figure (which displays the statement and the result of executing the stored procedure) can be used to obtain table information.

MySQL Linked Server on SQL Server 2008

Querying the table on the database

Data in the table on the linked server can be queried using the openquery() function. The syntax for this function shown next is very simple.

openquery ('linked server', 'query')

The next figure shows the result of running the openquery() function on the Linked_MySQL linked server.

MySQL Linked Server on SQL Server 2008

Although it should be possible to query the linked server using the four part syntax as in:

Select * from LINKED_MYSQL...employees

The above statement returns an error. This is probably a limitation of a combination of MSDASQL and the ODBC driver which does not provide the schema information correctly(this is just the author's opinion).

Are Remote Procedure Calls (RPC) allowed?

The easiest way to test this is to send out a call by running the following query against the linked server.

Execute('Select FirstName, LastName from employees') at Linked_MYSQL

If the linked server is not configured for RPC, then the result you get by running the above query is as shown in the next figure.

MySQL Linked Server on SQL Server 2008

Turn on RPC

Earlier on we skipped the Server Options page of the linked server. Back in the Management Studio right click linked server LINKED_MYSQL and from the drop-down choose to look at the properties at the bottom of the list. This brings up the LINKED_MYSQL properties window as shown. Click on Server Options. In the Server Options page change the values of RPC and RPCOUT to true, default for both being false.

MySQL Linked Server on SQL Server 2008

Now run the query that produced the error previously. The result is displayed in the next figure.

MySQL Linked Server on SQL Server 2008

You might have noted that only two columns were returned from the employees table. This was deliberate as trying to get all the column would produce an error due partly to the data types of data stored in the table and their compatibility with MSDASQL and the ODBC driver (Again, an author's opinion).

Creating Linked Server using TSQL

While the linked server can be created using the built-in wizard of the Management Studio, it can also be created using TSQL statements as in the following listing (run both statements, the first one creates the linked server and the second the logins).

Listing 2:

 Exec master.dbo.sp_addlinkedserver
@server=N'MySQlbyCode',
@srvprodcut=N'MySQL',
@provider=N'MSDASQL',
@datasrc='//dgdsbygo8mp3h.cloudfront.net/sites/default/files/blank.gif' data-original=N'MySQL_link'

Exec master.dbo.sp_addlinkedserverlogin
@server=N'MySQlbyCode',
@locallogin=NULL,
@rmtuser=N'root',
@rmtpassword=N'<your password>'
@rmtsrvname=N'localhost'

Summary

The article described the steps involved in configuring a MySql Linked server on SQL Server 2008 using the built-in New Linked Server wizard as well as TSQL. Method to query the linked server as well as enabling RPC were described.

If you have read this article you may be interested to view :

Learning SQL Server 2008 Reporting Services A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Published: March 2009
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Jayaram Krishnaswamy

Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.

He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.

He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.

Contact Jayaram Krishnaswamy

Books From Packt

Grails 1.1 Web Application Development
Grails 1.1 Web Application Development

Asterisk 1.4 – the Professional’s Guide
Asterisk 1.4 – the Professional’s Guide

ASP.NET 3.5 CMS Development
ASP.NET 3.5 CMS Development

Pentaho Reporting 3.5 for Java Developers
Pentaho Reporting 3.5 for Java Developers

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

ICEfaces 1.8: Next Generation Enterprise Web Development [RAW]
ICEfaces 1.8: Next Generation Enterprise Web Development [RAW]

C# 2008 and 2005 Threaded Programming: Beginner's Guide
C# 2008 and 2005 Threaded Programming: Beginner's Guide

Joomla! 1.5x Customization: Make Your Site Adapt to Your Needs
Joomla! 1.5x Customization: Make Your Site Adapt to Your Needs

 

 

 

Your rating: None Average: 4.3 (4 votes)
Perfect! by
Thanks very much for this -- it is just what I needed. It's been a few years since I had to add a linked server, and that was too long ago to remember. This information allowed me to add it with no problems.
Four part queries worked from 32-bit SQL 2005 by
A similar procedure posted elsewhere worked for us with two variables: SQL Server 2005 / 32-bit (worked) SQL Server 2008 R2 / 64-bit (doesn't work) Given that both ODBC and the MySQL Connector are different, along with SQL Server itself, and I can't say where the problem lies. I'd really like to get four-part notation back again.
New Linked server pcture by
picture of new linked server is blurry and unreadable. Make better picture or even better write down options which you imputed inside picture.

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
D
3
3
K
q
u
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software