developed in 2005, need to go back to 2000

I've developed a web application and to be honest I've been a bit dumb and not checked with my hosts. I assumed they used SQL Server 2005. But no, they only use 2000.

I'm sure I'll come across more problems but the first I'm having is I've used the ROW_NUMBER() function in a few stored procedures, but in 2000 I'm getting this error message

'ROW_NUMBER' is not a recognized function name.

Here is the stored procedure for reference

SELECT RowNumFROM (SELECT id, category, ROW_NUMBER() OVER(ORDER BY id) as RowNum FROM custrecords ) as CustRecord

anyone know the SQL Server 2000 equivalent?

Regrettably ROW_NUMBER is a new feature with SQL2005.

What precisely are you trying to achive with your SELECT statement?

|||

there isn't a equivalent of row_number() in SQL Server 2000. Depending on your requirement, you can use a subquery something like

select count(*) from tbl where <condition>

to do it.

Maybe you can explain what are you trying to do here. Or alternatively do the row numbering in your ASP.NET

|||

khtan:

there isn't a equivalent of row_number() in SQL Server 2000. Depending on your requirement, you can use a subquery something like

select count(*) from tbl where <condition>?

to do it.

Maybe you can explain what are you trying to do here. Or alternatively do the row numbering in your ASP.NET
?

thanks khtan, I'll try using the Count keyword method instead|||

Provided you read the dataset first, you can also have output parameters on the stored procedure that gets your batch size. Have a look at http://www.4guysfromrolla.com/webtech/041206-1.shtml (Efficiently Paging Through Large Result Sets in SQL Server 2000)

|||

TATWORTH:

Provided you read the dataset first, you can also have output parameters on the stored procedure that gets your batch size. Have a look at http://www.4guysfromrolla.com/webtech/041206-1.shtml ( Efficiently Paging Through Large Result Sets in SQL Server 2000)

wow, thanks tatworth. I started on the tutorial link you supplied above and eventually found myself doing the tutorial (A more efficent method for paging through large result sets : link below)

http://www.4guysfromrolla.com/webtech/042606-1.shtml

But I've hit a problem. Like I say I've done the tut above and it works great...if you only want the results to be in order of id.

I'm trying to change the order by other fields (name, date of birth) but I'm not getting anywhere.

Any one any tips?

|||

Please always remember when using output parameters and a dataset, to read the dataset before the output parameters - there is a "bug"/"feature" that only populates the output parameters after the dataset has been read.

As to your sort problem, the solution pobably lies along using "date of birth and id" instead of just "date of birth" - please post an example of the revised TSQL together with a script to create the table. I will look at it and attempt a fix the problem; however it may not be until next weekend that I can look at it.

|||

TATWORTH:

Please always remember when using output parameters and a dataset, to read the dataset before the output parameters - there is a "bug"/"feature" that only populates the output parameters after the dataset has been read.

As to your sort problem, the solution pobably lies along using "date of birth and id" instead of just "date of birth" - please post an example of the revised TSQL together with a script to create the table. I will look at it and attempt a fix the problem; however it may not be until next weekend that I can look at it.

wow, thanks tatworth

This is the stored procedure I created from the tutorial above, like I say it works if the order by is id, anything else and it doesn't work

ALTER PROCEDURE uk_members
(
@.username VarChar(20),
@.startRowIndex int,
@.maximumRows int
)
AS
DECLARE @.first_id int

SET ROWCOUNT @.startRowIndex

SELECT @.first_id = id FROM uk_orders
WHERE (username=@.username) AND (itemstat <> 'DELETE') ORDER BY id

SET ROWCOUNT @.maximumRows

SELECT id, username, suppliername, product, quantity
FROM uk_orders WHERE
(id >= @.first_id) AND (username=@.username) AND (suppliername <> 'SMITHS') ORDER BY id

SET ROWCOUNT 0

I've been messing around with the code but not got anywhere.

I really appreciate your help and of course anyone else offering any help is also really appreciated.