Post reply

Unable to establish a linked server from sql server to mysql

  • May 10, 2016 at 7:33 am

    #318811

    Hi,

    I am trying to create a linked server from sql server to mysql (remote)

    Steps I followed are as follows

    1. created a system dsn using 32bit odbc.

    Note :

    I am using windows 7, 64 bit. I installed 32 bit mysql odbc and created a new entry in the system dsn

    I ran the odbc C:\Windows\SysWOW64\odbcad32 and the driver is added correctly

    I also tested the connection while creating the System DSN and the connection was successful

    2. In the sql server, I created the linked server, using the the system dsn i created in step 1.

    3. In the security tab I used, "Be made using security context" and entered the login credentials.

    Note : I made sure the system DSN and "datasource" value in the linked server have the same names

    4. And when I try to connect, I get the error 7303 - datasource name not found and no default driver specified

    I failed to find a solution for the same

    Any help on this would be greatly appreciated

    Regards

    Rathi

  • May 10, 2016 at 8:07 am

    #1877795

    this page on the forums is my go to for linked servers to MySQL.

    if you are running 64 bit SQL, you need the 5.1 64 bit MySQL drivers. 32 bit will not cut it.

    the 13 page forum post here has lots of details:

    http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx

    this is my script i use, a stored procedure and a call to it like this,after digging through the 13 pages of the post:

    EXEC sp_CreateLinkedServerToMySQL

    @linkedservername ='Linky_To_MySQL',

    @mysqlip='127.0.0.1', --Local, so use the loopback

    @dbname = 'SandBox', --I actually Created this database

    @username='root', --The default mySQL user

    @password ='NotTh3RealPassword' --The Passowrd I changed to at install of MySQL

    EXEC sp_tables_ex Linky_To_MySQL

    http://www.sqlservercentral.com/Forums/FindPost1442435.aspx

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 11, 2016 at 1:32 am

    #1878062

    Amazing !! Thank you , it worked.

    Now, is it possible to query mysql without openquery?

    The 4 part name convention did not work

    Conceptually schema and database name in mysql is the same

    Thanks

    Rathi

  • May 11, 2016 at 5:28 am

    #1878109

    Once you have the Linked Server in place I have found a few different ways of interacting with the mysql database:

    Select Statements:

    select * from mysql5...country

    select * from OPENQUERY(mysql5, 'select * from country')

    Insert Statements:

    insert mysql5...country(code,name)

    values ('US', 'USA')

    insert OPENQUERY(mysql5, 'select code,name from country;')

    values ('US', 'USA')

    Other Statements:

    EXEC('truncate table country') AT mysql5;

    i also found you cannot bounce around to multiple databases in myql the way you can in a SQL linked server. i needed a separate linked server for each database.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 12, 2016 at 2:22 am

    #1878461

    Hi,

    Thanks again for the quick response.

    I tried with 4 part query. And it keeps throwing an error.

    It says 'Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "linky_to_mysql". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.'

    Also, I am unable to connect to mysql local. it keeps saying access denied to user with password yes.

    I have configured the mysql user credentials and provided the necessary grants. Also, provided grants to the sql server. I am not using the root user. I have created a new user with password

    Please help !!

    Thanks

  • May 12, 2016 at 5:33 am

    #1878482

    rathimittha.mb (5/12/2016)

    Hi,

    Thanks again for the quick response.

    I tried with 4 part query. And it keeps throwing an error.

    It says 'Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "linky_to_mysql". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.'

    Also, I am unable to connect to mysql local. it keeps saying access denied to user with password yes.

    I have configured the mysql user credentials and provided the necessary grants. Also, provided grants to the sql server. I am not using the root user. I have created a new user with password

    Please help !!

    Thanks

    details my friend, otherwise it's all guessing.

    you did provide the exact error, that's perfect, but we need to see exact statements you ran too; that will let us duplicate and test from thousands of miles away.

    the password error is obvious, you'll need to either put in the right password, or change it, or use a different username+password.

    you said in the previous post your linked server was working.

    if you run EXEC sp_tables_ex Linky_To_MySQL*, do you get results? (* = the name you gave the linked server)

    you said you tried a four part name, but show us the command you used. in my example, the linked server points to SandBox. so select * from mysql5...country is SandBox..country but you cannot specify the database name, you have to leave it as an implied lookup.

    that also means you cannot bounce around and try select * from mysql5.{anydb}..{anytable}

    four databases, as far as i know, means you need four linked servers.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 12, 2016 at 8:15 am

    #1878680

    Hi,

    Apologies for not being clear.

    I executed the query this way.

    select * from [linkytomysql][talendshouse][mytable];

    This does not work.

    And with regard to the connection to mysql. The steps you mentioned for creating the linked server worked beautifully to remote mysql. But not for my localhost mysql and the error is access denied as I mentioned in the post earlier

    Any thoughts?

    Thanks

    Rathi

  • May 12, 2016 at 8:31 am

    #1878685

    it should be

    select * from [linkytomysql]...[mytable];

    you cannot specify database or schema.

    can you connect to your local mysql , using the exact same credentials, via mysqladmin?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply