23

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?

Mauker
  • 11,237
  • 7
  • 58
  • 76
Kevin
  • 491
  • 1
  • 4
  • 6

7 Answers7

27

you can use the statement below

select * from [linkedServerName]...[databaseName.TableName]

but before executing the code above ,, you have to do some changes ..

In the SSMS

SSMS -> Expand "linked servers" Folder -> open Provider folder -> find MSDASQL and gets it's property

Then check "Level Zero Only" press Ok

Then execute the above query and Enjoy it !!!

Ajay2707
  • 5,690
  • 6
  • 40
  • 58
vahid basirat
  • 445
  • 5
  • 7
  • 2
    IMHO THIS post (and PST's one) is the most importat one: the syntax is tricky, I couldn't get my queries work until I realized that [database.table] must be quoted as a single entity by the point of view of SQLServer. – Giuseppe Guerrini May 14 '18 at 07:18
  • from where did you get [databaseName.TableName] ? with more intuitive [databaseName].[TableName] it does not work. If I could I give 10 points – Ozzy Dec 04 '18 at 13:51
26

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”

Kevin
  • 491
  • 1
  • 4
  • 6
7

Try like this:

SELECT * FROM [Linked_Server]...[db_name.table_name]

Working properly, however there are the problems of converting data types. Safer and more reliable to use is OPEQUERY.

SELECT * FROM OPENQUERY([Linked_Server], 'SELECT * FROM db_name.table_name')
PST
  • 346
  • 2
  • 5
5

You should be able to simply query the linked server directly.

select * from mylinkedserver.database.schema.mytable

EDIT:

Try with the three dot notation as noted in this post: http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/

SELECT * FROM MYSQLAPP...tables

Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "MySQLApp" reported an error. The provider did not give any information about the error. Msg 7312, Level 16, State 1, Line 1 Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "MySQLApp". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

This “four-part name” error is due to a limitation in the MySQL ODBC driver. You cannot switch catalogs/schemas using dotted notation. Instead, you will have to register another DSN and Linked Server for the different catalogs you want to access. Be sure and follow the three-dot notation noted in the example query.

Community
  • 1
  • 1
duffn
  • 3,690
  • 8
  • 33
  • 68
  • 2
    When I execute SELECT * FROM [Linked_Server].Database_name.DBO.Table_Name I get the following error: Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "Linked_Sever". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema. – Kevin Aug 12 '15 at 15:29
  • When I try select * from Linked_Server...Table_Name. I get An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "Linked_Server". – Kevin Aug 12 '15 at 16:00
5

There is an important point for using this:

SELECT * FROM [Linked_Server]...[db_name.table_name]

You must go on

Linked Server -> provider-> MSDASQL:

and make sure these three options have been checked

  • Dynamic Parameter
  • Level zero only
  • Allow inprocess

https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153024

Vineeth Sai
  • 3,389
  • 7
  • 23
  • 34
sanaz amini
  • 49
  • 1
  • 2
1

This solution is great for querying small tables, however it seems that it doesn't use indexes, so getting even few rows from large tables, even by field indexed on the remote server takes ages.

So - correct me if I'm wrong - for large datasets it's still better to use OPENQUERY, as the query is evaluated and optimized on the remote server, using indexes and so on.

Marcin
  • 137
  • 1
  • 10
0

In case anyone is still having trouble with this...I had to go into the linked server properties -> Server Option and change RPC and RPC Out to true. Then I could run with like this [linked server]...[table]

Caitlyn
  • 1