Drop Table On Linked Server

Question

Post reply

Drop Table On Linked Server

TcW_1978

SSCrazy

Points: 2018
More actions
August 26, 2010 at 9:53 am

#226937

Is there a way to do a drop table on a linked server?
I tried :
DROP TABLE LinkedSrvrName.Database.MyTable
Got :
The object 'LinkedSrvrName.Database.MyTable' contains more than the maximum number of prefixes. The maximum is 2.
I tried :
select * from openquery(LinkedSrvrName, 'DROP TABLE Database.MyTable')
Didn't work either...
Help? Somebody?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Kalyan C SSC-Addicted Points: 413 More actions · Answer 1

You can try this but I am not sure whether this will work or not. I didn't tried yet.

SELECT * FROM OPENQUERY(LinkedSrvrName, 'DROP TABLE Database.MyTab SELECT NULL')

TcW_1978 SSCrazy Points: 2018 More actions · Answer 2

Hi kalyanp79,

kalyanp79 (8/26/2010)
You can try this but I am not sure whether this will work or not. I didn't tried yet.
SELECT * FROM OPENQUERY(LinkedSrvrName, 'DROP TABLE Database.MyTab SELECT NULL')

It doesn't return an error, returns 'NULL' but it doesn't drop the table.

Lowell SSC Guru Points: 323518 More actions · Answer 3

i have this CREATE TABLe in my snippets, so i would assume drop table works the same way:

EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'

GO

EXECUTE ( 'CREATE TABLE AdventureWorks2008R2.dbo.SalesTbl

(SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;

--untested:

EXECUTE ( 'DROP TABLE AdventureWorks2008R2.dbo.SalesTbl' ) AT SeattleSales;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TcW_1978 SSCrazy Points: 2018 More actions · Answer 4

Hi Lowell,

Almost works, Error : Msg 7411, Level 16, State 1 Server is not configured for RPC

Found this article :

http://blog.sqlauthority.com/2007/10/18/sql-server-2005-fix-error-msg-7411-level-16-state-1-server-is-not-configured-for-rpc/

But the server referenced as the 'linked server' is the client's server so I need to check with them what can be done.

Thanks for the help, I really appreciate it.

TcW78

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

TcW_1978 (8/26/2010)
Hi Lowell,
Almost works, Error : Msg 7411, Level 16, State 1 Server is not configured for RPC
Found this article :
http://blog.sqlauthority.com/2007/10/18/sql-server-2005-fix-error-msg-7411-level-16-state-1-server-is-not-configured-for-rpc/
But the server referenced as the 'linked server' is the client's server so I need to check with them what can be done.
Thanks for the help, I really appreciate it.
TcW78

RPC is a setting on your end, not the client's. Check the properties for the linked server (RPC and RPC Out)

TcW_1978 SSCrazy Points: 2018 More actions · Answer 6

Derrick Smith (8/26/2010)
RPC is a setting on your end, not the client's. Check the properties for the linked server (RPC and RPC Out)

Indeed, I thought the error I was receiving needed an intervention on the client's side : Microsoft SQL Server Error : 259 Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.

But found this afterward : http://msdn.microsoft.com/en-us/library/aa258720(SQL.80).aspx

Didn't try it yet... if time's on my side, I will be able to test it this afternoon, posting results soon...