Check for Existence of and Delete Temp Table on CROSS SERVER

[kjv1611]

Is there an easy way to use this setup or similar on a CROSS SERVER?
[CODE SQL]IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL DROP TABLE #MyTempTable[/CODE]

I'm able to do it on the current server, but apparently not the connected server?

I know I can create temp tables on the connected server. For instance, this works:

SELECT 1 AS TestID INTO ConnectedServer.#Test
SELECT * FROM ConnectedServer.#Test

But I can't seem to get anything to work to check for existence of and delete that same temp table. I have tried these variations so far with no luck:

IF OBJECT_ID('ConnectedServer.tempdb..#Test') IS NOT NULL DROP TABLE ConnectedServer.Test

and

IF OBJECT_ID('ConnectedServer.tempdb.#Test') IS NOT NULL DROP TABLE ConnectedServer.Test

and

IF OBJECT_ID('ConnectedServer.#Test') IS NOT NULL DROP TABLE ConnectedServer.Test

Can anyone point me in the right direction? Does this method just not work on cross server operations, or else is there a different way I can carry out the same cleanup task? I basically want to check for existence of and delete the temp tables up front, in case I (or someone else behind me) ends up running the script multiple times in testing.

Thanks for any thoughts.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[jbenson001]

Check this link and scroll down to the code that use if exists( ...)
I think that is what you will need to use

http://stackoverflow.com/questions/...ete-if-it-exists-before-creating-a-temp-table

 

[kjv1611]

Thanks. I did think about that setup, but it doesn't seem to work either. It doesn't mention anything about a CROSS SERVER script.

Here's the code I tried to no avail, based on that snippet:

IF EXISTS (
     SELECT * FROM ConnectedServer.tempdb.dbo.sysobjects o
     WHERE o.xtype IN ('U')
          AND o.id = OBJECT_ID(N'tempdb..#Test')
)
DROP TABLE

And I modified it further just in case as well:

IF EXISTS (
     SELECT * FROM ConnectedServer.tempdb.dbo.sysobjects o
     WHERE o.xtype IN ('U')
          AND o.id = OBJECT_ID(N'[highlight #FCE94F][b]ConnectedServer.[/b][/highlight]tempdb..#Test')
)
DROP TABLE

Any other suggestions?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[fredericofonseca]

first of all don't use the term cross server - sql server naming is linked server

second - not possible. temp tables are local to the instance on which the sql referencing it is being executed against. (worded like this as it is possible to create/use a temp table on a linked server by using openquery)

see

http://stackoverflow.com/questions/2209556/is-it-possible-to-create-a-temp-table-on-a-linked-server

for some info on it.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[kjv1611]

Yeah, thanks on the CROSS SERVER vs LINKED SERVER. I've had CROSS SERVER used so much around me that I'm accustomed to using that now. What would be the appropriate terminology for JOINING across linked servers just to be sure?

And I tried using the EXECUTE() AT LinkedServer command and that went nowhere as well. It seems crazy to me.. I mean if you can create a temp table on the linked server, why can't you also delete/drop one?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Hold the phone! I believe I just found the answer. It CAN be done, and this blog article describes how:

http://www.mssqltips.com/sqlservertip/1757/dynamic-sql-execution-on-remote-sql-server-using-exec-at/

I tested a sample and it works! Here's how I tested:

EXEC (
	'USE tempdb
	IF OBJECT_ID(''dbo.#Table1'') IS NOT NULL
	DROP TABLE dbo.#Table1
	SELECT 1 AS TEST INTO dbo.#Table1
	SELECT * FROM dbo.#Table1'
	) AT MyLinkedServer

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SaltyTheFrog]

Hold the phone! I believe I just found the answer. It CAN be done, and this blog article describes how:"

I love when someone says categorically something can't be done and then someone shows it can

Sometimes when I'm posting for help, after doing my research, I'll mention it appears something can't be done. That usually inspires someone to find a way to do it. Maybe not always a best practice but something that works. Programmers just seem to be motivated by someone telling them something can't be done. We are on SQL 2005 so that wouldn't work for us though...