rows deletion affected by cursor

Hello,

I am using a cursor to navigate on data...of a table...
inside the while @.@.fetch_status = 0 command
I want to delete some rows from the table(temporary table)
in order to not be processed...
The problem is that I want this deletion to affect the rows the cursor has.

I declared a dynamic cursor but it does not work.

Does anyone know how I can do this??

Thanks :)Perhaps the fetch into @.var is already executed, changes to the resultset do not affect values in variables? If not, could you post your code?|||The deleted is executed before the fetch next statement.

My code is:

Set @.items = Cursor
For
Select T.patentrynr,t.patcode,t.patname,cast(t.groupid As Nvarchar(10)),g.groupname,
(select Sum(idaxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid )as Idaxia,
(select Sum(tamaxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid) As Tamaxia,
(select Sum(insuraxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid) As Insuraxia,
Itemnum,indvrate,indvamount,tamrate,
Tamamount,insurrate,insuramount,maxqty,tamname,gro upnum,t.groupid
From @.trans T Inner Join @.rates R
On R.groupid = T.groupid And R.groupid Is Not Null
And T.itemid!=cast(t.groupid As Nvarchar(10))
Inner Join Groups G On G.groupid = T.groupid
Order By T.patentrynr,t.groupid

Open @.items

Fetch From @.items Into
@.patentrynr,@.patcode,@.patname,@.itemid,@.itemname,@.i daxia,@.tamaxia,@.insuraxia,@.itemnum,
@.indvrate,@.indvamount,@.tamrate,
@.tamamount,@.insurrate,@.insuramount,@.maxqty,@.tamnam e,@.groupnum,@.groupid

While @.@.fetch_status = 0
Begin
If @.indvamount Is Not Null And @.groupnum Is Not Null And
@.idaxia Is Not Null And @.idaxia!=0
Begin
If @.groupnum > @.maxqty And @.maxqty Is Not Null
Begin
Set @.indvposo = @.maxqty*@.indvamount
End
Else
Begin
Set @.indvposo = @.groupnum*@.indvamount
End
End
Else If @.indvrate Is Not Null And @.idaxia Is Not Null
Begin
Set @.indvposo = @.idaxia*(@.indvrate/100)
End


Insert Into @.result (patentrynr,patcode,patname,tamname,itemid,itemnam e,indvtziros,
Tamtziros,insurtziros,indvpososto,tampososto,insur pososto,parakrat)
Values(@.patentrynr,@.patcode,@.patname,@.tamname,@.ite mid,@.itemname,@.idaxia,@.tamaxia,@.insuraxia,
@.indvposo,@.tamposo,@.insurposo,@.parakrat)


Set @.idaxia=null
Set @.tamaxia =null
Set @.insuraxia=null
Set @.itemnum=null
Set @.indvrate=null
Set @.indvamount=null
Set @.tamrate=null
Set @.tamamount=null
Set @.insurrate=null
Set @.insuramount=null
Set @.maxqty=null
Set @.indvposo=null
Set @.tamposo=null
Set @.insurposo=null

Delete From @.trans Where Patentrynr = @.patentrynr
And Groupid = @.groupid


Fetch Next From @.items
Into @.patentrynr,@.patcode,@.patname,@.itemid,@.itemname,
@.idaxia,@.tamaxia,@.insuraxia,@.itemnum,
@.indvrate,@.indvamount,@.tamrate,@.tamamount,
@.insurrate,@.insuramount,@.maxqty,@.tamname,@.groupnum ,@.groupid
End --end While|||I don't see a reason why rows would not be deleted from the variable table. Are you saying no rows at all are deleted from the table? What are the values of @.groupid and @.patentrynr prior the delete (what rows match)?

I've setup an example that basically does the same, perhaps it gives you an idea.

use monkey
go

set nocount on

declare @.varTab table ( myInt integer, myValue varchar(3))

insert into @.varTab (myInt, myValue) values (1, 'aaa')
insert into @.varTab (myInt, myValue) values (2, 'aaa')
insert into @.varTab (myInt, myValue) values (3, 'aaa')
insert into @.varTab (myInt, myValue) values (4, 'aaa')

declare @.myint integer
declare @.myvalue varchar(3)
declare cur_tab1 cursor DYNAMIC
for select myInt, myvalue from @.varTab

declare @.mtef cursor
set @.mtef = cur_tab1 -- ?

open @.mtef
fetch next from @.mtef into @.myint, @.myvalue
while @.@.fetch_status = 0
begin
-- update tab1 set myValue = 'bbb' where myInt = @.myInt + 1
delete from @.varTab where myInt = @.myInt
select 'myInt: ', @.myint, @.myvalue

fetch next from @.mtef into @.myint, @.myvalue
end

select * from @.varTab

deallocate @.mtef
go|||I have executed your example an it works...fine
but when i added a select statement in my code
before the fetch next statement to find what the table hoes
I found that the values are deleted...
I have also added a select statement after the fetch next...
to find out the values that will be next processed
and they are the next values found in the table before the deletion...

Do I have to change anything in the cursor declaration?|||I'm not sure what you're saying. So the rows are deleted from @.trans? What do you mean with "Do I have to change anything in the cursor declaration?" (assuming the delete works)?|||The delete works but...
I want the fetch next to fetch the next row in the table after the deletion.
This does not work.
It cursor fetches the next row in the table as it was before the deletion.
It seems that the data in the cursor is static...and it is not affected by the deletion.|||so it's like...

...
for select myInt, myvalue
from @.varTab
order by myInt -- order by clause
...
delete from @.varTab where myInt = @.myInt + 1 -- First run: delete myInt = 2
...
deallocate @.mtef
deallocate cur_tab1

The output includes all four rows...

Does your cursor declaration include an 'ORDER BY'-clause?
If it does, your cursor is converted into a KEYSET-cursor (see BOL on this).
What I know from keyset cursors is from BOL, so I'm a bit guessing here but I think the deletes are not visible because it's not the cursor doing the deletes.

rows column in sysindexes table got overflow

Hi, does anyone have the overflow issue with the column, rows in the
sysindexes table? We have table with over 3 billions records and it throws
error 8115 overflow error when I double click the table, which should return
row counts in the table. There is no issue with all data manipulation on thi
s
table even with count_big. When I checked the sysindexes table for this
table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
always max number of integer (even after new insert).
--
hm100This is because the procedure used by EM to return this information
(sp_MStablespace) is trying to force a bigint into an int variable using the
following code
SELECT @.rows = convert(int, rowcnt)
FROM dbo.sysindexes
WHERE indid < 2 and id = @.id
This is no longer used by management studio in SQL2005
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should
> return
> row counts in the table. There is no issue with all data manipulation on
> this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100|||"double-click the table"... Sounds like some bug in the tool you are using?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should retu
rn
> row counts in the table. There is no issue with all data manipulation on t
his
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100

rows column in sysindexes table got overflow

Hi, does anyone have the overflow issue with the column, rows in the
sysindexes table? We have table with over 3 billions records and it throws
error 8115 overflow error when I double click the table, which should return
row counts in the table. There is no issue with all data manipulation on this
table even with count_big. When I checked the sysindexes table for this
table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
always max number of integer (even after new insert).
--
hm100This is because the procedure used by EM to return this information
(sp_MStablespace) is trying to force a bigint into an int variable using the
following code
SELECT @.rows = convert(int, rowcnt)
FROM dbo.sysindexes
WHERE indid < 2 and id = @.id
This is no longer used by management studio in SQL2005
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should
> return
> row counts in the table. There is no issue with all data manipulation on
> this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100|||"double-click the table"... Sounds like some bug in the tool you are using?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should return
> row counts in the table. There is no issue with all data manipulation on this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100sql

rows become columns help!

I am building a calendar table for the most reason four weeks activitis and I have had a temp table data in table A (See my attached file) and I want to
make it as the format in table B as final. How to convert it? Please help!
Thank you!
Suincut this and paste

This should get you on your way.

More formating needed

select
case when dow = 1 then vactype else null end MON,
case when dow = 2 then vactype else null end TUES,
case when dow = 3 then vactype else null end WED,
case when dow = 4 then vactype else null end THUR,
case when dow = 5 then vactype else null end FRI,
case when dow = 6 then vactype else null end SAT,
case when dow = 7 then vactype else null end SUN,
nameitem,
weeknum
from(
select 'VAC DAY' AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/1/2005' AS FULLWORK,
datepart(dw,'1/1/2005') AS DOW,
1 as weeknum
union
select 'VAC DAY' AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/2/2005' AS FULLWORK,
datepart(dw,'1/2/2005') AS DOW,
2 as weeknum
union
select 'VAC DAY' AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/3/2005' AS FULLWORK,
datepart(dw,'1/3/2005') AS DOW,
2 as weeknum
union
select null AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/4/2005' AS FULLWORK,
datepart(dw,'1/4/2005') AS DOW,
2 as weeknum
union
select null AS VACTYPE,
'JOHN DOE' AS NAMEITEM,
'1/5/2005' AS FULLWORK,
datepart(dw,'1/5/2005') AS DOW,
2 as weeknum) a|||thanks thanks!

Rows as Colums

I know this is possible in DB2 and Oracle, but what about for SQL-server 2005

1) select X number of rows from table1

2) I need colums for each row of table1 in a new table

3) As such, Select (select * from X where x.id = @.ID), a,b,c from table Y where y.Id = @.ID

And I dont want to use IfExists.

Thanks

DK

I don't think it is possible the way your are describing.

You could try the pivot method described in this article:

http://dotnet.sys-con.com/read/45543.htm

rows as columns

is it possible to write a query so that we can have all rows of one column in a single column
TIA
Yes. You could concatenate all the rows in the query as:
SELECT
column1 + column2 + column3
FROM
yourtable
One thing to note here is, since the columns would have different datatypes if you try to concatenate varchar column with int column SQL Server might throw an error. So it is adviced to use CONVERT function to convert all the values into varchar, something like:
SELECT
( CONVERT(varchar(5),intcolumn1) + CONVERT(varchar(10),decimalcolumn2) + regularvarcharcolumn3 )
FROM
yourtable

Rows as Columns

This summary is not available. Please click here to view the post.

Rows and Columns

I have a query that reports all of the data I want. However it creates about 40 columns and 10 rows. I would like to swap the display so that it shows 10 columns and 40 rows, which would cause it to print out more readable. Can it be done?Are you using SQL Server 2005 or SQL Server 2000?|||

Hi,

in SQL Server 200 oyu will have to use appropiate CASE expressions, in SQL Server 2005 you can use the new PIVOT functionality-

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am using SQL 2000 which someone else said needed a CASE statement. What I have seen seems to want to add things. What I have is a list of numbers and then columns that are the results of my Query.

I have columns named Store , Name, Net, Tax, Deposits (and a bunch of others)

I have rows with the store numbers 2, 3, 4, 5 ,6 that I sort by

I would like the columns as rows and the store numbers across the top

Thanks

sql

Rows Affected By Delete

Hello all,
Is there someway to tell how many rows were affected by a delete statement? A variable perhaps?

Any help would be appreciated!
Brian@.@.ROWCOUNT stores the number of rows affected by the last statement. It is continually changing, so you may need to store the value in another variable immediately after your statement is executed.

rows

how do i find number of rows ?

i search almost all through google but i did not find any answer to my question

1) number of rows per page

2) total number of rows

?

thanks a lot in advance

nobody |||

Number of rows in a dataset: you could use for instance the Count() aggregate function. See MSDN for more details: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_1l6b.asp

Number of rows on a page - you can use aggregate functions in the page header or footer that will count how often a certain textbox (e.g. located in list/table details) occurs on the current page. E.g. =Count(ReportItems!textbox1.Value)
See also: http://msdn2.microsoft.com/en-us/library/ms159677.aspx

-- Robert

rownumbering w tsql lost to ADO loop -

Hello
Thanks to all who have been helping me to fight a battel
with tsql for rownumbering of my tables. I humbly admit
defeat for today. I had to throw the towel in and whipped
up an ADO loop to number some rows in a table. That took
me all of 3 minutes, if that. I wrestled with tsql all
day long. Well, here is one more shot at tsql.
create table t (rownum int, area char(1), yearnum int,
monthnum int, areaNum int, productNum int)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 3)
this yields
rownum area yearnum monum areanum prodnum
NULL A 2003 1 1 1
NULL A 2003 1 1 2
NULL A 2003 1 1 3
NULL B 2003 1 2 1
NULL B 2003 1 2 2
NULL B 2003 1 2 3
NULL C 2003 1 3 1
NULL C 2003 1 3 2
NULL C 2003 1 3 3
NULL A 2003 2 1 1
NULL A 2003 2 1 2
NULL A 2003 2 1 3
NULL B 2003 2 2 1
NULL B 2003 2 2 2
NULL B 2003 2 2 3
NULL C 2003 2 3 1
NULL C 2003 2 3 2
NULL C 2003 2 3 3
I need to number these rows in the order of
yearNum, MonthNum, areaNum, productNum
select * from t order by yearnum, monthnum, areanum,
productnum
how can I number the rownum column starting at 1 using
tsql?
Thanks in advance,
RonDo:
UPDATE t
SET rownum = ( SELECT COUNT(*)
FROM t t1
WHERE CAST( t1.yearnum AS VARCHAR) +
RIGHT( '0' + CAST( t1.monthnum AS VARCHAR), 2 ) +
CAST( t1.areaNum AS VARCHAR) +
CAST( t1.productNum AS VARCHAR) <=
CAST( t.yearnum AS VARCHAR) +
RIGHT( '0' + CAST( t.monthnum AS VARCHAR), 2 ) +
CAST( t.areaNum AS VARCHAR) +
CAST( t.productNum AS VARCHAR) )
WHERE rownum IS NULL ;
Btw, do you really need a reason to have such a column in the table.
Generally, it is a good idea to do such derivations while extracting the
data from the table or to use a view with the ranking expression.
Anith|||thanks for your reply. This worked perfectly.
And to answer your question if I really need this
column? Truthfully, no. I don't really need it. Well,
for my purposes, this column facilitates reporting and ad-
hoc queries for data analysts in my dept at the
workplace. My real problem was that I did not know how
to achieve this with t-sql. I was aware of using the
self join, but I did not know that you had to cast int
values as varchars to accomplish this numerating.
I believe that my exercise for today will be very
valuable in the future.
Thanks for your help,
Ron

>--Original Message--
>Do:
>UPDATE t
> SET rownum = ( SELECT COUNT(*)
> FROM t t1
> WHERE CAST( t1.yearnum AS VARCHAR) +
> RIGHT( '0' + CAST( t1.monthnum
AS VARCHAR), 2 ) +
> CAST( t1.areaNum AS VARCHAR) +
> CAST( t1.productNum AS VARCHAR)
<=
> CAST( t.yearnum AS VARCHAR) +
> RIGHT( '0' + CAST( t.monthnum
AS VARCHAR), 2 ) +
> CAST( t.areaNum AS VARCHAR) +
> CAST( t.productNum AS VARCHAR) )
> WHERE rownum IS NULL ;
>Btw, do you really need a reason to have such a column
in the table.
>Generally, it is a good idea to do such derivations
while extracting the
>data from the table or to use a view with the ranking
expression.
>--
>Anith
>
>.
>|||One more question if I may. I hope this isn't too
ignorant sounding, but...
I noticed a semi colon at the end of your code. Does
Enterprise Manager have a wizard that can generate the t-
sql for something like this? Man, I sure hope so. It
would sure save me a lot of heartache.

>AS VARCHAR), 2 ) +
VARCHAR)
><=
>AS VARCHAR), 2 ) +
VARCHAR) )
>in the table.
>while extracting the
>expression.
>.
>|||>> Does Enterprise Manager have a wizard that can generate the t-sql for
No, they are optional. You need not worry about it.
Btw, for the problem you posted, instead of concatenating the columns you
could do a series of OR-ed correlations like:
WHERE t1.yearnum <= t.yearnum
OR ( t1.yearnum = t.yearnum
AND t1.t1.monthnum <= t.t1.monthnum )
OR ( t1.yearnum = t.yearnum
AND t1.monthnum = t.t1.monthnum
AND t1.areaNum <= t.areaNum )
OR ( t1.yearnum = t.yearnum
AND t1.monthnum = t.t1.monthnum
AND t1.areaNum = t.areaNum
AND t1.productNum <= t.productNum )
Anith|||Thanks again for this explanation. I guess I will just
have to keep experimenting. This other method did work.

>--Original Message--
generate the t-sql for
>No, they are optional. You need not worry about it.
>Btw, for the problem you posted, instead of
concatenating the columns you
>could do a series of OR-ed correlations like:
>WHERE t1.yearnum <= t.yearnum
> OR ( t1.yearnum = t.yearnum
> AND t1.t1.monthnum <= t.t1.monthnum )
> OR ( t1.yearnum = t.yearnum
> AND t1.monthnum = t.t1.monthnum
> AND t1.areaNum <= t.areaNum )
> OR ( t1.yearnum = t.yearnum
> AND t1.monthnum = t.t1.monthnum
> AND t1.areaNum = t.areaNum
> AND t1.productNum <= t.productNum )
>--
>Anith
>
>.
>

RowNumber?

I want to add a record number to each record in my report.
I don't have any grouping on my report, so not sure if using
the rownumber() function works in that case'
C.The following should work:
=RowNumber(Nothing)sql

Rownumber()

I am trying to write a stored procedure to be used for custompaging and I get error with the below SP.
"Msg 207, Level 16, State 1, Procedure GetDealersPagedSP, Line 14 Invalid column name 'RowRank'."

What am I doing wrong?

CREATEPROCEDURE dbo.GetDealerSP

(
@.startRowIndexint,
@.maximumRowsint
)
As
SELECT installersemaid,dealerid,[name],address1,address2,city,[state],
zip,phone,fax
From
(
SELECT installersemaid,dealerid,[name],address1,address2,city,[state],
zip,phone,fax,ROW_NUMBER()OVER(ORDERBY [name]DESC)AS Rowbank
FROM dealerenrollment)as DealerWithRowNumbers
WHERE Rowbank> @.startRowIndexAND RowRank<=(@.startRowIndex+ @.maximumRows)
Go

Hi bhavin78,

bhavin78:

"Msg 207, Level 16, State 1, Procedure GetDealersPagedSP, Line 14 Invalid column name 'RowRank'."

...

CREATEPROCEDURE dbo.GetDealerSP

...

WHERE Rowbank> @.startRowIndexANDRowRank<=(@.startRowIndex+ @.maximumRows)
Go

Looks like it's a typo, that RowRank should be Rowbank according to the rest of your query.

Personally, I'd change the three instances of Rowbank to RowRank, as it's a little closer to describing the columns contents (actually, calling it RowNumber would be my first choice ;) ).

I hope that helps.

|||

--zip,phone,fax, ROW_NUMBER() OVER(ORDER BY [name] DESC)ASRowbank

You have used Rowbank not RowRank check it once

Rownumber with grouping

Hi everyone

I'm having a problem that I hope someone will be able to help me with. I've created a report with a table, and using =Rownumber(nothing) to give each row a number - this works fine.

Now, however, I'm trying to create a summary report. So I've introduced grouping on one of the columns, but unfortunately the Rownumber command now doesn't seem to be taking that into account, and still showing the original row numbers. So basically I'm ending up with numbers like 2, 6, 9, 15, etc.

I've tried changing the scope from nothing to the row name and to the group name, but they all do the same thing.

Does anyone know how to solve this?

Thanks,
Matt

The grouping should make the row numbers add up properly. Are you putting the group name within double quotes, like this?

=RowNumber("MyGroupName")|||

Thanks for your reply Darrell.

Yes I am using double quotes like that. Anything else gives me a syntax error when i try to run it.

Any other thoughts. I can't work out what the problem is here. Would it help if I posted a copy of my report?

|||

Hi again

I apologise if I'm sounding petulant here, but I really am at a loss. I can't find any information anywhere on the web, and I can't work out what else I could be doing wrong. If anyone has any ideas I would really appreciate hearing them.

Thanks,
Matt

|||did you ever solve this? i am having the same challenge. there is a serious lack of documentation on this.|||

Just solved this a few minutes ago. I love it when I answer my own questions.

The key is to use CountDistinct on the value you are grouping on. So use:

=RunningValue(Fields!YourGroupField.Value, CountDistinct, Nothing)

For my whole write-up, visit:

http://maxqtech.com/CS/blogs/david_leibowitz/archive/2006/08/22/3372.aspx

David Leibowitz

Business Intelligence Practice Manager

MaxQ Technologies

|||

Hey David

Thanks for your reply. I never did get it sorted out, so I appreciate your solution. Will remember it for next time :)

Matt

RowNumber using SQL Query

Hi All,
I have following table structure,

----------------------
ChallanID ProductID PublicationDate Description Qty Amt
----------------------
43 9 4/1/2006 ABC 1 880
43 10 5/1/2006 BCA 1 930
43 11 5/1/2006 CBA 1 230

I want a sql query which select all the record with a serial number eg:

---------------------
SN# ChallanID ProductID PublicationDate Description Qty Amt
----------------------
1 43 9 4/1/2006 ABC 1 880
2 43 10 5/1/2006 BCA 1 930
3 43 11 5/1/2006 CBA 1 230use a temp table with identity column and insert your result into the temp table and select it back.|||U havent mentioned about how records to be ordered? In what order u want to generate serial No:?|||temp table shemp table...

SELECT count(*) as [SS#],a.LastName
FROM Employees a join
Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName

ps. I got this example from somewhere and it is not original work. If the original author sees this and takes any offense I am will to erase from the forum.|||SELECT count(*) as [SS#],a.LastName
FROM Employees a join
Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName
No, that only works if you use a unique key i.e.
select id=1,name='ccc' into #t1 union all
select 3,'bbb' union all
select 4,'aaa' union all
select 9,'bbb'

select count(*) as [ss#], name=min(a.name)
from #t1 a, #t1 b
where a.id>=b.id
group by a.id
order by 1
else use a temp table with identity column as suggested by khtan
select ss#=identity(int,1,1),name into #t2 from #t1 order by name
select * from #t2|||Problem is, either way you have no guarantee that the "serial number" for any record won't change as the contents of the table changes. Seems to me a "serial number" is expected to be static, so you really should add it as a permanent column to your table (perhaps as an identity datatype).

RowNumber Scope

What is the syntax for getting the inner-most scope for RowNumber ie within
a given group so that it behaves as follows:
Group1
rec1
Group2
rec1.1 expect to see Rowcount=1
rec1.2 expect to see Rowcount=2
rec2
Group2
rec2.1 expect to see Rowcount=1
rec2.2 expect to see Rowcount=2After posting this I realized that RowNumber won't give me the specific
rownumber just a total|||Mike
Rownumber SHOULD give you what you want... just add the name of the group as
the parameter ie
=rownumber(group1)
This gives the running count ( which would be the row number), and resets on
each new Group1 group...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and
it''''s
community of SQL Professionals.
"Mike Harbinger" wrote:
> After posting this I realized that RowNumber won't give me the specific
> rownumber just a total
>
>|||That's what I thought but it does not seem to be resetting even though I am
setting the group scope. The first row in the group is an addtional column
heading that I only want to see once per group instance. I was trying to use
RowNumber to test for row1 so I can toggle the visibiilty of the column
headers. Maybe there is a better way to do this?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:60CED50A-C9CB-4F65-B9A8-ECA1F529A69F@.microsoft.com...
> Mike
> Rownumber SHOULD give you what you want... just add the name of the group
> as
> the parameter ie
> =rownumber(group1)
> This gives the running count ( which would be the row number), and resets
> on
> each new Group1 group...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and
> it''''s
> community of SQL Professionals.
>
> "Mike Harbinger" wrote:
>> After posting this I realized that RowNumber won't give me the specific
>> rownumber just a total
>>

RowNumber reset at start of each grouping

I'm creating a report where I'd like to list out the lines of the report. I
deceided to use RowNumber function but it is keeping a running total when I
use it like this: RowNumber(Nothing)
I used the report wizard to create a report and used the page grouping to
seperate pages by "team". How do I find the scope to tell RowNumber to
startover for each team?
ColinYou can set the scope of RowNumber by replacing Nothing with the name of the
group where you want the reset to occur. If your group name is Team then you
would use RowNumber("Team").
"Colin" wrote:
> I'm creating a report where I'd like to list out the lines of the report. I
> deceided to use RowNumber function but it is keeping a running total when I
> use it like this: RowNumber(Nothing)
> I used the report wizard to create a report and used the page grouping to
> seperate pages by "team". How do I find the scope to tell RowNumber to
> startover for each team?
> Colin
>
>sql

RowNumber Problem in Grouped Data

I have multiple (multilevel) groupings in my RDL (based on line level data
from SQL server). While using the RowNumber("GrouName") I am getting strange
results. For example If that grouping was made out of 3 individual lines from
data set, then I am getting Row number as 3.
What I want is irrespective of the no of lines involved in such aggregation,
I want the rownumbers based on the final aggregated line and not based on the
no of detailed lines involved in such groupings.
Any help will be appreciated!
Thanks,
VisualcppI get this also. With my groupings, RowNumber behaves identically to
CountRows. I have something that looks like:
Group1
Group 2
Details
Group2 and details are initially hidden so I want to alternate row
colors for the group1 header and footer rows. RowNumber("Group1")
returns the number of rows in that group, not the number of the
group. It's identical to CountRows("Group1").
If you figure out the solution to this, please let me know!
On Feb 15, 1:51 am, Visualcpp <Visual...@.discussions.microsoft.com>
wrote:
> I have multiple (multilevel) groupings in my RDL (based on line level data
> from SQL server). While using theRowNumber("GrouName") I am getting strange
> results. For example If that grouping was made out of 3 individual lines from
> data set, then I am getting Row number as 3.
> What I want is irrespective of the no of lines involved in such aggregation,
> I want the rownumbers based on the final aggregated line and not based on the
> no of detailed lines involved in such groupings.
> Any help will be appreciated!
> Thanks,
> Visualcpp|||I don't know if this is the proper way to get around this, but the
following works correctly. If you happened to have blank or null
group headings this method would not work.
=IIF(RunningValue(Fields!name.Value,CountDistinct,nothing) mod 2,
"Transparent", "LightYellow")
On Mar 11, 10:35 am, brandon.rich...@.gmail.com wrote:
> I get this also. With my groupings,RowNumberbehaves identically to
> CountRows. I have something that looks like:
> Group1
> Group 2
> Details
> Group2 and details are initially hidden so I want to alternate row
> colors for the group1 header and footer rows. RowNumber("Group1")
> returns the number of rows in that group, not the number of the
> group. It's identical to CountRows("Group1").
> If you figure out the solution to this, please let me know!
> On Feb 15, 1:51 am, Visualcpp <Visual...@.discussions.microsoft.com>
> wrote:
>
> > I have multiple (multilevel) groupings in my RDL (based on line level data
> > from SQL server). While using theRowNumber("GrouName") I am getting strange
> > results. For example If that grouping was made out of 3 individual lines from
> > data set, then I am getting Row number as 3.
> > What I want is irrespective of the no of lines involved in such aggregation,
> > I want the rownumbers based on the final aggregated line and not based on the
> > no of detailed lines involved in such groupings.
> > Any help will be appreciated!
> > Thanks,
> > Visualcpp- Hide quoted text -
> - Show quoted text -

Rownumber in result of query

Hi there,
Anyone knows how to get the rownumber in the result of the query?
thank you,
Max
Hi
Do you want to get the row of the original table or do you want to add a
numbering sequence to your result?
If it is the 2nd option, you need to add that manually before returning the
result to the client by means of a temp table.
Regards
Mike
"Max" wrote:

> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
>
|||Here is an example.
use pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
|||Max,
See:
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/?id=186133
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Max wrote:
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
|||use Northwind
select
(select count(*) from orders as A where A.orderid <= B.orderid) as
row_num,
orderid, customerid, convert(varchar(10), orderdate, 120) as
orderdate from orders as B order by orderid
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>

Rownumber in result of query

Hi there,
Anyone knows how to get the rownumber in the result of the query?
thank you,
MaxHi
Do you want to get the row of the original table or do you want to add a
numbering sequence to your result?
If it is the 2nd option, you need to add that manually before returning the
result to the client by means of a temp table.
Regards
Mike
"Max" wrote:
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
>|||Here is an example.
use pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>|||Max,
See:
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/?id=186133
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Max wrote:
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>|||use Northwind
select
(select count(*) from orders as A where A.orderid <= B.orderid) as
row_num,
orderid, customerid, convert(varchar(10), orderdate, 120) as
orderdate from orders as B order by orderid
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>

RowNumber Group

I have a report the group is a people name and the detail is their personal
informations. I wuold like insert a row numer in the group, try but in the
list sometime jump a numeber. For example:
...
...
10 John Smith
11 Luis Abrams
13 Mike Logan
...
...
25 Sam Carter
27 Tom Grey
...
how have a solution for my problem?
Thank's
MauroHi,
you can try use running value
=3DRunningValue(Fields!keyfieldinthegroup.Value, CountDistinct,
"groupname")
On Apr 3, 10:22=A0pm, Mauro <Ma...@.discussions.microsoft.com> wrote:
> I have a report the group is a people name and the detail is their persona=l
> informations. I wuold like insert a row numer in the group, try but in the=
> list sometime jump a numeber. For example:
> ...
> ...
> 10 John Smith
> 11 Luis Abrams
> 13 Mike Logan
> ...
> ...
> 25 Sam Carter
> 27 Tom Grey
> ...
> how have a solution for my problem?
> Thank's
> Mauro

RowNumber counts hidden rows?

Hello i have an expression build on RowNumber and i realized it counts hidden
rows (visibility = false)... this seems very odd to me.. is the way it was
designed'let me be more specific... i have a expression to change the row color... but
this isn't working because the report is incrementing RowNumber on hidden
rows... so the coloring seems random to the person reading the report. Is
this normal' Is there a work around?
"thejez" wrote:
> Hello i have an expression build on RowNumber and i realized it counts hidden
> rows (visibility = false)... this seems very odd to me.. is the way it was
> designed'|||check the visibility property before set row color.
"thejez" <thejez@.discussions.microsoft.com> escreveu na mensagem
news:29D9F5ED-DB50-4C48-9BD0-C62F1005EB97@.microsoft.com...
> let me be more specific... i have a expression to change the row color...
> but
> this isn't working because the report is incrementing RowNumber on hidden
> rows... so the coloring seems random to the person reading the report. Is
> this normal' Is there a work around?
> "thejez" wrote:
>> Hello i have an expression build on RowNumber and i realized it counts
>> hidden
>> rows (visibility = false)... this seems very odd to me.. is the way it
>> was
>> designed'sql

RowNumber count within a group- please help!

I am new to reporting services, so forgive me if this is a simple question.
I have a simple report with 2 levels of grouping such as this:
Group 1
-Group 2
-item 1
-Item 2
I want to add a RowNumber to show the number (1, 2, 3, etc) before each item
within Group 2. I read the help and it said to use RowNumber(Scope) where
scope is the name of the grouping. However, any time I put the name of the
group in, I get an error. How do I refer to the group? I'm using this:
=RowNumber(First(Fields!Objective.Value, "DonorTrac_v3"))
RowNumber (Nothing) gives me the number for the outer group, but i want the
numbers in the inner group.
Any help you can provide is appreciated. Thanks!=RowNumber("Group 2")
Put the group name in double quotes!
Charles Kangai, MCDBA, MCT
"giggleraz" wrote:
> I am new to reporting services, so forgive me if this is a simple question.
> I have a simple report with 2 levels of grouping such as this:
> Group 1
> -Group 2
> -item 1
> -Item 2
> I want to add a RowNumber to show the number (1, 2, 3, etc) before each item
> within Group 2. I read the help and it said to use RowNumber(Scope) where
> scope is the name of the grouping. However, any time I put the name of the
> group in, I get an error. How do I refer to the group? I'm using this:
> =RowNumber(First(Fields!Objective.Value, "DonorTrac_v3"))
> RowNumber (Nothing) gives me the number for the outer group, but i want the
> numbers in the inner group.
> Any help you can provide is appreciated. Thanks!|||ah-hah! Thank you :)
"giggleraz" wrote:
> I am new to reporting services, so forgive me if this is a simple question.
> I have a simple report with 2 levels of grouping such as this:
> Group 1
> -Group 2
> -item 1
> -Item 2
> I want to add a RowNumber to show the number (1, 2, 3, etc) before each item
> within Group 2. I read the help and it said to use RowNumber(Scope) where
> scope is the name of the grouping. However, any time I put the name of the
> group in, I get an error. How do I refer to the group? I'm using this:
> =RowNumber(First(Fields!Objective.Value, "DonorTrac_v3"))
> RowNumber (Nothing) gives me the number for the outer group, but i want the
> numbers in the inner group.
> Any help you can provide is appreciated. Thanks!

RowNumber

I understand that if using this function with "nothing" between parentheses, then the running row total never resets.

However, I am hoping to have the row counter reset when a group value changes. I tried putting both the field and the defined group name in the parentheses both with and without quotes, but I get an error.

What is the correct syntax for accomplishing this?

This may help...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=903428&SiteID=1

cheers,

Andrew

rownum w/ NOT IN

Hi,

SQL> select fname, lname, rownum
2 from sample;

FNAME LNAME ROWNUM
---- ---- ----
John Smith 1
John Smith 2

I wish to delete one of the above rows. Could someone please tell me what is happening below

SQL> select *
2 from sample
3 where rownum IN
4 (select rownum
5 from sample);

FNAME LNAME
---- ----
John Smith
John Smith

SQL> select *
2 from sample
3 where rownum NOT IN
4 (select rownum
5 from sample);

FNAME LNAME
---- ----
John Smith
John SmithI can't explain what is happening with your queries exactly, but it is due to the fact that ROWNUM does not behave as you expect.

ROWNUM is a tricky beast, as it is assigned to the records as they are selected. For example, this will NEVER return a row:

select * from mytable where rownum > 1;

It gets the first row from mytable, assigns rownum=1, then checks the condition rownum > 1, which is false.
It gets the second row, assigns rownum = 1 again (since no previous row has been selected yet), then checks the condition rownum > 1, which is false.
etc. etc. etc.

When you have duplicate records, the only way to distinguish them is by the ROWID, which is a physical address:

SQL> select fname, lname, rowid
2 from sample;

FNAME LNAME ROWID
---- ---- ----
John Smith AAA6BDAAFAAABIPAAA
John Smith AAA6BDAAFAAABIPAAB

Now you can:

delete sample where rowid='AAA6BDAAFAAABIPAAB';|||I'm sorry could you please explain the logic here, how is the second row also assigned 1 which leads to the result at the end of n rows as being
1
2
.
n

It gets the first row from mytable, assigns rownum=1, then checks the condition rownum > 1, which is false.
It gets the second row, assigns rownum = 1 again (since no previous row has been selected yet), then checks the condition rownum > 1, which is false.

I appreciate your comment as my problem is solved, just would like to understand rownum.

Cheers.|||ROWNUM applies to the output of the selection process, not the input. So for example if you select any 5 records from a table, they will always have ROWNUM values from 1 to 5, in the order the records were found. If the query has an ORDER BY clause, this is applied after the ROWNUMs have been assigned, hence:

SQL> select dname, rownum from dept;

DNAME ROWNUM
----- ----
ACCOUNTING 1
RESEARCH 2
SALES 3
OPERATIONS 4

SQL> select dname, rownum from dept order by dname;

DNAME ROWNUM
----- ----
ACCOUNTING 1
OPERATIONS 4
RESEARCH 2
SALES 3

SQL> select dname, rownum from dept where dname='SALES';

DNAME ROWNUM
----- ----
SALES 1

SQL> select dname, rownum from dept where dname='ACCOUNTING';

DNAME ROWNUM
----- ----
ACCOUNTING 1

See? If you think of the query processor as a program it looks like this:

-- Select records
ROWNUM = 0
loop
Get next row
If row matches WHERE clause then
ROWNUM = ROWNUM+1
output(ROWNUM) = this row
end if
end loop

ROWNUM In Oracle

Is there any way to implement ROWNUM in Oracle to select row no.
Is there any way to select the row no when retrieving records as
1 Tv
2 Fridge
3 RadioHi,
There is no concept of Rownum in sql server.
But you could write ur own query to get the serial number.
Use the below script as sample:-
create table item(item_code varchar(05))
go
insert into item values('a1')
insert into item values('a2')
insert into item values('a3')
insert into item values('a4')
go
SELECT (SELECT COUNT(i.item_code)
FROM item i
WHERE i.item_code >= o.item_code ) AS RowID,
item_code
FROM item o
ORDER BY RowID
Thanks
Hari
SQL Server MVP
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:3BDAC82C-4AB5-4631-ABAE-AB693B0E1312@.microsoft.com...
> Is there any way to implement ROWNUM in Oracle to select row no.
> Is there any way to select the row no when retrieving records as
> 1 Tv
> 2 Fridge
> 3 Radio|||Hi
There is not a ROWNUM function in SQLServer 2000 using an identity column is
usually the alternative. If you want to rank your values then you could use
a
construct like:
e.g
SELECT ( SELECT COUNT(*) FROM MyTable T WHERE t.id <= M.id ) AS Rank,
col1, col2
FROM MyTable M
These links may also help.
http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
http://www.microsoft.com/resources/...r />
0761.mspx
http://www.microsoft.com/sql/evalua...pare/oracle.asp
John
"Renjith" wrote:

> Is there any way to implement ROWNUM in Oracle to select row no.
> Is there any way to select the row no when retrieving records as
> 1 Tv
> 2 Fridge
> 3 Radio|||Hi
If it is a big table then the count(*) as inner query will create
performance problem '
"John Bell" wrote:
> Hi
> There is not a ROWNUM function in SQLServer 2000 using an identity column
is
> usually the alternative. If you want to rank your values then you could us
e a
> construct like:
> e.g
> SELECT ( SELECT COUNT(*) FROM MyTable T WHERE t.id <= M.id ) AS Rank,
> col1, col2
> FROM MyTable M
> These links may also help.
> http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
> http://www.microsoft.com/resources/.../>
/c0761.mspx
> http://www.microsoft.com/sql/evalua...pare/oracle.asp
> John
> "Renjith" wrote:
>|||Hi
It may, and indexing would reduce the problem.
You can also do something like:
CREATE TABLE MyTest ( id int not null identity(1,1), val char(1))
INSERT INTO MyTest ( val )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
DELETE FROM MyTest where val = 'C'
SELECT m.id, COUNT(*) as Rank, m.val
FROM MyTest m
JOIN MyTest r ON R.id <= M.id
GROUP BY m.id, M.val
ORDER BY 2
Another alternative would be do deligate the numbering to the client.
John
"Renjith" wrote:
> Hi
> If it is a big table then the count(*) as inner query will create
> performance problem '
> "John Bell" wrote:
>|||Hi
You may want to look at Itzik Ben-Gan's articles in the May 2005 SQL
Server Magazine.
http://www.windowsitpro.com/Article...5828/45828.html
http://www.windowsitpro.com/Article...2302/42302.html
http://www.windowsitpro.com/Article...2646/42646.html
John

ROWNUM function

Does SQL Server 2005 or SQL Express have the capability of the ROWNUM function found in Oracle (LIMIT in MySQL)?
please advice!
To select records from row #10 to row #20
Oracle:SELECT *FROM MyTableWHEREROWNUM>9ANDROWNUM<21
MySQL:SELECT *FROM MyTableLIMIT10,20
SQL Server:?
SELECT * FROM MyTable WHERE Row_Number() BETWEEN 10 and 20|||It is not working in SQL Express....why??|||

This one works:

SELECT

OrderID, OrderDate, RowNumberFROM(SELECT OrderID, OrderDate, ROW_NUMBER()OVER(orderby OrderID)as RowNumber

FROM

ORDERS)as tWHERE RowNumberBETWEEN 10 AND 15

Syntax in SQL Server 2005:

ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

sql

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.

rownum and sub rownum

I have a table with six records. 4 A records and 2 B records.
how do i count them by A and B. when I do a
select rownum, col from table;
i get:
1 A
2 A
3 A
4 A
5 B
6 B

How can I get the following result?
1 A 1
2 A 2
3 A 3
4 A 4
5 B 1
6 B 2

help PleaseWhat DBMS are you on? For Oracle there is the ROW_NUMBER function:

select row_number() over (order by col),
col,
row_number() over (partition by col order by 1)
from table;|||I am on Oracle. This worked. Thank you very much|||Hi, I am unable to understand what this criteria means:

ID = '"& request.querystring("oID") & "'

in the following statement:

select count(distinct hazmatclass) as hzcount
from manifestexp
where orderkey in
(
select orderkey
from manifestexp
where ID = '"& request.querystring("oID") & "'
)
group by hazmatclass;

* manfestexp is a view.
* ID is a column in that view.

Any hints please??|||That is (bad) ASP syntax. Someone is building a SQL statement as a character string in ASP, and concatenating into it an ID value from a field on a form.

I say bad syntax, because what they should be doing is using bind variables via a Prepared Statement.|||Thank you very much for your feedback.
I am not sure I understand the prepared statement part. How do I go about doing that.

Originally posted by andrewst
That is (bad) ASP syntax. Someone is building a SQL statement as a character string in ASP, and concatenating into it an ID value from a field on a form.

I say bad syntax, because what they should be doing is using bind variables via a Prepared Statement.

ROWNUM and ORDER BY

Am using Oracle 9i.

Just wanted to know in which order the db will execute my query if my query contains a 'WHERE ROWNUM < 1000' and an 'ORDER BY ...'.
The documentation says that the order of evauation depends upon the indexes used in the ORDER BY, but doesn't specify clearly in which order.

Please help.You can't use ROWNUM and ORDER BY in the same select because the pseudo column ROWNUM is affected before the sort.

So, you have to do :

Select ... FROM (SELECT ... FROM ... ORDER bY...) WHERE ROWNUM<1000

rownum alternate in MS-SQL

I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.
I want to get 100 rows onward to one particular data ... how can i ?This doesn't sound like a good idea... (using rownum)
Please enlighten me with the SQL you used in mySQL?
Also, what version of SQL Server are you using?|||SELECT TOP n

(Being deprecated for modification statements in SS 2008+)
SET ROWCOUNT = n

(SS 2005)
OVER () clause|||I want to get 100 rows from particular record and onward.

You need more than just TOP, don't you?
I don't like the use of rownum - I'm sure there's a better way to get what the OP wants!|||You need more than just TOP, don't you?yes, a WHERE condition, too

oh, and an ORDER BY clause, without which TOP is meaningless

:)|||I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.

I want to get 100 rows onward to one particular data ... how can i ?

ummmmmmmmmmmmmm

what particular row|||what particular rowthe one specified by the WHERE condition|||ummmmmmmmmmmmmm

what particular rowThat row, right there near the middle of my screen.

-PatP|||I'm feeling better now|||"Standard" for SQL 2005:

with cte
as
(select row_number () over (order by name) as num, object_id, name
from master.sys.tables)

select *
from cte
where num >= 4

Rownum

Hi folks,
SELECT * FROM mytable
100 rows returned.
Can i get a rownum column for each record; i.e. if 100 records returned; rownum order 1,2,3....100 along with the each record position.
is it possible without using cursor?
Howdy!see http://www.dbforums.com/t1058224.html|||Assuming that you have atleast one primary key or at least a unique constraint:

Select Count(RowTable.UniqueField) as RowNumber, Mytable.Fields
from Mytable
inner join Mytable RowTable on Mytable.UniqueField >= RowTable.UniqueField

Even I had the Same problem. Thanks To Blindman for his help regarding the query.|||Thanx to r937 and blindman! :)|||Assuming that you have atleast one primary key or at least a unique constraint
Also: NULL values won't be counted. In the other thread the values were used as columnnames, so NULL is quite unlikely. Not sure about myTable.sql

Rownum

I am trying to do a
select count(*) on tablename
where rownum>40000
There are 70000 rows in the table, but my results are 0. How do I get the count above the 40000?
Thanks.row num is alloted to the rows fetched from the query.
if u put the rownum > conditon, no rows will be fetched because it will not satisfy the condition while ROWS are in teh table.

I hope i am clear|||How can I return the rows above 40000? I need to put 70000 rows of data on Excel, but an Excel table holds only 67000 rows.
Thanks.|||Here is a solution,

select *
from table
minus
select *
from table
where rownum < x|||Thank you very much.

RowModCtr Not Updating ?

I would like to use the RowModCtr to evaluate how quickly several indexes ar
e
changing on a table but it seems I'm only witnessing an increment on a
clustered index. Suppose I have this:
CustInfoTable
CustNum
SSN
FName
LName
... with indexes like ...
clustered (PK) on custnum
non-clustered on ssn
non-clustered on fname, lname
Let's pretend I UPDATE ssn, I see the rowmodctr in sysindexes incremented
against the custnum (pk), not against the index on ssn. How does SQL Server
figure the number of rowmods against the index SSN ? Again, if I were to
UPDATE one row against fname, lname I see the reference in sysindexes
incrementing against the clustered (pk).
AutoUpdate Stats is off.
Can anyone shine light on this behavior? I am ultimately am trying to
figure out how quickly fragmentation is occuring on each index of a table so
that I can pin-point which to DBREINDEX during my maintenance window rather
than just blindly doing all.
Thanx.
-CqlboyI am pretty sure that the only one that counts in SQL2000 is the one on the
clustered index or heap. In 2005 each column is tracked individually and
thus a lot more accurate and useful overall.
Andrew J. Kelly SQL MVP
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:97050225-F44A-4DC3-A11B-0C424D3F1099@.microsoft.com...
>I would like to use the RowModCtr to evaluate how quickly several indexes
>are
> changing on a table but it seems I'm only witnessing an increment on a
> clustered index. Suppose I have this:
> CustInfoTable
> CustNum
> SSN
> FName
> LName
> ... with indexes like ...
> clustered (PK) on custnum
> non-clustered on ssn
> non-clustered on fname, lname
> Let's pretend I UPDATE ssn, I see the rowmodctr in sysindexes incremented
> against the custnum (pk), not against the index on ssn. How does SQL
> Server
> figure the number of rowmods against the index SSN ? Again, if I were to
> UPDATE one row against fname, lname I see the reference in sysindexes
> incrementing against the clustered (pk).
> AutoUpdate Stats is off.
> Can anyone shine light on this behavior? I am ultimately am trying to
> figure out how quickly fragmentation is occuring on each index of a table
> so
> that I can pin-point which to DBREINDEX during my maintenance window
> rather
> than just blindly doing all.
> Thanx.
> -Cqlboy|||Thanx. Yes, it seems that SQL Svr 2000 only increments the clustered or hea
p
and the rowmodctr value for other indexes is not maintained at all. Bummer.
-Cqlboy
"Andrew J. Kelly" wrote:

> I am pretty sure that the only one that counts in SQL2000 is the one on th
e
> clustered index or heap. In 2005 each column is tracked individually and
> thus a lot more accurate and useful overall.
> --
> Andrew J. Kelly SQL MVP
>
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:97050225-F44A-4DC3-A11B-0C424D3F1099@.microsoft.com...
>
>

rowmodctr & SMO.

From MSDN pg for sp_updatestats:

http://msdn2.microsoft.com/en-us/library/ms173804.aspx

'...In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items...'

I was hoping that somewhere in the SMO namespace I would find a way to programmatically obtain rowmodctr info., (in order to determine when stats need updating), but I failed to find any method or property within the stats-related classes through which I could do this. If I've missed something, or there is another way to programmatically obtain the same, then any suggestions/comments would be gratefully received.

Many thanks,

Tamim.

Tamim, because that information is available in the system view your best bet is to use ADO.Net and issue the appropriate queries against the system views to get the details you need.|||Thanks Allen, that's exactly what I've done.

rowmodctr

why rowmodctr in sysindexes table shows negative values instead of zero after
a index rebuild in sql 2000 ?
Thanks,
Ranga
Ranga,
The value of [rowmodctr] is increased just for index ID 0 or 1. For the
rest of indexes and statistics, it shows a relative value that has to be
added to the [rowmodctr] of the index 0 or 1 to get the true number of
changed rows for this index.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
AMB
"Ranga" wrote:

> why rowmodctr in sysindexes table shows negative values instead of zero after
> a index rebuild in sql 2000 ?
> Thanks,
> Ranga
|||Thanks...
I have two tables each has several non clustered indexes...for one of them
I see negative values in the rowmodctr, for the other table i see zero for
rowmodctr...though it is not causing any problems, just curious to know what
is behind this.
I did reindex first, and the value got set to zero, a nightly update
statistics job changed the zero value to a negative number ? Is this what
happenned ?
Ranga
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Ranga,
> The value of [rowmodctr] is increased just for index ID 0 or 1. For the
> rest of indexes and statistics, it shows a relative value that has to be
> added to the [rowmodctr] of the index 0 or 1 to get the true number of
> changed rows for this index.
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
> http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
>
> AMB
> "Ranga" wrote:

rowmodctr

why rowmodctr in sysindexes table shows negative values instead of zero afte
r
a index rebuild in sql 2000 ?
Thanks,
RangaRanga,
The value of [rowmodctr] is increased just for index ID 0 or 1. For the
rest of indexes and statistics, it shows a relative value that has to be
added to the [rowmodctr] of the index 0 or 1 to get the true number of
changed rows for this index.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
AMB
"Ranga" wrote:

> why rowmodctr in sysindexes table shows negative values instead of zero af
ter
> a index rebuild in sql 2000 ?
> Thanks,
> Ranga|||Thanks...
I have two tables each has several non clustered indexes...for one of them
I see negative values in the rowmodctr, for the other table i see zero for
rowmodctr...though it is not causing any problems, just curious to know wha
t
is behind this.
I did reindex first, and the value got set to zero, a nightly update
statistics job changed the zero value to a negative number ? Is this what
happenned ?
Ranga
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Ranga,
> The value of [rowmodctr] is increased just for index ID 0 or 1. For t
he
> rest of indexes and statistics, it shows a relative value that has to be
> added to the [rowmodctr] of the index 0 or 1 to get the true number of
> changed rows for this index.
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
> http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
>
> AMB
> "Ranga" wrote:
>sql

rowmodctr

why rowmodctr in sysindexes table shows negative values instead of zero after
a index rebuild in sql 2000 ?
Thanks,
RangaRanga,
The value of [rowmodctr] is increased just for index ID 0 or 1. For the
rest of indexes and statistics, it shows a relative value that has to be
added to the [rowmodctr] of the index 0 or 1 to get the true number of
changed rows for this index.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
AMB
"Ranga" wrote:
> why rowmodctr in sysindexes table shows negative values instead of zero after
> a index rebuild in sql 2000 ?
> Thanks,
> Ranga|||Thanks...
I have two tables each has several non clustered indexes...for one of them
I see negative values in the rowmodctr, for the other table i see zero for
rowmodctr...though it is not causing any problems, just curious to know what
is behind this.
I did reindex first, and the value got set to zero, a nightly update
statistics job changed the zero value to a negative number ? Is this what
happenned ?
Ranga
"Alejandro Mesa" wrote:
> Ranga,
> The value of [rowmodctr] is increased just for index ID 0 or 1. For the
> rest of indexes and statistics, it shows a relative value that has to be
> added to the [rowmodctr] of the index 0 or 1 to get the true number of
> changed rows for this index.
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
> http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
>
> AMB
> "Ranga" wrote:
> > why rowmodctr in sysindexes table shows negative values instead of zero after
> > a index rebuild in sql 2000 ?
> >
> > Thanks,
> > Ranga

row-locking question

i'm considering implementing my own artificial key table that would
work similarly to an identity value. insert transactions would first
have to visit this artificial key table, lock the artificial key row
defining the key needed by the data row to be inserted. after the table
lock, it would get the value, insert the record, increment the value,
and update the artificial key table.
the reason i am doing this in a user-defined fashion instead of using
an identity column are varied. first, sometimes the sequence needs to
be semi-random. also, i want to avoid all the issues with migrating
identity columns.
so, i have two questions:
1) assuming that the code and locking are written the the appropriate
levels of granularity, integrity, and speed, is this a reasonable thing
to attempt?
2) all the books i am reading say "trust SQL Server locking management"
-- but i'm guessing they didn't have this kind of use in mind. is this
a circumstance where manual transactional locking is appropriate?
thanks for any suggestions / advice,
jasonI've given this a lot of thought, because IDENTITY doesn't provide a
database-wide unique value and the size of ROWGUIDs hinder performance. I
had considered the possibility of two user-defined functions, one scalar
function that returns a single surrogate value, and one table-valued
function that returns a specified number of values. The main problem with
this scheme is the issue of locking. Either you can obtain the surrogates
prior to starting a transaction, or you have to find another way to deal
with the locks and blocking, or you set up separate identity ranges for each
table. What is needed is a way to serialize access to a "next key" table
outside of the calling transaction. I've thought about writing an extended
stored proceedure or using the sp_OA procs to do this because they can open
or use a separate shared connection, but I haven't had time to pursue the
issue futher, nor am I convinced that the performance will be satisfactory.
Extended procedures are deprecated in SQL Server 2005 because it hosts the
CLR, so anything I write now will probably have to be rewritten later.
"jason" <iaesun@.yahoo.com> wrote in message
news:1122929530.893879.301170@.g44g2000cwa.googlegroups.com...
> i'm considering implementing my own artificial key table that would
> work similarly to an identity value. insert transactions would first
> have to visit this artificial key table, lock the artificial key row
> defining the key needed by the data row to be inserted. after the table
> lock, it would get the value, insert the record, increment the value,
> and update the artificial key table.
> the reason i am doing this in a user-defined fashion instead of using
> an identity column are varied. first, sometimes the sequence needs to
> be semi-random. also, i want to avoid all the issues with migrating
> identity columns.
> so, i have two questions:
> 1) assuming that the code and locking are written the the appropriate
> levels of granularity, integrity, and speed, is this a reasonable thing
> to attempt?
> 2) all the books i am reading say "trust SQL Server locking management"
> -- but i'm guessing they didn't have this kind of use in mind. is this
> a circumstance where manual transactional locking is appropriate?
> thanks for any suggestions / advice,
> jason
>|||jason wrote:
> i'm considering implementing my own artificial key table that would
> work similarly to an identity value. insert transactions would first
> have to visit this artificial key table, lock the artificial key row
> defining the key needed by the data row to be inserted. after the
> table lock, it would get the value, insert the record, increment the
> value, and update the artificial key table.
> the reason i am doing this in a user-defined fashion instead of using
> an identity column are varied. first, sometimes the sequence needs to
> be semi-random. also, i want to avoid all the issues with migrating
> identity columns.
> so, i have two questions:
> 1) assuming that the code and locking are written the the appropriate
> levels of granularity, integrity, and speed, is this a reasonable
> thing to attempt?
> 2) all the books i am reading say "trust SQL Server locking
> management" -- but i'm guessing they didn't have this kind of use in
> mind. is this a circumstance where manual transactional locking is
> appropriate?
> thanks for any suggestions / advice,
> jason
You can do this, but try and keep the key value generation routine in a
separate transaction to prevent tying it to the new inserts themselves.
For example:
Begin Tran
Update dbo.keytest
Set NextKey = NextKey + 1
Select @.key = NextKey From dbo.keytest
Commit Tran
Insert Into dbo.MyTable (newkey) values (@.key)
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Actually you can save a few steps by doing this below. Since the Update is
ATOMIC by itself you don't need a BEGIN TRAN - COMMIT and the extra select.
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u3zEmPvlFHA.3256@.tk2msftngp13.phx.gbl...
> jason wrote:
> You can do this, but try and keep the key value generation routine in a
> separate transaction to prevent tying it to the new inserts themselves.
> For example:
> Begin Tran
> Update dbo.keytest
> Set NextKey = NextKey + 1
> Select @.key = NextKey From dbo.keytest
> Commit Tran
> Insert Into dbo.MyTable (newkey) values (@.key)
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||This kind of thing is best done at the raw engine level. if you really
need it, switch products to something with row-level locking. Have you
looked at Firebird, Interbase and other optimistic concurrency control
databases?|||seems reasonable. decreases the lock time, and only costs me an ID
value if the insert fails for some reason. thanks.|||seems like a good implementation, thanks!|||no, i haven't looked at any alternative database products. this
database is thoroughly intwined with a growing .NET middleware, and i
think the company is looking quite enthusiastically at Sql Server 2005,
with the .NET platform built in.
Sql Server does claim to have row-level locking. they just say it is
good to trust the lock manager for most tasks. is Sql Server manual
row-level locking inadequate in some way?
thanks,
jason|||Hi
SQL Server 2000 and 2005 both do full row level locking in their default
configurations.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jason" <iaesun@.yahoo.com> wrote in message
news:1122989683.046590.261270@.f14g2000cwb.googlegroups.com...
> no, i haven't looked at any alternative database products. this
> database is thoroughly intwined with a growing .NET middleware, and i
> think the company is looking quite enthusiastically at Sql Server 2005,
> with the .NET platform built in.
> Sql Server does claim to have row-level locking. they just say it is
> good to trust the lock manager for most tasks. is Sql Server manual
> row-level locking inadequate in some way?
> thanks,
> jason
>|||by the description of the task at hand, does this sound like something
i would have to employ manual locking to accomplish? or would beginning
a transaction with an update, and then commiting the transaction when
"safe" do all the locking i need?

ROWLOCK usage

hi. i don't have much experience with locking using lock hints so wondered i
f
someone could help me with usage of ROWLOCK. i am writing a number of procs
which will perform validation on data prior to performing updates. i need
read consistency for the duration of these procs whilst guaranteeing maximum
concurrency. therefore i intend to use the following pattern:
create procedure MyProcedure
@.MyParam int
as
-- wrap in transaction
begin transaction
-- obtain lock
select 1
from MyTable (HOLDLOCK, UPDLOCK)
where MyPKColumn = @.MyParam
-- other operations here involving SELECTs on MyTable
-- perform update
update MyTable
set MyOthercolumn = 'NewValue'
where MyPKColumn = @.MyParam
-- release all locks
commit transaction
what i don't like about this is that i am beginning my transaction earlier
than i would prefer, but otherwise this appears to meet my requirements. is
this a good strategy, or is there a better way? what issues might i face?
many thanks
kh> what i don't like about this is that i am beginning my transaction earlier
> than i would prefer, but otherwise this appears to meet my requirements.
> is
> this a good strategy, or is there a better way? what issues might i face?
You are probably also locking pages (or perhaps even the entire table). IF
you take this path you might want to look into specifying ROWLOCK so that
you only lock the particular row that you are working on.
It seems like you are trying to reinvent the wheel.
You don't account for the situation where the data has changed since the
user initially retrieved the data.
User A retrieves the data for PKcol = 1
User B retrieves the data for PKcol = 1
User A goes to lunch
User B starts updating the data (within the application GUI)
Userr B hits "save" and writes the data to the database
User A comes back from lunch, finishes updating the data within the GUI, and
clicks save.
The data that User B entered is overwritten by User A.
One way around this problem is to pass the old and new values to the stored
procedure. The WHERE clause would use the primary key and it would compare
the @.old params to the data that is in the table. If @.@.rowcount = 0 the
data was different and the update did not happen.
Now that I have you worried about that type of concurrency issue, lets get
back to your validation question.
Can't you validate data within the GUI?
If not you should perform data validation (I assume that this is the "--
other operations here involving SELECTs on MyTable" outside of a
transaction. Heck, I don't even see why you need a transaction in this
stored procedure. It does not seem to buy you anything.
I don't know what type of data validation you need to do. Lets say that you
cannot have multiple UserNames or FileNames within a table. You could do
something like this before update statement. The RETURN will cause the
stored procedure to end. It will also pass back (via the return code) the
value within the parens.
--validation check
IF EXISTS (SELECT * FROM dbo.MyTable WHERE UserName = @.MyUserName )
BEGIN
RETURN (1)
END
--validation check
IF EXISTS (SELECT * FROM dbo.MyTable WHERE FileName= @.MyFileName )
BEGIN
RETURN (2)
END
--everything is a-ok, lets update
UPDATE dbo.MyTable SET MyOthercolumn = 'NewValue'
WHERE MyPKColumn = @.MyParam
RETURN (0)
Keith Kratochvil
"kh" <kh@.newsgroups.nospam> wrote in message
news:FBDE5CBC-4CB8-4CE2-8928-13BB3624F0C4@.microsoft.com...
> hi. i don't have much experience with locking using lock hints so wondered
> if
> someone could help me with usage of ROWLOCK. i am writing a number of
> procs
> which will perform validation on data prior to performing updates. i need
> read consistency for the duration of these procs whilst guaranteeing
> maximum
> concurrency. therefore i intend to use the following pattern:
> create procedure MyProcedure
> @.MyParam int
> as
> -- wrap in transaction
> begin transaction
> -- obtain lock
> select 1
> from MyTable (HOLDLOCK, UPDLOCK)
> where MyPKColumn = @.MyParam
> -- other operations here involving SELECTs on MyTable
> -- perform update
> update MyTable
> set MyOthercolumn = 'NewValue'
> where MyPKColumn = @.MyParam
> -- release all locks
> commit transaction
> what i don't like about this is that i am beginning my transaction earlier
> than i would prefer, but otherwise this appears to meet my requirements.
> is
> this a good strategy, or is there a better way? what issues might i face?
> many thanks
> kh
>|||keith. many thanks. some notes for clarity:

> You are probably also locking pages (or perhaps even the entire table). I
F
> you take this path you might want to look into specifying ROWLOCK so that
> you only lock the particular row that you are working on.
sorry, copy and paste error: the lock hints should of course be (HOLDLOCK,
ROWLOCK) and since I am selecting using the Primary Key I am (hopefully) onl
y
locking a single record.

> You don't account for the situation where the data has changed since the
> user initially retrieved the data. <snip>
there is no user access to the database accept via our app server. users can
go for lunch as often as they like, data will never be left in an uncommitte
d
state other than during stored procedure execution. the usage of ROWLOCK
hopefully avoids the situation you describe since it is only used during wel
l
defined units of execution.

> One way around this problem is to pass the old and new values to the store
d
> procedure. The WHERE clause would use the primary key and it would compar
e
> the @.old params to the data that is in the table. If @.@.rowcount = 0 the
> data was different and the update did not happen.
i am intentionally taking a 'pessimistic concurrency' approach here

> Can't you validate data within the GUI? <snip>
the validation involves selects and inserts into other tables (auditing,
etc) and relates to the requirements of downstream applications rather than
business rules within our own application. it is therefore not appropriate t
o
perform this validation within our UI or app server.

> Heck, I don't even see why you need a transaction in this
> stored procedure. It does not seem to buy you anything.
the only reason that the validation takes place within a transaction is so
that the ROWLOCK is held and i can guarantee that the data has not changed
between the beginning of the validation and the ultimate commit of this data
to the database.
kh|||> users can go for lunch as often as they like
That sounds great. I would like to take 3 or 4 lunches per day!

> the only reason that the validation takes place within a transaction is so
> that the ROWLOCK is held and i can guarantee that the data has not changed
> between the beginning of the validation and the ultimate commit of this
> data
> to the database.
That sounds reasonable. You know your system better than any of us. I
guess you are taking the correct approach.
Keith Kratochvil|||cheers keith. so in summary:
- i know my app better than you
- you know sql server better than me
- my users will shortly need a strict exercise regime
kh