Sql Server 2008 "Invalid usage of the option NEXT in the FETCH statement."

[AdhamAwadhi]

Hi,
I'm using SQL Server 2008 and querying using this

cn.FindAsync<SmsMessage>(statement => statement
                                      .Where($"{nameof(SmsMessage.State):C} = 0 AND LEN({nameof(SmsMessage.Mobile):C}) BETWEEN @PhoneLengthMin AND @PhoneLengthMax AND {nameof(SmsMessage.EnterTime):C} >= @messageTime")
                                      .OrderBy($"{nameof(SmsMessage.Id):C}")
                                      .Skip((page - 1) * pageSize)
                                      .Top(pageSize)
                                      .WithParameters(new { PhoneLengthMin = _phoneLengthMin, PhoneLengthMax = _phoneLengthMax, messageTime = messageTime }))

But I got an error

"Invalid usage of the option NEXT in the FETCH statement."

[MoonStorm]

SQL Server 2008 might need a separate dialect. I don't have that version installed so I can't test it. What is the generated SQL?

[AdhamAwadhi]

I searched for this issue and I found Sql Server 2008 does not support FETCH.
This the generated SQL:

exec sp_executesql N'SELECT [Id],[MessageText] AS [Text],[Receiver] AS [Mobile],[State],[ReturnData] AS [Result],[EnterTime] FROM [tblSMS_Messages] WHERE [State] = 0 AND LEN([Receiver]) BETWEEN @PhoneLengthMin AND @PhoneLengthMax AND [EnterTime] >= @messageTime ORDER BY [Id] OFFSET 0 ROWS FETCH NEXT 32 ROWS ONLY',N'@PhoneLengthMin tinyint,@PhoneLengthMax tinyint,@messageTime datetime',@PhoneLengthMin=9,@PhoneLengthMax=12,@messageTime='2017-05-12 15:15:59.500'

I worked around it using this :

var sqlBuilder = OrmConfiguration.GetSqlBuilder<SmsMessage>();
           var query =  sqlBuilder.Format(
          $@"
SELECT {sqlBuilder.ConstructColumnEnumerationForSelect()}
FROM(SELECT    ROW_NUMBER() OVER(ORDER BY Id) AS RowNum, *
         FROM      {sqlBuilder.GetTableName()}
         WHERE		{nameof(SmsMessage.State):C} = 0 AND LEN({nameof(SmsMessage.Mobile):C}) BETWEEN @PhoneLengthMin AND @PhoneLengthMax AND {nameof(SmsMessage.EnterTime):C} >= @messageTime
       ) AS result
WHERE RowNum > @skippedItems
   AND RowNum <= @skippedItems + @pageSize
ORDER BY RowNum");
cn.QueryAsync<SmsMessage>(query,
                           new
                           {
                               PhoneLengthMin = _phoneLengthMin,
                               PhoneLengthMax = _phoneLengthMax,
                               messageTime = messageTime,
                               skippedItems = (page - 1) * pageSize,
                               pageSize = pageSize
                           });

[vantian]

I got same issue, I dont think it's because Sql Server 2008.
I tried both in Sql Server 2012 and Sql Azure and still got this exception

Message: System.Data.SqlClient.SqlException : Incorrect syntax near '1'.
Invalid usage of the option NEXT in the FETCH statement.

here's my code
Connection.FindAsync<TEntity>(statement => statement.Skip(0).Top(5));

I'm using Dotnetcore 1.1

EDIT

Nevermind, Looks like I have to add .OrderBy()
the code supposed to be like this
Connection.FindAsync<TEntity>(statement => statement.OrderBy($"ID ASC").Skip(0).Top(5));
it will generate the SQL Query to this
SELECT * FROM Member ORDER BY Id ASC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY

so if you want a pagination, those three functions are mandatory OrderBy() Skip() Top().

[dotnetchris]

I encountered this error when running against an old SQL 2008 R2 server. I installed SQL Express 17 on my box and the error went away.

[devseal]

If you encounter this problem with Azure SQL, just change the Compatibility Level of your database to 130 or higher. You can do this via script or SQL Server Management Studio by looking at the database properties->options.

[MoonStorm]

Currently looking at this but honestly we don't have the resources to maintain the tests for this old dinosaur.