I'm using Subsonic 3 and am just starting the process of putting my application into production. Locally, I develop using SQL Server 2008 but the production environment is using SQL Server 2000. I am getting the following error for paging:
'ROW_NUMBER' is not a recognized function name.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: 'ROW_NUMBER' is not a recognized function name.
I am looking at the Sql2005Generator:
private const string PAGING_SQL =
@"
SELECT *
FROM (SELECT ROW_NUMBER() OVER ({1}) AS Row,
{0}
{2}
{3}
{4}
)
AS PagedResults
WHERE Row >= {5} AND Row <= {6}";
I'm also getting a syntax error on a .First() LINQ operator:
account.Users.First().UserName
The generated query looks like the following:
SELECT TOP (1) [t0].[UserName] FROM [Users] AS t0
SQL Server 2000 does not like the parenthesis in the TOP portion of the query. The following works in SQL 2000:
SELECT TOP 1 [t0].[UserName] FROM [Users] AS t0