Asking for Column data type

This is an easy one , I need to know the way to check programatically for an SQL server 2000 column data type , if the result is a code , which codes are for the diferent datatypes ( varchar , text , smallint , etc ) , if you send me an url is ok.
thank youselect DATA_TYPE
from Information_Schema.Columns
where table_name = 'table_name'|||Thanks

Array data type in SQL Server 2005

Hi,

I was migrating from Oracle to SQL Server 2005 using SSMA (SQL Server Migration Assistant) but i`ve found an issue, i can′t find how to fix it. In my stored procedure in PL/SQL exists this lines:

TYPE T_ARRAY_COL IS VARRAY (1000 ) OF VARCHAR2 (50);
A_COLUMNS T_ARRAY_COL := T_ARRAY_COL();

Somebody know how can i simulate this data type ARRAY. I was reading http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/ but some things are not clear for me.... please help me, give me one hand.

Thank you

David

There is no array data type in SQL Server. You can simulate one using the built-in data types and a table (table variable or temporary table or permanent table).

declare @.t_array_col table( i int not null identity primary key, v varchar(50) not null )

insert into @.t_array_col values('a')

insert into @.t_array_col values('b')

....

select v from @.t_array_col where i = 1

select v from @.t_array_col where i = 2

...

Alternatively, you can just have fixed-length strings that represent the entire array and split them using the built-in functions.

declare @.array_str varchar(max)

set @.array_str = replicate(replicate('x', 2), 100) -- array of size 100, value is of fixed length - 2 bytes

select substring(@.array_str, (@.n * 2) + 1, 2) -- n varies from 0 through 99

set @.array_str = stuff(@.array_str, (@.n * 2) + 1, 2, replicate('z', 2)) -- initialize nth element

You can use varbinary(max) to store numeric values although it is tricky to convert from binary to one of the numeric data types & forth.

Also, there are more efficient ways to do this in SQL than trying to simulate arrays. You will get better performance by using set-based logic.

Array / Table As return Type

Hello All,

I have a scenario in which my stored procedure has to return few
variables with their value and also the collection. Now in SQL their is
no such as array, so the best is to return the table in place of.

I am execting the stored procedure by having sql command in place.
and created the various parameters(variables) those i need the values
of and secondly wondering how should i be creating the parameter as a
table returntype.

Any help on this would be a million worth useful.

I can excerpt the code if required.

Regards
Sandesh KadamSandesh (sandesh27uk@.gmail.com) writes:

Quote:

Originally Posted by

I have a scenario in which my stored procedure has to return few
variables with their value and also the collection. Now in SQL their is
no such as array, so the best is to return the table in place of.
>
I am execting the stored procedure by having sql command in place.
and created the various parameters(variables) those i need the values
of and secondly wondering how should i be creating the parameter as a
table returntype.

You cannot pass table variables as parameters.

I have an article on my web site that discusses a couple of ways to
skin the cat: http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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

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