Brad Marsh’s Weblog

Creating a Linked Server in MSSQL for a MySQL database
1. Download the MySQL ODBC driver from mysql.com (download from here) and Install MySQL ODBC driver on the MSSQL Server

2. Create a DSN using the MySQL ODBC driver

Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC)

> Click on the System DSN tab
> Click Add
>Select the MySQL ODBC Driver
> Click Finish

On the Login Tab:
> Type a name for your DSN.
> Type the server name or IP Address into the Server text box.
> Type the username needed to connect to the MySQL database into the user text box.
> Type the password needed to connect to the MySQL database into the password text box.
> Select the database you want to be able link to.

> make sure you test your DSN by Clicking the ‘Test’ button

NOTES: If your test was unsuccessful, you may need to add the server you are connecting from to the allowed list, and ensure the user you are using has privileges to the database you are trying to link to

3. Creating a Linked Server in SSMS (SQL Server Management Studio) for the MySQL database

there is 2 ways of doing this,

1. Using the GUI

SSMS (SQL Server Management Studio -> Expand Server Objects
> Right Click Linked Servers -> Select New Linked Server
On the General Page:
> Linked Server: Type the Name for your Linked Server
> Server Type: Select Other Data Source
> Provider: Select Microsoft OLE DB Provider for ODBC Drivers
> Product name: Type MySQLDatabase
> Data Source: Type the name of the DSN you created
On The Security Page
> Map a login to the Remote User and provide the Remote Users Password
> Click Add under Local server login to remote server login mappings:
> Select a Local Login From the drop down box
> Type the name of the Remote User
> Type the password for the Remote User

OR

2. through TSQL

– Add Linked Server
EXEC sp_addlinkedserver ‘mysqlDB’, ‘MySQL’, ‘MSDASQL’, Null, Null, ‘Driver={MySQL ODBC 5.1 Driver};DB=[DB_NAME];SERVER=[HOSTNAME];uid=[USER];pwd=[PASSWWORD]‘

Note: Change items enclosed with [  ] and the bold Items

Now test your linked server

if you are unable to connect there could be sevral problems here are a few things to check

Expand Providers > Right Click MSDASQL > Select Properties
> Enable Nested queries (not needed but handy)
> Enable Level zero only (this one’s the kicker)
> Enable Allow inprocess (this is a MUST)
> Enable Supports ‘Like’ operator (not needed but handy)

Change settings in SQL Server Surface Area Configuration for Features
> Enable OPENROWSET and OPENDATASOURCE support.

Restart SQL Server and SQL Server Agent

Run this TSQL (using the master database)

– Set up login mapping using current user’s security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘mySQLDB‘,
@useself = ‘TRUE’,
@locallogin = NULL
GO

 
Note: change the bold items

Ok so now you should be able to connect

use this to ensure you can query you Link server

– List the tables on the linked server
EXEC sp_tables_ex ‘mysqlDB’
GO

 Note: change the bold items

Now if you need to query your table, you cant use the typical select statements, like

SELECT * FROM data.data1..test
or
SELECT * FROM data.data1.user.test

Seems as though not everything is exposed properly, your not going crazy!

using something like this will work

SELECT * FROM OPENQUERY(data, ‘SELECT * FROM TEST’)

this link explains it a lot better

http://benjolo.com/blog/2003/08/27/mysql-mssql-linked-tables-or-just-kill-me-now/

Twitter This

Usuario: Password: