Update fails on linked server, Select works

Question

I am trying to understand why my update statement on my local server is failing on my remote server. I am receiving the following error:

OLE DB provider "SQLNCLI11" for linked server "WEBDB" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Msg 7341, Level 16, State 4, Line 1 Cannot get the current row value of column "[SQLNCLI11].AdditionalBarcode" from OLE DB provider "SQLNCLI11" for linked server "WEBDB". [COLUMN_NAME= AdditionalBarcode STATUS=Unknown DBBINDSTATUS]

This is my update statement

Update WEBDB SET Additionalbarcode = UDF_ADDITIONALBARCODE FROM
openquery(WEBDB, ' SELECT Id ,[AdditionalBarcode] FROM
...[ProductGroup] where IsActive = 1') WEBDB inner
join LocalDB..Items CI on WEBDB.Id = CI.itemcode where
nullif(UDF_ADDITIONALBARCODE,'') <> isnull(Additionalbarcode,'')

This is my select statement

Select itemcode, UDF_AdditionalBarcode, Id, Additionalbarcode FROM
openquery([remoteserver], ' SELECT Id ,[AdditionalBarcode] FROM
...[ProductGroup] where IsActive = 1') WEBDB inner
join LocalDB..Items CI on WEBDB.Id = CI.itemcode where
nullif(UDF_ADDITIONALBARCODE,'') <> isnull(Additionalbarcode,'')

Why would I receive an error during UPDATE but not select? Any idea on what would be the cause of the error in the update statement?

Answer 1

I don't know exactly why that is failing, but there are probably better alternatives.

First is the general best-practice to avoid the propriatary and confusing UPDATE FROM:

Start with a working SELECT and then do

with q as
(
 Select itemcode, UDF_AdditionalBarcode, Id, Additionalbarcode FROM
 openquery([remoteserver], ' SELECT Id ,[AdditionalBarcode] FROM
 ...[ProductGroup] where IsActive = 1') WEBDB inner
 join LocalDB..Items CI on WEBDB.Id = CI.itemcode where
 nullif(UDF_ADDITIONALBARCODE,'') <> isnull(Additionalbarcode,'')
)
update q set Additionalbarcode = UDF_ADDITIONALBARCODE

But I would go farther and simply spool the OPENQUERY results locally first:

Select *
into #webdb
FROM openquery([remoteserver], ' SELECT Id ,[AdditionalBarcode] FROM
...[ProductGroup] where IsActive = 1') 

When working across linked servers, being explicit about how you're moving data back and forth is usually best.

Answer 2

Any reason you could not simplify further and use a four part Linked Server address and ditch OPENQUERY altogether?

UPDATE  pg
SET     pg.Additionalbarcode = UDF_ADDITIONALBARCODE
FROM    WEBDB.DBName.dbo.ProductGroup AS pg
        INNER JOIN LocalDB..Items AS CI ON pg.Id = CI.itemcode
WHERE   1 = 1
        AND NULLIF(UDF_ADDITIONALBARCODE, '') <> ISNULL(pg.Additionalbarcode, '');