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.
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.
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.