Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Linked server to MySQL Server.

By Daniel Calbimonte,

Introduction

Sometimes we want to select, insert, update and delete data from heterogeneous data sources in SQL Server. This article provides an example to access to a MySQL table using SQL Server.

In this example we will select, insert, update and delete data from a MySQL Database using SQL Server. To do this, first we will create a MySQL ODBC connection and then, we will create a linked server.

Requirements

In order to create a mySQL linked server, it is necessary to install the MySQL Server Database.

Once installed, the SQL Server Database cannot connect to mySQL by default. A MySQL Connector is necessary and it needs to be installed before creating the linked server.

The following links lists the requirements to 

Create a MySQL Table

We will use this sample table in MySQL named students and we will add some data for our article in order to create the linked server.

To create the table, use the following commands:

use mysql;

create table students(name varchar(20));

insert into students(name) values("Jake");
insert into students(name) values("Roy");
insert into students(name) values("Rachel");

Now we have a table with some data in MySQL. The next step is to create the ODBC connection.

Create an ODBC connection

The ODBC connection is the connection that we will use in SQL Server to access to MySQL. This connection will provide the user, password, database and servername information.

We will create an ODBC connection for this purpose. The steps to create the connection are the following:

  • To create a ODBC connection,  Go to Windows start menu > Administative tools >DataSources ODBC and click the add button.
  • The MySQL Connector/ODBC Data Source Configuration should be displayed:

  • Select the MySQL ODBC driver and press Finish (This driver is installed with the connector specified in the requirements).
  • Specify the Data Source Name. e.g. mySQL conn
  • Specify the TCP/IP Server. It can be the IP or the localhost if the machine used is the local machine.
  • Specify the user, in this case root and the password (ask to the mysql administrator if you do not know the user database password)
  • Select the mySQL database.
  • Press OK.

Once this is done, we have a ODBC connection to MySQL. Let's use this connnection in SQL Server.

Create the linked server

In this section, we are going to create a linked server named MYSQLSRV. This linked server will use the MySQL ODBC connection created previously.   

The code used to create the MySQL linked server is the following:

/****** Object:  LinkedServer [MYSQLSRV]    Script Date: 02/08/2012 11:53:34 ******/
EXEC master.dbo.sp_addlinkedserver 
  @server = N'MYSQLSRV', 
  @srvproduct=N'mySQL conn', 
  @provider=N'MSDASQL', 
  @datasrc=N'mySQL conn'

 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin 
  @rmtsrvname=N'MYSQLSRV',
  @useself=N'False',
  @locallogin=NULL,
  @rmtuser=NULL,
  @rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption 
  @server=N'MYSQLSRV', 
  @optname=N'collation compatible', 
  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption 
  @server=N'MYSQLSRV', 
  @optname=N'data access', 
  @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
  @server=N'MYSQLSRV', 
  @optname=N'dist', 
  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption 
  @server=N'MYSQLSRV', 
  @optname=N'pub', 
  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption 
  @server=N'MYSQLSRV', 
  @optname=N'rpc', 
  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption 
  @server=N'MYSQLSRV', 
  @optname=N'rpc out', 
  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption 
  @server=N'MYSQLSRV', 
  @optname=N'sub', 
  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption 
  @server=N'MYSQLSRV', 
  @optname=N'connect timeout', 
  @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption 
  @server=N'MYSQLSRV', 
  @optname=N'collation name', 
  @optvalue=null
GO
EXEC master.dbo.sp_serveroption 
  @server=N'MYSQLSRV', 
  @optname=N'lazy schema validation', 
  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption 
  @server=N'MYSQLSRV', 
  @optname=N'query timeout', 
  @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption 
  @server=N'MYSQLSRV', 
  @optname=N'use remote collation', 
  @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption 
  @server=N'MYSQLSRV', 
  @optname=N'remote proc transaction promotion', 
  @optvalue=N'true'
GO

The @provider will indicate to use the ODBC connection. The @srvproduct and @datasrc parameters will receive the ODBC connection name created in the ODBC connection.

All the other values are used to configure internal properties in the linked server. You do not need to modify the values.

Select a MySQL table

Now, we have a linked server to MySQL created. The name is MYSQLSRV. Let’s access to the students table:

select * from openquery(MYSQLSRV, 'select * from students')

This query will return the following results:

Insert data in MySQL

To insert data to a MySQL tables with SQL Server, use the following query:

INSERT OPENQUERY (MYSQLSRV, 'SELECT name FROM students')
  VALUES ('John');

We have inserted a new row in the mySQL table using SQL Server.  To verify that the new row was inserted use the select query used select statement 

Delete data in a MySQL 

To insert data to a MySQL tables with SQL Server, use the following query:

DELETE OPENQUERY (MYSQLSRV, 'SELECT name FROM students where name=''John''')

Update data in a MySQL

To verify that the new row was inserted use the select query used select statement 

To update data to a MySQL table with SQL Server, use the following query:

UPDATE OPENQUERY (MYSQLSRV, 'SELECT name FROM students WHERE name = ''Joy''')
  SET name = 'Linda';

To verify that the new row was inserted use the select query used select statement 

Conclusion

In this article, we learned how to create a table in MySQL, insert data and create ODBC connections. Then we created a linked server in SQL Server to MySQL and then added select, insert, update and delete sentences.

References.

HOWTO: Setup SQL Server Linked Server to MySQL

http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/

Total article views: 5460 | Views in the last 30 days: 21
 
Related Articles
FORUM

Linked server using MySQL ODBC 5.1.8

Linked server using MySQL ODBC 5.1.8

FORUM

MYSQL Backups

how to take MYSQL Backups in windows Server 2003

FORUM

Migrate MYSQL into SQL Server 2008

MYSQL to SQL Server

ARTICLE

MySQL Primer for the SQL Sever DBA

This is an introduction into Mysql for the SQL Server user. It compares the various engines of Mysq...

FORUM

Passing parameters to a MySql Query

Using a MySql ODBC Connection

Tags
linked    
mysql    
server    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones