Unable to use fully qualified table name in dynamic SQL

Question

I'm trying to update a table on several remote servers by iterating over a list of server names and executing some dynamic SQL. See below.

DECLARE @Sql NVARCHAR(4000)
DECLARE @Server_Name VARCHAR(25)
SET @Server_Name='SomeServer'

SET @Sql='UPDATE ' + @Server_Name + '.dba_sandbox.dbo.SomeTable SET SomeCol=''data'''
PRINT @Sql
EXEC @Sql

Which produces the following output:

UPDATE SomeServer.dba_sandbox.dbo.SomeTable SET SomeCol='data'
Msg 7202, Level 11, State 2, Line 7
Could not find server 'UPDATE SomeServer' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Now SomeServer is a linked server. If I execute the printed out SQL statement it works fine. Note also, in the error message, it thinks the remote server is 'UPDATE SomeServer', rather than 'SomeServer'.

Answer 1

Have you tried brackets?

[SomeServer].[dba_sandbox].[dbo].[SomeTable]

Answer 2

Not sure what is causing it but have you tried creating the update in this form:

update a set somecol = 'data' from someserver.dba_sandbox.dbo.SomeTable a

Answer 3

The issue was I was using

EXEC @Sql

Not

Exec(@sql)

Answer 4

How about using parantheses:

EXEC(@sql);