Dynamics Community Forum Thread Details

Ok, this is a little involved and may be outside the scope of this forum because of the involvement with 3rd party software, but I'm stumped and figured it's worth a shot, being that there may be some people around with more experience than me working with the back end sql database.

We have an HQ database and several Store Ops databases, one of which is on the same server as the HQ database. All databases are SQL Express 2005. This database (the Store Ops one that shares the server with HQ) is for our main store where we receive all of our inventory before transferring it to the satellite stores. We have been using New West Mobile Manager on a couple of MC55 hand scanners synced with this database to perform various actions from the stockroom. We have been using these for some time without issue. Last week, I noticed some big discrepancies between our Store Ops number and HQ numbers. After a little bit of detective work, I traced it down to the hand scanners, which seem to be adding twice the desired number of units when they are used to receive products. The Purchase Order created lists the correct number of units received, and when data is uploaded to HQ via a 401 the quantities are altered correctly. Only the quantities in Store Ops are increased by twice the desired amount. I can't think of any update or change to any of the software or environment that coincided with this issue starting.

I contacted New West Support with the issue, and worked with them for a short while. They initiated a remote support session and verified that the issue was really happening, but were unable to replicate it on their end. Due to this, they say the issue is not with their software but with our environment, and so any further support by them would be billable (::Sigh::).

Trying to avoid having to go to the boss asking for money for support for a problem I can't seem to fix, I've been doing a lot of troubleshooting on our end. I've run Anjlabs SQL Profiler against the database when performing the receiving action that results in the problem, and I am seeing two stored procedures altering the Quantity field on the Items table. The first is:

exec sp_executesql N'UPDATE PurchaseOrderEntry WITH (ROWLOCK) SET QuantityReceived = 0, QuantityReceivedToDate = QuantityReceivedToDate + QuantityOrdered WHERE PurchaseOrderID = @POID UPDATE i WITH (ROWLOCK) set i.Quantity = i.Quantity + poe.QuantityOrdered, i.LastReceived = GETDATE(), i.LastUpdated = GETDATE(), i.LastCost = poe.Price
FROM Item i, PurchaseOrderEntry poe
WHERE i.[id]=poe.ItemID
AND poe.PurchaseOrderID=@POID
INSERT InventoryTransferLog(ReferenceID, ReferenceEntryID, ItemID, DetailID, Quantity, DateTransferred, StoreID, CashierID, [Type], ReasonCodeID, Cost) SELECT PurchaseOrderId, [ID], ItemId, 0, QuantityOrdered, getdate(), (SELECT StoreID FROM Configuration WITH (NOLOCK)), @CashierID, 1, 0, Price FROM PurchaseOrderEntry WITH (NOLOCK) WHERE PurchaseOrderId = @POID ',N'@POID int,@CashierID int',@POID=953, @CashierID=82

The second is:

exec sp_executesql N'UPDATE PurchaseOrderEntry WITH (ROWLOCK)
                          SET Price=@POCost, QuantityOrdered=@QuantityOrdered,
                          ShippingPerItem = @ShipCost,
                          OtherFeesPerItem = @OtherCost,
                          TaxRate = @TaxRate,
                          LastQuantityReceived = @QuantityReceived,
                          LastReceivedDate = GETDATE()
                          WHERE PurchaseOrderid = @POID
                          AND ItemID=@ItemID
                          AND ID=@POEID
UPDATE Item WITH (ROWLOCK) SET Quantity = Quantity + @QuantityReceived,
    LastReceived = getdate() WHERE [id]=@ItemID ',N'@POID int,@QuantityReceived decimal(28,0),@QuantityOrdered decimal(28,0),@POCost decimal(28,4),@LandedPOCost decimal(28,4),@ShipCost decimal(28,4),@OtherCost decimal(28,4),@POEID int,@ItemID int,@TaxRate decimal(28,0),@StoreID int,@CashierID int,@PurchaseOrderEntryID int',@POID=953,@QuantityReceived=1,@QuantityOrdered=1,@POCost=12.5000,@LandedPOCost=12.5000,@ShipCost=0.0000,@OtherCost=0.0000,@POEID=22494,@ItemID=13650,@TaxRate=0,@StoreID=1,@CashierID=82,@PurchaseOrderEntryID=22494

Both of these show the hand scanner as the host, and both show an IF (UPDATE(COST)) and IF (UPDATE(PRICE)) expression being evaluated as part of a stored procedure very near where they are executed (but this may be unrelated).

So, my question is: Where do i go from here? Can anyone think of an environmental reason this would be occurring? I'm kind of leaning toward New West being wrong and the issue lying with them, but I don't know that I'm willing to bet $150/hour on it.

I know this is pretty specific, but any help I can get would be greatly appreciated.

Thanks a lot,

Spencer