SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Tiny Trauma

By Mike Tanner,

The background.  We send a several dozen different sorts of communications to clients, and for emails, log which were sent and which failed.  Depending on the type of communication, failing emails are attempted to be automatically resent between 0 and 10 times, and a resend count kept.

The code had been in place for some time and was all working fine.  However, the other day I was doing some stats on historical emails, and got an Arithmetic overflow error' for some data sent somewhat over a year ago.

Here's an abridged version of the setup.  In table CommunicationDefn, there is

CommunicationID Int,
CommunicationName Varchar(50),
ResendLimit TinyInt,
etc. 

In table CommunicationLog, there is

CommunicationID Int,
ResendCount TinyInt,
etc.

I was actually using a view, CommunicationStatus, linking these, but I couldn’t see anything immediately wrong with it or the underlying data.  I eventually resorted to a 'binary chop' on the data by date to find the cause of the problem. It turned out that in the view there was a calculated field,

ResendLimit – ResendCount as RetriesLeft,

and for one now defunct communication, the ResendLimit was currently set to 2, but in one instance 3 had been sent before the limit had been reduced. This meant that RetriesLeft now had a value of -1, but negative values are not allowed for TinyInt, and this was the cause of the Arithmetic overflow.

Given the choice of casting to SmallInt in the view, or changing the definition, I decided to change the fields to SmallInt. The moral of the story: If an arithmetic calculation could be meaningful, you should probably use SmallInt rather than TinyInt data type, even if you think it will 'never' be negative.

 
Total article views: 893 | Views in the last 30 days: 893
 
Related Articles
FORUM

Arithmetic overflow

Arithmetic overflow

FORUM

arithmetic operations over strings

calculate arithmetic operation given a string; select '2*3' ....6

BLOG

Awarded – Microsoft Community Contributor Award 2012

Today, I’ve received an email from Microsoft stating that I’ve been awarded Microsoft Community Con...

BLOG

TinyInt problem in SSAS

If you have a source table that has a Tinyint primary key column defined as IDENTITY and you create ...

BLOG

Community recognition

On the first day of January 2017, I was honoured to receive an email naming me as a Microsoft MVP......

Tags
arithmetic overflow    
data type    
tinyint    
 
Contribute