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
Best Answer
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.