SELECT * FROM MySQL Linked Server using SQL Server without OpenQuery

mysql sql-server linked-server openquery
Kevin picture Kevin · Aug 12, 2015 · Viewed 34.8k times · Source

I am trying to query a MySQL linked server using SQL Server.

The below query runs just fine.

SELECT * FROM OPENQUERY([Linked_Server], 'SELECT * FROM Table_Name')

Is it possible to run the same query without using the OpenQuery call?

Answer

Kevin picture Kevin · Aug 13, 2015

Found the answer here. Now I can the three dot notation query. Thanks

http://www.sparkalyn.com/2008/12/invalid-schema-error/

Go to the provider options screenIn SQL Server 2005 you can see the list of providers in a folder above the linked server (assuming you have appropriate permissions). Right click on MSDASQL and go to properties. In SQL Server 2000, the provider options button is in the dialog box where you create the linked server. Check the box that says “level zero only”

Related questions

How does OPENQUERY differ for SELECT and INSERT?

I'm aware that the following query will pull down the result set from a linked server: SELECT * FROM openquery(DEVMYSQL, 'SELECT event_id, people_id, role_id, rank, last_updated FROM event_cast') However, is this the same case when …

Read More
SELECT * FROM Linked MySQL server

I have a SQL Server 2012.(120.120.55.15) Today I linked MySQL server(120.120.55.30) to my SQLServer and gave it a name "MYSQL". In Object Explorer everything seems fine. I can see MySQL server's database "exampleDataBase" and tables in it. But when I try …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More