I have a linked server called server02 properly configured on SQL Server 7.0 Service Pack 3 (SP3), and I want to truncate a table on a remote server called server01. When I try to execute the command
TRUNCATE TABLE server01.database01
.dbo.Tablename01
I get the error message that Figure 1 shows. How can I execute TRUNCATE TABLE against a table on a remote server?
TRUNCATE TABLE isn't a command that you can run directly by using the four-part name associated with a linked server. However, you can issue the TRUNCATE TABLE command against the linked server by using the sp_executesql stored procedure. Sp_executesql is designed primarily to help you parameterize a SQL query so that SQL Server can reuse the plan more easily. However, sp_executesql is also valuable when you're running commands against linked servers.
Listing 1 shows an example of how to use sp_executesql to execute SQL commands on a remote server even if the native commands don't directly support linked servers. Although this example demonstrates the execution of TRUNCATE TABLE, you can use this stored procedure to execute almost any type of SQL command. The sp_executesql stored procedure also supports flexible parameter substitution, although the example in Listing 1 doesn't require it. For more information about sp_executesql, see SQL Server Books Online (BOL).