Msg 244, Level 16 - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Msg 244, Level 16

Author

Topic

richardg
Starting Member

14 Posts

Posted - 2011-02-08 : 18:34:45

When I run a simple query in SQL 2005 I get:

Msg 244, Level 16, State 1, Line 1
The conversion of the varchar value '102001000010' overflowed an INT1 column. Use a larger integer column.

I have combed the data with a fine toothcomb to find the erroneous data but to no avail. The two different people have copied the data (only 4.1 GB, no big deal) to their systems and they have run it without any error. The only difference was that the two systems without the error were SQL 2008 R2. Although I cannot believe that the difference in SQL version would explain the error.

What do you think?

Thanks!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-08 : 18:38:00

What is the query?

At some point you are trying to look at that STRING but treating it is an INTEGER. Since it well exceeds the maximum number stored by an INTEGER field that is why the error is thrown. It isn't an data issue, it is a query issue..

Post the offending SQL Query and we can get you sorted out.

Poor planning on your part does not constitute an emergency on my part.

richardg
Starting Member

14 Posts

Posted - 2011-02-08 : 18:47:36

Thanks for replying that quickly!
The query is run in accounting software package.

Here is the query:
SELECT pvtArItemHist.PartID, pvtArItemHist.Qty, pvtArItemHist.TotSales, inqInItem.ProductLine, pvtArItemHist.Period, pvtArItemHist.Year
FROM KWI.dbo.inqInItem inqInItem, KWI.dbo.pvtArItemHist pvtArItemHist
WHERE inqInItem.ItemId = pvtArItemHist.PartID AND ((pvtArItemHist.Period=2) AND (pvtArItemHist.Year=2010))

Now, when it is set up like this, there is no problem. BUT, when you change the Fiscal year to 2011, either GL Period 1 or GL period 2 or both, that's when the error comes up.
So, the query works, but the data doesn't cooperate when it comes to 2011. And that is only in a system that is running SQL 2005. When the same data is run in a SQL 2008, it works fine -even when filtering on the year 2011.
Go figure. Maybe there is a limitation in SQL 2005 that doesn't exist in SQL 2008??
Thoughts?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-08 : 18:56:47

It isn't likely that it is some difference in the SQL Engines.

Which table and which field is that data in? (it is likely that the data exceeding the integer dimension is only in the year 2011 data!). Also, where is this data "going"..

What is happening is likely in this part: inqInItem.ItemId = pvtArItemHist.PartID

That error will typically be thrown on that type of statement because SQL will try to convert to identical datatypes. If one side is an integer (which is common for an ID field), and the other is a string..it will try to convert them.

I would bet that this would work (without even knowing more)

WHERE CAST(inqInItem.ItemId as bigint) = Cast(pvtArItemHist.PartID as bigint)

SOMETHING must be different in the structures of the tables between the two servers. On every version I have, that value would surely throw the same error.Just typing SELECT CAST('102001000010' as int) is all the checking necessary to prove it.

I don't recall anything for 2005 to 2008. There is likely something else going on in 2008 where that record is not even being returned..

Poor planning on your part does not constitute an emergency on my part.

richardg
Starting Member

14 Posts

Posted - 2011-02-08 : 19:10:26

Thanks for the suggestion. But... that gives me another error:
"Error converting data type varchar to bigint"

The ItemID and PartID fields are varchar types.

The fact that it works without problem in SQL 2008, is still the crucial thing here. The ability to convert data types should not have changed between the versions.
My thinking is that this is a piece of bad data. If I knew what field it was referring to, I have other info that will narrow the date it was entered. What annoys most people with many of Microsoft's Error messages is that they are non-specific.
On the other hand, you seem convinced that the error comes from ItemId or PartId. I can limit it to that.
Thanks!
You help is very appreciated!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-08 : 19:27:57

That is just it...it "doesn't" change significantly enough to cause that error.They wouldn't suppress that error in SQL 2008. Without more info..it is impossible to say what is different in the 2008 R2 version of this data...but it is surely structural--not version related.

I am guessing on the equality of the two fields. In my experience, that is where the error is thrown..but if both fields are varchar's then that is NOT likely the cause.

Is this data being inserted INTO a table somewhere? looking back at the error, it seems specific to the destination "Use a larger integer column"....might want to look there for INT columns and make sure that the order of columns is correct. You may be trying to insert the the partid into the year column or something.

ALso, what are the datatypes of these two fields: ((pvtArItemHist.Period=2) AND (pvtArItemHist.Year=2010))

If period and year are NOT integer columns, the value you referenced as the error could be in one of those two. It would try to force the column to be an integer since that is what the criteria specifies.

You could also try this real quick to "see" if that is where it might be..if the same error occurs, it isn't one of them.
((pvtArItemHist.Period='2') AND (pvtArItemHist.Year='2010'))

What is the structure of the destination? I would focus on that...

If you run the actual select statement and get an error

Safe to say---there is surely something different structurally in the 2008 info. That string would ALWAYS overflow an integer column

To find the offending data, you will have to use that string as criteria against each column that is a varchar long enough to contain it.

Poor planning on your part does not constitute an emergency on my part.

richardg
Starting Member

14 Posts

Posted - 2011-02-08 : 19:43:02

Both GL Period and Fiscal Year are "smallint"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-08 : 19:52:38

Be sure that the structures are identical on both servers, and double check the destination table. It is pretty identifiable that that value is trying to get inserted into an int column and won't fit. Something is different in one of the two setups between the 2008 and 2005 versions. (Not version control, definitely structural somewhere) That string would always throw that error when trying compare to an integer, or when attempting to insert it into an integer column.

Poor planning on your part does not constitute an emergency on my part.

richardg
Starting Member

14 Posts

Posted - 2011-02-08 : 20:22:28

You wrote:
"definitely structural somewhere"
Can you please point me in the right direction. I looked at the Database Properties (on the SQL 2005 machine) and I didn't see anything "weird" - but I am really a newbie.
When you say "structure" - where would that be.
All we did was a SQL Backup of the entire database (4+ GB) and passed to our machines here, which happen to run SQL 2008 R2. The fact that the data is PLUS 4 GB, woulds that matter? They just recently exceeded the 4 GB amount.
Grateful for all your help!
R

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-08 : 20:53:56

The size of the db doesn't matter.

by structure, I mean that something in the two tables being queried, or the destination where this data is going is different.

If you did a simple backup/restore, then that raises some other questions. Was the version on SQL 2008 R2 set to compatibility 90 for SQL 2005?

There are minor changes, but nothing at all that would "directly" make this error suddenly pop up.

This is puzzling though..Have you identified the column where this particular value resides? That is really the column of interest.

Where does this data end up? is this inside a stored procedure or view? does it get inserted somewhere? I am still leaning toward whatever destination it is going to will lead you down the right path to an answer.

Find the column in the below where that data actually resides, the follow that data...at some point that column is trying to end up as an integer. It would overflow in every version of SQL or any other DBMS system like it...INT has a finite capacity. There isn't an option to change it (unless I am really uninformed, which is completely plausible)

Poor planning on your part does not constitute an emergency on my part.

richardg
Starting Member

14 Posts

Posted - 2011-02-09 : 10:04:41

Thanks again for your continued help!
You wrote:
"Was the version on SQL 2008 R2 set to compatibility 90 for SQL 2005?"
I tried determining that, but I am not sure I know how. I tried looking it up, found a Stored Procedure called: sp_helpmergepublication, but I am not sure how to set it up tom run correctly.
Also, since the query works fine in 2008, it apparently imported correctly - so well that the data error (if that what it was) was "eliminated" or at least "masked".
Then you wrote:
"Have you identified the column where this particular value resides? That is really the column of interest."
Yes, that is the problem: The Microsoft Error message does not help in identifying the "bad" column. We know (?) that it is a varchar field and since the query only have a few fields, I have gone through ALL fields, and looked for values that exceed their columns' maximum values. Found nothing. The heart of the mater is that I cannot determine what column/field is the "culprit".

Then you wrote:
"Where does this data end up? is this inside a stored procedure or view? does it get inserted somewhere? I am still leaning toward whatever destination it is going to will lead you down the right path to an answer"
Well, it is just a report that shows a bunch of sales figures. That's where it is ending up. The end-user here is running a MS Query (with
the above SQL Code). It is not connected to any SP - it does not get inserted anywhere. They just want to see the results. (So, in the meantime I ran it here on my PC - and created a Excel spreadsheet for them, so that they at least had the data). But that would cumbersome in the long run to do each time).
When you say destination, I am not sure what you mean?
I apologize for my limited understanding of SQL and I really appreciate the help you have been providing and the patience you have shown.
THANKS!
R

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-09 : 11:59:16

Don't look for a column that exceeds the columns maximum values (you won't find any--if the value is IN the column, it can't exceed that columns maximum size). Look for a varchar field which contains '102001000010' as one of it's values.

You say this query works on 2008, but not on 2005?

This query is written directly from excel using MS Query?

Does the query work in SQL Query Analyzer?

If you have the data in excel, you can look for the value you there by hitting CTRL-A then CTRL-F and finding 102001000010 in a cell. That would tell you where the column is.

Is the error being returned via Excel's MS Query? or when you run the statement in Query Analyzer?

Poor planning on your part does not constitute an emergency on my part.

richardg
Starting Member

14 Posts

Posted - 2011-02-09 : 12:32:57

Well, it is a MS Query. Period. The other things I have run have been for diagnostic purposes, that's all. When I runt eh same query in SQL, on their system, the same error occurs. And Yes, I did do a search for the "102...." value and cannot find it any column. I think that value is not really a "value" but a conversion of something else. If the MS Error Message was a little more specific, we could narrow it down.

Remember the wording of the error message:
The conversion of the varchar value '102001000010' overflowed an INT1 column. Use a larger integer column.
It does not tell us what column but it seems to be trying to convert a varchar value to INT. BUT ONLY IF YOU SELECT DATA from 2011! And yes, I did go through the data - but I think I will never find the value "102........." because I am sure it is not in there - looking like that. It picks up something else, that is in varchar format, and then it tries to convert it.

Now, if you do the same thing on a SQL 2005 system (as the end-user has) in a different environment, it works fine.
So, in summary, it works fine in SQL 2008 and in SQL 2005 (outside of the end-user's walls).
So, therefore I think you were on the right right track when you were leading me towards looking at the setup of THEIR SQL 2005. Something is screed up, but I don't know what. Not the database, not the version, but the setup of SQL 2005.
One more piece of info. The end-user's server is running 64 bit. Is that a factor?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-09 : 13:02:09

quote:
Originally posted by richardg

Well, it is a MS Query. Period. The other things I have run have been for diagnostic purposes, that's all. When I runt eh same query in SQL, on their system, the same error occurs. And Yes, I did do a search for the "102...." value and cannot find it any column.

Well ain't that something. The column value it is throwing the error on doesn't exist in the actual data? Cool.

quote:

I think that value is not really a "value" but a conversion of something else. If the MS Error Message was a little more specific, we could narrow it down.

Remember the wording of the error message:
The conversion of the varchar value '102001000010' overflowed an INT1 column. Use a larger integer column.
It does not tell us what column but it seems to be trying to convert a varchar value to INT. BUT ONLY IF YOU SELECT DATA from 2011! And yes, I did go through the data - but I think I will never find the value "102........." because I am sure it is not in there - looking like that. It picks up something else, that is in varchar format, and then it tries to convert it.

.
Selecting data from the YEAR 2011 in the actual data?

Just for giggles..how is that "year" getting into the query? is it a parameter based on an excel cell value?

quote:

Now, if you do the same thing on a SQL 2005 system (as the end-user has) in a different environment, it works fine.
So, in summary, it works fine in SQL 2008 and in SQL 2005 (outside of the end-user's walls).
So, therefore I think you were on the right right track when you were leading me towards looking at the setup of THEIR SQL 2005. Something is screed up, but I don't know what. Not the database, not the version, but the setup of SQL 2005.
One more piece of info. The end-user's server is running 64 bit. Is that a factor?

Well that is a relief. It works fine in SQL, which means there really is no error there. Something on their end is messed up if the error only occurs there. 64 bit shouldn't matter for SQL engine, especially if it is purely an MS Query. The key element here is that value is being created and passed to the query from excel on THEIR side.

If the value doesn't exist in the data itself (on the SQL server), then start looking at the MS query and how it is generated (it is a stored querydef file? generated in code? does it have parameters linked to cell values which may be containing bad data?

[/quote]

Poor planning on your part does not constitute an emergency on my part.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-09 : 20:38:46

quote:
Originally posted by richardg

When I run a simple query in SQL 2005 I get:

Msg 244, Level 16, State 1, Line 1
The conversion of the varchar value '102001000010' overflowed an INT1 column. Use a larger integer column.

I am a bit suspicious about the error message. The "Msg 244" part does looks like comming from SQL Server. But the "INT1 column" & "Use a larger..." doesn't seems normal.
This is what i get from my SQL Server 2005

"The conversion of the varchar value '102001000010' overflowed an int column. Maximum integer value exceeded."

KH
[spoiler]Time is always against us[/spoiler]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-09 : 20:44:00

The error looks to me like something that might come from a .NET application. It doesn't seem to come from Excel either.

Poor planning on your part does not constitute an emergency on my part.

richardg
Starting Member

14 Posts

Posted - 2011-02-10 : 16:06:55

To Khtan and to dataguru1971,

The error that is generated display the exact same message, whether you run it in MS Query or in SQL. Here is the message (from MS Query or SQL, take your pick):
Msg 244, Level 16, State 1, Line 1
The conversion of the varchar value '102001000010' overflowed an INT1 column. Use a larger integer column.

Whether you are in a MS Query (and open the SQL code option) or in SQL, the code is the same:

SELECT pvtArItemHist.PartID, pvtArItemHist.Qty, pvtArItemHist.TotSales, inqInItem.ProductLine, pvtArItemHist.Period, pvtArItemHist.Year
FROM KWI.dbo.inqInItem inqInItem, KWI.dbo.pvtArItemHist pvtArItemHist
WHERE inqInItem.ItemId = pvtArItemHist.PartID AND ((pvtArItemHist.Period=2) AND (pvtArItemHist.Year=2011))
Note. the GL Period in the WHERE statement can be left out, set to 1 (January) or left at 2 (Feb) - the results are the same.
Note also that if you choose Year 2010, or 2009 or any other year, it also works fine.
Which of course leads us to believe that it's in the data.
And then again, running it on SQL 2005 and SQL 2008 on a computer outside of the client's, it works fine (when selecting Fiscal Year 2011).
That brings us back to what dataguru1971 wrote a while back:
"by structure, I mean that something in the two tables being queried, or the destination where this data is going is different."

Yes, it is weird. I cannot determine whether this is data related, computer-related or both or neither.

Thanks for your continued help. Really appreciate it

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-10 : 17:18:39

"The conversion of the varchar value '102001000010' overflowed an INT1 column. Use a larger integer column."

There is no such column datatype INT1 in SQL Server itself.

It is not directly data related, and when I keep considering structure..it is the column data types I am referring to. This is more typical of an asp.net error...INT1 indicates a 1 byte integer which must be <255 .INT2 would be -32767 to 32767. INT4 would be the equivalent of the SQL Server INT datatype.

Are you SURE this error is being generated by SQL Server in SQL Server Management Studio Query analyzer?

Is it the excel file itself throwing the error?

When you say the SQL is the same, that is not entirely true. There are subtle differences in syntax between MS Query and SQL Server. I spent a lot of time in both...I have never seen this an INT1 error like this (specifically INT1...that is not a datatype in Excel, Access, or SQL server...

Poor planning on your part does not constitute an emergency on my part.

richardg
Starting Member

14 Posts

Posted - 2011-02-10 : 17:44:43

I just rerun it in SQL2005 (on my client's PC) and:
"Msg 244, Level 16, State 1, Line 1
The conversion of the varchar value '102001000010' overflowed an INT1 column. Use a larger integer column."
When I said that it as "the same" I didn't mean that MS Query and SQL are the same, I meant that the Error messages are identical. See above. I could have copied the above error message from MS Query or from SQL, they are the same.
You wrote:
"Are you SURE this error is being generated by SQL Server in SQL Server Management Studio Query analyzer?
All I do is open SQL 2005 SQL Mgmt studio. I click on New Query, select the Database and then paste the code from MS Query. The results are identical.
Then you wrote:
"Is it the excel file itself throwing the error?"
Well, I don't know as I cannot make the query or view in SQL working, as the error prevents any further action. But when selecting another year, it works fine and then of course the Excel would look good too.

And I believe you when you say you have spent many hours in both MS Query and in SQL. You are the expert and I believe what you say. And when you say there is no such thing as a INT1 value in SQL Server, all I can say is: This is the error.
Really weird, as it works outside the walls of this client AND it works at the client site if you select another year......
So, we are back in this dead-end street: Is it data or is their system?
It seems to be one of those mysteries that doesn't have an explanation.
Go Figure!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-10 : 19:23:27

Run this on the SQL 2005 machine please:

SELECT TABLE_NAME,COLUMN_NAME,COLLATION, DATA_TYPE
FROM INFORMATION_SCHEMA.Columns where TABLE_NAME in ('inqInItem','pvtArItemHist')
ORDER BY TABLE_NAME

Let me see the results of that.

Also..

Can you run this --does this alone produce the error?
SELECT pvtArItemHist.PartID, pvtArItemHist.Qty, pvtArItemHist.TotSales, inqInItem.ProductLine, pvtArItemHist.Period, pvtArItemHist.Year
FROM KWI.dbo.inqInItem inqInItem, KWI.dbo.pvtArItemHist pvtArItemHist
WHERE inqInItem.ItemId = pvtArItemHist.PartID

It sounds as if the column containing 2011 is what is throwing the error...so it "sounds" as if the overflowing value is stored there.

Run this in SQL Query analyzer on the machine causing the error:

SELECT pvtArItemHist.Year
FROM pvtArItemHis
ORDER BY pvtArItemHist.Year DESC

and see if all of the values are 4 digit years.

Poor planning on your part does not constitute an emergency on my part.

richardg
Starting Member

14 Posts

Posted - 2011-02-10 : 20:06:57

How do I send you a file?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources