mysqlsql-servert-sqllinked-server

Linked Server - Can see tables but not columns

I am trying to query a linked server, when I expand object explorer in SSMS, I can see all db's on the server and all tables within the respective db's but cannot expand to see the columns. I also cannot run any queries with the following errors:

1 - If I right click on the table name and click script table as - select to - new query window, I get the following error

[LinkedServerName].[singhm]..[testtable] contains no columns that can be selected or the current user does not have permissions on that object.

2 - If I run the an openquery statement as follows: 

select * 
from openquery(LinkedServerName ,'select * from [singhm]..[testtable]')

I get the following error:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".

For context purposes, My linked server is an ODBC connection to a MySQL db datasource.

I would be grateful for any advice and/or direction regarding this matter.

Many thanks, Manpaal Singh

Solution

  • I needed to download and install a different odbc provider. I can now query the linked server using the following syntax.

    select top 10 * from openquery(MYSQL,'select * from singhm.testtable')