Devart Forums

Hi
Trying to activate inventory tracking in products table
UPDATE [BIGCOMMERCETS]...Products
SET InventoryTracking = 'simple',
FROM [BIGCOMMERCETS]...Products BCP
where BCP.Sku = '143869'

fail with following error:
OLE DB provider "MSDASQL" for linked server "BIGCOMMERCETS" returned message "Row cannot be located for updating. Some values may have been changed since it was last read.".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "BIGCOMMERCETS" could not UPDATE table "[BIGCOMMERCETS]...[Products]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

When i manually change Inventory traking on BigCommerce site then it work.

Running on MS-SQL 2008R2 Windows server 2008R2.
Try on skyvia and it work.

Thanks,

Please try updating the required field with the following query: In our test environment, we have successfully performed a similar update in SQL Server Management Studio

I try this way too and still same error.
I also try on MS-SQL 2014
UPDATE [BCTS]...Products SET InventoryTracking = 'simple' where Sku = '171965'
OLE DB provider "MSDASQL" for linked server "BCTS" returned message "Row cannot be located for updating. Some values may have been changed since it was last read.".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "BCTS" could not UPDATE table "[BCTS]...[Products]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

Hi,

I was able to resolve issue.
Here is the query i use.
UPDATE BCTSTarget
SET InventoryLevel = CASE WHEN P.quantityonhand < 0 THEN 0
ELSE P.quantityonhand
END
FROM OPENQUERY(
[BIGCOMMERCETS],
'SELECT Sku,InventoryTracking,InventoryLevel,IsVisible
FROM Products
WHERE (InventoryTracking = ''simple'') AND (IsVisible = 1) AND (SKU IS NOT NULL)') AS BCTSTarget
INNER JOIN (SELECT skuno, CASE WHEN quantityonhand < 0 THEN 0
ELSE quantityonhand
END AS quantityonhand FROM [SMART].[SmartBOS].[dbo].[PRODUCTS] WHERE storeno = '07000') AS P
ON (BCTSTarget.SKU = P.skuno)
WHERE (BCTSTarget.InventoryLevel <> P.quantityonhand)

But some times i still get same error.
I have 1100 records. Is this why error occur.
Executed as user: NT AUTHORITY\NETWORK SERVICE. The OLE DB provider "MSDASQL" for linked server "BIGCOMMERCETS" could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized. [SQLSTATE 42000] (Error 7343) OLE DB provider "MSDASQL" for linked server "BIGCOMMERCETS" returned message "Row cannot be located for updating. Some values may have been changed since it was last read.". [SQLSTATE 01000] (Error 7412). The step failed.

Thanks.

Try running NT SERVICE\MSSQLSERVER as your Windows user and let us know the result