Googling for using Linked Server functionality in SQL server to link a MySQL database returns lots of results. However, reaching a final answer may take 5 or 6 hours of cursing. Below, I am describing my adventure hope saving you some curse:
1) Install “MySQL ODBC 5.1 Driver” Connector for ODBC [here]
2) Go to ODBC Data Source Administrator (Control Panel/Administrative Tools/Data Sources (ODBC))
3) Add a new System DSN:
- Select MySQL ODBC 5.1 Driver
- Fill out MySQL data as below:
Click on details and in “Connection” tab, check “Allow big result sets” and “Use compression”.
Most of these mentioned completely [here] but do not check “Don’t cache results for forward only cursors” and “Force use of forward only cursors”.
4) Open SQL server management studio,
5) Go to Server ObjectsLinked ServersProviders, right click on MSDASQLProperties,
6) Check “Nested queries”, “Level zero only”, “Allow inprocess”, and “Support ‘Like’ operator” and click OK,
7) Now, click on Linked Servers nodeNew Linked Servers. Suggest a name for Linked Server, and Product Name. For the Data source choose the name you used to create the ODBC. Make sure that the Provider is “Microsoft OLE DB Provider for ODBC Drivers” and click OK.
The tricky part: If everything is installed correctly and you didn’t get any error message, still you [may] cannot query like most humans do. You need to use OPENQUERY function. So your queries will be as below:
SELECT * FROM OPENQUERY(mysqlapp, 'SELECT ID,FirstName,LastName FROM customers')
INSERT OPENQUERY(mysqlapp, 'SELECT ID,FirstName,LastName FROM customers where 1=0')VALUES(4,'Amir','Mohtasebi')
Update OPENQUERY(mysqlapp, 'SELECT ID,LastName FROM customers where ID=3') SET LastName='Mohtasebi'
Delete OPENQUERY(mysqlapp, 'SELECT ID FROM customers where ID=3')
Note: Remember that for Insert and Update statements, there should be more than one column in the defined in the query and the primary key alone throughs some funny exceptions.
Enjoy.