Aritnmetic overflow error

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...

adathelad

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)

ShogunWade

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.

Hey...Allen in Dallas! Didn't notice it was you....

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

Kevin in Dallas

ASKER

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 %>

<%
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 %>
      
      <%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 %>
            
            <%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

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

ASKER

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

ASKER

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

ASKER

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

ShogunWade

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.

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

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.

<offtopic>
Kevin,

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

</offtopic>

acperkins....nope, not at all...unless it was directed at me :P

<offtopic>
Kevin,

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

</offtopic>

>>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...

Now if we could just get some feedback from the questioner...

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 ;-)

ASKER

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?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

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)

>>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.

Can you tell us what was the outcome?

ASKER

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