Arranging data on multiple rows into a sigle row (converting rows into columns)

Hello,

I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:

user1 answer1
user1 answer2
user1 answer3
user2 answer1
user2 answer2
user2 answer3

For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so thateach user will be on a single row with all of that user's answers on that single row (A column for each answer):

user1 answer1 answer2 answer3
user2 answer1 answer2 answer3

How can this be done? How can all answers of a user appear on a single row

Thanx,
Danny.

sql server 2005 or 2000?

In sql server 2005, I believe the answer is with the new pivot or unpivot commands. In 2000, it gets much trickier.

Of course, I think excel can do it's own pivoting as well.

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

Arithmetic overflow error converting expression to data type datetime

I tried this new SQL2K5 Performance Dashboard Reports using custom reports in Management Studio.

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

But running it first on any server gives me this error:

Difference of two datetime columns caused overflow at runtime.

Has anybody come across this error? How to fix it?

Thanks in advance!

- Rupesh

first check the sp2 is applied or not.

ref : http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx

Because DATEDIFF returns and int once you have connection that is more than 24 days or so old it will overflow the dattype if you modify the procedure so caluclates the differnce in minutes first converts this to milliseconds then add the number of minutes diffrence onto the start time and then calculate the remianing number of milli seconds it will work so basicalyy if you modify trhe offending line

sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

to

sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS

BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute,

DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

then it will work

hopes this helps the rest of you who have the same problem.

Madhu

|||

Hi,

I am facing an error saying ‘Arithmetic overflow error converting expression to data type datetime.’ In data base due to my following query.

Then I tried with cast and convert function too, still I got the error.

select*

from datetable

wherecast(('May 29 20076:30:00:000PM' - endtime) as int) >=2

andcast(('May 29 20076:30:00:000PM' - endtime)as int)<=3

anddatetable_id= 102

order by datetable_iddesc

I got this beacause of some bad ‘endtime’ data in datetable for datetable_id102 : 5465-08-12 12:00:00.000.

But I need to support all type of date here and the table is also huge. So I have this col as indexed.

I thought of to use datediff func here. again I am not sure what will be the performance impact on my query, coz it will diff and convert to int and compare for each of the row.

So can any body suggest how efficiently can I handle this?

Thanks

~Dhiru

Arithmetic overflow error converting expression to data type bigint

I am attempting to setup a replication from SQL Server 2005 that will be read by SQL Server Compact Edition (beta). I'm having trouble getting the Publication Wizard to create the Publication. Sample table definition that I'm replicating:

USE dbPSMAssist_Development;
CREATE TABLE corporations (
id NUMERIC(19,0) IDENTITY(1964,1) NOT NULL PRIMARY KEY,
idWas NUMERIC(19,0) DEFAULT 0,
logIsActive BIT DEFAULT 1,
vchNmCorp VARCHAR(75) NOT NULL,

vchStrtAddr1 VARCHAR(60) NOT NULL,
vchNmCity VARCHAR(50) NOT NULL,
vchNmState VARCHAR(2) NOT NULL,
vchPostalCode VARCHAR(10) NOT NULL,
vchPhnPrimary VARCHAR(16) NOT NULL,
);
CREATE INDEX ix_corporations_nm ON corporations(vchNmCorp, id);
GO

When the wizard gets to the step where it is creating the publication, I get the following error message:

Arithmetic overflow error converting expression to data type bigint. Changed database context to 'dbPSMAssist_Development'. (Microsoft SQL Server, Error: 8115).

I can find no information on what this error is or why I am receiving the error. Any ideas on how to fix would be appreciated.

Thanks in advance ...

David L. Collison

Any day above ground is a good day.

We need more information - are you doing any filtering or joining of any articles that may cause this error?

You can also do a profile trace of the publisher when you click on the OK button to complete the wizard so you can see what stored proc and statement it's failing on. Let us know what you find.

|||Greg ...

This has to be due to the size of my key on the file. I resized the field ID to NUMERIC(12,0) and now the replication wizard completes the setup properly.

I would have anticipated the replication engine to utilize the definition - obviously it doesn't like big numbers. Ok, I know it will take a long time to create that many records to worry about filling up the key at 12 digits let alone 19, but I like to plan ahead. ;-)

Have a good one!

David L. Collison
Any day above ground is a good day!

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.