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

I’ve just skimmed through my stats and found that googling for the above expression is popular so much that you would never expect. Something tells me that most of the people need some reassuring words in English and not this transcript from the black box of an extraterrestrial space ship.

So. This the error message that you see when you try to run a SQL SELECT in a Microsoft SQL server environment on a MySQL database where the MySQL server is defined as a linked server.

When you face this problem you have probably done the first step successfully - defined the link. As far as I’m concerned, the only way to do that is using the MySQL ODBC driver:

EXEC master.dbo.sp_addlinkedserver @server = N’LinkedServerName’, @srvproduct = N’MySQL’, @provider = N’MSDASQL’, @provstr = N’Driver = {MySQL ODBC 3.51 Driver}; DB=[database]; SERVER=[server]; uid=[user_id]; pwd=[password]‘

Having done this, you are able to browse the MySQL server for databases and the databases for tables, views, etc. And then … you’re stuck here without a vague chance to access your data.

Don’t ask. I don’t know either. It may be a feature as well as a bug.

But you can have your data through the openquery command of the T-SQL like this:

SELECT * FROM OPENQUERY(LinkedServerName, ‘SELECT * FROM [table name]‘) AS tbl1

Quite strange. And uneasy. Not running the command itself but passing it as a string to a function is not what you normally do. Not even on linked servers.

Tags: , ,

Leave a Comment