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.
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.
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.
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.
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.
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.
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=N'MySQL_link'
Exec master.dbo.sp_addlinkedserverlogin
@server=N'MySQlbyCode',
@locallogin=NULL,
@rmtuser=N'root',
@rmtpassword=N'<your password>'
@rmtsrvname=N'localhost'
@datasrc=’MYSQL32′, @provstr=’DRIVER={MySQL ODBC 5.1 Driver};SERVER=192.168.241.32;Port=3306;USER=root;PASSWORD=12345;OPTION=3;DATABASE=cms_test;’
Thanks…
Bur getting following erron in SQL, during making link server.
“Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “MYSQLAPP”.
OLE DB provider “MSDASQL” for linked server “MYSQLAPP” returned message “[MySQL][ODBC 5.1 Driver]Access denied for user ‘root’@’localhost’ (using password: NO)”. (Microsoft SQL Server, Error: 7303)
please give some solution.