How to truncate table on Linked Server?

Suppose you want to truncate a table on Linked Server.
If you try to execute something like this:

TRUNCATE TABLE LinkedServerName.DbName.dbo.TableName

You will get the following error:
The object name 'LinkedServerName.DbName.dbo.TableName.'
contains more than the maximum number of prefixes. The maximum is 2.

This is because DDL statements are not supported on Linked Servers.
And "Truncate Table" is DDL statement.

However there is a way to do it:
If you have SQL 2005 then try the following:

EXEC('TRUNCATE TABLE DbName.dbo.TableName) AT LinkedServerName

In SQL 2000 and 2005 the following will also work:

EXECUTE LinkedServerName.DbName.dbo.sp_executesql
N'TRUNCATE TABLE dbo.TableName'

Tweet