SQL server linked server update

Currently working on a script which can update a number of different tables across linked servers. I use 08/12 SQL server management studio.

I am having an issue where I have tried to set up the linked server string as a variable, so I can set this at the beginning and reference when needed through the script. I have been working on the assumption I can declare this as a local variable, as I have for other parts of my script that are updating the local table/server, but when running the script, I receive an incorrect syntax error for the section of code my linked server string variable is located.

My code is structured as:

declare @string varchar(max)

set @string = '[server,instance].[database].dbo.table1'

update @string
set field = updatevariable
where record = identifier

Is it possible to use a linked server as a variable?

Solution

You can dynamically construct SQL Update string, and then pass it to exec. Example:

declare 
  @srv nvarchar(max) = N'[server].[db].[dbo].[table]',
  @id int = 100,
  @value nvarchar(max) = N'some value',
  @sql nvarchar(max)

set @sql = N'update ' + @srv 
         + ' set field = ''' + @value + ''' '
         + ' where record = ' + cast(@id as nvarchar)

exec(@sql)