Take Away Notes

I received this error while using ROW_NUMBER function for Paging in my project.

The root cause for the issue is

ROW_NUMBER function is not compatible with SQL 2000.

Though I was working on SQL 2005 but I was getting the same error.

After struggling a lot on the web, I realized that though I was using SQL 2005 interface, but it was actually accessing the SQL 2000 instance.

I came to know this by using below query

select @@version  ( which gaves me MS SQL 2000 )

To solve this issue, I tried one solution

I went in SQL 2005 Server,

right click on SQL Database >> Select Option >> Select Compatibility Level from the dropdown to SQL 2005.

Unfortunalty I was not getting SQL 2005 name in the dropdown.

My brother gave me sugession to reinstall SQL 2005 and give a new instance name while installing it.

NOTE : When u install SQL Server 2005, by default it select to DEFAULT INSTANCE. Instead of proceeding with the existing default instance, Choose NEW INSTANCE by entering new instance name. Proceed the installation.

One installation is done, I selected the instance name from the dropdown of Connect SQL Server Page and entered in SQL 2005.

Alas… the error got solved….

I was able to work on my project happily…

Thanks to my brother, he is well sound programmer…

Hope this helps u too…

To learn about ROW_NUMBER() click below links
http://blogs.conchango.com/jamiethomson/archive/2005/02/16/1025.aspx