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 

• The MySQL Database installed.
• The MySQL Connector installed.
• SQL Server installed

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/