0

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?

1
  • Permissions? Maybe the user of the linked server has SELECT permission but not UPDATE permissions? – Francesco Mantovani Nov 26 '20 at 13:44
0

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.

1
  • 1
    I tried as cte and received the same error. I'll try the tempdb approach. – MattC Nov 25 '20 at 15:10
0

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, '');

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.