Posted by Erland Sommarskog on 05/15/06 13:38

Neil (nospam@nospam.net) writes:
> Can one use Truncate Table on a linked server table? When I try it, I
> get a message that only two prefixes are allowed. Here's what I'm
> using:
>
> Truncate Table svrname.dbname.dbo.tablename

I guess the error message answers your question.

There is also a good reason for this not being permitted. Truncate Table
is a special operation, that may not make sense on some other data source.
Remember that a linked server does not have to be SQL Server.

If you are on SQL 2005, you can say:

EXEC('TRUNCATE TABLE dbname.dbo.tablename') AT svrname

On earlier versions of SQL Server you are probably better off using DELETE
instead, although it could possible to do it with OPENQUERY.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Logging Into Linked Server
• Prev in forum: Truncate Table On Linked Server?
• Thread view: Re: Truncate Table On Linked Server?

[Reply to this message]