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.
Global temp tables can be referenced from the same connection or a different connection so long as the global temp table has not gone out of scope. The database connection is the same when a temp table is created and referenced from a script within the same SSMS tab.
So in such cases, SQL Server provides us with a feature called temporary tables which can be created at runtime and used to store the data temporarily. They can be used to create a workspace for processing the data inside stored procedure or functions. They can be used for complex joins.
SQL Server provides two types of temporary tables according to their scope: Local Temporary Table. Global Temporary Table.
To create a Global Temporary Table, add the “##” symbol before the table name. Global Temporary Tables are visible to all connections and Dropped when the last connection referencing the table is closed. Global Table Name must have an Unique Table Name.
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
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..
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With