Do I have to use OpenQuery to query a MySQL Linked Server from SQL Server?

Question

I'm querying a MySQL linked server from SQL Server.

I can query the linked server using OpenQuery as in the following example.

SELECT * FROM OPENQUERY(MyLinkedServer, 'SELECT * FROM SomeTable')

I have tried querying the linked server using a four-part name as in the following example.

SELECT * FROM MyLinkedServer.MyDatabase.DBO.SomeTable

That returns an error stating that "the provider does not expose the necessary interfaces to use a catalog or schema."

Is there any way to query a linked server without using OpenQuery?

Thanks!

Update: Per Shredder's answer, the correct syntax is as follows.

SELECT * FROM MyLinkedServer...SomeTable

`EXEC('SELECT * FROM SomeTable') AT MyLinkedServer` - is far better imho because it executes MySQL quite directly and doesn't do weird stuff like OPENQUERY which can lead to strange errors. — low_rents, Jul 17 '15 at 12:17

Nick Rolando · Accepted Answer · 2011-11-18T19:10:45.887

It is possible, but MS recommends that you use the openquery:

This method lets SQL Server send the command directly to the linked OLAP provider, without trying to parse it.

You can have a look at the examples here under "Linked server examples with four-part names"

Also, I found this:

If you receive these error messages, a table may be missing in the [Linked DB] schema or you may not have permissions on that table.

I would verify that your mysql db has given access rights to your sql server.

1 Answers1