asked on

Perplexed! Trying to link a MySQL server to MS SQL 2005

I think I have read about every post on EE (and the Internet) regarding this subject. I must be dense....
I have a SQL 2005 server that I want to link a MySQL database to that resides on a different server. I then want to be able to run queries against the linked MySQL database from the SQL 2005 server.

I have added the MySQL sever to the Linked Servers by right clicking and adding a new server.
Linked server: Ciscoworks
Other data source
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product name: Ciscoworks
Data source: Cisco
Provider string: Driver={MySQL ODBC 3.51 driver};SERVER=myServerIPAddress; Option=3; DataBase=TrueControl; User=MyUsername;Password = mypassword;
Location (left blank)
Catalog (left blank)

When I run sp_testlinkedserver ciscoworks I get the error:

OLE DB provider "MSDASQL" for linked server "Ciscoworks" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Ciscoworks".
-------
I have tried a LOT of different options the past 3 hours so I might be over complicating things.

Suggestions?

Guy Hengel [angelIII / a3]

empty the data source property. the connection string holds it all.
now, I assume (but want to verify with your) that the mysql odbc driver IS installed on the sql server box, and that a test connection in the odbc administration tool is working.

side question: is your sql server box 32 bit or 64 bit?

edrz01

ASKER

Thanks Angellll (and congrats!)

The server is 32 bit.
I logged into the server that has the MySQL database and looked in Data Sources (ODBC). Under System DSN I see one I created called Ciscoworks and the driver is MySQL ODBC 3.51 Driver. When I click on configure I see all of the settings I previously put into it. I can click on Test and it works.

On my SQL 2005 server I checked and you were right - it wasn't installed (thought I did it already). I added it and worked through the adding account with the @servername issue. I was finally able to get the MySQL ODBC connector on my SQL2005 server to connect to the MySQL server.

So now on my SQL2005 server I have a System data source called Ciscoworks....
When I try to execute a query from SQL Management Studio like

SELECT * FROM [ServerNameGoesHere]...[truecontrol.rn_device]

I get this message:

OLE DB provider "MSDASQL" for linked server "ServerNameGoesHere" returned message "[MySQL][ODBC 3.51 Driver]Access denied for user: 'CM@F.Q.D.N' (Using password: YES)".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "ServerNameGoesHere".

Any other ideas? Getting oh, so close.....

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

edrz01

ASKER

Angel, you are a genius! That worked, once I granted permission to the user it worked.