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

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.

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

5 个解决方案

#1

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.

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

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

有关使用链接服务器的准则和限制的详细信息，请参阅：

Guidelines for Using Distributed Queries (SQL 2008 Books Online)

使用分布式查询的准则（SQL 2008联机丛书）

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
在远程服务器上有一个存储过程，它将创建一个持久表，其名称基于IN参数
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.

不理想，但可能的工作。

#2

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.

这适用于在我的环境中链接到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

#3

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

是的，你可以，但它只持续连接的持续时间。您需要使用EXECUTE AT语法;

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

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

在SERVER2上，以下工作（1分钟）;

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.

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

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

This is more accademic than of practical use!

这比实际使用更具有学术性！

#4

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.

更多信息：例如表变量和临时表的比较，包括使用表变量的缺点。

#5

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.

我迟到了2年，但是你可以使用sp_executeSQL完成这个任务，并为它提供一个动态查询来远程创建表。

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

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

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

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

#1