CODEWITHSUNDEEP
sqlsql-server-2008
MrC
MrC

Reputation: 217

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

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?

Upvotes: 4

Views: 1418

Answers (1)

Andriy M
Andriy M

Reputation: 77657

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';

Upvotes: 3

Related Questions