为什么在本地服务器上使用OPENQUERY不好？

I'm writing a script that is supposed to run around a bunch of servers and select a bunch of data out of them, including the local server. The SQL needed to SELECT the data I need is pretty complicated, so I'm writing sort of an ad-hoc view, and using an OPENQUERY statement to get the data, so ultimately I end up looping over a statement like this:

我正在编写一个应该在一堆服务器上运行的脚本,并从中选择一堆数据,包括本地服务器。 SELECT我需要的数据所需的SQL非常复杂,所以我正在编写一种特殊的视图,并使用OPENQUERY语句来获取数据,所以最终我最终循环遍历这样的语句:

exec('INSERT INTO tabl SELECT * FROM OPENQUERY(@Server, @AdHocView)')

However, I've heard that using OPENQUERY on the local server is frowned upon. Could someone elaborate as to why?

但是,我听说在本地服务器上使用OPENQUERY是不受欢迎的。有人可以详细说明原因吗?

3 个解决方案

#1

Although the query may return multiple result sets, OPENQUERY returns only the first one.

虽然查询可能返回多个结果集,但OPENQUERY仅返回第一个结果集。

OPENQUERY does not accept variables for its arguments.

OPENQUERY不接受其参数的变量。

OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name.

OPENQUERY不能用于在链接服务器上执行扩展存储过程。但是,可以使用由四部分组成的名称在链接服务器上执行扩展存储过程。

If the sp_addlinkedserver stored procedure is used within same script, the credentials used on the remote server are hardcoded into the script, visible to anyone who has a copy

如果在同一脚本中使用sp_addlinkedserver存储过程,则远程服务器上使用的凭据将硬编码到脚本中,对于拥有副本的任何人都可以看到

Reference:

OPENQUERY

#2

In addition to what @OMG Ponies said, it's simply unnecessary. There's no reason to introduce ad-hoc query and distributed transaction semantics when you don't have to. When you use OPENQUERY you take on all of the negative aspects of dynamic SQL, including less predictable plans and the server's inability to accurately track dependencies.

除了@OMG Ponies所说的,它根本就没有必要。没有必要时,没有理由引入即席查询和分布式事务语义。当您使用OPENQUERY时,您将承担动态SQL的所有负面影响,包括不太可预测的计划以及服务器无法准确跟踪依赖性。

OPENQUERY also requires the local user to have permissions to the target server, which is probably not what you want unless it's an administrative script. You can't expect every user of one database to have the same permissions to every other database.

OPENQUERY还要求本地用户拥有目标服务器的权限,这可能不是您想要的,除非它是管理脚本。您不能指望一个数据库的每个用户对每个其他数据库具有相同的权限。

#3

Just a followup.

只是一个后续。

OpenQuery is good when you have to compare or manipulate some rowsets from stored procedures.

当您必须比较或操作存储过程中的某些行集时,OpenQuery很好。

for example if you have to compare results from two servers (test and rollout server) when you migrate from SQL Server 2005 to SQL server 2008 for example, then you can do the following query:

例如,如果在从SQL Server 2005迁移到SQL Server 2008时必须比较来自两个服务器(测试和转出服务器)的结果,则可以执行以下查询:

select * into test_table from OpenQuery(testServer, 'exec testdb.dbo.test_sp');
select * into rollout_table from OpenQuery(rolloutServer, 'exec testdb.dbo.test_sp');

select * from test_table
except
select * from rollout_table;

select * from rollout_table
except
select * from test_table;

to see any discrepancies.

看到任何差异。

#1

Although the query may return multiple result sets, OPENQUERY returns only the first one.