Is there a limit to the number of items in the RMS SO (1.2/1.3) database?
I've gotten a call from a store manager and he tells me that when they try to add an item they get a "run time error 6 - overflow". His solution is to delete an "old" item and then a new item can be added.
I find it hard to beleive there is a limit, since this store has been open for many years and the database has never been shrunk, meaning that every transaction from day one is stored... Last time I looked it was like 9GB (yeah, I know, we need to fix this). So why the trouble with items?
Eric, Because RMS uses integers as the primary id, there is a limit of items that can be present, of course this limit is 2,147,483,647, so if you have cleared 2.1 billion items you have a problem. You may be able to change this to big int which would get you 9.2 quintillion, though I am not certain how changing the ID field might affect the database. My guess is there is probably something else going on here. Any customizations or such present?
No customizations... Pretty much RMS straight out of the box... Unless you want to count the inclusion of Mercury Pay (SmartPayments by TPI) for EDC.
The number of items in the db is around 40,000... 40K is the "magic number" that the manager identified. When the number of items gets to 40K, he needs to delete some items before new items can be added.
The number of items right now is 39538... I have not actually seen the problem occur, but he said it is accompanied with a "Run time error 6 - Overflow" - I was convinced he (rather the data entry person) was doing something wrong, but I think they know what they are doing - This location has been open for several years.
We are opening a new store, and this has the potential to become a bit of a problem as the new location is going to be larger.
Eric, We have customer well over the 400,000 mark. Running 10 times more items than you have should be no problem. Michael may be on to something that is causing this issue. Things to look for:
-Version of RMS (If you don't know, open SO Manager and go to Help > About Store Operations Manager)
-Version of SQL (MSDE/SQL Server 2000/SQL Express 2005/SQL Server 2005)
-What Version of Windows are you running (Windows 95/98/ME, Windows 2000, Windows XP, Windows Vista)
-Are there any system customizations or tweaks that have beam implemented, like SQL Triggers, Windows Services, or scheduled tasks?
-Version of RMS: 1.2.0189 (I'm new to this job, and the story I heard is that an upgrade to 1.3 was attempted, but did not work. The person that tried no longer works here). I'm sure it's on my list to get to 1.3 and possibly
2.0. Long story, but we have 3 stores and need to get on HQ first. So that will be the path.
-Version of SQL: How would I tell? The odd thing is that all the machines that are running RMS (in this store, including the server) have the SQL Server Service Manager in the system tray. I watched another RMS SO install, where another version of SQL server was already installed, so RMS did not install the included version of "MSDE 2000" (hope I remembered that right). On this other machine the SQL SSM was not in the sytem tray. But since this machine was a bit rogue (it was to be the HQ server, but a new store is opening first, so it became SO), so the drive was formated, the OS (Windows Server 2003 SE SP2), and a clean install of RMS SO performed. Thisis not where the problem is, at least not yet I suspect. [OK, I know that was too much info]
-Version of Windows: For the system in question the server is Windows Server
2003 SE SP1, the machine where the error is observed is a Windows XP machine. I asked, OK begged, to please call me when this happens, as I wanted to try an item add on the server, but of course managers are always too busy to help with trouble shooting. So while I'd like to see if the problem is isolated to the data entry machine, or the system (meaning I would try the add on the server), I don't know. But my thought is there are (as of yesterday)
1,073,990 transactions in the system (another item on my to do list), so it does not seem like the item count should be an issue.
-Are there any system customizations or tweaks that have been implemented, like SQL Triggers, Windows Services, or scheduled tasks? The only thing that I am aware of is that the TPI SmartPayments (the store uses Mercury) is installed. I know the installation runs some SQL on the db, but I do not know what it does exactly, except mess with tenders... It shouldn't touch the item db though, right? Oh, there is also a "custom button" on the register called "Price Check" , which is mapped to "ZephyrHooks.PriceChecker", but this was someone else's experiment that was never cleaned up. Attempting to use the button tells me the license does not match the dongle (so I guess it's another item on the to do list - long list, huh?).
OK, I know that was way longer than was expected, but I'm exasperated. It seems like one simple thing, but being the only person working on this (and a new store), I really have my hands full.
-I certainly would upgrade the customer (provided they are on Microsoft Maintenance), minimally to the newest update of 1.2, preferably to 2.0. This alone may fix your issues.
-To check your version of SQL go to Add/Remove Programs. It will either be Microsoft SQL Server 2005 or Microsoft SQL Server Desktop Engine.
-I was more curious than anything, though Microsoft did not claim support for Windows 2003 Server until 2.0 SP1.
-The price checker tool I would suggest you clean up, remove the button from manager and/or any hooks that may be in the registry. This is likely not the problem but you should get rid of it to be sure. I would also look into ensuring that you are running the newest version of TPI as I know this application is tightly integrated into RMS.
My main priority at the moment is getting a new store open... It's a pain to have to work around this issue, but with a working workaround (I guess that's what makes it a "workaround "), I'll focus on my new install first... There is so much to do, this (at the moment) is the least of my worries.
Ryan is on the right track, you have reached the limit of your sql database free version, 4gb (or less if the original install was limited in size. The answer is to get the real sql version, I think you need sql 2000. Best to upgrade to the current version of RMS AND get sql 2005 full version.
I guess the one thing that is confusing me the most about this is that the number of transactions in the db is over a million...
The database is currently 9,824.56 MB. I plan to use this db in another store so I did a backup/restore to create a temp db, and after deleting all the transactions (then doing another backup and restore - not sure if there is a cleaner way) the size is down to 212.50 MB... Now I have not tried to add more items to this "no transaction" database, but this size issue still confuses me.
The current db seems to be well over any size limit I have see for the "free" (included?) versions of SQL server... Why can I have ~9,612.06 MB of transactions, but only 40,000 rows in my item db?
Oh, and this may shed the needed light... I checked the SQL Server version and it's "SQL Server 2000" (support info button in the add/remove applet shows version 8.00.761)... Might this be it? How do I tell what service pack, if any, is installed? I have a 3a disc... Not sure if it's installed, or if it will help.
I suspect this means that the install (on that rogue server - now the new store's new RMS SO server) needs a better SQL server too... It currently only has "Microsoft SQL Server Desktop Engine". I have the Microsoft SQL Server
2005 disc (and license) that was installed on it... I guess I need to update SQL on this machine too.
I would also like to say thank you to you all for helping me understand this problem.
I installed SQL Server 2005 and I still hit the ~40,000 item wall... When it occured, the number of rows in the item db was 39.738...
I checked the "Database -> Upgrade..." option in RMS SO Admin, and it tells me the current version is 1.20 and that it is up to date.
I guess I'll have to figure this out after the store opens... For now I'm having the store manager delete items that they no longer carry (or never did
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here.
All logos and trade names are the property of their respective owners.