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;')