What is wrong with this sql???? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

What is wrong with this sql????

Author

Topic

enak
Starting Member

34 Posts

Posted - 2005-02-02 : 12:00:50

I have a SQL statement that keeps giving me an error.

SQL:
INSERT INTO tblDefendants
SELECT 2135032621 AS CaseID, Defendant, InActive, LeadDefendant, FirmID, Counsel, Attn, rpfname,
rplname, rptitle, rlmatter, relationship, rlMatterLink, LastupdatedBy, LastUpdatedDate, AddedBy, AddedDate
From tblDefendants WHERE CaseID = 2135032619

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

Table:
DefID int <== this is the identity field.
CaseID int
Defendant varchar
InActive bit
LeadDefendant bit
FirmID int
Counsel varchar
Attn nvarchar
rpfname varchar
rplname varchar
rptitle varchar
rlmatter varchar
relationship varchar
rlMatterLink varchar
LastUpdatedBy varchar
LastUpdatedDate smalldatetime
AddedBy varchar
AddedDate smalldatetime

Thanks,
enak

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-02 : 12:06:12

You are trying to insert the CaseId into the DefID column
Explicity declare the columns which to insert into

eg
INSERT MyTable(Col1,Col2,..............Col4 etc)
VALUES(1,2,....................4, etc)

Andy

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-02 : 12:08:31

check your identity field... integer can hold numbers only so big:

From BOL:

quote:
int:
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

enak
Starting Member

34 Posts

Posted - 2005-02-02 : 12:10:46

OK, that made sense but it still does not work. Here is what I now have:

INSERT INTO tblDefendants(CaseID, Defendant, InActive, LeadDefendant, FirmID, Counsel, Attn, rpfname,
rplname, rptitle, rlmatter, relationship, rlMatterLink, LastupdatedBy, LastUpdatedDate, AddedBy, AddedDate)
SELECT 2135032621 AS CaseID, Defendant, InActive, LeadDefendant, FirmID, Counsel, Attn, rpfname,
rplname, rptitle, rlmatter, relationship, rlMatterLink, LastupdatedBy, LastUpdatedDate, AddedBy, AddedDate
From tblDefendants WHERE CaseID = 2135032619

enak
Starting Member

34 Posts

Posted - 2005-02-02 : 12:12:32

I tried to shorten the integer being inserted. No change.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-02 : 12:14:24

what does this return:

Select max(DefId) From tblDefendants

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

enak
Starting Member

34 Posts

Posted - 2005-02-02 : 12:16:35

Select max(DefId) From tblDefendants returns 2147483628

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-02 : 12:20:09

ok.... notice this is curiuosly close to the upper limit for the int data type. can you change your table? You need to make defId a bigint.

That should solve your problem.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-02 : 12:20:43

Your identity seed value has maxed out:

DBCC CHECKIDENT ('tblDefendants', NORESEED)

Be One with the Optimizer
TG

enak
Starting Member

34 Posts

Posted - 2005-02-02 : 13:01:26

Yes, we have reached the max value. We will have to reset it. Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources