ODBC Driver for BigCommerce fail to update InventoryTracking in products table

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
igorg
Posts: 7
Joined: Fri 05 Apr 2019 13:27

ODBC Driver for BigCommerce fail to update InventoryTracking in products table

Post by igorg » Wed 24 Apr 2019 02:42

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,

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: ODBC Driver for BigCommerce fail to update InventoryTracking in products table

Post by MaximG » Fri 26 Apr 2019 13:02

Please try updating the required field with the following query:

Code: Select all

UPDATE [BIGCOMMERCETS]...Products SET InventoryTracking = 'simple where Sku = '143869'
In our test environment, we have successfully performed a similar update in SQL Server Management Studio

igorg
Posts: 7
Joined: Fri 05 Apr 2019 13:27

Re: ODBC Driver for BigCommerce fail to update InventoryTracking in products table

Post by igorg » Fri 26 Apr 2019 14:26

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.

igorg
Posts: 7
Joined: Fri 05 Apr 2019 13:27

Re: ODBC Driver for BigCommerce fail to update InventoryTracking in products table

Post by igorg » Thu 02 May 2019 19:37

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.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: ODBC Driver for BigCommerce fail to update InventoryTracking in products table

Post by MaximG » Fri 06 Sep 2019 15:01

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

Post Reply