Friday, 20 March 2009

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.

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=;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
sp_configure 'Ad Hoc Distributed Queries', 1

5) Fetch the data!

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

No comments: