Row_Number() fails to return anything

I am using SQLExpress and want to do some custom paging with a grid but can not get the sproc to produce any results. I don't get any error just a resultset of 0 rows whenever I include the row_number function.

Sample Table:
refID int identity increments by 1
refSubject nvarchar(100)
refBody nText

Sample sproc that works
create proc sp_RefListing as
select refid
, refsubject
from myTable

Sample Sproc that doesn't work:
with OrderedRefList as
(Select refid
,refsubject
,row_number() OVER (order by refsubject) as rownum
from myTable)
select refid
,refsubject
,rownum
from OrderedRefList
where rownum < 10

When I execute I get no errors or warnings during save, but I get no data.

Is there something I am doing wrong, is there a setting in SQLExpress I need to change to allow row_number?

Thanks in advance for your assistance,

Al

Did you ever get an answer to this question? I am having the same issue. The SPROC doesn't return anything when executed within Visual Studio 2005 Pro, but will return fine if the same query is put into a view, or if the SPROC is called from MS Access or SQL Management Studio. Appears to be a problem with Visual Studio 2005.sql

Row_Number() fails to return anything

I am using SQLExpress and want to do some custom paging with a grid but can not get the sproc to produce any results. I don't get any error just a resultset of 0 rows whenever I include the row_number function.

Sample Table:
refID int identity increments by 1
refSubject nvarchar(100)
refBody nText

Sample sproc that works
create proc sp_RefListing as
select refid
, refsubject
from myTable

Sample Sproc that doesn't work:
with OrderedRefList as
(Select refid
,refsubject
,row_number() OVER (order by refsubject) as rownum
from myTable)
select refid
,refsubject
,rownum
from OrderedRefList
where rownum < 10

When I execute I get no errors or warnings during save, but I get no data.

Is there something I am doing wrong, is there a setting in SQLExpress I need to change to allow row_number?

Thanks in advance for your assistance,

Al

Did you ever get an answer to this question? I am having the same issue. The SPROC doesn't return anything when executed within Visual Studio 2005 Pro, but will return fine if the same query is put into a view, or if the SPROC is called from MS Access or SQL Management Studio. Appears to be a problem with Visual Studio 2005.

'ROW_NUMBER' is not a recognized function name.

Hi experts,
recently i download the SQLExpress 2005 version to do some testing,
when i try to run a new feature which all ROW_NUMBER, but SQL give me
error
'ROW_NUMBER' is not a recognized function name.
i wondering after install the 05, we need to add-on some package to
support this kind of feature,. which is not default '
* the sample is from
http://msdn.microsoft.com/sql/learn...tsqlenhance.asp
pls give advise, many thanksJust the routine check. I hope you are not connecting to a SQL Server 2000
instance from the SQL Srvr Management Studio express edition.|||Check your database compatibility level
exec sp_dbcmptlevel '<Name of db>'
HTH
Kalen Delaney, SQL Server MVP
"XJ" <ianyian@.hotmail.com> wrote in message
news:1148698630.294392.99660@.g10g2000cwb.googlegroups.com...
> Hi experts,
> recently i download the SQLExpress 2005 version to do some testing,
> when i try to run a new feature which all ROW_NUMBER, but SQL give me
> error
> 'ROW_NUMBER' is not a recognized function name.
> i wondering after install the 05, we need to add-on some package to
> support this kind of feature,. which is not default '
> * the sample is from
> http://msdn.microsoft.com/sql/learn...tsqlenhance.asp
> pls give advise, many thanks
>|||Hi Kalen,
i catch u, which give me 80, yes i agree that i have sql 2000
before, but i already uninstall it, rght now under the service name
call XJ\SQLEXPRESS.
and i have use "Microsoft SQL Server Management Studio Express",
but even i try to create a new DB, still give me 80,. pls give some
idea how can i create a DB which have 2005 feature under "studio
express" or any other way ,. .. many thanks|||Hi Kalen , Omnibuzz
Bcos the instance make me some confuse ,. finally i can
connect ! ya ,. many thanks !!!!