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
 General SQL Server Forums
 New to SQL Server Programming
 Ambigious error

Author  Topic 

WJHamel
Aged Yak Warrior

651 Posts
Posted - 2012-04-17 : 09:40:59
Doing an insert into from one table to another on matching columns, am receiving the following:

Msg 232, Level 16, State 2, Line 1
Arithmetic overflow error for type varchar, value = 591017724.000000.
The statement has been terminated.

and it's not pointing to any specific line in the query.

WTH?????

robvolk
Most Valuable Yak

15732 Posts
Posted - 2012-04-17 : 09:51:46
Are you sure the DDL for the tables is identical? Did you check the source table for a such a value? Are there any +-*/ operations in the query?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2012-04-17 : 09:53:06
At least one of the columns has data that is too long for the receiving column.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts
Posted - 2012-04-17 : 09:53:29
The DDL for the two tables is not identical. I'm not trying to insert all rows from A to B, but am selecting specific rows:


INSERT INTO [ARREST].[dbo].[ARR_PER]
           ([BEGIN]
           ,[ARRESTNO]
           ,[ARRESTNOB]
           ,[ARRESTTYPE]
           ,[WARSTATUS]
           ,[CODE]
           ,[SHOWCODE]
           ,[ECSOID]
           ,[LNAME]
           ,[FNAME]
           ,[MNAME]
           ,[TITLE]
           ,[DOB]
           ,[APPROX_AGE]
           ,[RACE]
           ,[SEX]
           ,[HISPANIC]
           ,[HEIGHT]
           ,[WEIGHT]
           ,[HAIR]
           ,[EYES]
           ,[SSN]
           ,[OCA]
           ,[IDNO]
           ,[IDSTATE]
           ,[IDTYPE]
           ,[BIRTHPLACE]
           ,[NATION]
           ,[HOUSENU]
           ,[QUAD]
           ,[STREET]
           ,[HOUSESU]
           ,[CITY]
           ,[STATE]
           ,[ZIP]
           ,[DISTGRID]
           ,[ECSO_BAK]
           ,[ECSOIDBAK]
           ,[GENAPPEAR]
           ,[DEMEANOR]
           ,[WEAPON]
           ,[WEAPFEAT]
           ,[CLOTHING]
           ,[C01]
           ,[C02]
           ,[C03]
           ,[C04]
           ,[C05]
           ,[C06]
           ,[C07]
           ,[C08]
           ,[C09]
           ,[C10]
           ,[C11]
           ,[C12]
           ,[C13]
           ,[C14]
           ,[C15]
           ,[DESCRIPT1]
           ,[DESCRIPT2]
           ,[ARREST]
           ,[STATEMENT]
           ,[END]
           ,[ETHNICITY]
           ,[RESIDENTSTATUS]
           ,[DISPOSITION]
           ,[DRUGTYPE1]
           ,[DRUGTYPE2]
           ,[DRUGTYPE3]
           ,[DRUGQUANTITY1]
           ,[DRUGQUANTITY2]
           ,[DRUGQUANTITY3]
           ,[DRUGMEASUREMENT1]
           ,[DRUGMEASUREMENT2]
           ,[DRUGMEASUREMENT3]
           ,[UNIQUEKEY]
           ,[ALIAS]
           ,[COMPLEXION]
           ,[BUILD]
           ,[FEATURES]
           ,[P_STNO]
           ,[P_STDIR]
           ,[P_STREET]
           ,[P_APT]
           ,[P_CITY]
           ,[P_STATE]
           ,[P_ZIP]
           ,[P_DATE]
           ,[B_STNO]
           ,[B_STDIR]
           ,[B_STREET]
           ,[B_APT]
           ,[B_CITY]
           ,[B_STATE]
           ,[B_ZIP]
           ,[B_PHONE]
           ,[B_NAME]
           ,[XMITSORTDATE]
           ,[COD_TYPE1]
           ,[COD_TYPE2]
           ,[COD_TYPE3]
           ,[COD_TYPE4]
           ,[COD_TYPE5]
           ,[ADD_SOURCE]
           ,[OCCU]
           ,[INS_NO]
           ,[CITIZEN]
           ,[IND_ALCOHOL]
           ,[IND_DRUGS]
           ,[L_PHONE]
           ,[RES_TYPE]
           ,[CODEF_NUM]
           ,[TRANSMITTED]
           ,[P_PHONE]
           ,[ZONE]
           ,[GEOSUB2]
           ,[GEOSUB3]
           ,[LATITUDE]
           ,[LONGITUDE]
)
     SELECT
           [BEGIN]
           ,[ARRESTNO]
           ,[ARRESTNOB]
           ,[ARRESTTYPE]
           ,[WARSTATUS]
           ,[CODE]
           ,[SHOWCODE]
           ,[ECSOID]
           ,[LNAME]
           ,[FNAME]
           ,[MNAME]
           ,[TITLE]
           ,[DOB]
           ,[APPROX_AGE]
           ,[RACE]
           ,[SEX]
           ,[HISPANIC]
           ,[HEIGHT]
           ,[WEIGHT]
           ,[HAIR]
           ,[EYES]
           ,[SSN]
           ,[OCA]
           ,[IDNO]
           ,[IDSTATE]
           ,[IDTYPE]
           ,[BIRTHPLACE]
           ,[NATION]
           ,[HOUSENU]
           ,[QUAD]
           ,[STREET]
           ,[HOUSESU]
           ,[CITY]
           ,[STATE]
           ,[ZIP]
           ,[DISTGRID]
           ,[ECSO_BAK]
           ,[ECSOIDBAK]
           ,[GENAPPEAR]
           ,[DEMEANOR]
           ,[WEAPON]
           ,[WEAPFEAT]
           ,[CLOTHING]
           ,[C01]
           ,[C02]
           ,[C03]
           ,[C04]
           ,[C05]
           ,[C06]
           ,[C07]
           ,[C08]
           ,[C09]
           ,[C10]
           ,[C11]
           ,[C12]
           ,[C13]
           ,[C14]
           ,[C15]
           ,[DESCRIPT1]
           ,[DESCRIPT2]
           ,[ARREST]
           ,[STATEMENT]
           ,[END]
           ,[ETHNICITY]
           ,[RESIDENTSTATUS]
           ,[DISPOSITION]
           ,[DRUGTYPE1]
           ,[DRUGTYPE2]
           ,[DRUGTYPE3]
           ,[DRUGQUANTITY1]
           ,[DRUGQUANTITY2]
           ,[DRUGQUANTITY3]
           ,[DRUGMEASUREMENT1]
           ,[DRUGMEASUREMENT2]
           ,[DRUGMEASUREMENT3]
           ,[UNIQUEKEY]
           ,[ALIAS]
           ,[COMPLEXION]
           ,[BUILD]
           ,[FEATURES]
           ,[P_STNO]
           ,[P_STDIR]
           ,[P_STREET]
           ,[P_APT]
           ,[P_CITY]
           ,[P_STATE]
           ,[P_ZIP]
           ,[P_DATE]
           ,[B_STNO]
           ,[B_STDIR]
           ,[B_STREET]
           ,[B_APT]
           ,[B_CITY]
           ,[B_STATE]
           ,[B_ZIP]
           ,[B_PHONE]
           ,[B_NAME]
           ,[XMITSORTDATE]
           ,[COD_TYPE1]
           ,[COD_TYPE2]
           ,[COD_TYPE3]
           ,[COD_TYPE4]
           ,[COD_TYPE5]
           ,[ADD_SOURCE]
           ,[OCCU]
           ,[INS_NO]
           ,[CITIZEN]
           ,[IND_ALCOHOL]
           ,[IND_DRUGS]
           ,[L_PHONE]
           ,[RES_TYPE]
           ,[CODEF_NUM]
           ,[TRANSMITTED]
           ,[P_PHONE]
           ,[ZONE]
           ,[GEOSUB2]
           ,[GEOSUB3]
           ,[LATITUDE]
           ,[LONGITUDE]
 FROM ARREST.DBO.arr_PER_obtsnoid

GO
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2012-04-17 : 09:55:13
Is there a float or decimal value coming in and you want to put it into a varchar(10)?

Try something like
convert(varchar(10),convert(decimal(10,0),YourColumn))

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts
Posted - 2012-04-17 : 09:56:14
checking. please hold.
Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts
Posted - 2012-04-17 : 10:04:27
I'm in the process of removing columns from both sides of the select that i don't really need to make it easier to isolate the offending data. this is going to take a while. I'll be back.
Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts
Posted - 2012-04-17 : 15:36:29
it turned out to be erroneous data in the SSN column. it was due to allowing SSIS to bring that column in from excel as a FLOAT rather than a varchar. The number it converted it to was approximately 40 digits long. Using LEN in a query would not pick up the length of a float, so i had to convert the column to a varchar and reimport it, at which point i could identify the offending data.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site