http://www.memestreams.net/users/jello/blogid9868464/
This took me days to get right, so I'm saving someone else the headache:
The most relevant HOWTO document on how to do this is in a PDF here:
http://developer.infi.nl/daniel/Linking%20MySQL%20and%20MSSQL.pdf
I have mirrored it here:
http://www.lucision.com/legal/rjurney/Linking%20MySQL%20and%20MSSQL.pdf
There is a truncated HTML version of this article on the same site, but it stops in the middle of the provider string because there is an unescaped bracket. If you view source, you can see the entire article.
Provider strings are important. You can find them, including one for MySQL, here:
http://www.connectionstrings.com/
Now, I followed the directions in these tutorials and still could not get it to work. What made it work? I restarted SQL Server 2005 and it just all worked immediately, with a query like this:
SELECT * FROM OPENQUERY(LINKEDLUCISION, 'select * from mydatabase.tablename')
GO
Queries of the format SELECT * FROM LINKEDLUCISION.mydatabase..tablename
still do not work. I dunno how to do these, but in my case we will be creating a VIEW that uses the OPENQUERY, so we are pretty much set.
Edited 1 time(s). Last edit at 11/01/2006 05:51PM by Russell Jurney.