Can drop temptable from different server?

Question

Post reply

Can drop temptable from different server?

naunton

Old Hand

Points: 321
More actions
October 7, 2010 at 2:50 am

#227322

Dear all,
I want to know that can I drop temp table from different server?
tables are existed in Server1
but, I will create storeprocedure to drop these table from Server2
if (exists(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME = 'TEMP1'))
begin
Drop TABLE .dbo.TEMP1
end
my problem is I can't pass the servername(Server1) to check if temp tabels are exist or not.
Pls,Is there anyway to drop table from different server?
Thank you.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Try this:

IF (EXISTS ( SELECT 1

FROM LINKED_SERVER_NAME.REMOTE_DATABASE_NAME.INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND TABLE_NAME = 'TEMP1' ))

BEGIN

EXECUTE('DROP TABLE REMOTE_DATABASE_NAME.dbo.TEMP1') AT LINKED_SERVER_NAME

END

-- Gianluca Sartori

naunton Old Hand Points: 321 More actions · Answer 2

Thank you Gianluca Sartori.

when i run this

EXECUTE('DROP TABLE REMOTE_DATABASE_NAME.dbo.TEMP1') AT LINKED_SERVER_NAME

got error : Incorrect syntax near 'AT'.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Are you sure you're working on SQLServer 2008? With version 2005+ this should work.

-- Gianluca Sartori

naunton Old Hand Points: 321 More actions · Answer 4

Oh sorry..

I'm running it on sql2000.

but, when I run on sql2008

got error : 'Linkserver' is not configured for RPC.

Think my DBA need to do something configure on linkserver.

May I ask another question here?

Can I create table to remote server? If can create, what permission or option must have when create linkserver?

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

naunton (10/11/2010)
Oh sorry..
I'm running it on sql2000.
but, when I run on sql2008
got error : 'Linkserver' is not configured for RPC.
Think my DBA need to do something configure on linkserver.

Yes, RPC has to be anabled by DBA to perform this task.

May I ask another question here?
Can I create table to remote server? If can create, what permission or option must have when create linkserver?

If it is a work table to perform a particular task, I suggest using a temp table (#tableName).

If it is a real permanent table, ask your DBA to create one.

-- Gianluca Sartori

naunton Old Hand Points: 321 More actions · Answer 6

Thank you again, accept your idea to create #tablename.

when I run this for testing

select top 1 * into [linkserver1].dbname1.##test from table1

the message result show as below and it create temptable in current server

Database name 'linkserver1' ignored, referencing object in tempdb.
(1 row(s) affected)

Actually I want it to create in remote server.

Any idea pls?

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

naunton (10/11/2010)
Thank you again, accept your idea to create #tablename.
when I run this for testing
select top 1 * into [linkserver1].dbname1.##test from table1
the message result show as below and it create temptable in current server
Database name 'linkserver1' ignored, referencing object in tempdb.
(1 row(s) affected)
Actually I want it to create in remote server.
Any idea pls?

I think you will have to pre-create the temp table explicitly (CREATE TABLE #test, not SELECT/INTO) and then insert with INSERT INTO/SELECT.

-- Gianluca Sartori

naunton Old Hand Points: 321 More actions · Answer 8

yes, it is right had to created temp table on that server first and then insert into it from linkserver.

Thank you.