是否可以在链接服务器上创建临时表?

发布于 2024-08-20 23:10:28 字数 154 浏览 7 评论 0原文

我正在对远程链接服务器进行一些相当复杂的查询,如果能够在临时表中存储一些信息,然后对其执行联接,那么这将很有用 - 所有这些都与远程数据有关。在本地创建临时表并通过网络连接它们的速度非常慢。

是否可以强制在远程服务器上创建临时表?假设我没有足够的权限来创建自己的真实(永久)表。

I'm doing some fairly complex queries against a remote linked server, and it would be useful to be able to store some information in temp tables and then perform joins against it - all with the remote data. Creating the temp tables locally and joining against them over the wire is prohibitively slow.

Is it possible to force the temp table to be created on the remote server? Assume I don't have sufficient privileges to create my own real (permanent) tables.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

梨涡 2024-08-27 23:10:28

在我的环境中,这适用于链接到 SQL 2005 SP3 的 SQL 2005 SP3。但是,如果您检查 tempdb,您会发现该表实际上位于本地实例上,而不是远程实例上。我在其他论坛上看到了这个决议,并希望引导您远离这个。

create table SecondServer.#doll
(
  name varchar(128)
)
GO
insert SecondServer.#Doll
select name from sys.objects where type = 'u'


select * from SecondServer.#Doll

This works from SQL 2005 SP3 linked to SQL 2005 SP3 in my environment. However if you inspect the tempdb you will find that the table is actually on the local instance and not the remote instance. I have seen this as a resolution on other forums and wanted to steer you away from this.

create table SecondServer.#doll
(
  name varchar(128)
)
GO
insert SecondServer.#Doll
select name from sys.objects where type = 'u'


select * from SecondServer.#Doll
雅心素梦 2024-08-27 23:10:28

我迟到了 2 年,但您可以使用 sp_executeSQL 并为其提供动态查询来远程创建表来完成此操作。

Exec RemoteServer.RemoteDatabase.RemoteSchema.SP_ExecuteSQL N'Create Table here'

这将在远程位置执行临时表创建。

I am 2 years late to the party but you can accomplish this using sp_executeSQL and feeding it a dynamic query to create the table remotely.

Exec RemoteServer.RemoteDatabase.RemoteSchema.SP_ExecuteSQL N'Create Table here'

This will execute the temp table creation at the remote location..

裸钻 2024-08-27 23:10:28

无法在链接的远程服务器上直接创建临时表。事实上,您不能对链接服务器使用任何 DDL。

有关使用链接服务器的准则和限制的更多信息,请参阅:

分布式查询使用指南(SQL 2008 联机丛书)

一种解决方法(这是我的想法,只有当您在远程服务器上拥有权限时,这才有效)您可以:

  • 在远程服务器上有一个存储过程,该过程将创建一个持久表,其名称基于 IN
  • 参数远程存储过程将运行一个查询,然后将结果插入到该表中,
  • 然后在本地对该表进行查询,执行与所需的任何本地表的任何联接,
  • 在完成后调用远程服务器上的另一个存储过程来删除远程表,这

并不理想,但一个可能的解决方法。

It's not possible to directly create temporary tables on a linked remote server. In fact you can't use any DDL against a linked server.

For more info on the guidelines and limitations of using linked servers see:

Guidelines for Using Distributed Queries (SQL 2008 Books Online)

One work around (and off the top of my head, and this would only work if you had permissions on the remote server) you could:

  • on the remote server have a stored procedure that would create a persistent table, with a name based on an IN parameter
  • the remote stored procedure would run a query then insert the results into this table
  • You then query locally against that table perform any joins to any local tables required
  • Call another stored procedure on the remote server to drop the remote table when you're done

Not ideal, but a possible work around.

莫多说 2024-08-27 23:10:28

是的,您可以,但它仅在连接期间持续。
需要使用EXECUTE AT语法;

EXECUTE('SELECT * INTO ##example FROM sys.objects; WAITFOR DELAY ''00:01:00''') AT [SERVER2]

在 SERVER2 上,以下内容将起作用(1 分钟);

SELECT * FROM ##example

但它不适用于本地服务器。
顺便说一句,如果您在使用 ##example 的第二台服务器上打开事务,则该对象将保留到事务关闭为止。它还会阻止第一台服务器上的创建语句完成。即在 server2 上运行,并且 server1 上的事务将无限期地继续。

BEGIN TRAN
SELECT * FROM ##example WITH (TABLOCKX)

这是学术性多于实际用途!

Yes you can but it only lasts for the duration of the connection.
You need to use the EXECUTE AT syntax;

EXECUTE('SELECT * INTO ##example FROM sys.objects; WAITFOR DELAY ''00:01:00''') AT [SERVER2]

On SERVER2 the following will work (for 1 minute);

SELECT * FROM ##example

but it will not work on the local server.
Incidently if you open a transaction on the second server that uses ##example the object remains until the transaction is closed. It also stops the creating statement on the first server from completing. i.e. on server2 run and the transaction on server1 will continue indefinately.

BEGIN TRAN
SELECT * FROM ##example WITH (TABLOCKX)

This is more accademic than of practical use!

唐婉 2024-08-27 23:10:28

如果内存不是太大问题,您还可以使用表变量作为临时表的替代方案。当运行需要针对链接服务器进行临时数据存储的存储过程时,这对我有用。

更多信息:例如这个表变量和临时表的比较,包括使用表变量的缺点。

If memory is not much of an issue, you could also use table variables as an alternative to temporary tables. This worked for me when running a stored procedure with need of temporary data storage against a Linked Server.

More info: eg this comparison of table variables and temporary tables, including drawbacks of using table variables.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文