Linked SQL Servers - Microsoft SQL 2008

Expand / Collapse

Microsoft SQL has always included a way for you to link your server to other SQL servers for remote querying and management.  Here is how to link a Microsoft SQL 2008 server to a MySQL server.  In order to follow this article you must first install the MySQL ODBC driver which can be found here - http://dev.mysql.com/downloads/connector/odbc/ - and can be installed by running the appropriate .msi and clicking next till the wizard is finished.

Creating an ODBC DSN for MySQL

Go to Control Panel > Administrative Tools > Data Sources (ODBC).  Select the System DSN tab then click Add.  The ODBC driver used for creating this ODBC DSN is the one installed on the machine when the MySQL Server was installed as shown.

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.  All you have to do is put in the appropriate connection settings here.

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.

 

Steps to create a linked server from Management Studio

Log into SQL Server Management Studio, right click the Linked Servers node to display a drop-down menu as shown in the next figure.

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.

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.

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.

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.

 

 

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.

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.

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.




Rate this Article:


Details
Category: Database

Last Modified:Wednesday, June 15, 2011 9:16 AM

Type: HOWTO

Level: Intermediate

Rated 5 stars based on 1 vote

Article has been viewed 4,494 times.

Options
Email Article Email Article


Social Bookmarks Social Bookmarks