rownum equivalent ?

Hi,
Rownum returns the serial number for the records in Oracle.
Id there an equivalent for the same in SQL Server ?
select rownum from test_table;
Please advise,
Thanks
Samsqlserver has none, the clostest match is to add an identifier-column.

RowCount or Equivalent

I have a query that returns a set of rows - sorted by part#. On the report I can hide the duplicates (part#). How can I test the part# so that whenever a new part# starts I can reverse image the whole l line. I have not defined any groups. Is this a must?

Sorry I am not understanding your question.

RowCount does not exist, use CountRows instead, then What does 'reversing the image' means.

If you are after counting rows returned by some reports parameters, something along these lines should help.

=iif(CountRows("SM") <= 0,"Your query returned no result.",(iif(CountRows("SM") >= 64500,"Report is too large to open in Excel. Please use CSV Export then use another database like Access. " & "Number of rows returned: " & CountRows("SM") , "Number of rows returned: " & CountRows("SM") )))

Philippe

RowCount or Equivalent

I have a query that returns a set of rows - sorted by part#. On the report I can hide the duplicates (part#). How can I test the part# so that whenever a new part# starts I can reverse image the whole l line. I have not defined any groups. Is this a must?

Sorry I am not understanding your question.

RowCount does not exist, use CountRows instead, then What does 'reversing the image' means.

If you are after counting rows returned by some reports parameters, something along these lines should help.

=iif(CountRows("SM") <= 0,"Your query returned no result.",(iif(CountRows("SM") >= 64500,"Report is too large to open in Excel. Please use CSV Export then use another database like Access. " & "Number of rows returned: " & CountRows("SM") , "Number of rows returned: " & CountRows("SM") )))

Philippe

RowCount is returning null

i have 2 stored procedures: a delete and a select. the delete sp returns the rowcount properly. the select returns null. the code for both sp's is extremely simple and extremely similar. when i execute the select sp in server management studio the rowcount shows a 1 as expected. but the calling method gets null.

SP Code

ALTER

PROCEDURE [dbo].[RetrieveEmployeeKeyFromAssignmentTable]

@.assignmentPrimaryKey

int,

@.rowCount

intOUTPUT

AS

BEGIN

SETNOCOUNTON;SELECT employeePrimaryKeyFROM assignmentTableWHERE primaryKey= @.assignmentPrimaryKey;SET @.rowCount=@.@.RowCount;

END

c# code

SqlConnection

conn = GetOpenSqlConnection();if (conn ==null) returntrue;SqlDataReader reader =null;SqlParameter p1 =newSqlParameter(); SqlParameter p2 =newSqlParameter();try{SqlCommand command =newSqlCommand();

command.CommandText =

"RetrieveEmployeeKeyFromAssignmentTable";

command.CommandType =

CommandType.StoredProcedure;

command.Connection = conn;

p1.ParameterName =

"@.assignmentPrimaryKey";

p1.Value = assignmentPrimaryKey;

p2.ParameterName =

"@.rowCount";

p2.Direction =

ParameterDirection.Output;

p2.Value = 0;

command.Parameters.Add(p1); command.Parameters.Add(p2);

reader = command.ExecuteReader();

if (p2.Value ==null)//always true

any suggestions would be appreciated.

thanks. matt

also, reader.HasRows is true.

matt

|||

Hello,

first, I was suspicious of your code that you assigned the value of output parameter p2.value=0.

but it was O.K., and I was wrong , I tested with query analyzer.

I looked carefully in your code, find that you called with ExecuteReader ==> it is connected data object.

you have to close the Reader object before you try to get Output or else paremeter values.

verified with internet search...

|||

EXCELLENT.

thank you very much.

matt

Row sequence number

Hello all,

Im currently using a SQL Serve 2K. Would like to do a select
which returns the row number - this should not be physically stored in
the database. So for example, I would like to do a query against the
CUSTOMER table and receive:

* rowID || name
1 Evander
2 Ron
3 Scoth
4 Jane

I dont want to store the ID, because if I change the order by
clause, the sequence may modifiy, and, for another example, having the
same set of data, I would receive:

* rowID || name
1 Scoth
2 Ron
3 Jane
4 Evander

could someone help me ?

best regards,
Evandrohttp://support.microsoft.com/defaul...b;EN-US;q186133

--
Anith|||Use Front end application to number the result

Madhivanan

Row sequence number

Hello all,

Im currently using a SQL Serve 2K. Would like to do a select
which returns the row number - this should not be physically stored in
the database. So for example, I would like to do a query against the
CUSTOMER table and receive:

* rowID || name
1 Evander
2 Ron
3 Scoth
4 Jane

I dont want to store the ID, because if I change the order by
clause, the sequence may modifiy, and, for another example, having the
same set of data, I would receive:

* rowID || name
1 Scoth
2 Ron
3 Jane
4 Evander

could someone help me ?

best regards,
EvandroLet's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.

>> could someone help me ? <<

You can help yourself by doing about one week's worth of reading on
RDBMS. You are making a fool of yourself by not knowing the basics.|||SQL 2000 does not have this pseudo-column. It is introduced in 2005.
The only way I know how to way around it is to dump your query into
temp. table that has anextra column (let's call it rowID ) which is an
auto-increment.|||Sergey (afanas01@.gmail.com) writes:
> SQL 2000 does not have this pseudo-column. It is introduced in 2005.
> The only way I know how to way around it is to dump your query into
> temp. table that has anextra column (let's call it rowID ) which is an
> auto-increment.

Neither does SQL 2005 have any pseudo-column. row_number() is a function,
and you can set it up so that it restarts on some defined partition.

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

Row numbering unpredictable

Hi,
I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by. The source is
a view. The code I have is:
SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by
When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.
Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)
Cheers,
Chris Smith
http://www.cswd.co.uk/Assuming CustomerName is unique:
select
(select count (*)
from #tmp t1
where t1.CustomerName <= t2.CustomerName) as rownum
, *
from
#tmp t2
order by
t2.CustomerName
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<cseemeuk@.googlemail.com> wrote in message
news:1144755017.455143.6760@.v46g2000cwv.googlegroups.com...
Hi,
I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by. The source is
a view. The code I have is:
SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by
When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.
Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)
Cheers,
Chris Smith
http://www.cswd.co.uk/|||you could create the table first with an ID column, then insert into
it. I suspect (though have no evidence) that the select into #tmp with
an id column created then is having issues with the order by|||(cseemeuk@.googlemail.com) writes:
> I need to create a stored procedure that returns the row number (for
> paging) AFTER the data has been sorted with an order by. The source is
> a view. The code I have is:
> SELECT rownum = IDENTITY(1,1,bigint), *
> INTO #tmp
> FROM viewName
> ORDER BY CustomerName -- field name I'm ordering by
> When I recieve the results back, the rownum column is not the same
> order as the customername (it jumps half way to a high number?!?),
> which means I can't page it based on rownum without jumping all over
> the dataset.
> Anyone got any ideas on how to solve that other than client side paging
Create the table with CREATE TABLE, and then use INSERT with SELECT ORDER
BY. Add OPTION (MAXDOP 1) as an extra precaution. I've been told from MS
people that it's guaranteed to work. Whether that really is true, I'm not
completely convinced of, but fairly. In any case, SELECT INTO is *not*
guaranteed to work that way, so stay away from it.
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|||Thanks - works perfectly. The INTO was the problem - appears to be no
guaranteed order to the IDENTITY(bigint, 1,1)
All sorted
Cheers,
Chris Smith
http://www.cswd.co.uk/|||The order is not guaranteed when you use SELECT INTO.
See
http://support.microsoft.com/defaul...kb;en-us;273586
For a list of paging options see
http://www.aspfaq.com/show.asp?id=2120
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<cseemeuk@.googlemail.com> wrote in message
news:1144755017.455143.6760@.v46g2000cwv.googlegroups.com...
> Hi,
> I need to create a stored procedure that returns the row number (for
> paging) AFTER the data has been sorted with an order by. The source is
> a view. The code I have is:
> SELECT rownum = IDENTITY(1,1,bigint), *
> INTO #tmp
> FROM viewName
> ORDER BY CustomerName -- field name I'm ordering by
> When I recieve the results back, the rownum column is not the same
> order as the customername (it jumps half way to a high number?!?),
> which means I can't page it based on rownum without jumping all over
> the dataset.
> Anyone got any ideas on how to solve that other than client side paging
> (in ADO :-P)
> This is SQL 2000 SP3 (pah!)
> Cheers,
> Chris Smith
> http://www.cswd.co.uk/
>|||One would think this type of thing,so common and important,
would have a kb or something written by MS.Are you aware of any
link?If none exists I would ask you to kindly request something in
'writing'.Key points of an enterprise database should not be rattling
around just in someone head! :)
Clarity,clarity and nothing but clarity.
Regards from:
www.rac4sql.net
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A28BF07D031Yazorman@.127.0.0.1...
> (cseemeuk@.googlemail.com) writes:
> Create the table with CREATE TABLE, and then use INSERT with SELECT ORDER
> BY. Add OPTION (MAXDOP 1) as an extra precaution. I've been told from MS
> people that it's guaranteed to work. Whether that really is true, I'm not
> completely convinced of, but fairly. In any case, SELECT INTO is *not*
> guaranteed to work that way, so stay away from it.
>
> --
> 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|||Steve Dassin wrote:
> One would think this type of thing,so common and important,
> would have a kb or something written by MS.Are you aware of any
> link?If none exists I would ask you to kindly request something in
> 'writing'.Key points of an enterprise database should not be rattling
> around just in someone head! :)
> Clarity,clarity and nothing but clarity.
http://support.microsoft.com/defaul...kb;en-us;273586
Do not assume that article means that all INSERTs will always cause
IDENTITY to be generated in a predetermined order. There are at least
some situations where that doesn't work - whether by design or a bug I
can't say.
Perhaps the safest course is to assume that you cannot control the
IDENTITY sequence with ORDER BY. In my view the wisest and most logical
solution is to use other methods like the ROW_NUMBER function for
example.
I can think of at least two good reasons for not using IDENTITY the way
proposed by the KB. Firstly IDENTITY is normally intended as an
arbitrary surrogate key - using the values in any "meaningful" way is a
compromise you don't need and is something it just isn't designed for.
Secondly, this supposed behaviour of an "ordered" INSERT looks contrary
to the set-based nature of an INSERT statement. Whether or not it works
today, it seems undesirable to assume that it should always work that
way in future. One would hope and expect that the engine could optimise
out any redundant sorting in INSERT...SELECT queries. That seems to be
what happens in some cases today and maybe it will happen more often in
future versions due to improvements in the optimiser. Just some things
to bear in mind.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> Anyone got any ideas on how to solve that other than client side paging <
<
The basic principle of a tiered architecture is that display is done in
the front end adn NEVER in the database. Why are you sing violating
40 years of Software Engineering?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144803667.948395.290650@.i40g2000cwc.googlegroups.com...
<<
> The basic principle of a tiered architecture is that display is done in
> the front end adn NEVER in the database. Why are you sing violating
> 40 years of Software Engineering?
>
Forty years of a life sentence is enough.Time to let the innocent free.
Convicted on trumped up,unsubstantiated and false charges.In other words,
NONSENSE.
The thread:
Monday, April 10, 2006 9:48 PM
microsoft.public.sqlserver.programming
Re: Membership Timeline Spanning
contains a response that further clarifies things:
"Itzik Ben-Gan" writes
>.
>In my previous reply I mentioned the ANSI OVER clause (with an ORDER BY
>option). It is really brilliant, and I wonder if the designers of the
>feature themselves knew how profound it is. I believe this option to be the
>bridge between cursors and sets; sort of the holy grail of SQL. :-)
To quote Bob Dylan:
'I would not feel so alone if everyone where getting stoned':)
Yes I agree with you in principal.The 'real' paradign shift has
little to do with the clr and everything to do with exploding
the perverted myth of the exclusivity of'set based' constructs.
The idea one can legitimately think in terms of rows without being
labelled an sql Jodus has arrived.But calling this windowing a
'profound' kind of insight and bestowing on the designers the aura
of 'brilliance' would be a mistake.It is at best an example of
'better late than never'.Calling this state of affairs profound
would surely overshadow the accountability that the commericial
database world should be held to.The fact that this mindset change
has taken almost 30 years should be seen as appalling.Neo-cons of
the industry had hijacked sense with sql creationism and marketing.
WMD was replaced with client/server and a tiered approach.A theory
was misapplied to a retrival mechanism and unapplied to a design
mechanism.An approach that vendors marketted that allowed them to
hide both their intellectual and creative shortcomings.Their db
failures made for the 'client'.And now the clr in the db has replaced
the client.And of course the dreaded cursor.This demanded regime
change and the field was bankrupted for 30 years.For this we are to
praise Ceasar?I think not.
It is interesting to look at the fanfare that vendors are using
to usher in this new paradign.In their documentation Oracle refers
to their analytic functions in windows as an example of
'data densification'.This phrase is supposed to illustrate the
flip side of the Group By.It was obviously borrowed from the idea
of pacification,right out of the Pentagon.This is the best they could
come up with?Any army of engineers berefit of language and concepts.
Not to be out done,MS in its highly touted BOL offers the next best
thing - absolutely Nothing!No explanations,no history no seqways.
The functions are thrown around like so much spaghetti on a wall.
If you write about concepts someone may quote you.MS needn't worry
now.Least I be accused of favortism,IBM was too busy pleasing its
shareholders to write anything intelligible.
Finally,to your point about MS leaving out a large chunk of analytic
material this was obviously not an oversight but just insurance
that anything done with sql-99 could most definitly be easily ported
to the competition.Less is more.Please!If they weren't sure of
what they were doing they could have at least looked at Oracle
which is probably about 8 years ahead.Or even looked at RAC to see what
you and I are really talking about :)
Interested readers maybe surprised that many of the ideas in sql
analytics can be found in the SAS (Statistical Analysis System) Data
Step...introduced about 20 years ago!Many of the Oracle extensions
(First/Last) can also be found here.MySql allows mixing of variables
and columns in a SELECT.Most of the analytics can be easily simulated
in a single SELECT.And of course little RAC, way ahead of its time:)
Some musing from:
www.rac4sql.net

Rounding error

This should return 73.34...

However, it returns a 0 instead.

select convert(decimal(18,2),(3370)/(4595)*100)

Can you pl advise.

Either add a .0 to the end of the numbers, or explicitly convert to float.

The reason is that SQL Server, when dividing integers, returns an integer.

select convert(decimal(18,2),(3370.0)/(4595.0)*100.0) returns 73.34

BobP

|||

To add to Bob's comment:

IF both the dividend and divisor are whole numbers (int), SQL assumes you want the results as an int.

IF either of the two contains a decimal, a float will be returned. For example:

SELECT (( 3370./4595 ) * 100.0 )

--
73.3405000

|||

The datatype on the field, acctno is Int.. I use the convert statement to convert to float. but it still returns the data in INt ie it return 3370 instead of 3370.0.. Though I AM using covnert to float, data is still returned as an INT.

Select round(count(convert(float,convert(decimal(38,5),acctno))),2) from tbl1
WHERE (tbl1.balance<>0).

Any ideas how I can return The value as 3370.0

|||

Count returns an int... so there is no decimal.

Use convert(float, count(...

BobP

|||

Whoa nellie! What's with the round(), count() convert(), convert(), etc.

There is reason to convert(), round(), etc., it you are only interested in the count of rows meeting the criteria.

SELECT cast( count( AcctNo ) AS decimal(10,2))

FROM tbl1

WHERE tbl1.Balance <> 0

'should' do the trick.

Rounding decimals for numbers AFTER the AVG func. Please take a l

My query returns numbers to the report, these are already rounded (this is
made to happen in query as they are cast to int).
After I get these integers to my report, I tend to group them, and AVG a
group set. If there is a lot of the numbers in the equation, the total tends
to decimalise to a really ridiculous number (x.xxxxxxxxxxxx). So, I try
putting d or n in the custom format properties for this textbox, I also try a
number of the default formatting options. Even I try to use the <format> tag
in the XML. All to no effect.
The only thing I can think is that I tend to write out a '%' after the
number (to show it's a percentage, it's purely cosmetic). Here is a line of
the AVG function, and how I am using it . I would really appreciate it if
anyone knew what I might be goofing up on here!
=iif(Avg(Fields!DoneIt.Value) = 0, "0%", Avg(Fields!DoneIt.Value) & "%")Did you try just using the following expression:
=Avg(Fields!DoneIt.Value)
and then just use formatcodes like P, P0, P1, etc.?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt Swift" <MattSwift@.discussions.microsoft.com> wrote in message
news:EA1C086B-CA02-4314-8CED-970B2D72B5EB@.microsoft.com...
> My query returns numbers to the report, these are already rounded (this is
> made to happen in query as they are cast to int).
> After I get these integers to my report, I tend to group them, and AVG a
> group set. If there is a lot of the numbers in the equation, the total
> tends
> to decimalise to a really ridiculous number (x.xxxxxxxxxxxx). So, I try
> putting d or n in the custom format properties for this textbox, I also
> try a
> number of the default formatting options. Even I try to use the <format>
> tag
> in the XML. All to no effect.
> The only thing I can think is that I tend to write out a '%' after the
> number (to show it's a percentage, it's purely cosmetic). Here is a line
> of
> the AVG function, and how I am using it . I would really appreciate it if
> anyone knew what I might be goofing up on here!
> =iif(Avg(Fields!DoneIt.Value) = 0, "0%", Avg(Fields!DoneIt.Value) & "%")|||In your report, go to [ Report Properties ] from your [ Report ] menu.
On the [ Code ] tab, paste this in...
--[ BEGIN CODE ]--
Public Function ConvertToInt(ByVal x_obj As Object) As String
Return String.Format("{0:#}", x_obj)
End Function
--[ END CODE ]--
Now, change your textbox's formula to:
=ConvertToInt(Avg(Fields!DoneIt.Value)) & "%"
Andrew Bruderer
"Robert Bruckner [MSFT]" wrote:
> Did you try just using the following expression:
> =Avg(Fields!DoneIt.Value)
> and then just use formatcodes like P, P0, P1, etc.?
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Matt Swift" <MattSwift@.discussions.microsoft.com> wrote in message
> news:EA1C086B-CA02-4314-8CED-970B2D72B5EB@.microsoft.com...
> > My query returns numbers to the report, these are already rounded (this is
> > made to happen in query as they are cast to int).
> >
> > After I get these integers to my report, I tend to group them, and AVG a
> > group set. If there is a lot of the numbers in the equation, the total
> > tends
> > to decimalise to a really ridiculous number (x.xxxxxxxxxxxx). So, I try
> > putting d or n in the custom format properties for this textbox, I also
> > try a
> > number of the default formatting options. Even I try to use the <format>
> > tag
> > in the XML. All to no effect.
> >
> > The only thing I can think is that I tend to write out a '%' after the
> > number (to show it's a percentage, it's purely cosmetic). Here is a line
> > of
> > the AVG function, and how I am using it . I would really appreciate it if
> > anyone knew what I might be goofing up on here!
> >
> > =iif(Avg(Fields!DoneIt.Value) = 0, "0%", Avg(Fields!DoneIt.Value) & "%")
>
>

round function

Can anyone reproduce this with sql 2005?
SELECT ROUND(95.99, -1)
returns:
An error occurred while executing batch. Error message is: Arithmetic
Overflow.
if you try it with anything less than 95 it works. It errors out any
time it is supposed to round up to a number with 1 more digit (i.e. 95
to 100, 996 to 1000).
This worked in sql 2000.
The fix is "select convert(float,round(95.99, -1)"
Very strange.returns 100 for me on
Microsoft SQL Server 2005 - 9.00.2040.00 (Intel X86)
Mar 13 2006 11:20:51
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<clemlau@.yahoo.com> wrote in message
news:1157591318.990113.257650@.h48g2000cwc.googlegroups.com...
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||Returns 100.00 for me on:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<clemlau@.yahoo.com> wrote in message
news:1157591318.990113.257650@.h48g2000cwc.googlegroups.com...
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||Even I get an error
An error occurred while executing batch. Error message is: Arithmetic
Overflow.
My @.@.version returns
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
"clemlau@.yahoo.com" wrote:
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||Anything from ROUND(95.99, -1) to ROUND(99.99, -1) gives an error. Strange!
"clemlau@.yahoo.com" wrote:
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||I get the error
An error occurred while executing batch. Error message is: Arithmetic
Overflow.
Here is the version number
Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
So it works on 32 bit, but does not work 64 bit.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<clemlau@.yahoo.com> wrote in message
news:1157591318.990113.257650@.h48g2000cwc.googlegroups.com...
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||On Thu, 7 Sep 2006 09:11:22 -0400, Hilary Cotter wrote:
>I get the error
>An error occurred while executing batch. Error message is: Arithmetic
>Overflow.
>
>Here is the version number
>Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
>Apr 14 2006 01:11:53
>Copyright (c) 1988-2005 Microsoft Corporation
>Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
>
>So it works on 32 bit, but does not work 64 bit.
Hi Hilary,
It doesn't work on all 32-bit systems, though.
SELECT @.@.VERSION
SELECT ROUND(95.99, -1)
On SQL-2005:
Microsoft SQL Server 2005 - 9.00.2153.00 (Intel X86)
May 8 2006 22:41:28
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
An error occurred while executing batch. Error message is: Rekenkundige
overloop.
On SQL-2000:
Microsoft SQL Server 2000 - 8.00.2187 (Intel X86)
Mar 9 2006 11:38:51
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
An error occurred while executing batch. Error message is: Rekenkundige
overloop.
Hugo Kornelis, SQL Server MVP