Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Unanswered: Arithmetic overflow error converting IDENTITY to data type int

    I have a table with an INT primary key (SQL Server 2000).

    The table has been truncated (so no rows of data and, I believe, the identity seed reset).

    I then attempt to add a SINGLE row and get the following error message:

    Server: Msg 8115, Level 16, State 1, Line 1
    Arithmetic overflow error converting IDENTITY to data type int.
    Arithmetic overflow occurred.

    This table has existed in the schema for about 4 years, and is truncated nightly and multiple rows inserted successfully.

    I have used SQL Server's analysis tools [DBCC CHECKTABLE (tableName),
    DBCC CHECKALLOC, DBCC SHOWCONTIG and sp_help (tableName)] and they all indicate that the table has no underlying problems.

    I scripted the table and created a clone (slightly different name) and successfully added my single row to that table.

    So...something is wrong with this table. But what? Obviously I could drop the table and re-create it, but I'd first like to understand what's gone wrong with it just in case there's something fundamentally wrong.

    Any ideas?

    Thanks in advance

    Griff

  2. #2
    Join Date
    Oct 2009
    Posts
    4
    Ah ha

    Fixed it, but still like advice on this.

    I know that the table is truncated nightly (not "delete from table" which doesn't delete).

    I also manually created a truncate statement which ran when I was investigating this.

    But, when I attempted to add the data, it errored (as shown in previous post).

    I then forcibly reseeded the table using:
    DBCC CHECKIDENT (tableName, RESEED, 0)

    and guess what...the row inserted properly.

    So...it implies that in this instance, my truncate statement was NOT reseeding the table. Wierd...

    Ideas?

    Griff

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    In your INSERT statement, did you supply a value for the ID? Was that too big to fit into an INT?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Oct 2009
    Posts
    4
    Nope - didn't provide a value for the ID (it's an identity column so it generates this itself).

    Here is a schema (table & column names amended for this post)

    CREATE TABLE [dbo].[myTable](
    [a] [varchar](8) NULL,
    [b] [varchar](16) NULL,
    [c] [int] NULL,
    [d] [int] NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
    PRIMARY KEY NONCLUSTERED
    (
    [ID] ASC
    )
    ) ON [PRIMARY]

    And here's the insert statement:

    insert into myTable(a, b, c, d)
    values ('xxx','12345', 1, 1)

  5. #5
    Join Date
    Oct 2009
    Posts
    4
    Nope - didn't provide a value for the ID (it's an identity column so it generates this itself).

    Here is a schema (table & column names amended for this post)

    CREATE TABLE [dbo].[myTable](
    [a] [varchar](8) NULL,
    [b] [varchar](16) NULL,
    [c] [int] NULL,
    [d] [int] NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
    PRIMARY KEY NONCLUSTERED
    (
    [ID] ASC
    )
    ) ON [PRIMARY]

    And here's the insert statement:

    insert into myTable(a, b, c, d)
    values ('xxx','12345', 1, 1)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •