Truncate Table Across Linked Server

Question

Truncate Table Across Linked Server

Donna Robbins

Old Hand

Points: 355
More actions
February 14, 2006 at 1:39 pm

#96599

Good Afternoon All,

Can anyone point me to where I can find documentation regarding Linked Servers and DDL? I have a developer who is trying to truncate a table across a linked server. I know that DDL statements are not supported, but want to have the documentation in hand when I make them change the code. Any help is appreciated.

Best Regards,

Donna Robbins

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

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

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

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

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 2

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."

Perry-300990 Default port Points: 1401 More actions · Answer 3

However, you could switch to calling sp_executesql remotely, like so:

execute mylinkedserver.tempdb.dbo.sp_executesql "TRUNCATE TABLE dbo.t1"

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

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."

Donna Robbins Old Hand Points: 355 More actions · Answer 5

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,

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

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."

dhawalbatavia Valued Member Points: 60 More actions · Answer 7

thanks guys, this post really helped and i learned a nw thing.