I came up with Idea of this article, when I tried to create replication setup between MS SQL 2008 and MySQL. I was unable to do it since MS SQL provides only two non SQL Subscriptions/Publishers options.
However I created the linked server through which I can access and query the MySQL databases and tables.
A linked server (a virtual server) may be considered a more flexible way of achieving remote access, with the added benefits of remote table access and distributed queries. Microsoft manages the link mechanism via OLE DB technology. Specifically, an OLE DB data source points to the specific database that can be accessed using OLEDB.
Let’s try and create MySQL linked Server on MS SQL Server 2008 and query a database.
Step 1: Create ODBC DSN for MySQL
To create ODBC DSN you need to …[Read more]