Results 1 to 7 of 7

Thread: link to mysql

  1. #1
    Join Date
    Oct 2003
    Posts
    47

    Unanswered: link to mysql

    I tired to create a link 'MYSQL' in a SQL Server using the following code :

    EXEC sp_addlinkedserver
    @server = ‘mysql’,
    @provider = 'MSDASQL',
    @provstr = 'DRIVER={MySQL ODBC 3.51 Driver};SERVER=MySQLServer;UID=root;PWD=;'

    NB: I have a DB by the name of : MYSQL

    There is no errors during creation ...

    The link creation is OK but I am not able to see tables from SQL Server…

    Any Idea ?

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Linked server login mappings can be added using sp_addlinkedsrvlogin and refer to books online for more information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Oct 2003
    Posts
    47
    No it ok for the link creation but we have the following error :

    Msg 7314, Niveau 16, État 1, Ligne 1

    when execution :

    select * from mysql...host

    any idea ?

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    When setting up the linked server you need to map your local login to appropirate remote/destination server's login.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Oct 2003
    Posts
    47
    The mapping is OK ? really, I don't inerstant the pb ?
    Did you try to to this with a MYSQL DB ?

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    You shoudl use OPENQUERY method to access the data.

    However, the openquery() method ALWAYS worked while the suggested method of SERVER.CATALOG.OWNER.TABLE will have some issues.

    Ensure you've MyODBC installed properly on SQL server.
    I don't have any issues in using MySQL as LS.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Oct 2003
    Posts
    47
    Could you please give us an example (with MySQL Server)

    Could you tell us what is :

    SERVER under MySQL
    CATALOG under MySQL
    OWNER under MySQL

    Could you give us a query example with a table from the default Mysql DB.

    Indeed, I successed to have a resultat when using OPENQUERY :

    But it doesn't work every time (it depends of the row nb in the table I think).

    So it is OK for :

    SELECT * FROM OPENQUERY (mysql,'SELECT * FROM db where 1=2')

    But not for :

    SELECT * FROM OPENQUERY (mysql,'SELECT * FROM db)

    Error : Msg 7347

    Help !!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •