ASP writing data to SQL Server 2000

I am having a problem with an ASP program that inserts data into a table on
SQL Server 2000.

No error msg is returned upon submission and the confirmation msg that
displays after the commit command is sent to the server displays, but when
we go to the DB, the data sent isn't there. This is an occassional
occurance and usually the data is there, just some times, it isn't. Other
forms function just fine, using the *exact* same file to perform the submit
function (all the forms "include" the same submit page). The only
difference we can find is a trigger on the table having problems which
executes upon update, capturing the information about who updated the record
when. From what we can see, this is the only programmatic difference. The
other thought tickling our minds was the possiblity of a simultaneous
submission, since all the users submit with the same db user name via the
form, if user 1's data gets written but not yet commited, user 2's data is
submitted, then the commit transaction is submitted by user 1 as the program
steps run in sequence, would the commit by user one cause eiither of the
records inserted but not commited to be lost? If so, why wouldn't that be causing problems on other forms ...Can you post some of the code that is being used? Particularly the trigger code... Also, use profiler to watch what is being sent to the SQL Server to determine if it is a SQL issue, or a ASP code issue.|||

Thank you Louis,

I will look into getting profiler turned on.

The trigger is as follows (specific variables replaced with generic names):

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [trig_Table_doUpdateTimestamp] ON [dbo].[Table_1_0]
FOR UPDATE
AS
set nocount on
declare @.var1 datetime
declare @.var2 varchar(50)
declare @.var3 varchar(50)
declare @.var4 varchar(50)
declare @.var5 varchar(30)
set @.var1 = (select sourc_var1 from inserted)
set @.var2 = (select sourc_var2 from inserted)
set @.var3 = (select sourc_var3 from inserted)
set @.var4 = (select sourc_var4 from inserted)
set @.var5 = (select sourc_var5 from inserted)
update Table_1_0 set lastUpdateDT = getdate(), numberChanges = ( ( numberChanges + abs(numberChanges) ) /2 ) + 1 , lastupdateUser = SUSER_SNAME()
where sourc_var1 = @.var1 and sourc_var2 = @.var2 and sourc_var3 = @.var3 and sourc_var4 = @.var4 and sourc_var5 = @.var5
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Arithmetic overflow error converting expression to data type int.

select convert(bigint,21568194 * 100)
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Why does this happen, How do I fix?select convert(bigint,CAST(21568194 AS bigint) * 100)
OR
select CAST(21568194 AS bigint) * 100
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:BB3435B9-09B1-4260-B03B-5F19E78E084F@.microsoft.com...
> select convert(bigint,21568194 * 100)
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
> Why does this happen, How do I fix?

Arithmetic overflow error ...

Hello,
When i execute one query the following error appear,
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data
type int.
I cant understand what is happening but i check one site,
that talk about ".... NOTE: The overflow fix has been
incorporated into Coefficient version 1.1.6 and later
releases. Please update your software to the current
version. The text for the original 1.1.5 fix follows: "
I dont know what to do, but i send you the query because
its possible that im doing something wrong.
select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
[Avarage Exec Time in Milliseconds],
max(datediff(millisecond,s.StartTime,c.EndTime)) as
[Maximum Exec Time in Milliseconds],
min(datediff(millisecond,s.StartTime,c.EndTime)) as
[Minimum Exec Time in Milliseconds]
from T1 s, T2 c
where s.Textdata like c.Textdata
go
Thanks,
Best regards
datediff produces an INT. So, if the difference between starttime and
endtime is larger than ~2 billion, overflow.
You might take the datediff in minutes, cast to BIGINT, and multiply by 60.
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17cba01c44992$6ea5d0b0$a401280a@.phx.gbl...
> Hello,
> When i execute one query the following error appear,
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data
> type int.
> I cant understand what is happening but i check one site,
> that talk about ".... NOTE: The overflow fix has been
> incorporated into Coefficient version 1.1.6 and later
> releases. Please update your software to the current
> version. The text for the original 1.1.5 fix follows: "
> I dont know what to do, but i send you the query because
> its possible that im doing something wrong.
> select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Avarage Exec Time in Milliseconds],
> max(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Maximum Exec Time in Milliseconds],
> min(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Minimum Exec Time in Milliseconds]
> from T1 s, T2 c
> where s.Textdata like c.Textdata
> go
> Thanks,
> Best regards
|||> You might take the datediff in minutes, cast to BIGINT, and multiply by
60.
After the AVG (which might have the same problem on BIGINT).

Arithmetic overflow error ...

Hello,
When i execute one query the following error appear,
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data
type int.
I cant understand what is happening but i check one site,
that talk about ".... NOTE: The overflow fix has been
incorporated into Coefficient version 1.1.6 and later
releases. Please update your software to the current
version. The text for the original 1.1.5 fix follows: "
I dont know what to do, but i send you the query because
its possible that im doing something wrong.
select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
[Avarage Exec Time in Milliseconds],
max(datediff(millisecond,s.StartTime,c.EndTime)) as
[Maximum Exec Time in Milliseconds],
min(datediff(millisecond,s.StartTime,c.EndTime)) as
[Minimum Exec Time in Milliseconds]
from T1 s, T2 c
where s.Textdata like c.Textdata
go
Thanks,
Best regardsdatediff produces an INT. So, if the difference between starttime and
endtime is larger than ~2 billion, overflow.
You might take the datediff in minutes, cast to BIGINT, and multiply by 60.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17cba01c44992$6ea5d0b0$a401280a@.phx.gbl...
> Hello,
> When i execute one query the following error appear,
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data
> type int.
> I cant understand what is happening but i check one site,
> that talk about ".... NOTE: The overflow fix has been
> incorporated into Coefficient version 1.1.6 and later
> releases. Please update your software to the current
> version. The text for the original 1.1.5 fix follows: "
> I dont know what to do, but i send you the query because
> its possible that im doing something wrong.
> select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Avarage Exec Time in Milliseconds],
> max(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Maximum Exec Time in Milliseconds],
> min(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Minimum Exec Time in Milliseconds]
> from T1 s, T2 c
> where s.Textdata like c.Textdata
> go
> Thanks,
> Best regards|||> You might take the datediff in minutes, cast to BIGINT, and multiply by
60.
After the AVG (which might have the same problem on BIGINT).

Arithmetic overflow error ...

Hello,
When i execute one query the following error appear,
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data
type int.
I cant understand what is happening but i check one site,
that talk about ".... NOTE: The overflow fix has been
incorporated into Coefficient version 1.1.6 and later
releases. Please update your software to the current
version. The text for the original 1.1.5 fix follows: "
I dont know what to do, but i send you the query because
its possible that im doing something wrong.
select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
[Avarage Exec Time in Milliseconds],
max(datediff(millisecond,s.StartTime,c.EndTime)) as
[Maximum Exec Time in Milliseconds],
min(datediff(millisecond,s.StartTime,c.EndTime)) as
[Minimum Exec Time in Milliseconds]
from T1 s, T2 c
where s.Textdata like c.Textdata
go
Thanks,
Best regardsdatediff produces an INT. So, if the difference between starttime and
endtime is larger than ~2 billion, overflow.
You might take the datediff in minutes, cast to BIGINT, and multiply by 60.
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17cba01c44992$6ea5d0b0$a401280a@.phx
.gbl...
> Hello,
> When i execute one query the following error appear,
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data
> type int.
> I cant understand what is happening but i check one site,
> that talk about ".... NOTE: The overflow fix has been
> incorporated into Coefficient version 1.1.6 and later
> releases. Please update your software to the current
> version. The text for the original 1.1.5 fix follows: "
> I dont know what to do, but i send you the query because
> its possible that im doing something wrong.
> select avg(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Avarage Exec Time in Milliseconds],
> max(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Maximum Exec Time in Milliseconds],
> min(datediff(millisecond,s.StartTime,c.EndTime)) as
> [Minimum Exec Time in Milliseconds]
> from T1 s, T2 c
> where s.Textdata like c.Textdata
> go
> Thanks,
> Best regards|||> You might take the datediff in minutes, cast to BIGINT, and multiply by
60.
After the AVG (which might have the same problem on BIGINT).

Arithmetic overflow error

I got the following error when running a SP:

Server: Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting nvarchar to data type numeric.
The statement has been terminated.

The stupid thing is, that there is no data conversion at all. It's an insert into SLQ server table where data is retrieved from an Oracle View (using ADO DB link). I got 4 other SP's, doing the same thing for resp 4 other tables, which works fine. Those :mad: SP won't work. I don't know why. Below I put the table structure, view structure and SP I used:

Table:
Contract_No varchar (20) NOT NULL
Registration_Date_Time datetime NOT NULL
AGC varchar (4) NOT NULL
Salesgroup varchar (4) NOT NULL
Group_ varchar (8) NOT NULL
Activity_Type varchar (4) NULL
Type char (1) NULL
Group_Description varchar (50) NULL
Stock_Um varchar (4) NULL
B_Qty numeric(11, 4) NULL
B_Cost numeric(23, 4) NULL
C_Qty numeric(11, 4) NULL
C_Cost numeric(24, 4) NULL
D_Qty numeric(11, 4) NULL
D_Cost numeric(24, 4) NULL

Oracele view:
CONTRACT_NO VARCHAR2(20)
AGC VARCHAR2(4)
SALESGROUP VARCHAR2(4)
GROUP_ VARCHAR2(8)
ACTIVITY_TYPE VARCHAR2(4)
TYPE CHAR(1)
GROUP_DESCRIPTION VARCHAR2(50)
STOCK_UM VARCHAR2(4)
B_QTY NUMBER
B_COST NUMBER
C_QTY NUMBER
C_COST NUMBER
D_QTY NUMBER
D_COST NUMBER

Stored procedure:
CREATE PROCEDURE mis_Upload_Contract_Kosten
@.strType varchar(10),
@.strDate varchar(19)
AS
declare @.strInsert as varchar(1000);
declare @.strSelect as varchar(1000);
declare @.strWhere as varchar(1000);
declare @.strSql as varchar(3019);

SET @.strWhere = ''

SET @.strInsert = 'INSERT C_Contract_Kosten (
Contract_No
, AGC
, Salesgroup
, Group_
, Activity_Type
, Type
, Group_Description
, Stock_Um
, B_Qty
, B_Cost
, C_Qty
, C_Cost
, D_Qty
, D_Cost
, Registration_Date_Time)'

SET @.strSelect = ' SELECT gLCK.Contract_No
, gLCK.AGC
, gLCK.Salesgroup
, gLCK.Group_
, gLCK.Activity_Type
, gLCK.Type
, gLCK.Group_Description
, gLCK.Stock_Um
, gLCK.B_Qty
, gLCK.B_Cost
, gLCK.C_Qty
, gLCK.C_Cost
, gLCK.D_Qty
, gLCK.D_Cost
, ' + char(39) + @.strDate + char(39) + '
FROM Glovia..LIVE.C_CONTRACT_KOSTEN as gLCK
WHERE gLCK.Contract_No NOT LIKE '' IND*''
AND NOT EXISTS
( SELECT vCC.Contract_No
FROM V_Contracts_Closed as vCC
WHERE vCC.Contract_No = gLCK.Contract_No)
AND EXISTS
( SELECT cc.Contract_No
FROM C_Contracten as cc
WHERE cc.Registration_Date_Time = ' + char(39) + @.strDate + char(39) + '
AND cc.Contract_No = gLCK.Contract_No)'

IF @.strType = 'closed'
BEGIN
SET @.strWhere = ' AND NOT(gLCK.Contract_Close_Date IS NULL)'
END

IF @.strType = 'open'
BEGIN
SET @.strWhere = ' AND gLCK.Contract_Close_Date IS NULL'
END

SET @.strSql = @.strInsert + @.strSelect + @.strWhere

EXEC (@.strSql)
GOWell, at least the problem is solved. There was something wrong in the data itself. The x_QTY en x_COST fields contained sometimes a value with 41 numbers (huge list of numbers after the decimal sign). I changed it in the Oracle views, and now it's all working.