Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 Aug 22 '12 at 19:43
add comment

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
    
Perhaps you can add the details here as well. –  ypercube Jan 23 at 12:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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