By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,648 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Truncate Table On Linked Server?

P: n/a
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
May 15 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Neil (no****@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, es****@sommarskog.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
May 15 '06 #2

P: n/a
Thanks.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
Neil (no****@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, es****@sommarskog.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

May 15 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.