Unable to execute stored procedure to insert into table on linked server

sql, sql-server-2008

Solution

The syntax of the `TRUNCATE TABLE` command, as per the manual, is this:

TRUNCATE TABLE 
    [ { database_name .[ schema_name ] . | schema_name . } ]
    table_name
[ ; ]

As you can see, this command only supports names consisting of no more than three components, i.e. you can't specify a linked server name with `TRUNCATE TABLE`.

One solution could be to use a remote call to `sp_executesql`, like this:

EXECUTE SERVERB.DatabaseB.sys.sp_executesql N'TRUNCATE TABLE dbo.TableB';

Problem

I have a table A on Server A which has a set of columns of user data. I need to insert this into table B on Server B. I've written a stored procedure to run this insert statement every night (SQL below). If I select and execute any part of the procedure, then it works fine, but if I attempt to execute the procedure as a whole, then it gives me an error: The object name ServerB.DatabaseB.dbo.TableB has more than the maximum number of prefixes. The maximum is 2. T-SQL statement: ``` IF EXISTS (SELECT * FROM SERVERA.DatabaseA.dbo.TableA) BEGIN TRUNCATE TABLE SERVERB.DatabaseB.dbo.TableB INSERT INTO SERVERB.DatabaseB.dbo.TableB SELECT Firstname, Surname, Username FROM SERVERA.DatabaseA.dbo.TableA END ``` Does anyone have any ideas what I'm doing wrong?

Original source