creating temp table in remote server ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

creating temp table in remote server ?

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-10-26 : 12:51:18

hi I wonder if its possible to create a global temp table in the tempdb database of a remote server, something like this:

select *
into [someRemoteServer].tempdb.dbo.##globalTempTable
from myTable

I tried to do this but was unsuccessful

---------

thank you

heze
Posting Yak Master

192 Posts

Posted - 2006-10-26 : 13:37:35

actually I tried

select *
into [someRemoteServer].##globalTempTable
from myTable

and it works, the problem is that the .##globalTempTable is stored in my local server
when I wury the sysobjects in the remote server, the table is not there

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-26 : 13:38:39

Well is it a permission thing?

What error did you get?

BUT

I question the wisdom of this. Why do you want to do this?

Your example looks like your data is resident locally

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

heze
Posting Yak Master

192 Posts

Posted - 2006-10-26 : 13:48:34

ok X,

I want to join a local table A with a remote table B
I have full admin rights in the local server but only read permissions in the remote
the remote table B is huge so if I do
select * from tableA a left join TableB b
on a.id=b.id

the query takes forever, however I dont need the entire table B,
so
I was thinking on reducing the table B in its own server to only the records that I will need to join,
and then join it and then just bring back to the local server only what I need,
I am able to do
select *
into ##global
from TableB

in the query analyzer when I open it connected to the remote server, so I dont know if it is a permission thing

regarding the error message, there is no error message its only that the ##tableB appears to be stored in my local server rather than in the remote one (at least that is what I assume since i can see it in the local.tempdb..sysobjects but not in the remote.tempdb..sysobjects) if I am correct in my assumption, then I am left with the same preoblem of importing all the rows to my local server and the query takes forever.
Oh by the way, the where clause is not very useful because local Table A is a dated (temporal) table (snapshots) whereas the table B is transactional and has the current state so I either have to do a join or an where id in (some id list)

thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-26 : 16:09:47

There's a better way - just use the OPENQUERY function (see Books Online) to pass the query to the remote server that you would have used to create the temporary table on the remote server and then SQL Server will only get those rows from the remote server.

Note that it is however wrong to assume that SQL Server is bringing the whole table across anyway, when you use a remote table in a query SQL Server will do a very good job of bringing over only the data that is needed for the query. But - if you want fine control over exactly what is brought over use OPENQUERY.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources