Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    22

    Unanswered: Linking from SQL server to MySQL

    Hi all, hoping someone can help.
    I have created an ODBC connection to a remote MySQL database.
    I can then go into DTS wizard and specify this ODBC connection, it all goes through fine and I can see the tables on the remote server and import data.
    The problem arises when I try and create a linked server using the same ODBC connection. I get the following error, when I try to view the tables.
    I am so stuck. Any help would be very much appreciated.
    Attached Thumbnails Attached Thumbnails error.bmp  

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first guess would be a permissions issue in the MySQL login used within the DSN, or an IP permission problem in the MySQL login. I don't have any easy way to set up a test case this week, but those would be where I'd start looking.

    -PatP

  3. #3
    Join Date
    Dec 2004
    Posts
    22

    Thanks

    Really don't know a lot about MySQL permissions so any more help would be great, so I can go back to are website designers with suggestions of what the problem might be on there end.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The security and permissions for MySQL work a lot different than they do for SQL Server.

    The IP address figures prominently into MySQL permissions. A login might have one set of permissions at one ip address or range (say inside the firewall), and drastically different permissions at others. If you ran your DTS package from a machine with an IP address that was "blessed", you could have one set of permissions, but when making a linked server connection on the SQL Server (which would have another address), the same MySQL id might have much less permission.

    Another possibility is that the ODBC driver on the SQL Server might not be current. It has been relatively recent that the MySQL team has put any effort into making the ODBC drivers compatible with Windows 2003 Server.

    The biggest problem at this point is that there are so many ways for the connection to go bad, and the MySQL ODBC drivers still aren't very good at giving usable diagnostics (as you've noticed!). The problem is almost certainly solvable, but without help from someone pretty technically knowledgable that understands both your NT and your MySQL configuration, it may take many tries to work out the problem and its solution.

    -PatP

Posting Permissions

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