Blogger Widgets
  • Sharing Photos using SignalR
  • TFS Extenstion - allows copy work items between projects
  • Displaying jquery progressbar with ajax call on a modal dialog
  • Managing windows services of a server via a website
  • Exploring technologies available to date. TechCipher is one place that any professional would like to visit, either to get an overview or to have better understanding.

Search This Blog

Friday 2 December 2011

SQL Server Error : Arithmetic overflow error converting expression to data type int

After running a simple aggregate sql "select sum(imagesize) from images" is producing following error:-
Arithmetic overflow error converting expression to data type int
The table "images" is a very simple, having datatype of imagesize as bigint. The table has at least 2 million rows and should not be a problem at all. Tried number of options but no luck and finally found out the issue is actually because of the aggregate function it self. Aggregate function "sum" returns int but the totals of imagesize field is exceeding the size of int and hence was showing the error message. So the fix for this is changing the sql to return bigint as follows :-
select sum(cast(imagesize as bigint)) as 'TotalImageSize' from images
Now this worked.
Your most unhappy customers are your greatest source of learning. – Bill Gates

1 comments:

Copyright © 2013 Template Doctor . Designed by Malith Madushanka - Cool Blogger Tutorials | Code by CBT | Images by by HQ Wallpapers