Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003

    Question Unanswered: Linked Server PROBLEM For MySQL Database

    I created a ODBC DSN on my Windows Server for a remote UNIX box MySQL database. The TEST button returns the 'successful' message for the MySQL databaser. Also, when I use an ODBC DSNless string I found, that also successfully connects when I use it in some ASP pages.

    However, I can not get a successful connection in the Enterprise Manager trying to use the DSN I created nor the DSNless string I used in the ASP pages. Can anyone tell me how to config the Enterprise Manager Linked Server settings listed below to connect to the MySQL database(s).

    I added a new linked server, and selected the Microsoft OLEDB Provider for ODBC Drivers as the Provider name.

    Does anyone know how do I configure the items below:

    On the General tab:" option.
    Product Name: xxxxxxxxxxxxxxxxxx
    Data Source: xxxxxxx
    Provider String: xxxxxxxx
    Location: xxxxxxx
    Catalog: xxxxx

    On the Security tab:" option.
    Remote Login: xxxxxx
    With password: xxxxxx


  2. #2
    Join Date
    Dec 2003
    Inland Empire


    I found a wonderful solution to this problem...

    In case the link is gone, here is the way to create a Linked Server to a MySQL database, in a nutshell...

    1) Create a System DSN on the server running MSSQL, connecting to the MySQL database. Let's call it <the_new_mysql_dsn>.

    2) Run the following stored procedure, with appropriate arguments...

    EXEC sp_addlinkedserver @server='<linked_server_label>', @srvproduct='MySQL', @provider='MSDASQL', @datasrc='<the_new_mysql_dsn>'


    You can then open up ol' Enterprise Manager and see the new linked server added in. Right click on it to see the Properties. On mine, the settings showed up as...

    Linked server: <linked_server_label>
    Provider name: Microsoft OLE DB Provider for ODBC Drivers
    Product name: MySQL
    Data source: <the_new_mysql_dsn>

    Everything else is blank.


    Incidentally, just to try it out, I deleted my new linked server, and then added a new one, using the following settings...

    Linked server: <linked_server_label>
    Provider name: Microsoft OLE DB Provider for ODBC Drivers
    Provider string: DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=<mysql_database>;SERVER=<my sql_server>;UID=;PORT=;OPTION=0;STMT=;

    And it works as well (the connection string was generated within Visual Interdev). I am using no passwords yet, I'm still in the early development phases, but this should handle the basics of connecting to a MySQL database from SQL Server.

Posting Permissions

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