Linked Server to MySQL Problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan - 23 Jul 2008 00:09 GMT

I am trying to create a linked server from SQL Server 2005 to a MySQL 5
database. On the real database server (SQL Server), I have installed the
ODBC driver for MySQL and created a system DSN. I then added a linked server
from SSMS. From SSMS it appears that it is able to connect and displays all
of the databases and tables that are in the MySQL server that I linked to.

The problem comes when I try to run queries against it.

SELECT * FROM [mylinkedserver].[mydbname].CustomerList
throws this error:
--Msg 208, Level 16, State 1, Line 1
--Invalid object name 'mylinkedserver.mydbname.CustomerList'.

SELECT * FROM [mylinkedserver].[mydbname]..CustomerList
throws this error:
--Msg 7202, Level 11, State 2, Line 1
--Could not find server 'mylinkedserver' in sys.servers. Verify that the
correct server name was specified. If necessary, execute the stored
procedure sp_addlinkedserver to add the server to sys.servers.

What should the syntax be for querying this linked server? What else could
be wrong?

Reply to this Message

David Hay - 23 Jul 2008 13:00 GMT

I'm not a my SQL guru, but I think you are missing part of the name of
the table. you don't have the schema required, it should go like
this. pulling in a lot of data."

server.database.schema.table

Be wary of using 4 part naming, quote from below link
"Another thing to note is that there may be some performance issues if
you are linking servers across your LAN or WAN and also if you are

http://www.mssqltips.com/tip.asp?tip=1095

OpenQuery while a bit more difficult to write at time, is much more
efficent for working with large sets across the WAN.

See Below for some insight to the differences between the two.

http://sql-server-performance.com/Community/forums/p/22814/130233.aspx

Good luck!
David Hay

Reply to this Message