Can drop temptable from different server?

[Naunt]

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.

[Blue_Boy]

DROP TABLE FROM LinkedServer.YourDB.dbo.#TableName Hope it helps you.

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

[Naunt]

thank you for your help. but, got error like this

The object name 'server1.dbname1.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

and the other fact is I have to check if table is exist or not. if I select from Information.schema I can't put server name before Information.Schema

SELECT 1 FROM INFORMATION_SCHEMA.TABLES

SELECT 1 FROM servername.dbname.INFORMATION_SCHEMA.TABLES (this code not work)

[Blue_Boy]

You have to create linked[^], then you can try to delete temp table

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

[Naunt]

Yah, already link even I can select from it, but not delete.

[Lost User]

naunt wrote:

even I can select from it, but not delete.

The DELETE and the SELECT statement require the same prefixes when referring to a linked server. Did you get the same error when executing the delete, or a different one? Can you post both the command and the error-message?

I are Troll :suss:

[Naunt]

Hi! Eddy Vluggen, sorry I mean I can't drop. Delete is work fine. select & delete work fine.

select * from [linkserver].dbname1.dbo.test
delete [linkserver].dbname1.dbo.test where field1='yyyy'

but, not for drop

DROP TABLE [linkserver].dbname1.dbo.test
got err --> The object name 'linkserver.dbname1.dbo.test' contains more than the maximum number of prefixes. The maximum is 2.

[Blue_Boy]

Worths to check this link.[^]

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

[Lost User]

Seems that you can't directly drop a remote table then. How about creating a sproc in the remote database that takes a tablename and drops it? Could that be called with the three prefixes?

I are Troll :suss: