1

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

2

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

|improve this answer|||||
  • 1
    Wow it worked. thank you! I think I should know that already. – Racer SQL Oct 20 '16 at 11:54
  • I just need to use SET XACT_ABORT ON because i'm tyring to use begin tran. – Racer SQL Oct 20 '16 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.