Arithmetic overflow occured when running sp_monitor.

As I was not party to the discussion, could you please
fill me in ?

>--Original Message--
>I have modified sp sp_monitor to reflect MS suggestion of
changing data type
>and sp_monitor2 worked for a few days. Now I am again
getting the Arithmetic
>overflow occured. Could I get some help with this error?
Does this have
>anything to do with the server uptime? I am running SQL
2000 sp3 (818) on an
>active passive cluster.
>Thanks for any help!
>.
>
Sure Peter -
I have been getting this error on one of my production SQL Servers in SQL
Query analyzer.
Server: Msg 8115, Level 16, State 2, Procedure sp_monitor, Line 94
Arithmetic overflow error converting expression to data type int.
I was told that this is a common(ish) problem but he did not recall that it
ever
got "fixed".
I was told I could create a copy of the procedure and
change the datatype of @.idle from int to decimal. This
shold prevent this error. I made the change to the sp_monitor and saved it
as sp_monitor2. It ran fine for a few days but now I am getting the same
error again.
I was wondering if the overflow had something to do with uptime on the
server because my server has been up for more that 100 day and when I run
select @.@.CPU_Busy I get the same error.
Hope this explains!
Thanks,
"Peter The Spate" wrote:

> As I was not party to the discussion, could you please
> fill me in ?
>
> changing data type
> getting the Arithmetic
> Does this have
> 2000 sp3 (818) on an
>
|||@.idle as well as @.@.CPU_BUSY both return an integer of the
number of milliseconds since SQL Server was last started so
yes...being up more than 100 days can contribute.
Did you try using bigint for @.idle in your version of
sp_monitor2?
-Sue
On Thu, 12 Aug 2004 08:53:02 -0700, "KatherineP"
<KatherineP@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sure Peter -
>I have been getting this error on one of my production SQL Servers in SQL
>Query analyzer.
>Server: Msg 8115, Level 16, State 2, Procedure sp_monitor, Line 94
>Arithmetic overflow error converting expression to data type int.
>I was told that this is a common(ish) problem but he did not recall that it
>ever
>got "fixed".
>I was told I could create a copy of the procedure and
>change the datatype of @.idle from int to decimal. This
>shold prevent this error. I made the change to the sp_monitor and saved it
>as sp_monitor2. It ran fine for a few days but now I am getting the same
>error again.
>I was wondering if the overflow had something to do with uptime on the
>server because my server has been up for more that 100 day and when I run
>select @.@.CPU_Busy I get the same error.
>Hope this explains!
>Thanks,
>
>
>"Peter The Spate" wrote:

Arithmetic overflow occured when running sp_monitor.

As I was not party to the discussion, could you please
fill me in ?

>--Original Message--
>I have modified sp sp_monitor to reflect MS suggestion of
changing data type
>and sp_monitor2 worked for a few days. Now I am again
getting the Arithmetic
>overflow occured. Could I get some help with this error?
Does this have
>anything to do with the server uptime? I am running SQL
2000 sp3 (818) on an
>active passive cluster.
>Thanks for any help!
>.
>Sure Peter -
I have been getting this error on one of my production SQL Servers in SQL
Query analyzer.
Server: Msg 8115, Level 16, State 2, Procedure sp_monitor, Line 94
Arithmetic overflow error converting expression to data type int.
I was told that this is a common(ish) problem but he did not recall that it
ever
got "fixed".
I was told I could create a copy of the procedure and
change the datatype of @.idle from int to decimal. This
shold prevent this error. I made the change to the sp_monitor and saved it
as sp_monitor2. It ran fine for a few days but now I am getting the same
error again.
I was wondering if the overflow had something to do with uptime on the
server because my server has been up for more that 100 day and when I run
select @.@.CPU_Busy I get the same error.
Hope this explains!
Thanks,
"Peter The Spate" wrote:

> As I was not party to the discussion, could you please
> fill me in ?
>
> changing data type
> getting the Arithmetic
> Does this have
> 2000 sp3 (818) on an
>|||@.idle as well as @.@.CPU_BUSY both return an integer of the
number of milliseconds since SQL Server was last started so
yes...being up more than 100 days can contribute.
Did you try using bigint for @.idle in your version of
sp_monitor2?
-Sue
On Thu, 12 Aug 2004 08:53:02 -0700, "KatherineP"
<KatherineP@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sure Peter -
>I have been getting this error on one of my production SQL Servers in SQL
>Query analyzer.
>Server: Msg 8115, Level 16, State 2, Procedure sp_monitor, Line 94
>Arithmetic overflow error converting expression to data type int.
>I was told that this is a common(ish) problem but he did not recall that it
>ever
>got "fixed".
>I was told I could create a copy of the procedure and
>change the datatype of @.idle from int to decimal. This
>shold prevent this error. I made the change to the sp_monitor and saved i
t
>as sp_monitor2. It ran fine for a few days but now I am getting the same
>error again.
>I was wondering if the overflow had something to do with uptime on the
>server because my server has been up for more that 100 day and when I run
>select @.@.CPU_Busy I get the same error.
>Hope this explains!
>Thanks,
>
>
>"Peter The Spate" wrote:
>

Arithmetic overflow occured when running sp_monitor.

I have modified sp sp_monitor to reflect MS suggestion of changing data type
and sp_monitor2 worked for a few days. Now I am again getting the Arithmetic
overflow occured. Could I get some help with this error? Does this have
anything to do with the server uptime? I am running SQL 2000 sp3 (818) on an
active passive cluster.
Thanks for any help!As I was not party to the discussion, could you please
fill me in ?
>--Original Message--
>I have modified sp sp_monitor to reflect MS suggestion of
changing data type
>and sp_monitor2 worked for a few days. Now I am again
getting the Arithmetic
>overflow occured. Could I get some help with this error?
Does this have
>anything to do with the server uptime? I am running SQL
2000 sp3 (818) on an
>active passive cluster.
>Thanks for any help!
>.
>|||@.idle as well as @.@.CPU_BUSY both return an integer of the
number of milliseconds since SQL Server was last started so
yes...being up more than 100 days can contribute.
Did you try using bigint for @.idle in your version of
sp_monitor2?
-Sue
On Thu, 12 Aug 2004 08:53:02 -0700, "KatherineP"
<KatherineP@.discussions.microsoft.com> wrote:
>Sure Peter -
>I have been getting this error on one of my production SQL Servers in SQL
>Query analyzer.
>Server: Msg 8115, Level 16, State 2, Procedure sp_monitor, Line 94
>Arithmetic overflow error converting expression to data type int.
>I was told that this is a common(ish) problem but he did not recall that it
>ever
>got "fixed".
>I was told I could create a copy of the procedure and
>change the datatype of @.idle from int to decimal. This
>shold prevent this error. I made the change to the sp_monitor and saved it
>as sp_monitor2. It ran fine for a few days but now I am getting the same
>error again.
>I was wondering if the overflow had something to do with uptime on the
>server because my server has been up for more that 100 day and when I run
>select @.@.CPU_Busy I get the same error.
>Hope this explains!
>Thanks,
>
>
>"Peter The Spate" wrote:
>> As I was not party to the discussion, could you please
>> fill me in ?
>>
>> >--Original Message--
>> >I have modified sp sp_monitor to reflect MS suggestion of
>> changing data type
>> >and sp_monitor2 worked for a few days. Now I am again
>> getting the Arithmetic
>> >overflow occured. Could I get some help with this error?
>> Does this have
>> >anything to do with the server uptime? I am running SQL
>> 2000 sp3 (818) on an
>> >active passive cluster.
>> >Thanks for any help!
>> >.
>> >

Arithmetic overflow error when executing Stored Procedure

When I execute a stored procedure it outputs the expected value, but also throws the following exception.

Arithmetic overflow error converting expression to data type int.
The 'usp_TicketCreate' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Any help would be appreciated, at the moment I am just catching the exception in my code and ignoring it, but I would like to get rid of the exception all together.

Thanks for the help.

SBProgrammerchange the data type of that column to bigint which is presently integer..
integer data type can only stored data upto 32k and if the value exceeds this limit then the sql server's error is generated..

i hope this will solve you problem

Mukund Tambe|||Check the source code of your stored procedure. You are probably returning a variable that is NULL, in other words, something like:CREATE PROCEDURE myProcedure
AS

DECLARE @.badVariable INT -- Declare, but leave NULL

RETURN @.badVariable-PatP|||The code for the Stored Procedure is as follows:

ALTER PROCEDURE dbo.usp_TicketCreate
(
@.IsActive bit = true,
@.TicketStatusId int = 0,
@.TicketTypeId int = 0,
@.TicketTypeGroupId int = 0,
@.TicketPriorityId int = 0,
@.LocationId int = 0,
@.BuildingId int = 0,
@.RoomId int = 0,
@.RoomOther nvarchar(100) = '',
@.AssetFound bit = false,
@.AssetId int = 0,
@.SerialNumber nvarchar(50) = '',
@.InventoryTag nvarchar(20) = '',
@.AssetTagNumber nvarchar(10) = '',
@.ManufacturerId int = 0,
@.Model nvarchar(50) = '',
@.AssignedToUserId int = 0,
@.EndUserId int = 0,
@.EndUser_Name nvarchar(50) = '',
@.EndUser_Email nvarchar(50) = '',
@.EndUser_Phone nvarchar(50) = '',
@.EnteredByUserId int = 0,
@.EnteredTimeStamp datetime,
--@.ProblemDescription nvarchar(2000) = '',
@.TicketId bigint = 0 OUTPUT
)
AS

/* SET NOCOUNT ON */

EXEC @.TicketId = ufx_TicketNewId @.EnteredTimeStamp

-- Update Table TicketId with the NextId
UPDATE
TicketId
SET
NextId = NextId + 1

INSERT INTO Tickets
(
TicketId,
IsActive,
TicketStatusId,
TicketTypeId,
TicketTypeGroupId,
TicketPriorityId,
LocationId,
BuildingId,
RoomId,
RoomOther,
AssetFound,
AssetId,
SerialNumber,
InventoryTag,
AssetTagNumber,
ManufacturerId,
Model,
EndUserId,
EndUserName,
EndUserEmail,
EndUserPhone,
EnteredByUserId,
EnteredTimeStamp
--ProblemDescription
)
VALUES
(
@.TicketId,
@.IsActive,
@.TicketStatusId,
@.TicketTypeId,
@.TicketTypeGroupId,
@.TicketPriorityId,
@.LocationId,
@.BuildingId,
@.RoomId,
@.RoomOther,
@.AssetFound,
@.AssetId,
@.SerialNumber,
@.InventoryTag,
@.AssetTagNumber,
@.ManufacturerId,
@.Model,
@.EndUserId,
@.EndUser_Name,
@.EndUser_Email,
@.EndUser_Phone,
@.EnteredByUserId,
@.EnteredTimeStamp
--@.ProblemDescription,
)

RETURN @.TicketId

--------------
The Function is as follows:

ALTER FUNCTION dbo.ufx_TicketNewId
(
@.DateNow DateTime
)
RETURNS bigint
AS
BEGIN

Declare @.TicketId nvarchar(5)
Declare @.Month nvarchar(2)
Declare @.Day nvarchar(2)
Declare @.Year nvarchar(4)
Declare @.ReturnValue bigint

SELECT @.Year = DatePart(Year, @.Datenow)
SELECT @.Month = DatePart(Month, @.Datenow)
SELECT @.Day = DatePart(Day, @.Datenow)

IF LEN(@.Month) = 1
SELECT @.Month = '0' + @.Month

IF LEN(@.Day) = 1
SELECT @.Day = '0' + @.Day

SELECT @.TicketId = [NextId] FROM TicketId

SELECT @.ReturnValue = @.Year + @.Month + @.Day + @.TicketId

RETURN @.ReturnValue

END

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

The Output after executing the Stored Procedure is as follows:

Running [dbo].[usp_TicketCreate] ( @.IsActive = True, @.TicketStatusId = 0, @.TicketTypeId = 1, @.TicketTypeGroupId = 1, @.TicketPriorityId = 1, @.LocationId = 111, @.BuildingId = 111, @.RoomId = 111, @.RoomOther = , @.AssetFound = False, @.AssetId = 0, @.SerialNumber = , @.InventoryTag = , @.AssetTagNumber = , @.ManufacturerId = 1, @.Model = , @.AssignedToUserId = 0, @.EndUserId = 131, @.EndUser_Name = Test User, @.EndUser_Email = Test.User@.TestEmail.com, @.EndUser_Phone = x, @.EnteredByUserId = 131, @.EnteredTimeStamp = 3/14/2007, @.TicketId = 0 ).

Arithmetic overflow error converting expression to data type int.
The 'usp_TicketCreate' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
(2 row(s) affected)
(0 row(s) returned)
@.TicketId = 20070314107
@.RETURN_VALUE = 0
Finished running [dbo].[usp_TicketCreate].

Thanks again for all your help.

SBProgrammer|||SubProgrammer,

It's like Pat Said

Oh, and you should never use RETURN with a value...SQL Server can overwrite it..use an output variable|||Brett,

Thanks for your reply. How should you return a value than, I am just doing as my manager had done before me. I would appreciate a sample of how to correctly implement this.

SBProgrammer|||Pat,

I have posted code and not sure where my variable is null, since I initiate all of the parameters. If you could help point out what I am missing, or whether or not I am doing this correctly. I would appreciate it.

Thanks so much for your time and information.

SBProgrammer|||The return value from a stored procedure must be an INT value, and 20070314107 is too big to be an INT. This will raise the overflow error, and return a NULL value to the caller.

As Brett pointed out, the @.ticketID OUTPUT variable in your procedure certainly can be used by the calling code. You just can't use the INT return value from the procedure like you can use BIGINT one from the function.

Note that playing character conversion games (like you do for the values in the function) will hurt you at some point in the future. Guaranteed, no question in my mind. It isn't a question of if, but only a question of when it will hurt you.

-PatP

Arithmetic overflow error when doing outer joins

Hey everyone,

I have two tables, one is a large table (v_userviews) containing a list of all the servers and various information about those servers. The other table (l_printers) contains printer information for those servers. I am working on a view to consolidate the printer information in l_printers with the other server information in v_userviews.

I've been trying to get outer joins to work but I am getting this error:
"Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation."

Here is my select statement:

select u.propid, u.address,
SUM((CASE u.Tree WHEN 'tree1' then 1 ELSE 0 END)) AS One,
SUM((CASE u.Tree WHEN 'tree2' then 1 ELSE 0 END)) AS Two,
SUM((CASE u.Tree WHEN 'tree3' then 1 ELSE 0 END)) AS Three,
SUM((CASE u.Tree WHEN 'tree4' then 1 ELSE 0 END)) AS Four,
SUM((CASE u.Tree WHEN 'tree5' then 1 ELSE 0 END)) AS Five,
SUM((CASE u.Tree WHEN 'tree6' then 1 ELSE 0 END)) AS Six,
SUM((CASE u.Tree WHEN 'tree7' then 1 ELSE 0 END)) AS Seven,
SUM((CASE u.Tree WHEN 'tree8' then 1 ELSE 0 END)) AS Eight,
SUM((CASE u.Tree WHEN 'tree9' then 1 ELSE 0 END)) AS Nine,
SUM((CASE u.Tree WHEN 'tree10' then 1 ELSE 0 END)) AS Ten,
SUM((CASE u.Tree WHEN 'tree11' then 1 ELSE 0 END)) AS Eleven,
SUM((CASE u.Tree WHEN 'tree12' then 1 ELSE 0 END)) AS Twelve,
SUM((CASE u.Tree WHEN 'tree13' then 1 ELSE 0 END)) AS Thirteen,
SUM((CASE u.Tree WHEN 'tree14' then 1 ELSE 0 END)) AS Fourteen,

count(u.server) as totalservers,
sum(cast(left(u.totalspace,len(u.totalspace)-2) as int)) as totalspace,
sum(cast(left(u.totalusedspace,len(u.totalusedspac e)-2) as int)) as totalusedspace,
count(p.printer) as numprinters

from serverops.dbo.v_userviews u LEFT OUTER JOIN novell_twr.dbo.l_printers p ON u.propid = p.propid
where u.os='netware'and u.state in ('ny', 'nj', 'fl')
group by u.propid, u.address

the l_printers table is in this format:

Printers Server Propid
nvarchar nvarchar varchar

Thanks for all your help. :beer:Try commenting out all the sums, and see what the count of total servers looks like, I am going to guess it is somewhat larger than you expect. If so, then you will have to look over how you are joining the two tables.|||Try commenting out all the sums, and see what the count of total servers looks like, I am going to guess it is somewhat larger than you expect. If so, then you will have to look over how you are joining the two tables.

You were right, the total servers was definitely larger than expected. Will I have to use another method other than outer joins to consolidate the two tables?|||How many servers do you have?|||How many servers do you have?

Around 4800|||what's defined as tiny int?

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

smallint

Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).

tinyint

Integer data from 0 through 255.

post some ddl|||what's defined as tiny int?

post some ddl

Nothing is defined as tinyint

Here are some DDL

L_Printers:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[L_Printers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[L_Printers]
GO

CREATE TABLE [dbo].[L_Printers] (
[Printer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Server] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

R_Servers (v_userviews is actually just a distinct top 100 view of r_servers):
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[R_Servers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[R_Servers]
GO

CREATE TABLE [dbo].[R_Servers] (
[Server] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Type] [varchar] (51) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Classification] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPX Internal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Secondary IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_IP1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_IP2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_RIBIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Branch #] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OS] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSVersion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InProduction] [bit] NULL ,
[NOTES] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tree] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NWContext] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomain] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomainRole] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTXEUpdate] [datetime] NULL ,
[MacAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CommonName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TFloors] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bank_Floors] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalEmp] [float] NULL ,
[Address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Street Address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Room] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Floor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPResponse] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ResponseOK] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TapeDrive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TapeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DriveCapacity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IP Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBPW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Remote PW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Console PW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeviceStatus] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBInstalled] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPack] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeviceOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ASource] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BackupDetails] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServerID] [numeric](10, 0) NOT NULL ,
[Model] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Serial] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AssetNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Memory] [numeric](18, 0) NULL ,
[ROM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPU] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPUSpeed] [decimal](18, 0) NULL ,
[TotalCPUs] [int] NULL ,
[Vendor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PODate] [datetime] NULL ,
[ReceivedDate] [smalldatetime] NULL ,
[ActivationDate] [smalldatetime] NULL ,
[RefreshDate] [smalldatetime] NULL ,
[WarrantyEndDate] [smalldatetime] NULL ,
[TowerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TowerID] [int] NULL ,
[DistrictID] [int] NULL ,
[Priority] [int] NULL ,
[Severity] [int] NULL ,
[SupportComment] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SiteContact] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupportStaff] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PSRegion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DistrictManager] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DMName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupportQueue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[District] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Domain] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[inProductionDate] [smalldatetime] NULL ,
[Region] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalSpace] [nvarchar] (257) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalUsedSpace] [nvarchar] (257) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackPosition] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackRow] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSType] [int] NULL ,
[Project#] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClusterID] [numeric](18, 0) NULL ,
[RIBLicenseKey] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prop_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [datetime] NULL ,
[CheckSum] [int] NULL ,
[ManagmentServer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastAudit] [datetime] NULL ,
[RecordAuditor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimarySupport] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVM] [bit] NULL ,
[KVMType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[expanse] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOBOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryRecordOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreationDate] [datetime] NULL ,
[Strategic] [bit] NULL ,
[ServiceBillingCC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HardwareSupport] [bit] NULL ,
[Updated] [datetime] NOT NULL
) ON [PRIMARY]
GO

Arithmetic overflow error for data type smallint, value = 46080

Hello friends, in this moments I have a problem with my Ms Sql server 2000,
at the enterprise manager I can't see the databases, a message error
"Arithmetic overflow error for data type smallint, value = 46080" appears,
but the server is seems to be still working, what it is?
Regards,
Jess R.
Hi
You may want to run SQL profiler to see what is being run by Enterprise
manager.
It could be similar (but not necessarily the same thing as!) to
http://support.microsoft.com/default...b;en-us;234028
Also check the version of SQL Server http://www.aspfaq.com/show.asp?id=2160.
John
"Jesus Brito" wrote:

> Hello friends, in this moments I have a problem with my Ms Sql server 2000,
> at the enterprise manager I can't see the databases, a message error
> "Arithmetic overflow error for data type smallint, value = 46080" appears,
> but the server is seems to be still working, what it is?
> Regards,
> Jesús R.
>
>

Arithmetic overflow error for data type smallint, value = 46080

Hello friends, in this moments I have a problem with my Ms Sql server 2000,
at the enterprise manager I can't see the databases, a message error
"Arithmetic overflow error for data type smallint, value = 46080" appears,
but the server is seems to be still working, what it is?
Regards,
Jess R.Hi
You may want to run SQL profiler to see what is being run by Enterprise
manager.
It could be similar (but not necessarily the same thing as!) to
http://support.microsoft.com/defaul...kb;en-us;234028
Also check the version of SQL Server http://www.aspfaq.com/show.asp?id=2160.
John
"Jesus Brito" wrote:

> Hello friends, in this moments I have a problem with my Ms Sql server 2000
,
> at the enterprise manager I can't see the databases, a message error
> "Arithmetic overflow error for data type smallint, value = 46080" appears,
> but the server is seems to be still working, what it is?
> Regards,
> Jesús R.
>
>

Arithmetic overflow error for data type smallint, value = 46080

Hello friends, in this moments I have a problem with my Ms Sql server 2000,
at the enterprise manager I can't see the databases, a message error
"Arithmetic overflow error for data type smallint, value = 46080" appears,
but the server is seems to be still working, what it is?
Regards,
Jesús R.Hi
You may want to run SQL profiler to see what is being run by Enterprise
manager.
It could be similar (but not necessarily the same thing as!) to
http://support.microsoft.com/default.aspx?scid=kb;en-us;234028
Also check the version of SQL Server http://www.aspfaq.com/show.asp?id=2160.
John
"Jesus Brito" wrote:
> Hello friends, in this moments I have a problem with my Ms Sql server 2000,
> at the enterprise manager I can't see the databases, a message error
> "Arithmetic overflow error for data type smallint, value = 46080" appears,
> but the server is seems to be still working, what it is?
> Regards,
> Jesús R.
>
>

Arithmetic overflow error converting numeric to data type numeric for View

Hi,
When I do
Select * From V_TABLE Where Column = 1
, I get arithmetic exception. Column type is int.
When I do
Select * From V_TABLE Where Column = 10
, I get no arithmetic exception at all.
When I do
Select * From TABLE Where Column = 1
, I get no arithmetic exception at all.
So far I undestood that I get exceptions for view only and only if
expression in Where is 1. Any ideas why it happens?
SQL server is MS SQL server 2005.Sergey
What if you run SELECT * FROM V_TABLE Where Column = '1', do you still get
error?
"Skochkar" <Sergey.Kochkarev@.mail.ru> wrote in message
news:1192530234.022276.104230@.i13g2000prf.googlegroups.com...
> Hi,
> When I do
> Select * From V_TABLE Where Column = 1
> , I get arithmetic exception. Column type is int.
> When I do
> Select * From V_TABLE Where Column = 10
> , I get no arithmetic exception at all.
> When I do
> Select * From TABLE Where Column = 1
> , I get no arithmetic exception at all.
> So far I undestood that I get exceptions for view only and only if
> expression in Where is 1. Any ideas why it happens?
> SQL server is MS SQL server 2005.
>|||> So far I undestood that I get exceptions for view only and only if
> expression in Where is 1. Any ideas why it happens?
Can you please post your DDL (CREATE TABLE and VIEW)?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Skochkar" <Sergey.Kochkarev@.mail.ru> wrote in message
news:1192530234.022276.104230@.i13g2000prf.googlegroups.com...
> Hi,
> When I do
> Select * From V_TABLE Where Column = 1
> , I get arithmetic exception. Column type is int.
> When I do
> Select * From V_TABLE Where Column = 10
> , I get no arithmetic exception at all.
> When I do
> Select * From TABLE Where Column = 1
> , I get no arithmetic exception at all.
> So far I undestood that I get exceptions for view only and only if
> expression in Where is 1. Any ideas why it happens?
> SQL server is MS SQL server 2005.
>

Arithmetic overflow error converting numeric to data type numeric

Guys

I'm getting the above when trying to populate a variable. The values in question are :
@.N = 21
@.SumXY = -1303765191530058.2251000000
@.SumXSumY = -5338556963168643.7875000000

When I run, SELECT (@.N * @.SumXY) - (@.SumXSumY * @.SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@.N * @.SumXY) - (@.SumXSumY * @.SumXSumY)@.R2Top is NUMERIC (38, 10)

Any ideas ??Sorry - did a typo - problem code is
SELECT @.R2Top = (@.N * @.SumXY) - (@.SumXSumY * @.SumXSumY)
not
SELECT R2Top = (@.N * @.SumXY) - (@.SumXSumY * @.SumXSumY)|||Try:
SELECT R2Top = CAST((@.N * @.SumXY) - (@.SumXSumY * @.SumXSumY) as NUMERIC (38, 10))|||Thanks blindman but still same error.

If I change data type of @.R2Top to FLOAT I get no error but aren't there accuracy issues with FLOAT ? I have further calculations to perform in the code and the accuracy is very important|||What are the datatypes for @.N, @.SumXY, and @.SumXSumY?|||OK. Here is your problem. numeric(38, 10) only leaves you 28 digits to the left of the decimal. Your problem requires 32 digits to the left of the decimal.

This code works:declare @.N int
declare @.SumXY decimal(26, 10)
declare @.SumXSumY decimal(26, 10)
declare @.R2Top numeric(38, 6)

set @.N = 21
set @.SumXY = -1303765191530058.2251000000
set @.SumXSumY = -5338556963168643.7875000000

select @.R2Top = (@.N * @.SumXY) - (@.SumXSumY * @.SumXSumY)
select @.R2Top|||Thanks blindman

Arithmetic overflow error converting numeric to data type nume

My results can never be > 9.99 as it is being divided by itelsef plus
something else.
I.e. possible is 1.00 to 0......
"Erland Sommarskog" wrote:

> dpc (dpc@.discussions.microsoft.com) writes:
> I guess the problem is that the count does not account for results that
> are > 9.99.
> But without access to the data it is very difficult to say exactly what
> happens.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>examnotes (dpc@.discussions.microsoft.com) writes:
> My results can never be > 9.99 as it is being divided by itelsef plus
> something else.
That "something else" can be negative. At least from my corner of
ignorance.
Anyway, the best you can do is this:
SELECT *
FROM #tblOutput
WHERE cast(cnSR / isnull(cn210,0) + isnull( nSR, 0) as decimal(13,2)) > 9.99
and (isnull(cn210,0) + isnull(cnSR,0) ) > 0
and cnsr is not null
and cnsr <> 0
and cn210 is not null ;
That will give you some clue of the data that is causing you problems.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 23 Aug 2005 23:10:04 -0700, dpc wrote:

>My results can never be > 9.99 as it is being divided by itelsef plus
>something else.
Hi dpc,
It is not. As I already mentioned in my previous reply, you missed some
parentheses. The formula
X = Y / Z + Y
will divide Y by Z, then add Y to that. It's the same as Y * (1 + 1/Z)
What you need is this:
X = Y / (Z + Y)
The extra parentheses ensure that Z + Y is calculated first, and that Y
is then divided by this value.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Oh, I see what your problem is, you are not using parenthesis correctly.
Update #tblOutput
SET pctProcSR = cast (cnSR / isnull(cn210,0) + isnull( cnSR, 0) as
decimal(3,2) )
Where ( isnull(cn210,0) + isnull(cnSR,0) ) > 0 and cnsr is not null and
cnsr <> 0 and cn210 is not null ;
Where pctProcSR is Decimal(3,2)
cast (cnSR / isnull(cn210,0) + isnull( cnSR, 0) as decimal(3,2) )
You are doing:
Division comes before addition in precedence, so you are doing the divsion
first. Change to:
cnSR / (isNull(cn210,0) + isnull(cnSR,0))
And you should have your problem fixed.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"dpc" <dpc@.discussions.microsoft.com> wrote in message
news:C5CC542D-3253-4772-B90B-2FE83173043D@.microsoft.com...
> My results can never be > 9.99 as it is being divided by itelsef plus
> something else.
> I.e. possible is 1.00 to 0......
> "Erland Sommarskog" wrote:
>|||Sorry Hugo, didn't see you had already said that. Messy messy thread :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:jpbpg11e74g15mm3gjmmli02mfni6ha@.
4ax.com...
> On Tue, 23 Aug 2005 23:10:04 -0700, dpc wrote:
>
> Hi dpc,
> It is not. As I already mentioned in my previous reply, you missed some
> parentheses. The formula
> X = Y / Z + Y
> will divide Y by Z, then add Y to that. It's the same as Y * (1 + 1/Z)
> What you need is this:
> X = Y / (Z + Y)
> The extra parentheses ensure that Z + Y is calculated first, and that Y
> is then divided by this value.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Arithmetic overflow error converting float to data type numeric.

HI All,
I have two table, one I am importing data from into the other. The table
that I am importing datafrom is supplied to me, so it is the way it is.
Problem is that the numeric values in the supplied table is floating This
data is used in financial calculations, so I don't think that the floating
type would give me correct financial information.
SO I am importing the data into a table with decimal datatype. Problem is I
keep getting the above error
The data looks something like
48.099968662
I have tried many variations of the deciaml type, including something like
Decimal(12,12). But still the error
What Im I missing here, can some one point me in the right direction
Thanks
Robertselect convert(decimal(11,9),'48.099968662')
decimal(s,p)
s = total number of digits
p = precision (after the dot)
so the first digits should almost always be greater than the second
digit
unless you do something like this
select convert(decimal(9,9),'0.099968662')
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Robert Bravery wrote:
> HI All,
> I have two table, one I am importing data from into the other. The table
> that I am importing datafrom is supplied to me, so it is the way it is.
> Problem is that the numeric values in the supplied table is floating This
> data is used in financial calculations, so I don't think that the floating
> type would give me correct financial information.
> SO I am importing the data into a table with decimal datatype. Problem is
I
> keep getting the above error
> The data looks something like
> 48.099968662
> I have tried many variations of the deciaml type, including something like
> Decimal(12,12). But still the error
> What Im I missing here, can some one point me in the right direction
> Thanks
> Robert

Arithmetic overflow error converting expression to data type int.

I've got this error message while generate the output with ASP:

"Microsoft OLE DB Provider for SQL Server (0x80004005)
Arithmetic overflow error converting expression to data type int."

it indicate that the error is related to this line:
"rc1.Movenext"

where rc1 is set as objconn.Execute(sql).

Not all outputs result like this, it happens when it has many relationships with other records, especially with those records which also have many relationships with other records.

Can anyone suggest a solution?
I've tried to increase the size of the database file, but it doesn't work.could you perhaps show the query that caused the error?|||Here is the query:

CREATE procedure rp_co_relatedcompanies @.companyregistrationno varchar(20)
as
set nocount on
--used for related companies details

select a.companypersonid,a.personname,a.personaddress1,a. personaddress2,a.personcity,a.personstate,a.person country,
a.personzip,a.personphone,a.personfax,a.personemai l,a.personuniqueid,b.principalbusiness,b.secondryb usiness,
c.relationwithcompany,c.numberofshares, c.classofsharesheld , cast(d.noofissuedshares as int) 'noofissuedshares',
'percentageofsharehold' = case when c.numberofshares =0 then 0 else c.numberofshares/d.noofissuedshares*100 end, c.otherremarks
from companyperson a
left join companystructure d on a.personuniqueid=d.companyregistrationno
left outer join operationsandactivity b on a.personuniqueid = b.companyregistrationno
left outer join relationshipcompanyperson c on a.companypersonid = c.companypersonid
where c.companyregistrationno = @.companyregistrationno and c.relationshipwith = 2
and a.deletionflag=0 order by a.personname
set nocount off
GO|||can you see anything in that query where there might be a problem "converting expression to data type int"

the immediate suspect is cast(d.noofissuedshares as int)

another suspect is c.numberofshares/d.noofissuedshares*100

check those and let us know|||Table DDL would help a lot. Heck, I was betting on the c.companyregistrationno being an INT and the @.companyregistrationno failing the implicit Cast...

Anybody want to start a pool on this? Or another option (not as much fun, but the problem would get solved a lot faster) would be for yllas to post the DDL for all of the tables in this query!

-PatP

Arithmetic overflow error converting expression to data type int.

Hello, I'm using SQL Server 7.0 (SP2) for my distributor and many subscribers. It seems that when I replicate using my subscribers, some of them hit the error "Arithmetic overflow error converting expression to data type int". However, I realised that after waiting for a while before replicating now, it seems okay. Anybody can advice how I can prevent this from happening? Thanks!Refer to this KBA (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q307533&gssnb=1) to fix the issue.

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 converting expression to data type int.

Hi,
I using the following query

select dateadd(s, 1185255439727, '01-01-1970 05:30:00')

on MSSQL 2005. I get the error

Arithmetic overflow error converting expression to data type int.

If I use the query

select dateadd(ss, 1088728808 , '01-01-1970 05:30:00') I dont get this error. Looks big numbers are having problem. Please help

Quote:

Originally Posted by Hulikal

Hi,
I using the following query

select dateadd(s, 1185255439727, '01-01-1970 05:30:00')

on MSSQL 2005. I get the error

Arithmetic overflow error converting expression to data type int.

If I use the query

select dateadd(ss, 1088728808 , '01-01-1970 05:30:00') I dont get this error. Looks big numbers are having problem. Please help

maybe you should consider adding year instead of seconds on your date...that's a lot of seconds|||I would take the number of seconds, divide it out;

There are 31536000 Seconds in a year so store that value in a variable as 1 or 2, ect depending upon how many years you have.

Subtract 31536000 * (the value stored from above) from your starting value to get the remaining time. Then do the same for days, then minutes, then seconds.

Do not do this for months as each month has varying number of days.

This way you will date add to Year, Days, Minutes, Seconds.

This will be more manageable.

Arithmetic overflow error converting expression to data type int when using Fill from a Sq

I thought I'd post this quick problem and answer, as I couldn't find the answer when searching for it.

I tried to call a stored procedure on SQL Server 2000 using the System.Data.SqlClient objects, and was not expecting any unusual issues. However when I came to call the Fill method I received the error "Arithmetic overflow error converting expression to data type int."

My first checks were the obvious ones of verifying that I'd provided all the correct datatypes and had no unexpected null values, but I found nothing out of order. The problem turns out to be a difference on the maximum values for integers between C# and SQL Server 2000. Previously having hit issues with SQL Server integers requiring Long Integer types in VB6, I was aware that these are 32-bit integers, so I was passing in Int32 variables. The problem was that Int32.MaxValue is not a valid integer for SQL Server. Seeing as I was providing an abitrary upper value for records-per-page (to fetch all in one page), I was simply able to change this to Int16.MaxValue and will hit no further problems as this is also well beyond any expected range for this parameter.

If anyone can name off the top of their heads what value should be provided as a maximum integer for SQL Server 2000, this might be a useful addition, but otherwise I hope this spares others some hunting if they also experience this problem.

James Burton

Hi,

Please check in the database to see if there is any calculation to be done in your DataSet.

Sometimes, there might be expression columns that add value to this column which might make the exception.

Arithmetic overflow error converting expression to data type int

Under certain circumstances I am getting the following error

"Arithmetic overflow error converting expression to data type int"

when running the following code:

SELECT Count(*), Sum(GrossWinAmount)
FROM LGSLog
WHERE
(CurrentDate >= '9/1/2004 8:00:00 AM') And (CurrentDate <= '9/27/2004 7:59:59 AM')

If I remove the "Sum(GrossWinAmount)" from the select, it works fine. I therefore believe that Sum is causing the error. Is there a version of Sum that works with larger variables, such as a BigInt? If not, is there some way to do the equivalent using larger numbers? I need to allow for the possibility of obtaining one month's summary, and sometimes the summary value is apparently too large for Sum to handle.My first suggestion would be:SELECT Count(*), Sum(Cast(GrossWinAmount AS MONEY))
FROM LGSLog
WHERE CurrentDate >= '9/1/2004 8:00:00 AM'
And CurrentDate <= '9/27/2004 7:59:59 AM'-PatP|||I don't think that would accomplish anything in this case since floating point and monitary values such as this are stored in the database, for the most part, as an SQL int type (multiplied by 100 and rounded in the program(s) before being written... don't ask why... it was being done this way before I started working on the system).|||Did you try it, or are you just guessing that it won't work?

-PatP|||Yep. I just tried changing the query as follows (I changed the date range to only include a week's worth of data... in the program, these are actually datetime variables)...
:
declare @.Totals TABLE
(
GameCount int default 0,
Win int default 0,
Adj int default 0,
Bet int default 0
)
declare @.Count int
INSERT INTO @.Totals
SELECT Count(*) As GameCount, Sum(CAST(l.GrossWinAmount AS MONEY)) As Win, Sum(CAST(l.AdjustedWinAmount AS MONEY)) As Adj, Sum(CAST(l.TotalBetAmount AS MONEY)) As Bet
FROM LGSLog l
WHERE (CurrentDate >= '9/20/2004 8:00:00 AM') And (CurrentDate <= '9/28/2004 7:59:59 AM')
GROUP BY l.MasterID
ORDER BY l.MasterID
SELECT @.Count=COUNT(*) FROM @.Totals
if (@.Count = 0)
Begin /* make sure something valid is returned if nothing found */
INSERT INTO @.Totals VALUES (0, 0, 0, 0)
End
SELECT * FROM @.Totals
:
and got the following error...
:
There is insufficient result space to convert a money value to int.
The statement has been terminated.
:
Which suggests to me that Sum is still wanting to RETURN an int size value, which is what I thought. The theory is that it is not the size of what is being passed into Sum(), but the fact that Sum() is trying to return a value that is too big because it wants to return a value the size of an int!|||How about the DDL for LGSLog

And how many rows are in the result set?

What are the MIN and MAX Values for those columns?

What is the average wind-speed velocity of a sparrow?|||Could it be that the result went outsideof the alloweable boundaries (922337203685477.5807 or -922337203685477.5808)?|||I think I finally got it to work. It took a hybrid of your original suggestion. I cast the sum results to money values / 100 (floats gave me too imprecise a number). I also changed the result set data fields to money values. Then, in the program, I multiplied by 100 and rounded and then converted to long integer (the program already expcected the values as a fixed point type stored in a long... fixed 2 places left of the right most digit... it was easier this way than to make major mods throughout program). I just hope this does not introduce rounding errors, which was the main reason for storing as fixed point values converted to integers in the first place.

This technique seemed to work getting data as far back as one month (no overflow/conversion errors).

declare @.Totals TABLE
(
GameCount int default 0,
Win money default 0,
Adj money default 0,
Bet money default 0
)
declare @.Count int
INSERT INTO @.Totals
SELECT Count(*) As GameCount, Sum(CAST(l.GrossWinAmount AS money) / 100) As Win,
Sum(CAST(l.AdjustedWinAmount AS money) / 100) As Adj, Sum(CAST(l.TotalBetAmount AS money) / 100) As Bet
FROM LGSLog l
WHERE (CurrentDate >= '9/01/2004 8:00:00 AM') And (CurrentDate <= '9/28/2004 7:59:59 AM')
GROUP BY l.MasterID
ORDER BY l.MasterID
SELECT @.Count=COUNT(*) FROM @.Totals
if (@.Count = 0)
Begin
INSERT INTO @.Totals VALUES (0, 0, 0, 0)
End
SELECT * FROM @.Totals

The grouping broke the results into anywhere from a couple of rows to over 25, depending on how far back I wanted to go. This also helped to reduce the size of the value being summed at the server. If the number got too big in the program, I could just use a larger integer size, such as an int32 or int64 in the program, which is not an option I had at the server.|||Instead of casting to MONEY and doing the divide by 100 then multiplying the sum by 100, just cast the column to BIGINT which is an int64 equivalent.

I'm still having trouble getting my head around the idea that you overflowed a MONEY... MONEY is big enough to express the US national debt in Argentine Pesos!

-PatP|||I don't think it actually "OVERFLOWED" the money type. I think it had no problem working with money. I think what was happening is that when "Sum" RETURNED the sum value, SQL tried to convert the "money" value to an "int" type, overflowing the "int" type during the conversion. This is why I think that the fields in the result set had to go from the integerial type to a money. I think I tried converting the value being passed into Sum, and also tried storing the result into a bigint, but never both at the same time. It always resulted in a conversion to an integer at some point... going in or coming out of Sum.

Apparently, if you pass an int type into Sum, it is going to return an int, which is what I was doing originally. If you pass in a money, it will sum internally as a money type and return a money type. But if the receiver variable is an int, it will downcast to an int, overflowing the resulting value. If you store the result into a money type, then it is going straight from a money to a money... no downcast necessary.|||For what it is worth, MONEY actually stores as an 8 byte signed integer, with implicit scaling by 1e4 (basically four digits after the decimal place). If your application is dealing with ODBC data directly like a C++ program would, you'd see the result come back as a long int that needed to be divided by 10000 (or in your case only 100).

-PatP

Arithmetic overflow error converting expression to data type datetime.

Ya ? am taking this error message in Asp.Net and i am not inserting a new row my database.

i have a databese and my fields

EmailID-->int

EmailAdress-->varchar(100)

DateTime-->datetime

IPAdress-->char(15)

Please help me,thanks

Most likely a disparity between the application date format and the date format SQL Server 'expects'. (Application passing the date parameter in the form of dd/mm/yyyy, and SQL Server accepting that as mm/dd/yyyy -and the ensuing date is invalid.

Have the application pass the date parameter in the ISO form of: yyyy/mm/dd OR yyyymmdd.

Arithmetic overflow error converting expression to data type datetime

I have a sql server 2000 database...using vb.net 2005... I have a form which allow user to find outstanding transaction by month selected in dropdownlist... If it matches the month it will populate out in datagrid, this date is store as dd/MM/yyyy... below is the following code:

Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadIf ddl_payment.SelectedItem.Text ="Outstanding"Then ddl_months.Visible =True datagrid1.Visible =False If ddl_months.Visible =True Then config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHEREDATEPART(mm,dateOfPurchase)='" + ddl_months.SelectedValue + "'AND balance > 0") bindData() datagrid1.Visible = False datagrid.Visible = True End If ElseIf ddl_payment.SelectedItem.Text = "Fully Paid" Then config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHERE balance = 0") bindData1() datagrid.Visible =False datagrid1.Visible =True ddl_months.Visible =False Else datagrid.Visible =False datagrid1.Visible =False ddl_months.Visible =False End If End Sub

Below is the error:

Arithmetic overflow error converting expression to data type datetime.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.

Source Error:

Line 76: cmd.CommandText = sqlStatementLine 77: cmd.Connection = connLine 78: cmd.ExecuteNonQuery()Line 79: Line 80: reader = cmd.ExecuteReader

Source File:c:\inetpub\wwwroot\TAKA\App_Code\config.vb Line:78

Stack Trace:

[SqlException (0x80131904): Arithmetic overflow error converting expression to data type datetime.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +862234 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739110 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 TAKA.config.dataReader2(String sqlStatement) in c:\inetpub\wwwroot\TAKA\App_Code\config.vb:78 TAKA.PaymentStatus.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\TAKA\PaymentStatus.aspx.vb:65 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

I have another table which is staff table and it datefield is store in MM/dd/yyyy format

DATEPART returns an integer so wrappingddl_months.SelectedValue in quotes is probably pointless.

Seeing as it's a sql exception I would say it's extremely likely that the field "dateOfPurchase" is of the wrong type. Perhaps ur storing it as a string or an int...?

Also sql doesn't store dates in a locale specific format, the fact that u've said "I have another table whihc is staff table and it datefield is store in MM/dd/yyyy format" suggests to me ur storing ur dates as strings which will not work.

|||

Furthermore executing unparameterised queries against sql is slap-worthy offence. Use parameters... or get hacked.

|||

I waited so long for it approved zzz...ya...worldspawn thx for reply... 4get to said that my date is in nvarchar format... erm so any idea how to get the month in the database in this format? Or do I need change it to smalldatetime format? I need smth like For example my database has this date(23/10/2007, 21/09/2007,22/10/2007)

How can i retrieve by month, If ddl_months i selected '10' den that 2 records with month 10 will shown in datagrid...

Thank in adv

|||

If u want to keep it as a string u'll need to store it in a local generic format such as yyyy-mm-dd. You should then be able to CAST/CONVERT to smalldatetime and then run DATEPART on the converted value.

But i would highly recommend storing it as smalldatetime; unless you have some need to store it as text...?

If you just change it to smalldatetime your code will start working (except remove the quotes from ur sql string. 10 not '10')

That's interesting about the approval. I used to get that but stopped seeing it. I assumed they'd just made everything unmoderated but it's probably just for user's that havent reached 'participant' status.

|||

Would it be safe if i jus change my database dateOfPurchase to smalldatetime format? Cos i received prompt when trying to save... It say data may be lost when trying to convert nvarchar to smalldatetime... below is a few line example of my code... I select a date from calendar and display it in a textbox then save all the data enter in database... will there be any serious problem if i change?

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

todayD.Text = Calendar1.TodaysDate().ToString("dd/MM/yyyy")

End Sub

PrivateSub Calendar1_SelectionChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles Calendar1.SelectionChanged

'display selected date from calendar

tb_dop.Text = Calendar1.SelectedDate().ToString("dd/MM/yyyy")

EndSub

PublicSub AddTransaction1(ByVal dateOfPurchaseAsString,ByVal totalAmountAsString,ByVal paymentReceivedAsString,ByVal bAsString,ByVal receiptInvoiceAsString,ByVal remarksAsString,ByVal csngInvoiceAsString,ByVal transDetailsAsString,ByVal coNameAsString)

'declare the INSERT method for INSERT transaction

Dim strSQLAsString ="INSERT INTO custTransaction (dateOfPurchase, totalAmount, paymentReceived, balance, receiptInvoice, remarks, csngInvoice, transDetails, coName) VALUES ('" + dateOfPurchase +"'," + totalAmount +"," + paymentReceived +"," + b +",'" + receiptInvoice +"','" + remarks +"', '" + csngInvoice +"', '" + transDetails +"', '" + coName +"')"

con1.Open()

Dim cmdAsNew SqlCommand(strSQL, con1)

cmd.ExecuteNonQuery()

con1.Close()

EndSub

PrivateSub btn_add2_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btn_add2.Click

dateOfPurchase = tb_dop.Text

If tb_csngInvoice.Text ="None"Then

'check for duplication

If tb_receiptInvoice.Text = config.dataReader2("SELECT receiptInvoice FROM custTransaction WHERE receiptInvoice = '" + tb_receiptInvoice.Text +"'")Then

emsg.Text ="The InvoiceNo. already exist OR cannot be None"

'ElseIf tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text + "'") Then

' emsg.Text = " The csngInvoiceNo. already exist"

Else

emsg.Text =""

AddTransaction1(dateOfPurchase, tb_totalAmount.Text, tb_payment.Text, tb_bal.Text, tb_receiptInvoice.Text, tb_remarks.Text, tb_csngInvoice.Text, tb_transD.Text, lbl_coName3.Text)

'redirect to add transaction successful page

Response.Redirect("AddTransactionSuccessfully.aspx")

EndIf

ElseIf tb_receiptInvoice.Text ="None"Then

'check for duplication

If tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text +"'")Then

emsg.Text ="The InvoiceNo. already exist OR cannot be None"

'ElseIf tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text + "'") Then

' emsg.Text = " The csngInvoiceNo. already exist"

Else

emsg.Text =""

AddTransaction1(dateOfPurchase, tb_totalAmount.Text, tb_payment.Text, tb_bal.Text, tb_receiptInvoice.Text, tb_remarks.Text, tb_csngInvoice.Text, tb_transD.Text, lbl_coName3.Text)

'redirect to add transaction successful page

Response.Redirect("AddTransactionSuccessfully.aspx")

EndIf

EndIf

EndSub

Thank you for ur time

|||

Is this production database? Honestly i have no idea what would happen exactly but i think it's safe to say ur existing date fields will become incorrect and it may encounter and error trying to convert them and not even work... make sure sql server's date format is dmy and it should work without any problems, if it's still set to mdy u'll have big problems.

Also if you want to display a date in the form dd/MM/yyyy use the inbuilt ToShortDateString method which will convert the date to that format (as long as ur server is configured to use that dateformat by default).

Please read a tutorial on how to property construct a sql command using parameters and not string concatenation. String concatentation leaves u open to sql injection attacks... plus it's just crappy.

|||

Hi, worldspawn can i know how u convert the sql server to display date in dd/MM/yyyy... although i have set the my own server to UK time but it still display as MM/dd/yyyy... Thanks

|||

I'm not a sql guru but I believe u type:

SET DATEFORMAT dmy

So just connect to the sql server with query analyzer or whatever and execute that. It's 6pm, i'm off home, best of luck :)

|||

Hi,

I think the here type of the field as being smalldatetime or datetime is not the issue.

Smalldatetime can handle dates between 1/1/1900 and 6/6/2079 with the accuracy of 1 minute

Datetime can handle dates between 1/1/1753 and 31/12/9999 with the accuracy of 3.33 miliseconds.

So you do not need to change the type of the field. Do it if you have billions of rows to decrease the database size because datetime consumes 8 bytes and smalldatetime consumes 4 bytes. But as mentioned above the accuracy should not be important for that filed.

I think shifting from datetime to smalldatetime will increase the performance.

If you want to know if some data would be truncated. 

select dateOfPurchase from custTransaction where dateOfPurchase < cast('01/01/1900 00:00:00' as datetime) and dateOfPurchase > cast('06/06/2079 00:00:00' as datetime)

run this if you have any result than dont convert to smalldatetime.

 About the main problem, I am not sure you should analyze the script with profiler but maybe this helps;

First be sure  ddl_months.SelectedValue is integer

then try the code as 

 config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHEREDATEPART(m,dateOfPurchase) = cast('"+ ddl_months.SelectedValue + "', as integer)AND balance > 0")

Hope this helps.

|||

Hi yvzman,

A nice infomation to note... Thank for replying... But i think is use CONVERT instead of CAST... dunno y CAST is not longer supported by my script