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

Question

I make quite complex queries on a remote linked server, and it would be useful to be able to store some information in temporary tables and then perform joins against it - all with the remote data. Creating temporary tables locally and combining them with the wire is overly slow.

Is it possible to create a temporary table on a remote server? Suppose I do not have sufficient privileges to create my own real (persistent) tables.

Answer 1

It is not possible to directly create temporary tables on a linked remote server. In fact, you cannot use DDL against a linked server.

See below for recommendations and limitations on using linked servers.

Distributed Query Considerations (SQL 2008 Books Online)

One job (and from my head, and this will only work if you have permissions on the remote server):

• there is a stored procedure on the remote server that will create a persistent table with a name based on the IN parameter
• The remote stored procedure launches the query and then inserts the results into this table.
• Then you query locally for this table to make any joins to any local tables.
• Call another stored procedure on the remote server to delete the remote table when you are done.

Not perfect, but possible work.

Answer 2

This works with SQL 2005 SP3 related to SQL 2005 SP3 in my environment. However, if you check tempdb, you will find that the table is actually in the local instance, not the remote instance. I saw this as a resolution in other forums and wanted to push you away from it.

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

Answer 3

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] 

In SERVER2 it will work (within 1 minute):

 SELECT * FROM ##example 

but it will not work on the local server. Incidentally, if you open a transaction on a second server that uses the ## example, the object remains until the transaction is closed. It also stops the create statement on the first server. that is, on server2, and the transaction on server1 will continue indefinitely.

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

This is more practical than practical use!

Answer 4

If memory is not very important, you can also use table variables as an alternative to temporary tables. This worked for me when starting a stored procedure with the need for temporary data storage on a linked server.

Additional information: for example, this is a comparison of table variables and temporary tables, including the disadvantages of using table variables.

Answer 5

I am 2 years late for the party, but you can do this with sp_executeSQL and submit it a dynamic query to create the table remotely.

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

This will create a temp table in a remote location.