Geek Answers Handbook

SELECT * FROM MySQL Linked Server using SQL Server without OpenQuery

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

The following query is just fine.

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

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

+12
source share
5 answers

Found the answer here. Now I can request three-point notations. Thanks

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

Go to the provider settings screen. In SQL Server 2005, you can see the list of providers in the folder above the linked server (provided that you have the appropriate permissions). Right-click on MSDASQL and go to properties. In SQL Server 2000, the Provider Settings button is located in the dialog box where you create the linked server. Check the box next to "Level 0 only."

+17
source

you can use the instructions below

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

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

in SQL Server Management Studio, go to the "Linked Servers" folder, open the "Providers" folder, find MSDASQL and get its property, then select the "Only Zero Level" checkbox, click Ok ... then run the query and enjoy it.

+10
source

Try it like this:

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

We work correctly, but there are problems with the conversion of data types. It is safer and more reliable to use OPEQUERY.

 SELECT * FROM OPENQUERY([Linked_Server], 'SELECT * FROM db_name.table_name')
+5
source

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

 select * from mylinkedserver.database.schema.mytable

EDIT:

Try with three-point 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

Message 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for the linked server "MySQLApp" reported an error. The provider did not give any error information. Msg 7312, Level 16, State 1, Line 1 Incorrect use of schema or directory for OLE DB provider "MSDASQL" for linked server "MySQLApp". A four-part name was provided, but the provider did not provide the necessary interfaces for using the catalog or schema.

This four-part error is due to a limitation in the MySQL ODBC driver. You cannot switch directories / schemes using dotted notation. Instead, you will have to register another DSN and associated Server for the different directories that you want to access. Be sure and follow the three-point designation indicated in the sample request.

+4
source

There is an important point to use this:

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

You have to go

Linked Server → provider-> MSDASQL:

and make sure that these three parameters have been checked

  • Dynamic parameter
  • Level 0 only
  • Allow In Process

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

0
source

More articles:


All Articles