Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2005
    Posts
    5

    Unanswered: How to create a linked server to MySQL from MsSQL?

    I can create a linked server to another MsSQL from MsSQL,but encounter error when create a linked server to MySQL:
    Error 7399:OLE DB provider 'MSDASQL' reported an error.
    Data source name not found and no default driver specified.
    ......

    Anyone can help me?
    Thanks!

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up re

    try this step,

    1. Installed MyODBC driver version for windows,u can download from mysql site
    2. Create a system ODBC DSN using this driver to a specific server and database.

    3. In SQL Server 2K Enterprise Manager, created a linked server using Microsoft OLE DB Provider for ODBC Drivers and specified the DSN in the Data Source.

    and select query should be,

    SELECT * FROM OPENQUERY(linkedservername, 'database.table')

    or

    SELECT * FROM OPENQUERY(linkedservername, 'select * from table)




    ----cheers,
    ---http://mallier.blogspot.com

  3. #3
    Join Date
    Nov 2005
    Posts
    5
    Yes,I did as you said.But still get this error.

    The setting are:
    Server Type:Microsoft OLE DB Provider for ODBC
    DataSource:mydsn
    Provider stringATABASE=mydb;DSN=mydsn;SERVER=myserver;UID=myuid;P WD=mypwd
    Location:myserver
    Catalog:mydb

    Something is wrong?

  4. #4
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up Re

    I dont have Mysql here,
    try this,

    Server Type:Microsoft OLE DB Provider for ODBC
    DataSource:mydsn

    and remove other properties.


    ----cheers,
    ---http://mallier.blogspot.com

  5. #5
    Join Date
    Nov 2005
    Posts
    5
    Even I linked to the MsSQL server NOT MySQL,
    if put myserver in data source,it's ok.
    but if put mydsn in data source,it got same error.

  6. #6
    Join Date
    Nov 2005
    Posts
    5
    Sorry,I created dns in wrong location.
    After I corrected this mistake,linking to MsSQL is ok,but encountered not responding when link to MySQL.(I've verified that the dsn is ok.)

  7. #7
    Join Date
    Nov 2005
    Posts
    5
    I've fixed this problem,it's due to I didn't enable root access from remote machines.
    Thanks.

Posting Permissions

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