August 26, 2010 at 9:53 am
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
August 26, 2010 at 10:13 am
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')
August 26, 2010 at 10:18 am
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.
August 26, 2010 at 10:21 am
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
August 26, 2010 at 11:02 am
Hi Lowell,
Almost works, Error : Msg 7411, Level 16, State 1 Server is not configured for RPC
Found this article :
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
August 26, 2010 at 3:35 pm
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 :
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)
August 31, 2010 at 10:14 am
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...
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy