March 20, 2011 at 6:24 am
Hi,
Do you know how to create a global temp table on a remote server? I tried to use EXEC (
'Select * into ##tbl') at linkedserver to create a global temp table on the remote server, but I found I can't query anything is we select it via linked server, do you know why? really appriciated if you can share anything with me? Thanks a lot 🙂
Thanks
Lindsay
March 20, 2011 at 10:02 am
A global temp table will only exist as long as there is a process accessing it. My bet is that once your process completes, there is no active connection accessing the temp table and it is removed.
Why do you need a global temp table?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 20, 2011 at 11:22 pm
Thanks a lot Jeffrey.
I only want to use that temp table to compare the data with another table on that remote server. But like you explained, the process has finished once I create the temp table via linked server, and it should't be queried I want to get the data via linked server, right?
Thanks
Lindsay
March 21, 2011 at 12:42 am
Just test as follows:
--========================Linked Server creating
EXEC master.dbo.sp_addlinkedserver @server = N'LENOVO\SQL2005', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LENOVO\SQL2005',@useself=N'False',@locallogin=NULL,@rmtuser=N'USDBA',@rmtpassword='USDBA'
GO
EXEC master.dbo.sp_serveroption @server=N'LENOVO\SQL2005', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LENOVO\SQL2005', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LENOVO\SQL2005', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LENOVO\SQL2005', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LENOVO\SQL2005', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LENOVO\SQL2005', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LENOVO\SQL2005', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LENOVO\SQL2005', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LENOVO\SQL2005', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LENOVO\SQL2005', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LENOVO\SQL2005', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LENOVO\SQL2005', @optname=N'use remote collation', @optvalue=N'true'
GO
--========================Linked Server creating
GO
EXECUTE ('
--create temp table
IF OBJECT_ID(''tempdb.dbo.##SalesTbl'',''U'') IS NOT NULL
DROP TABLE ##SalesTbl
CREATE TABLE ##SalesTbl (SalesID int, SalesName varchar(10));
--data insert
INSERT INTO ##SalesTbl
SELECT 1,''AA''
UNION ALL
SELECT 2,''BBB''
--show data
SELECT *
FROM ##SALESTBL'
,'SalesData')
AT [LENOVO\SQL2005];--Linked Server
GO
March 21, 2011 at 6:25 am
Thanks a lot cherish:)
The query you offered should worked for us, but it won't work if I moved the select query out of the EXECUTE string. seems that they are in different scop, right? but I have to run separately, becasue I will use it to compare the data on my local machine.... is there any way fingure out it?
Thanks
Lindsay
EXECUTE ('
--create temp table
IF OBJECT_ID(''tempdb.dbo.##SalesTbl'',''U'') IS NOT NULL
DROP TABLE ##SalesTbl
CREATE TABLE ##SalesTbl (SalesID int, SalesName varchar(10));
--data insert
INSERT INTO ##SalesTbl
SELECT 1,''AA''
UNION ALL
SELECT 2,''BBB''
--show data
SELECT *
FROM ##SALESTBL'
,'SalesData')
AT [LENOVO\SQL2005];--Linked Server
GO
SELECT *
FROM [LENOVO\SQL2005].tempDB.dob.##SALESTBL
GO
March 21, 2011 at 10:03 am
seems to me this would be a good application for a CTE
March 22, 2011 at 7:05 am
Uripedes,
what do you mean of CTE? this is new for me.
Thanks
Lindsay
March 22, 2011 at 7:23 am
it's Common Table Expression. you can set up a statement that will pull a result set that you can treat as a table. see this link:
March 22, 2011 at 11:37 am
Lindsay, do you really need a temp table on the linked server? Or, do you need a temp table locally so you can compare to your local table?
Instead of trying to create a global temp table on the remote server, try creating a local temp table on your instance instead. For example:
SELECT ...
INTO #myLocalTempTable
FROM linkedserver.database.schema.object
WHERE ...
Once that is complete, you have a local temp table available to perform your comparisons.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 24, 2011 at 6:34 am
Thanks a lot Uripedes and Jeffrey.
Basically, I need to compare the data from the remote server, and all the data should located on that server, but I have to save the compare result to one local DB... and the result only has little data. in order to have a high perf, i hope I can do some operation on the remote server, (save the temp data on the remote server...), and then the remote server only return the finally result to the local DB.. That is why I want to search a way to save the temp table on that remote server, and read that temp table via linked server. Anyway.. we can save all the data into our local server, and do the compare if I can't find a better way. Thanks for your help^_^
Thanks
Lindsay
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply