?
Solved

Aritnmetic overflow error

Posted on 2005-04-05
29
Medium Priority
?
899 Views
Last Modified: 2008-01-09
Good morning expert,

When users clicks Submit on ASP web site they get
Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E57) [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type varchar. /ppoNEXTCIDMA/IMA_addMAP.asp, line 39
I think this is a database error but not sure.
System down.
Any idea how to troubleshoot?

Thanks.

Allen in Dallas
0
Comment
Question by:9apit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 7
  • +3
29 Comments
 
LVL 11

Expert Comment

by:Otana
ID: 13707586
Can you show us your code?
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13707593
You need to be troubleshooting the underlying stored procedure...capture the command sent by ASP by using SQL Profiler, then start working with that.

Could be as simple as submitting 21 characters into a varchar(20) field/parameter...

0
 
LVL 23

Expert Comment

by:adathelad
ID: 13707624
First step would be to look at the IMA_addMAP.asp source and see what SQL statement is being executed around line 39.

Also, see what data the users are entering before they click submit.

If possible, post these 2 bits of info up (blank out any confidential information if you need to)
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 18

Expert Comment

by:ShogunWade
ID: 13707633
Yep,  run up profiler capture the sql its execuuting and post it here, then im sure we can spot it.    If pos also tell us the parameters being used.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13707852
Hey...Allen in Dallas!  Didn't notice it was you....

If you need assistance on the profiler part, let us know...

Kevin in Dallas
0
 

Author Comment

by:9apit
ID: 13708208
Hello expert,  
This is the code in IMA_addMAP.asp from begnning thru line 62.  The line mentioned in the error message, line 39, begins
objRs.Open strSQL, objConn

+++++++++++beginning of code in file IMA_addMAP.asp

<%@ Language=VBScript %><% Response.CacheControl = "no-cache" %><% Response.AddHeader "Pragma", "no-cache" %>
<%response.expires=0%><%'on error resume next%>
<%'**********************************************************************
  'PURPOSE:
  ' code for adding and modifying the mapping information.
   
  ' MODIFICATION HISTORY:
  '  Date       Release   By                        Description
  '  ========== ========= ===============   =============================
  '  10/11/2002 1.0       kiran Indukuri    Initial creation
  '       20/08/2003 1.1              Kiran                        Fixed the bug for displaying duplicate CIDMA ID
  ' *********************************************************************
 
%>
<% ' Local Variable Declaration *************************************************************************
      Dim lfilename
      Dim limgno
      Dim lattNo
      Dim lrePricedID
      Dim i
      Dim strSQL
      i=0
   '****************************************************************************************************
%>
<%if Request.Form("Mode")="add" then %>
  <!-- #include file="openCon.txt"  -->  
    <%  
    lfilename=Request.Form("txtFileName")
    limgno=Request.Form("txtimageNo")
    lattNo=Request.Form("txtimageatt")          
      objRs.Open "SELECT * FROM ima_map_information where var_filename='" & lfilename & "' AND int_image_number=" & limgno,objConn,3,2
      if objRs.RecordCount>0 then %>
      <!-- #include file="closeCon.txt"  -->
      <%Response.Redirect "ima_Map.asp?status=3&lfilename=" & lfilename &  "&lrePricedID=" &lrePricedID
      end if
      objRs.Close       

      strSQL = "exec usp_ins_map_info " & cint(session("userid")) & ",'" & lfilename & "'," & limgno & "," & lattNo
      objRs.Open strSQL, objConn
      
      'objRs.open "select convert(char(6),getdate(),12) + convert(char(5),isnull(max(int_Reprice_claim_ID),10000) + 1),isnull(max(int_Reprice_claim_ID),10000) + 1  from ima_map_information where convert(char(10),dt_created,103) = convert(char(10),getdate(),103)",objConn,3,2
      'lrePricedID=objRs(1)
      lExternalClaimid=objRs(0)
      if lExternalClaimid <= 0 then %>
            <!-- #include file="closeCon.txt"  -->
            <%Response.Redirect "ima_Map.asp?status=2&lfilename=" & lfilename & "&limgno=" & limgno & "&lattNo=" & lattNo
      end if
      'objRs.close
             
      'objRs.Open "ima_Map_information",objConn,3,2
      'objRs.AddNew
      'objRs("int_user_id")=session("userid")
      'objRs("var_filename")=lfilename
      'objRs("int_image_number")=limgno
      'objRs("int_number_of_attachments")=lattNo
      'objRs("int_reprice_claim_id")=lrePricedID
      'objRs("var_external_claim_id")=lExternalClaimid
    'objRs("dt_Created")=now
    'objRs("dt_last_modified")=now
   
      'objRs.Update
      'objRs.close      
      %>      


+++++++++++++ end of code posted

Just above kine 39 the code sets strSQL to
usp_ins_map_info

++++++++ stored procedure code

/****** Object:  Stored Procedure dbo.usp_ins_map_info    Script Date: 5/7/2004 6:56:05 PM ******/

create procedure usp_ins_map_info (
                        @int_User_ID int,
                        @var_Filename varchar(15),
                        @int_Image_number int,
                        @int_number_of_attachments int --,
--                        @var_external_claim_id varchar(15) output
                        )

as
/*******************************************************************************
  PURPOSE:
    to insert a new record into map information table and return external claim id
    which is generated by system.

  PARAMETERS:
      int_User_ID       - User Id
      var_Filename    - Batch No
      int_Image_number - Starting Page No
      int_number_of_attachments - No of Attachments
      var_external_claim_id  - External Claim Id (OUTPUT)
   
  RESULT SET:
    returns  
          "var_external_claim_id" for success
          -1 for failure      

  MODIFICATION HISTORY:
    date       release   by        description
    ========== ========= ========= ===============================================
    01/03/2002 1.0       Vara      Initial creation
*******************************************************************************/

declare @rep_id                         bigint,
          @var_external_claim_id       varchar(30)

      begin tran ins_imap

      select @rep_id = isnull(max(int_Reprice_claim_ID),10000) + 1,
             @var_external_claim_id = convert(char(6),getdate(),12) + convert(char(5),isnull(max(int_Reprice_claim_ID),10000) + 1)
      from ima_map_information with (tablockx)
      where convert(char(10),dt_created,103) = convert(char(10),getdate(),103)

      insert into ima_map_information (int_User_ID, var_Filename, int_Image_number, int_number_of_attachments,int_Reprice_claim_ID,
                  var_external_claim_id, dt_created, dt_last_modified)
      values (@int_user_id, @var_filename, @int_image_number, @int_number_of_attachments,@rep_id,
                  @var_external_claim_id, getdate(), getdate())
      
      if @@error = 0
            begin
            commit tran ins_imap
            select @var_external_claim_id
            end
      else
            begin
            rollback tran ins_imap
              select -1
            end

return 0


GO

+++++++++end of stored procedure code


Now I must admit my ignorance. I am a VB/ASP guy. The DBA quit last week and I
know some Emterprise Manager and Query Analyzer In SQL Server 2000 but I don't
know how to "capture the command set to ASP ny using SQL Profiler" Is this something simple
you can tell me how to do or do I need to read the Help section on SQL Profiler?

Thanks

Allen in Dallas
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13708516
>>The DBA quit last week<<  See my profile :-)

--------------------------------------------------------------------

Profiler can be daunting until you get to know it...

Start it from the SQL Server Program group
File>>New Trace

Pick your server and authentication method

Go to the Events tab:
Remove everything
Add: Errors and Warnings (all items)
Add: Stored Procedures (all items)

Go to the Filters tab:
Under DatabaseID, enter the database id of the one containing the stored proc (find this in sysdatabases table from master db)
Under TextData, enter %usp_ins_map_info%

Get your application ready to a point the error is about to occur
Click Run in Profiler
Click whatever in teh application (Submit?)
Wait for the error
Stop profiler and look for the line that has the stored proc call and the parameters with it


That should get you close
0
 

Author Comment

by:9apit
ID: 13708874
Hello Kevin in Dallas,

Got everything done up to Click Run in Profiler. The application is web-based
ASP so (forgive my ignorance) I am not sure how to get it to run in the profiler.

Thanks.

Allen in Dallas
0
 

Author Comment

by:9apit
ID: 13709200
Hello Kevin,

I got the trace running and brought my web appl to the point where when
I click th Submit button it gives me the 500.001 error. I click submit.
I see in the SQL Profiler window beneath the name of the trace two
columns, Event Class and SPID the rows look like
TraceStart
SP- ExecContextHit       57
SP- CacheHit                58
SP- ExecContextHit       57
SP- CacheHit                57
SP- CacheHit                58
this is most of the about 60 or so entries although there is
RPC: Staring                58
SP- CacheInsert           58
SP: StmtStarting         58
SP: StmtStarting         58
SP: StmtComplete        58
and
RPC Starting               57
and
RPCComplete              57
Unfortunately I am not sure what I am looking
for. Any ideas?

Thanks.

Allen in Dallas
PS will contact thru database forum for investigation of consultancy  
0
 

Author Comment

by:9apit
ID: 13709309
One  more thing I have the trace runnung on one machine on my desk and the web appl
running on another when I do something to the web appl I can see it making entries to the
trace when I do something in the appl like log in or hit the Submit button. But not sure what
the entries mean.

Thanks.

Allen in Dallas
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13709313
personally i think profiler is the wrong way to go to solve this one, since you have the sorce code easily to hand.

Whait I would do is to place a response.write into the code to see exactly what string is being passed to sql and then use query analyser to issue that statement, this will make it easier to pinpoint the error.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13709731
ShogunWade presents a good alternative to Profiler...they both net the same thing I assume.  I'm not an ASP guy so profiler is my only tool for this sort of troubleshooting.  Ultimately, I always want to see what the SQL engine actually received.  If response.write does that correctly go there.

If you want to stick with profiler...you are looking in the TextData column for the actual call to the stored procedure....along with all of the submitted parameters.  We will take that, copy it into Query Analyzer and go from there
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13709786
ShogunWade,

>>personally i think profiler is the wrong way to go to solve this one<<
And I agree with you, but you will find a carpenter always prefers to a use a hammer ...

9apit,

You seem to be assigning a bigint (@rep_id) to an int column (int_Reprice_claim_ID).  But that does not really explain the error. you mentioned originally.

You need to get the exact parameters passed to the stored procedure and then test in SQL Query Analyzer.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13709826
<offtopic>
Kevin,

I trust you did not find my poor sense of humor regarding the DBA acronym as offensive.

</offtopic>
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13709846
acperkins....nope, not at all...unless it was directed at me :P
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13709988
<offtopic>
Kevin,

I was referring to my joke in another thread, about what DBA stood for.

</offtopic>
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13710046
<way offtopic, sorry 9apit>

Yes, I know :-)

</way offtopic, sorry 9apit>
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13710169
>>And I agree with you, but you will find a carpenter always prefers to a use a hammer ...<<

When the only tool you own is a hammer, every problem begins to resemble a nail...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13711275
Now if we could just get some feedback from the questioner...
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13711309
Patience amigo...he'll be back :-)  He's probably off checking my site trying to figure out what I charge to come sort this out ;-)
0
 

Author Comment

by:9apit
ID: 13711721
Hello Kevin and ShoGunWade

The error message at the ASP page is
Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E57) [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type varchar. /ppoNEXTCIDMA/IMA_addMAP.asp, line 39

The SQL Profiler quits returning results after a stored procedure fires called usp_ins_map_info. I took the exec usp_ins_map_info statement iwth the parameters and ran it in Query analyzer and got:

Server: Msg 8115, Level 16, State 2, Procedure usp_ins_map_info, Line 41
Arithmetic overflow error converting expression to data type varchar.
Server: Msg 515, Level 16, State 2, Procedure usp_ins_map_info, Line 46
Cannot insert the value NULL into column 'var_external_claim_id', table 'ppoNEXT_CIDMA.dbo.IMA_Map_Information'; column does not allow nulls. INSERT fails.
The statement has been terminated.

The first part of the statement is the same as the the error message received from ASP which leads me to think that this stored procedure is the one having a bad hair day. The second part about       Cannot insert the value NULL into column 'var_external_claim_id',   makes me wonder why the appl is trying to inseert a null value.

Any ideas?

Allen

PS Shogun where in the code above would you try the response.write insertion and what would the syntax of the statement be?

PSS I got SQL profiler to work pretty well. When I opened the the Trace Template to show my boss the trace the TextData quit working. I tried buildeing a new template using my note from before but still the TextData column is appearing but nothing is being written into the column. Has this ever happened to you Kevin?

0
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 1000 total points
ID: 13711780
>>PSS I got SQL profiler to work pretty well. When I opened the the Trace Template to show my boss the trace the TextData quit working. I tried buildeing a new template using my note from before but still the TextData column is appearing but nothing is being written into the column. Has this ever happened to you Kevin?<<
Nope.


>>Server: Msg 515, Level 16, State 2, Procedure usp_ins_map_info, Line 46
Cannot insert the value NULL into column 'var_external_claim_id', table 'ppoNEXT_CIDMA.dbo.IMA_Map_Information'; column does not allow nulls. INSERT fails.
The statement has been terminated.<<

Please post what you ran in QA....combined with the sp definition above, we may find the culprit

One of these must be getting a null from the page:
 values (@int_user_id, @var_filename, @int_image_number, @int_number_of_attachments,@rep_id, @var_external_claim_id)

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 13712420
From the Line number, it would appear that this is the problem:

select @rep_id = isnull(max(int_Reprice_claim_ID),10000) + 1,
         @var_external_claim_id = convert(char(6),getdate(),12) + convert(char(5),isnull(max(int_Reprice_claim_ID),10000) + 1)
from ima_map_information with (tablockx)
where convert(char(10),dt_created,103) = convert(char(10),getdate(),103)

What happens if you run this in SQL Query Analyzer:
select *
from ima_map_information with (tablockx)
where convert(char(10),dt_created,103) = convert(char(10),getdate(),103)

Is the dt_created column defined as datetime?
0
 

Author Comment

by:9apit
ID: 13712487
Hey Kevin,

The stored procedure call is
exec usp_ins_map_info 68,'0404h021',1,50

In the parameters above the 68 is the userID, the ,'0404h021'
is the batchID- a string input by the user ,
the ,1 is the begnning number of pages and
 50 is the ending number of pages. So where is the
var_external_claim_id? If it is not in the parameters it must
be null and maybe that is what is causing the error. So how do we
get var_external_claim_id to not be null?

Thanks

Allen in Dallas
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13712543
See if you can post the result of this:

select isnull(max(int_Reprice_claim_ID),10000) + 1,
            convert(char(6),getdate(),12) + convert(char(5),isnull(max(int_Reprice_claim_ID),10000) + 1)
from ima_map_information with (tablockx)
where convert(char(10),dt_created,103) = convert(char(10),getdate(),103)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13712557
>>So where is the var_external_claim_id?<<
It is coming from the previous query which is failing with the "Arithmetic overflow error " That is the reason @var_external_claim_id is null.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13717610
Can you tell us what was the outcome?
0
 

Author Comment

by:9apit
ID: 13717931
Hello asperkins,

The out come was I found a data entry error where the
user was supposed to put in a five diigit number like 12345, but
the number input was 1234512345, a ten digit number in
a field that has a en eigth digit max. This happened in the column
right before the column that was identified in the in the
ASP message. I believe that the program abended where the
too big number was input and the database got no number
for the  next column (the coulmn identified by the error
message) and interpreted this lack of data as a null value
thus the message.
We removed the ten digit number and replace it with a
the correct five digit number and the error  went away.

Thanks.

Allen in Dallas

0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13718140
Cool...yet another case for application side data validation :-)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question