Is it possible to create a temp table on a linked server?

Question

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.

Answer 1

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

Answer 2

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.