Truncate Table On Linked Server?

[Neil]

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

[Erland Sommarskog]

Re: Truncate Table On Linked Server?

Neil (nospam@nospam. net) writes:[color=blue]
> 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[/color]

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.tabl ename') 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@sommarsk og.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

[Neil]

Re: Truncate Table On Linked Server?

Thanks.

"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns97C480A DED55Yazorman@1 27.0.0.1...[color=blue]
> Neil (nospam@nospam. net) writes:[color=green]
>> 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[/color]
>
> 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.tabl ename') 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@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]