Error Querying MySQL server linked to SQL Server 2008

Question

I've created a linked server in MS SQL Server 2008 to a remote MySQL server. When I try to query any tables, I get an error:

.tablename. contains no columns that can be selected or the current user does not have permissions on that object.

Does anyone know why this happens?

UPDATE: Looks like this is a known issue with MSSQL SERVER 2008

http://support.microsoft.com/kb/971261

• user11045: This condition existed but the data was still accessable up until release of MySQL odbc 5.1.11 Now the data is not visisble until you use the OPENQUERY syntax

Answer 1

it looks like a permission issue with the authentication mode you're using against your mysql server. You can test the linked servers using the stored procedure "sp_testlinkedserver" - details here. See if you can take data from your linked server using simple select statement: "select top 10 * from server.database.schema.table".

The MS error is for the action "Script as" - when you want to script the schema of an object, not for simply selecting data.

Answer 2

I've just stumbled on this post while encountering the same issue, this link shows how to link up properly. I'm using 5.2 connector and it walks you through each setting you need. http://www.packtpub.com/article/mysql-linked-server-on-sql-server-2008

• ypercubeᵀᴹ: Perhaps you can add the details here as well.

Answer 3

You are querying it the wrong way.

Do this:

SELECT * FROM OPENQUERY(My_Linked_Server_Name, 'select * from My_Linked_Server_Name.My_Table_Name');

enter image description here