April 20, 2017 at 5:15 am
Hi Guys,
I have an issue where an insert of a record in a table fails due to the following error:
'Numeric overflow in conversion of value 2,163,483,016'.
The table ID(Primary Key) field has a bigint datatype. So I am assuming that the above error occurs on the ID field. Can someone please advise as to why the record insert is getting stuck to auto-increment if the ID field is bigint. Also, how do i know that it failed on the ID field and not another column field in the table?
Thanks in advance
IC
April 20, 2017 at 5:23 am
Do you have the INSERT statement that caused the error? That should make it fairly plain which column caused it, when you see the number from the error message. Are you passing in numbers with the thousand separators in? If so, that'll fail - use the value 2163483016 instead.
John
April 20, 2017 at 5:26 am
The BigInt datatype can store a value of up to 2^63-1, That equates to 9,223,372,036,854,775,807, which is far larger than your number.
What is the rest of the DDL of your table? Can you provide a sample INSERT statement that fails?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 20, 2017 at 5:34 am
Imke Cronje - Thursday, April 20, 2017 5:15 AMHi Guys,I have an issue where an insert of a record in a table fails due to the following error:
'Numeric overflow in conversion of value 2,163,483,016'.
The table ID(Primary Key) field has a bigint datatype. So I am assuming that the above error occurs on the ID field. Can someone please advise as to why the record insert is getting stuck to auto-increment if the ID field is bigint. Also, how do i know that it failed on the ID field and not another column field in the table?
Thanks in advance
IC
Post the DDL (create table) script and the actual insert statement please. If the table has any triggers, post those also.
😎
April 20, 2017 at 7:16 am
Eirikur Eiriksson - Thursday, April 20, 2017 5:34 AMImke Cronje - Thursday, April 20, 2017 5:15 AMHi Guys,I have an issue where an insert of a record in a table fails due to the following error:
'Numeric overflow in conversion of value 2,163,483,016'.
The table ID(Primary Key) field has a bigint datatype. So I am assuming that the above error occurs on the ID field. Can someone please advise as to why the record insert is getting stuck to auto-increment if the ID field is bigint. Also, how do i know that it failed on the ID field and not another column field in the table?
Thanks in advance
ICPost the DDL (create table) script and the actual insert statement please. If the table has any triggers, post those also.
😎
Hi Guys,
I wuold love to give you the insert record statement but unfortunately cannot since its SQL auto incrementing the ID value when a new record coming in.
April 20, 2017 at 7:21 am
Imke Cronje - Thursday, April 20, 2017 7:16 AMHi Guys,I wuold love to give you the insert record statement but unfortunately cannot since its SQL auto incrementing the ID value when a new record coming in.
Use an Extended Events session (or trace if you're more comfortable with that) to capture the statement, then.
John
April 20, 2017 at 7:27 am
Imke Cronje - Thursday, April 20, 2017 7:16 AMEirikur Eiriksson - Thursday, April 20, 2017 5:34 AMImke Cronje - Thursday, April 20, 2017 5:15 AMHi Guys,I have an issue where an insert of a record in a table fails due to the following error:
'Numeric overflow in conversion of value 2,163,483,016'.
The table ID(Primary Key) field has a bigint datatype. So I am assuming that the above error occurs on the ID field. Can someone please advise as to why the record insert is getting stuck to auto-increment if the ID field is bigint. Also, how do i know that it failed on the ID field and not another column field in the table?
Thanks in advance
ICPost the DDL (create table) script and the actual insert statement please. If the table has any triggers, post those also.
😎Hi Guys,
I wuold love to give you the insert record statement but unfortunately cannot since its SQL auto incrementing the ID value when a new record coming in.
What about the DDL (create table) script and the triggers if any?
😎
April 20, 2017 at 7:33 am
John Mitchell-245523 - Thursday, April 20, 2017 7:21 AMImke Cronje - Thursday, April 20, 2017 7:16 AMHi Guys,I wuold love to give you the insert record statement but unfortunately cannot since its SQL auto incrementing the ID value when a new record coming in.
Use an Extended Events session (or trace if you're more comfortable with that) to capture the statement, then.
John
The issue won't appear anytime soon since I reseeded the table to fix the issue.
April 20, 2017 at 7:39 am
Imke Cronje - Thursday, April 20, 2017 7:33 AMJohn Mitchell-245523 - Thursday, April 20, 2017 7:21 AMImke Cronje - Thursday, April 20, 2017 7:16 AMHi Guys,I wuold love to give you the insert record statement but unfortunately cannot since its SQL auto incrementing the ID value when a new record coming in.
Use an Extended Events session (or trace if you're more comfortable with that) to capture the statement, then.
John
The issue won't appear anytime soon since I reseeded the table to fix the issue.
I'm not really sure that's the solution. Also, if the seed was the problem, it implies your id column is an int, rather the than bigint datatype you think it is.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 20, 2017 at 7:42 am
Imke Cronje - Thursday, April 20, 2017 7:33 AMThe issue won't appear anytime soon since I reseeded the table to fix the issue.
Then you need to put it on a test server and force it to appear. Either that or wait until you get called in the middle of the night because it's happened again, and then face questions in the morning about why you didn't fix it last time it happened.
Still need table DDL, as well, please.
John
April 20, 2017 at 7:52 am
John Mitchell-245523 - Thursday, April 20, 2017 7:42 AMImke Cronje - Thursday, April 20, 2017 7:33 AMThe issue won't appear anytime soon since I reseeded the table to fix the issue.Then you need to put it on a test server and force it to appear. Either that or wait until you get called in the middle of the night because it's happened again, and then face questions in the morning about why you didn't fix it last time it happened.
Still need table DDL, as well, please.
John
Thanks John. There won't be calls in the middle of the night since all failures are written to an exception table. As requested, see below. There are no triggers:
CREATE TABLE [dbo].[table1](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Col2] [datetime] NOT NULL,
[Col3] [bigint] NOT NULL,
[Col4] [varchar](40) NOT NULL,
[Col5] [varchar](250) NOT NULL,
[Col6] [varchar](250) NULL,
[Col7] [varchar](250) NULL,
[Col8] [varchar](250) NULL,
[Col9] [varchar](1000) NULL,
[Col10] [int] NOT NULL,
[Col11] [int] NOT NULL,
[Col12] [int] NOT NULL,
[Col13] [varchar](500) NULL,
[Col14] [int] NOT NULL,
[Col15] [int] NOT NULL,
[Col16] [int] NOT NULL,
[Col17] [int] NULL,
[Col18] [varchar](32) NULL,
[Col19] [varchar](512) NULL,
[Col20] [varchar](32) NULL,
[Col21] [bit] NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
April 20, 2017 at 7:55 am
Can you post the insert statement with its values? It's not going to be coming from the identity column. With a bigint data type it can take numbers far larger than the one you posted. It'll be one of the INT columns breaking.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2017 at 9:08 am
Is this table possibly used as a staging area for processing data.
If it is, are many deletes, and no truncate? That would be one scenario where the IDENTITY gets to the max value.
Another would be that there was an IDENTITY_INSERT into the table with a very high value for ID.
April 20, 2017 at 9:37 am
DesNorton - Thursday, April 20, 2017 9:08 AMIs this table possibly used as a staging area for processing data.
If it is, are many deletes, and no truncate? That would be one scenario where the IDENTITY gets to the max value.Another would be that there was an IDENTITY_INSERT into the table with a very high value for ID.
Sorry, but both of those are not possible given the OP's statements. The identity is defined on a BIGINT, which has a large enough value that you can do MANY ORDERS OF MAGNITUDE more inserts than in an integer. As Paul Randall calculated here (Bigint Exhaustion), if you inserted 100000 values PER SECOND it would take you 2.95 MILLION YEARS to run out of possible values. And if IDENTITY_INSERT caused the problem then the error would not state that the upper limit of an INT had been exceeded, it would be the upper limit of a bigint.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply