'ROW_NUMBER' is not a recognized function name. - SQL sever 2005

I installed SQL Server 2005 standard edition.

Try to run a query with the new function - 'ROW_NUMBER' and got this error statement

'ROW_NUMBER' is not a recognized function name.

Any ideas? Thx
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Hi musclejack,

musclejackAuthor Commented:
That's what I did

SELECT Row, Name
FROM Product
) AS ProductsWithRowNumbers
WHERE Row >= 6 AND Row <= 10

Aneesh RetnakaranDatabase AdministratorCommented:
WITH ProductsWithRowNumbers AS
SELECT ROW_NUMBER() OVER (order by ProductID) AS Row, Name
FROM Product
FROM ProductsWithRowNumbers
WHERE Row between 5 and 10;
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

musclejackAuthor Commented:
I dont think it is syntax problem.  nevermind.
Aneesh RetnakaranDatabase AdministratorCommented:
I also think so, Do you have both sql server 2005 and 2000 installed on the same macine
musclejackAuthor Commented:
yes =)

yes, i have 2000 on it for a while and installed 2005 on top.  I googled search
never say I can't keep both in the same machine. what do you think? thx,
Aneesh RetnakaranDatabase AdministratorCommented:
i think you installed 2005 withoout specifying a 'different' instance name ...hope my second post is also giving syntax error
hi musclejack,
did you restore database of sql 2000 on sql 2005 in that case it wont use 2005 functionality to do that you have to
right click you database go to properties in options change the compatibility level of your database to sql server 90

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
musclejackAuthor Commented:
Thanks aneeshattingal and imran_fast
i have faced same problem before.

Now i have created new instnace name..

But not able to login now..

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.