[RESOLVED] Conversion of varchar value' overflowed an INT2 column

[JuggaloBrotha]

I have a query where I'm inserting a bunch of records from a table on varchars (imported from a text file) into a table with correct smallint/int fields along with some other conversions and after 2.5 hours it's giving the error:

The conversion of the varchar value '34474' overflowed an INT2 column. Use a larger integer column.

My query is:

Insert Into [dbo].[GeoFullDemos]
Select [Zip9]
      ,SubString([Zip9], 1, 5) As [Zip5]
      ,SubString([Zip9], 6, 4) As [Zip4]
      ,Cast([LAT] As Float) As [Latitude]
      ,Cast([LON] As Float) As [Longitude]
      ,Cast([BLOCK] As VarChar(15)) As [CensusBlock]
      ,Cast([BLKGRP] As VarChar(15)) As [BlockGroup]
      ,Cast([STUSAB] As Char(2)) As [StateAbbr]
      ,Cast([STATE_FIPS] As Char(10)) As [StateFips]
      ,Cast([STNAME] As VarChar(20)) As [StateName]
      ,Cast([COUNTY_FIPS] As Char(5)) As [CountyFips]
      ,Cast([CNTYNAM] As Char(5)) As [CountyName]
      ,Cast([MACODE] As VarChar(30)) As [MACode]
      ,Cast([MANAME] As VarChar(40)) As [MAName]
      ,Cast([PONAME] As VarChar(25)) As [POName]
      ,Cast([CITYNAME] As VarChar(30)) As [CityName]
      ,Cast([CITY_FIPS] As VarChar(30)) As [CityFips]
      ,Cast([CBSANAME] As VarChar(45)) As [CBSAName]
      ,Cast([CBSACODE] As VarChar(5)) As [CBSACode]
      ,Cast([CBSATYPE] As VarChar(1)) As [CBSATypeCode]
      ,Cast([AREACODE] As Char(3)) As [AreaCode]
      ,Cast([ACSTTPOP0] As VarChar(5)) As [AgeTotal]
      ,[ImportProcessing].[GetAgeCode]([ACSAGE4Y0],[ACSAGE59],[ACSAGE1014],[ACSAGE1519],[ACSAGE2024],[ACSAGE34Y0],[ACSAGE44Y0],[ACSAGE54Y0],[ACSAGE64Y0],[ACSAGE65Y0]) As [AgeCode]
      ,Case When IsNull(Try_Cast([ACSAGE17Y0] As SmallInt), 0) > = 100 Then 'Y' Else 'N' End As [Under18Present]
      ,[ImportProcessing].[GetMaritalStatusCode]([NEVMAR0],[MARRIED0],[WIDOW0],[DIVORC0]) As [MaritalStatusCode]
      ,[ImportProcessing].[GetRaceTypeCode]([WHITE0],[BLACK0],[AMIND0],[ASIAN0],[HAWAII0],[OTHRACE0],[TWORACES0],[HISPAN0],[WHNOHSP0]) As [RaceCode]
      ,[ImportProcessing].[GetHouseholdTypeCode]([MARKID0],[MKID0],[FKID0],[NOFMHH0],[SINGLE0]) As [HouseholdTypeCode]
      ,[ImportProcessing].[EducationLevelCode]([TT8GR0],[TT12GR0],[TTHS0],[TTSMCOL0],[TTASSOC0],[TTBACH0],[TTGRAD0]) As [EducationLevelCode]
      ,[ImportProcessing].[GetMaleArmedForcesCode]([MARMFRC0],[MEMPLOY0],[MUNEMPL0],[MNOWORK0]) As [MaleArmedForcesCode]
      ,[ImportProcessing].[GetFemaleArmedForcesCode]([FARMFRC0],[FEMPLOY0],[FUNEMPL0],[FNOWORK0]) As [FemaleArmedForcesCode]
      ,[ImportProcessing].[GetIncomeLevelCode]([INC10HH0],[INC15HH0],[INC20HH0],[INC25HH0],[INC30HH0],[INC35HH0],[INC40HH0],[INC45HH0],[INC50HH0],[INC60HH0],[INC75HH0],[INC100HH0],[INC125HH0],[INC150HH0],[INC200HH0],[INC201HH0]) As [IncomeLevelCode]
      ,IsNull(Try_Cast([MEDHHIN0] As Int), 0) As [MedianHHIncome]
      ,IsNull(Try_Cast([AGGHHIN0] As Int), 0) As [AggregateHHIncome]
      ,IsNull(Try_Cast([PCAPIN0] As Int), 0) As [PerCapitaIncome]
      ,IsNull(Try_Cast([MDFAMIN0] As Int), 0) As [MedianFamilyIncome]
      ,IsNull(Try_Cast([ACSTOTHU] As SmallInt), 0) As [TotalHousingUnits]
      ,[ImportProcessing].[GetHomeOccupiedCode]([ACSOWOCHU0],[ACSRNOCHU0]) As [HomeOccupiedCode]
      ,IsNull(Try_Cast([ACSAVGHHSZ] As Decimal(5,2)), 0.00) As [AverageHouseholdSize]
      ,[ImportProcessing].[GetUnitsInStructureCode]([UNIT1D0],[UNIT1AT0],[UNIT4S0],[UNIT19S0],[UNIT20S0]) As [UnitsInStructureCode]
      ,IsNull(Try_Cast([MDYRBLT0] As SmallInt), 0) As [MedianYearBuilt]
      ,[ImportProcessing].[GetHomeValueCode]([H30VAL0],[H60VAL0],[H100VAL0],[H200VAL0],[H300VAL0],[H500VAL0],[H501VAL0]) As [HomeValueCode]
      ,IsNull(Try_Cast([MEDVALO0] As Int), 0) As [MedianValueOwnerOccupiedHousing]
      ,[ImportProcessing].[GetRentAmountCode]([NORENT0],[G200RNT0],[G300RNT0],[G500RNT0],[G750RNT0],[G1000RNT0],[G1001RNT0]) As [RentAmountCode]
From [ImportProcessing].[GeoFullDemoStaging];

Is there a way to find out what column it's throwing this error on? I'm not finding anything in the Sql Logs which is where I was hoping it would be logged.

Also I've tried commenting out each of the columns individually and it runs fine, but then doesn't when all of the columns are in the query, which has me wondering what the server is even doing when it's running the full thing.

[TysonLPrice]

Can you do a select of the raw data, not conversions, and look at the numbers displayed. A very large on might jump out at you. Or, maybe just go down the list changing int to BIGINT until you find it (if this is MS SQL).

[techgnome]

Well, the max value of a SmallInt is 32,676 ... and 34474 definitely exceeds that... there are two places where you cast to a SmallInt:
Case When IsNull(Try_Cast([ACSAGE17Y0] As SmallInt), 0) > = 100 Then 'Y' Else 'N' End As [Under18Present]
and
IsNull(Try_Cast([ACSTOTHU] As SmallInt), 0) As [TotalHousingUnits]

My guess is that it has to be one of those. But I also see that you're calling a number of other functions... the problem could lay in one of those calls too.
If I had to guess, I'd look at the ACSTOTHU field... that seems to be the one most likely to be the one causing the issue.

-tg

[JuggaloBrotha]

Can you do a select of the raw data, not conversions, and look at the numbers displayed. A very large on might jump out at you. Or, maybe just go down the list changing int to BIGINT until you find it (if this is MS SQL).

When I first built the final table from the source data (no documentation file was sent so I had to find the max length of each field and whether it was all numeric or had other characters in it too) and none of the smallint/int fields had the length of characters to exceed an int column. It was a very tedious process.

Well, the max value of a SmallInt is 32,676 ... and 34474 definitely exceeds that... there are two places where you cast to a SmallInt:
Case When IsNull(Try_Cast([ACSAGE17Y0] As SmallInt), 0) > = 100 Then 'Y' Else 'N' End As [Under18Present]
and
IsNull(Try_Cast([ACSTOTHU] As SmallInt), 0) As [TotalHousingUnits]

My guess is that it has to be one of those. But I also see that you're calling a number of other functions... the problem could lay in one of those calls too.
If I had to guess, I'd look at the ACSTOTHU field... that seems to be the one most likely to be the one causing the issue.

-tg

I've tried doing a Select Count(*) From (Select IsNull(Try_Cast([ACSTOTHU] As SmallInt), 0) As [TotalHousingUnits] From [ImportProcessing].[GeoFullDemoStaging]) As a; on each of those columns and the queries didn't error.

I also did the same for each of the function calls individually and none of them errored, but like I said when I run all this together as one big query it gives me the error message and I would like to know where Sql logs this so I can see if it tells me which column it had the problem on. We're using Sql Server 2014.

[szlamany]

Simply remove the INSERT so it's a simple SELECT

Comment out EVERYTHING from LATITUDE down with /*..... */ syntax.

Run the SELECT - and it will work - right?

Now move down the "/*" token a few items in the SELECT list - run it until it fails...

Should take you 2 minutes to find the column with the issue.

[JuggaloBrotha]

Simply remove the INSERT so it's a simple SELECT

Comment out EVERYTHING from LATITUDE down with /*..... */ syntax.

Run the SELECT - and it will work - right?

Now move down the "/*" token a few items in the SELECT list - run it until it fails...

Should take you 2 minutes to find the column with the issue.

I already said I did that, no errors, run the full thing with the insert and get the error.
The destination table matches the query output already too.

[szlamany]

Alter the output table to have larger datatype and see it works. Then alter table each column back to your desired size. Might that work?

[techgnome]

The destination table matches the query output already too.

That might be the issue then.. if the value 34474 exceeds the max value for a smallint, you can't stuff it into a smallint field. The select probably doesn't care if the type overfills, but the insert will certainly care.

On a side note... I've never heard of a type "Int2" ... could it be a custom data type by chance? But then, I don't see it specifically in the SQL provided, which suggests maybe it's an issues with the table design after all.

-tg

[JuggaloBrotha]

On a side note... I've never heard of a type "Int2" ... could it be a custom data type by chance? But then, I don't see it specifically in the SQL provided, which suggests maybe it's an issues with the table design after all.

-tg

I've never heard of an "Int2" datatype either, but it's what the Sql error message says.
I'll try it with an Into clause and see what table the select generates.

[TysonLPrice]

I've never heard of an "Int2" datatype either, but it's what the Sql error message says.
I'll try it with an Into clause and see what table the select generates.

When I try to create a table with INT2 I get this:

Msg 2715, Level 16, State 7, Line 2
Column, parameter, or variable #1: Cannot find data type int2.

Maybe it is something internal?

[techgnome]

That's why I wondered if it was a custom type of some kind. I know that in our DB Domain, we have a couple of custom datatypes that on the surface may look like numbers but are actually custom datatypes.

-tg

[TysonLPrice]

That's why I wondered if it was a custom type of some kind. I know that in our DB Domain, we have a couple of custom datatypes that on the surface may look like numbers but are actually custom datatypes.

-tg

I should have mentioned we are on MS SQL 2008. Maybe it is a newer datatype...

[JuggaloBrotha]

I should have mentioned we are on MS SQL 2008. Maybe it is a newer datatype...

It's not a datatype I know of in Sql Server 2014 either, but googling the error it goes back a few versions....

Here's a screenshot of me getting the error:

[szlamany]

I found this

"bigint int(INT4) smallint(INT2) tinyint(INT1) "

[TysonLPrice]

I found this

"bigint int(INT4) smallint(INT2) tinyint(INT1) "

Did you find it relative to PostgresSQL?

https://stackoverflow.com/questions/...in-postgressql

[szlamany]

No - in this book - chart here

https://books.google.com/books?id=Pi...%20sql&f=false

The 1, 2 and 4 are simply the size of the "word" - byte, word, longword for those old timers...

[TysonLPrice]

So that makes me wonder about that error message. This is what I get:

declare @Int int
set @int = 100000000000000

Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type int.

But I have no idea what is going on under the covers as far as messaging goes.

[szlamany]

Can you turn INDEXES off to speed up the INSERT? I'm assuming you might have a couple of indexes...

Also - do you triggers that are being fired by the touching of this destination table??

[JuggaloBrotha]

Can you turn INDEXES off to speed up the INSERT? I'm assuming you might have a couple of indexes...

Also - do you triggers that are being fired by the touching of this destination table??

No indexes defined yet, also no triggers instead we use functions and stored procedures.
But when we do define them our routines drop them, truncate the table, insert the new data, then add them which is all done in stored procedures; we do that because we regularly work with sets exceeding 200 million records.

[JuggaloBrotha]

Took me a while but I was able to find the offending field and change my conversion to Int instead of SmallInt, was like finding a needle in a haystack. Why doesn't Sql Server log this stuff?

[TysonLPrice]

Why doesn't Sql Server log this stuff?

Then we wouldn't make the big bucks

[szlamany]

There could be dozens and hundreds of issues (and more!) in a complex query feeding into another table. How would you start the logging? Maybe it's the result of some math or formula (did the wider "datatype" win out??) making the source of the error not as clear. Maybe it's the result of collation settings.

The point to take away here is that there is no "check data for clear sailing" pass done. The data is moved and if the move is not legal - hammer drops...