We could need to access to MySQL from SQL Server. An easy way to do it is creating a linked server that uses an ODBC connection.
How to link SQL Server and MySQL, step by step
- Download the ODBC client of MySQL from here, choosing the platform of the SQL Server.
- Install using the assistant (only have to click next, next..) and set a system DSN. To configure the DSN use the Manager of data sources ODBC, select the 'System DSN', click add and select 'MySQL ODBC 5.3 driver'.
Click OK and you will see a form like this. Fill in the text boxes and test the connection using the 'Test' button.
- Create the linked server in the database.
We have to connect to the SQL Server database, with SQL Server Management Studio, por example, and in the 'objects tree' select 'Server objects', click on right button over 'Linked servers', and finally select 'New linked server', fill in the data in text boxes, and that's all.
Now we will have access to the MySQL server. If we explore the list of linked servers we can select our new linked server and navigate and see the tables of the linked MySQL database.
We can use it in SQL queries by using the T-SQL function openquery as this:
select [campo,] from OPENQUERY([servidor vinculado], [select mysql])