Cannot execute stored procedure to insert into table on linked server

Question

I have table A on server A that has a set of user data columns. I need to insert this into table B on server B. I wrote 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 try to execute the procedure as a whole, then it will give me an error:

The object name ServerB.DatabaseB.dbo.TableB has more than the maximum number of prefixes. maximum 2.

T-SQL operation:

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?

Answer 1

The syntax for the TRUNCATE TABLE command is as follows :

 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 cannot specify the name of a linked server with TRUNCATE TABLE .

One solution could be to use a remote call to sp_executesql , for example:

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