Update linked server table based on local table

Question

i'm trying to update a remote table using linked server, based on id of my local table.

I'm trying this:

update [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2
 set [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2.cod = tabela1.codigo
 from [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2 
 join tabela1 
 on tabela1.id=[HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2.id

The multi-part identifier "HQSQLHOM01\SQLHOM01.dba_teste.dbo.tabela2.id" could not be bound.

i tried to update the local server based on the linked server but I still receive errors like the TABLE_NAME could not be bound.

update tabela1 
 set tabela1 .cod=[HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2.cod
 from [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2 
 join tabela1 
 on [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2.id=tabela1.id

Msg 4104, Level 16, State 1, Line 40 The multi-part identifier "HQSQLHOM01\SQLHOM01.dba_teste.dbo.tabela2.cod" could not be bound.

There's a lot of questions like this one on stackoverflow but none of them is working.

tabela1=local

Answer 1

You cant have 5 identifiers, this should do the trick

UPDATE T2
SET T2.cod = T1.codigo
FROM [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2 AS T2
JOIN tabela1 AS T1
 ON T1.id = T2.id

https://technet.microsoft.com/en-us/library/ms190406(v=sql.105).aspx

• Racer SQL: I just need to use SET XACT_ABORT ON because i'm tyring to use begin tran.