Sql-server – Update fails on linked server, Select works

linked-serversql serversql server 2014

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?

Best Answer

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.

Related Question