Reaching the Outer Limits

, 2002-10-14

Reaching the Outer Limits


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 

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.


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

© October 2002

Return to Steve Jones Home



5 (2)




5 (2)

Related content

Using Exotic Joins in SQL - Part 2

In the previous article, you saw how the BETWEEN operator could be used in joins to solve problems dealing with range-based data. In this article, Chris Cubley will show you how to take joins even further by using multiple criteria in joins as well as using the greater than, less than, and not equals operators in joins.


13,242 reads

A Normalization Primer

For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.

5 (3)


17,243 reads