'row_number' is not a recognized function name. Incorrect syntax near the keyword 'AS'.

Question

I've created a simple project with an SQL Server database with dozens of tables and plenty of indices but nothing really complicated. No triggers, no stored procedures, no additional "database magic". The code is written in C#, using the Entity model and the Dynamic Data Site to just set up the basics real fast, so some typing monkeys could be put to work and do some basic data entries while I will modify the project to become more mature.

Tested it on SQL Server 2005 and all worked fine. So I've made a setup through Visual Studio and sent it over to the typing Monkeys and their administrator. All they had to do was: 1) Create a new database. 2) Execute the Create script for the database. 3) Install the setup I gave them. 4) Modify the connection string, which happens to be placed in a special config file for their convenience. 5) Use the web interface and notify me if something goes wrong.

And something went wrong. This complete error: *Line 1: Incorrect syntax near '('. 'row_number' is not a recognized function name. Incorrect syntax near the keyword 'AS'.*

I don't use 'row_number' in my code. I just use Linq for the queries. Besides, because of the entity model I don't even have to worry much about doing any SQL stuff. (even though I'm good at it.)

My first guess is that they're using a faulty connection string. They might be installing this application on SQL Server (which should still work) but they didn't change the connection string completely and now my project thinks it's using SQL Server 2005. (Or whatever.) Am I right or is this caused by some other nasty bug?

Full error:

[SqlException (0x80131904): Line 1: Incorrect syntax near '('. 'row_number' is not a recognized function name. Incorrect syntax near the keyword 'AS'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33 System.Data.SqlClient.SqlDataReader.get_MetaData() +83 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +387

[EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +423
System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute(ObjectContext context, ObjectParameterCollection parameterValues) +743
System.Data.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) +157
System.Data.Objects.ObjectQuery`1.GetIListSourceListInternal() +13 System.Data.Objects.ObjectQuery.System.ComponentModel.IListSource.GetList() +7 System.Web.UI.WebControls.EntityDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments, Creator qbConstructor) +1168 System.Web.UI.WebControls.EntityDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +102
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +19 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +142 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +73 System.Web.UI.WebControls.GridView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +72 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +44 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842

---

This application was previously build on an SQL Server 2000 system and then it also worked just fine. However, the test database was upgraded to 2005 while the production database still uses 2000. I would expect that this should cause no problems, but am I correct in my assumption?

Answer 1

ROW_NUMBER() is used to perform things like Skip/Take, at least on SQL Server 2005/2008. If you are targetting an earlier version of SQL Server it could get confused.

LINQ-to-SQL makes a reasonable attempt to support SQL 2000 (although not everything is supported) - I honestly don't know whether EF does, though.

Answer 2

Looking at the exception, it seems like connection is pointing to a SQL Server database which is not SQL 2005.

ROW_NUMBER is supported in SQL 2005.

Answer 3

Well, I did a simple test. I've connected my project with an SQL Server 2000 database, installed the database and ran the SQL scripts. Then -without recompiling- I used my site to connect to this database and it failed. It was the same error.

Then the next part of the test: I refreshed the entity models in my project and recompiled the whole project. Still connected with SQL Server 2000, I started the site again and there it was, my beautiful site. :-)

As it turns out, the Entity model (and LINQ-TO-SQL) will detect which database you use when you compile your project. If you use 2005, the final code gets optimized for 2005 and you won't be able to install the whole thing on SQL Server 2000.

So, annoying as it is, I will have to continue to develop on an SQL Server 2000 machine. (And kick someone's arse because he was supposed to test my setup on a system similar to the end user!)

Answer 4

I know this is an old thread, but I think it still has some importance.

I ran into the same problem. I was developing on a sql2008 server and deploying to a sql2000 server. Instead of developing on your 2000 machine, open up your .edmx file in wordpad and change the manifest token to "2000". Then rebuild and publish. Much easier than developing on a different machine.