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?
I'll go back to my searching now...
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?
Thank you for the reply Ryan.
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.
Any additional insight greatly appreciated.
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?
Ryan & Michael,
-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
-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.
Thank you in advance.
-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.
Thank you for the insight Ryan.
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.
Thanks for the info Jerry...
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
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
Well to anyone who is still reading...
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
- this was a db migration from many years ago).
Thanks for reading.