Reaching the Outer Limits
Introduction
I work for a fairly large company, J D Edwards, and being a software company, we have heavily leveraged various technology solutions to manage our IT world. Recently I ran into an issue with the McAfee ePolicy Orchestrator that distributes the updates for all the anti-virus McAfee clients. We were moving to a new server and gentleman doing the installation was having problems and called me.
The Issue
My colleague called me over before the install to verify and ensure the database was backed up and could be restored in the event of any issues. Always a good idea, especially in this case.
A little while later he called me back because the clients were unable to connect to the machine (or so he thought). He assumed some type of connection error because he was getting OLE-DB errors reports by the SQL Server driver in his log file and no data was being inserted into the table he was watching. The actual error in the log file was:
20021003151253 [DAL]: COM Error :80040e57 in ExecQueryNoStatus
Meaning = IDispatch error #3159
Source = Microsoft OLE DB Provider for SQL Server
Description = Arithmetic overflow error converting IDENTITY to data type int.
20021003151253 [EPOServer]: Failed to SetSettingValue, Err=-15
20021003151253 [naimserv::servdal_ModifyAgentProps]: Modify agent props for {15C6C3BE-B17D-4537-8F44-0E032634E55D} failed!
An interesting error to say the least, but it was obvious to me that this was not a connection issue. The clients were connecting fine, and performing some actions which were generating this error.
So I decided to dig in. A new problem and one that looked interesting.
Detective Work
My first step was to run Profiler and determine what the clients were doing. So I started a quick trace and looked for the error. Since this was a non-fatal type error, I stopped the trace after a few minutes of seeing nothing and started digging through the results.
I noticed there were a number of SELECTs being run (pointing to no connection issue) and a few stored procedures. I decided to focus on the stored procedure. Since this wasn't working and I had permission from the ePO product manager, I cut and pasted a stored procedure call into QA and ran it. Results:
(0) rows affected Arithmetic overflow error converting IDENTITY to data type int.
An interesting error. IDENTITY is a reserved word, so I opened the stored procedure code to see what it was doing. It was fairly simple and consisted of an UPDATE statement, a check for ROWCOUNT< 1 and an insert if the ROWCOUNT was < 1 or 0. Your basic update or insert sequence. Even more interesting. So I looked at the table. A pretty simple table that looked like the following:
CREATE TABLE [dbo].[ProductSettings] ( [AutoID] [int] IDENTITY (1, 1) NOT NULL , [ParentID] [int] NOT NULL , [SettingName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [SectionName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Value] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [State] [tinyint] NULL ) ON [PRIMARY]
Your basic table with nothing special. So I then focused on the AutoID column, which had the identity property set.
My first step was to run dbcc checkident. If you're not familiar with this one, I wrote about it in Fixing Your Identity. This reported a value of 2147483647 for the identity. A big number, but this was an INT. That's a 4 billion something odd number, right?
This through me for a minute and I did a couple test inserts and sure enough it kept giving me the same error. Then I checked the identity value a little closer. What id this is a signed integer? Then the max value is 2 billion something, I'm guessing around 2,147,483,647.
Since this was a test database (at this point) and I had a backup, I truncated the table (After asking the managers) and reset the identity to 1. We started the service and sure enough clients started connecting and inserting values.
Conclusions
If you check Books Online, this is what you will see. An INT is a signed integer and the max value is 2,147,483,647. That's a lot of rows!!!! We've tried to do the math and figure out how we got that many rows, but we haven't been able to make it work. McAfee as been contacted, so we shall see what they say, but it certainly was an interesting, though not very hard to solve, problem.
I've never run into this, though I'm sure some have. That's why (IMHO) the SQL Server team created the bigint to handle this. OF course that only pushes the problem further out, but it does push it out. This is one of those reasons why not to use an Identity property.
As always I welcome feedback on this article and anyone's stories related to this. Please use the "Your Opinion" button below. Please also rate this article.
Steve Jones
©dkRanch.net October 2002
