Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Store Operation upload...
Microsoft Dynamics RMS (Archived)

Store Operation upload to HQ quantity issues

(0) ShareShare
ReportReport
Posted on by Grier Fleischhauer 780

Something screwy is occuring with quantiy sold items uploading to HQ. I have two stores, one store uploads quantities correctly, the other store uploads random quantities. Today the one store with issues sold exactly 100 items, when I ran a HQ descrepency report I had 98.65 total item descrepencies. Looking at each item in the descrepency report mostly the items in HQ were off by one or two. But the really strange behavior is that I had many items decrement by a fraction, so an item would be 1.1 or 2.65 etc in HQ. All of the items should be whole numbers.  What could possibly be going wrong and how can I correct this?

Recent changes to my system: our server software went to sql2008 express r2 which houses both HQ and the one store that the inventory is getting screwed up. I installed the backwards compatibility software on the server for sql2008/2005.  RMS SO is 2.0.1004 and HQ is 2.0.1004. The other store is running sql2005 express.

*This post is locked for comments

  • Verified answer
    Grier Fleischhauer Profile Picture
    Grier Fleischhauer 780 on at
    RE: Store Operation upload to HQ quantity issues

    As a follow up to my issue, the Store Operation's database had some unknown corruption.  Restoring the Database from a backup did not solve the problem because my backups had the same corruption in it.  The solution to fixing the database was to export a new database from headquarters. However the database that I was trying to use for the template was also corrupted and the export would not complete.

    The final solution was that I had to create or restore a new template database from RMSDB.BCK, then use this as my template to export my store to a new database.  Doing this I had some minor issues with passwords etc, but fixed them with sql commands. There were other small items that I had to do tidy up the database (such as net display) and I am missing some historical detail sales information for a few vendors which I can live with.

    Best Regards,

    Grier

  • Jeff @ Check Point Software Profile Picture
    Jeff @ Check Point ... 13,380 on at
    RE: Store Operation upload to HQ quantity issues

    Both of you made mention of restoring so I thought I'd throw it out there with what we are doing with our clients.

    One should always test their backups occasionally, no sense in backing up crappy data!! ☺

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: Store Operation upload to HQ quantity issues

    Are you asking me? I'm not sure where this miss-communication keeps coming from, but I'm not currently having any issues. Over a year ago I found that, starting when a backup had been restored, our Store Ops started numbering several important identity values from zero. I fixed it at the time, and have not run into any problems since. I mentioned it in this discussion because I recognized the symptoms in Grier's post as indicating a similar situation, which appears to be a correct diagnosis based on his earlier response (though it didn't totally fix all of the problems he was seeing).

    Incidentally, we have automated backups that run nightly, but haven't had any reason to restore from them in quite some time.

  • Jeff @ Check Point Software Profile Picture
    Jeff @ Check Point ... 13,380 on at
    RE: Store Operation upload to HQ quantity issues

    I'm curious, why the restore so often and why is the backup so old.

    On the Server machine, we backup our client's database(s) hourly, on the :45 or HQ, the :50. I've posted our backup scripts here many times as well as the Scheduled task settings.

    On the hour, a backup of anything that's changed to the drive in the last hour to a backup drive, that includes the database backup. We use Symantec System Recovery Desktop (new name for Norton Ghost) and Server versions.

    Then do a full hard drive backup weekly also to that backup drive, which keeps 2-3 months of backups, depending on the backup drive's size.  Again using the SSR backup program.  We could go back to any backup in those 2-3 months and restore the complete drive or database or any other file.

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: Store Operation upload to HQ quantity issues

    Hi Heidi,

    In my case, this actually happened over a year ago, and has since been cleared up. It's good advice, though, and the issue seems to pop up often enough when a Store Ops database is restored from a backup that I'm sure it'll come in handy. Thanks a lot.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Store Operation upload to HQ quantity issues

    HI Spencer,

    Sounds like you have a few issues. Perhaps a store database had been restored from backup causing the duplicate transaction and PO #s?.  Here's the query to  start at a new ID to prevent duplicates.  Before you run in, at HQ you should run the query SELECT MAX(ID) FROM PurchaseOrder WHERE StoreID = X  once you've learned the MaxID for the store, you can set the next ID at the store to be higher with this query (Replace NEWID and STOREID in the query before running it):

    SET IDENTITY_INSERT PurchaseOrder ON

    INSERT PurchaseOrder (ID, StoreID, PONumber)

    VALUES(NEWID, STOREID, 'DeleteMe')

    SET IDENTITY_INSERT PurchaseOrder  OFF

    Once that's sorted out, I think you should check for duplicates in the database.  Duplicate alias code, or duplicate item codes or alias codes that exist as items, wreak havoc in RMS, sometime selling a completely wrong item after you've scanned a product.  It can cause physical counts to post values to all the wrong counts.

    --- 1: Query to find duplicate Itemlookupcodes (Action is required if duplicates are found)

    SELECT ItemLookupCode FROM Item GROUP BY ItemLookupCode HAVING COUNT (ItemLookupCode) > 1

    --- 2: Query to find duplicate Alias Codes (If you get any results, either manually delete or use query 4 to delete)

    SELECT Alias FROM Alias GROUP BY Alias HAVING COUNT (Alias) > 1

    --- 3: Query to find Alias codes that exist in the Itemlookupcode (If you get any results, use query 5 to delete)

    SELECT Alias FROM Alias WHERE Alias IN (SELECT ItemLookupCode FROM Item)

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: Store Operation upload to HQ quantity issues

    Oh, also, just a heads up, whenever we encountered the repeating transaction numbers, the same thing was going on in the purchase order table, causing random items to be added to PO's and Interstore transfers in HQ. Keep an eye out for that. It's going to be a bit trickier to solve, since there is no "Set Next PurchaseOrder ID" option.

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: Store Operation upload to HQ quantity issues

    As for the internal number in HQ vs Store Ops, the value in Items.ID in the HQ database should match the value in Items.HQID  in the Store Ops database. It should be pretty easy to compare, since both databases are on the same machine. You can reference other databases on the same server using using the {databasename}.dbo.{tablename} format. So to test if these values are the same, you could open HQ administrator and run something like

    select * from item as HQitem join {store ops databasename}.dbo.item as SOitem on HQitem.itemlookupcode = SOitem.itemlookupcode where HQitem.id <> SOitem.HQID

    I don't know that this would be your problem, though, as a mismatch between the two databases would most likely result in discrepancies between the detailed sales reports, which you said are lining up now.

    Have you tried running an Item movement report on one of the weirdly decremented items and seeing if it reflects the change thats supposed to occur or the screwy change that is occurring?

  • Grier Fleischhauer Profile Picture
    Grier Fleischhauer 780 on at
    RE: Store Operation upload to HQ quantity issues

    Okay I think there was two issues going on at the same time. Spencerm's suggestion fixed the detail sales issues because somehow the restored Store Operations database arbitrarily set the transaction number about 150,000 transactions less than the largest transaction number. So I corrected that and now atleast the detail sales in HQ and SO line up perfectly.

    I'm still having the screwy decrements of inventory. I installed the XP and SBS hotfixes that Jeff suggested, but today I am still having the same screwy random type changes in inventory (fractional, too many and too less decrements).  I am wondering if somehow the HQ database internal number that it assigns to the products and the SO database internal number it assigns are not lining up with each other. Is there a way to verify this?  I'm at a loss here. If someone has any other suggestions I'm all ears.

    Thank you Jeff and Spencerm for your suggestions.

    Grier

  • Jeff @ Check Point Software Profile Picture
    Jeff @ Check Point ... 13,380 on at
    RE: Store Operation upload to HQ quantity issues

    The hotfix will only work going forward, not fix the existing issues. You need to fix the store,s info and HQ needs a 190 worksheet after a 501 has been received.

    yes, you need the hot fix on you sbs machine too.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans