SELECT * FROM MySQL Linked Server 使用 SQL Server 不使用 OpenQuery

SELECT * FROM MySQL Linked Server using SQL Server without OpenQuery

提问by Kevin

I am trying to query a MySQLlinked server using SQL Server.

我正在尝试MySQL使用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 OpenQuerycall?

是否可以在不使用OpenQuery调用的情况下运行相同的查询？

回答by Kevin

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

在这里找到了答案。现在我可以进行三点符号查询。谢谢

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

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”

转到提供程序选项屏幕在 SQL Server 2005 中，您可以在链接服务器上方的文件夹中看到提供程序列表（假设您具有适当的权限）。右键单击 MSDASQL 并转到属性。在 SQL Server 2000 中，提供程序选项按钮位于创建链接服务器的对话框中。选中“仅零级”框

回答by vahid basirat

you can use the statement below

你可以使用下面的语句

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

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

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

但是在执行上面的代码之前，你必须做一些改变..

in the SQL Server Management Studio , go to "linked servers" Folder , open Providers Folder , find MSDASQL and gets it's property then check "Level Zero Only" press Ok ... then execute above query and Enjoy it !!!

在 SQL Server Management Studio 中，转到“链接服务器”文件夹，打开 Providers 文件夹，找到 MSDASQL 并获取它的属性，然后检查“仅零级”按确定...然后执行上述查询并享受它！

回答by PST

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.

正常工作，但是存在转换数据类型的问题。使用起来更安全、更可靠的是 OPEQUERY。

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

回答by duffn

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/

尝试使用本文中提到的三点符号：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.

消息 7399，级别 16，状态 1，第 1 行链接服务器“MySQLApp”的 OLE DB 提供程序“MSDASQL”报告错误。提供者没有提供有关错误的任何信息。消息 7312，级别 16，状态 1，第 1 行对于链接服务器“MySQLApp”的 OLE DB 提供程序“MSDASQL”的架构或目录的使用无效。提供了一个由四部分组成的名称，但提供程序没有公开使用目录或模式所需的接口。

这个“四部分名称”错误是由于 MySQL ODBC 驱动程序的限制造成的。您不能使用点分符号切换目录/模式。相反，您必须为要访问的不同目录注册另一个 DSN 和链接服务器。请务必遵循示例查询中注明的三点符号。

回答by sanaz amini

There is an important point for using this:

使用这个有一个重要的点：

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

You must go on

你必须继续

Linked Server -> provider-> MSDASQL:

链接服务器 -> 提供者 -> 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

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