SQL Server 2008 : Script for MySql 5.1 Linked Server

I blogged a few days ago about creating a linked server to MySql from MS Sql 2008.
Further to that post, here is a script that does the steps for you.

Script for Configuring MySql ODBC link from SQL 2008 -

EXEC master.dbo.sp_addlinkedserver @server = N'MYSQL', @srvproduct=N'mysql', @provider=N'MSDASQL', @datasrc=N'mysqldsn', @provstr=N'DRIVER={MySQL ODBC 5.1 Driver};DSN=mysqldsn;SERVER=172.16.0.9;DATABASE=MySqlDatabaseName;UID=remote_user;password=remote_password;'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYSQL',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYSQL',@useself=N'False',@locallogin=N'mysql_link',@rmtuser=N'remote_user',@rmtpassword='remote_password'

GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

SQL Server 2008 : MySql 5.1 Linked Server

1 ) Download & Install the ODBC driver.
It needs to be installed on the instance where SQL Server is installed.

http://dev.mysql.com/downloads/connector/odbc/5.1.html

2) Create a system DSN (data source name).
i. Start > Control Panel > Administrative Tools > Data Sources (ODBC)



ii. Click Add, select mySQL ODBC driver, click Finish >


iii. Enter server details and connection parameters >


iv. Click OK, and the DSN has been created >



v. Click Configure, and adjust the settings on the 'Flags 1' tab >



vi. Click the 'Flags 2' tab and select the following >



vii. Click the 'Flags 3' tab and select the following >



viii. Click the 'Test' button to verify connectivity.


3) Create a linked server in SQL Server.

Update 30/03/2008 - Steps are given below, or if you want to use a script, theres a template here.

else, the steps are >

i. Expand Linked Servers (under ServerName > Server Objects)



ii. Right click 'Linked Servers' to add a new one and enter details to match the DSN you created. >



iii. Provide an appropriate connection string on this screen too >
Driver={MySQL ODBC 5.1 Driver};Server=172.16.0.9;Database=dbname;User=username;Password=password;


iv. Provide Security details on the security tab.
Map a local sql user (previously created) to the remote user.



v. Change provider properties for compatibilty.
It is called MSDAsql and is located here >



vi. Set the provider properties >


4) Enable OpenRowset / OpenDataSource

Either using the interface > Enabling OpenRowset in SQL 2005
or via commands >

sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
go

5) Fetch the data!

SELECT * FROM OPENQUERY(mysql, 'SELECT * from tablename limit 10;')