February 14, 2006 at 1:39 pm
February 14, 2006 at 2:00 pm
Hello Donna,
If you are using SQL 2000, then Books Online is the best resource to find information. Type "Linked Servers" and "Truncate Table" as search criteria.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_4uuq.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_2hk5.asp
Thanks and have a nice day!!!
Lucky
February 15, 2006 at 10:17 am
It is not possible. You can select, insert, update and delete (with proper linkage and permissions of course) but if you attempt a truncate statement you will receive the following error:
Server: Msg 117, Level 15, State 1, Line 1
The object name 'mylinkedserver.tempdb.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
based on the following SQL statement:
truncate table mylinkedserver.tempdb.dbo.t1
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 15, 2006 at 12:47 pm
However, you could switch to calling sp_executesql remotely, like so:
execute mylinkedserver.tempdb.dbo.sp_executesql "TRUNCATE TABLE dbo.t1"
February 15, 2006 at 1:31 pm
I stand corrected (there's always more than one way to do something in SQL). The one fly in ther ointment is that the user executing the command must be a dbo.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 15, 2006 at 2:30 pm
Thanks everyone for your replies. I knew DDL was not supported by linked servers, but could not find specifically that TRUNCATE is a DDL statement. I found in Henderson's book, Guru's Guide to Transact-SQL in chapter 4, that TRUNCATE is definately DDL and can show it to the developer so they can't argue. Since there are so few rows in the table, a delete will work fine.
Best Regards,
February 15, 2006 at 3:02 pm
Thank you. I learned something new today, that 'truncate' is a DDL statement ! I thought it might be DML
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 6, 2008 at 11:06 am
thanks guys, this post really helped and i learned a nw thing.
Viewing 8 posts - 1 through 8 (of 8 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