6

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

Improve this question
1
  • 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
    – user11045
    Commented Aug 22, 2012 at 19:43

3 Answers 3

Reset to default
5

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.

Improve this 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

Improve this answer
1
  • 1
    Perhaps you can add the details here as well.
    – ypercubeᵀᴹ
    Commented Jan 23, 2014 at 12:08
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.