U4-9190 - SQL Server 2012 - Page throws: Incorrect syntax near '@0'. Invalid usage of the option NEXT in the FETCH statement.

Created by Rasmus Eeg Møller 16 Nov 2016, 07:55:45 Updated by Rasmus Eeg Møller 11 Jan 2018, 19:23:40

Relates to: U4-9195

Subtask of: UAASSCRUM-721

What did you do? Created a custom poco table using umbraco persistence, added a few rows.

  [TableName(Constants.AssignedTableName),
    ExplicitColumns,
    PrimaryKey("Id", autoIncrement = true)]
  public class Assigned
  {
    [Column("Id")]
    [PrimaryKeyColumn(AutoIncrement = true)]
    public int Id { get; set; }

    [Column("UserId")]
    public int UserId { get; set; }

    [Column("NodeId")]
    public int NodeId { get; set; }
  }

The tried to fetch the the rows using

  public PagedResult<Assigned> GetPaged(long page, long itemsPerPage, params int[] userId)
  {
    var sql = new Sql()
      .Select("*")
      .From<Assigned>(dbContext.SqlSyntax);

    try
    {
      var pagedResult = dbContext.Database.Page<Assigned>(page, itemsPerPage, sql);

      var pagedModel = new PagedResult<Assigned>(pagedResult.TotalItems, pagedResult.CurrentPage, pagedResult.ItemsPerPage)
      {
        Items = pagedResult.Items
      };

      return pagedModel;
    }
    catch (System.Exception ex)
    {
      throw ex;
    }
  }

What actually happened?

  <Error>
    <Message>An error has occurred.</Message>
    <ExceptionMessage>
    Incorrect syntax near '@0'. Invalid usage of the option NEXT in the FETCH statement.
    </ExceptionMessage>
    <ExceptionType>System.Data.SqlClient.SqlException</ExceptionType>
    <StackTrace>
    at OurAssigned.Services.AssignedService.GetPaged(Int64 page, Int64 itemsPerPage, Int32[] userId) in C:\Repositories\git.datagraf.dk\Niras\OurUmbraco\OurAssigned\Services\AssignedService.cs:line 101 at OurAssigned.Controllers.AssignedController.GetPaged(Int32 page, Int32 itemsPerPage, Int32[] userIds) in C:\Repositories\git.datagraf.dk\Niras\OurUmbraco\OurAssigned\Controllers\AssignedController.cs:line 86 at lambda_method(Closure , Object , Object[] ) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()
    </StackTrace>
  </Error>

Found this on stackoverflow: http://stackoverflow.com/questions/19617714/incorrect-syntax-near-offset-modift-sql-comm-2012-to-2008 According to this: https://our.umbraco.org/documentation/getting-started/setup/install/install-umbraco-manually It should work on SQL Server 2008, unless support has been dropped?

2 Attachments

Comments

Sebastiaan Janssen 16 Nov 2016, 08:09:51

What's your Umbraco version please??


Rasmus Eeg Møller 16 Nov 2016, 09:51:26

@sebastiaan Woops sorry forgot that one.. it's 7.5.3


Sebastiaan Janssen 16 Nov 2016, 13:25:54

Strange, so we switch to this newer syntax when we detect you're on SQL 2012 (because it's more efficient) - on this line we switch to SQL CE syntax, which actually understands the OFFSET etc.: https://github.com/umbraco/Umbraco-CMS/blob/5397f2c53acbdeb0805e1fe39fda938f571d295a/src/Umbraco.Core/Persistence/UmbracoDatabase.cs#L181

This leads to the following method:

https://github.com/umbraco/Umbraco-CMS/blob/5397f2c53acbdeb0805e1fe39fda938f571d295a/src/Umbraco.Core/Persistence/PetaPoco.cs#L740

Which.. as I read it, in your case should go into if (databaseType == DBType.SqlServer || databaseType == DBType.Oracle) and then do the old-fashioned query.

I wonder if it's not detection your SQL version correctly.. hmm.


Stephan 16 Nov 2016, 17:15:21

As far as I can tell we should not be using the OFFSET ROWS...FETCH NEXT syntax with SqlServer 2008 but only with 2012 and later. Any chance you can report the output of the following query on your server:

SELECT SERVERPROPERTY('productversion')

?


Rasmus Eeg Møller 17 Nov 2016, 10:01:57

@zpqrtbnk @sebastiaan The following query SELECT SERVERPROPERTY('productversion') returned 11.0.2100.60


Rasmus Eeg Møller 17 Nov 2016, 10:07:02

Well that just awesome... it's not a sql 2008... but 2012 ^^


Stephan 17 Nov 2016, 10:59:48

version "11.0.2100.60" is 2012 indeed ;-) So at least it makes sense that we use the new syntax. So now, just to confirm, you are still seeing the error, and the server is 2012? Or is the server wrongly reporting it is 2012 but actually is 2008, which would be super weird?


Rasmus Eeg Møller 17 Nov 2016, 11:08:09

@zpqrtbnk Agreed that would be +super+ wierd! I wrongly reported it as SQL Express 2008, because we usually use that version on our staging environments. I could to to update from RTM to SP1 and see if that resolves the issue?


Stephan 17 Nov 2016, 16:17:51

so that would be 2012 SP1? yes, if you can update and report that'd be great

also... if you're not scared by a bit of reflection, just before doing dbContext.Database.Page<Assigned>(... you could try to paste the following code, which should report the exact SQL that is executed, in the Umbraco log - that would be helpful too.

var prop = dbContext.Database.GetType().GetProperty("EnableSqlTrace", BindingFlags.NonPublic | BindingFlags.Instance); prop.SetValue(dbContext.Database, true);


Stephan 29 Nov 2016, 11:15:20

@rasmuseeg any chance you have a minute to look into it?


Rasmus Eeg Møller 29 Nov 2016, 16:06:36

@zpqrtbnk Haven't got around to trying the paged function, but the database have been updated. Will report back soon.


Shannon Deminick 28 Dec 2016, 23:11:37

@rasmuseeg any chance you've been able to have a look at this?


Rasmus Eeg Møller 31 Dec 2016, 09:18:00

@Shandem Hi there, yes the issue seams to be resolved by updating SQL Server 2012 to the lastest version. Sorry it took to long, but wasn't sure that updating resolved the issue. The issue occurred because the tables was created by a custom umbraco MigrationBase. Restarting the server, not just the SQL server, resolved the issue as well. Updating to latest version, does not require a server restart after migrations.


Sebastiaan Janssen 02 Jan 2017, 16:36:45

Thanks @rasmuseeg - for now I'll close the issue. If it pops up again for someone else we can re-open it.


Tim Geyssens 25 Apr 2017, 09:36:07

Just ran into this, you need to have an order by otherwise paged won't work


Dennis Öhman 28 Aug 2017, 13:31:27

@timgeyssens Thank you! Worked for me

Upgraded a site from 7.3.8 to 7.6.5 and some paged queries on custom tables gave this error. Running Azure SQL

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statement. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteDbDataReader(CommandBehavior behavior) in c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:line 248 at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass4.<ExecuteReaderWithRetry>b__3() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, RetryPolicy retryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteReaderWithRetry(IDbCommand command) at Umbraco.Core.Persistence.Database.d__71.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at Umbraco.Core.Persistence.Database.Fetch[T](String sql, Object[] args) at Umbraco.Core.Persistence.Database.SkipTake[T](Int64 skip, Int64 take, String sql, Object[] args) at Umbraco.Core.Persistence.Database.SkipTake[T](Int64 skip, Int64 take, Sql sql)


Rasmus Eeg Møller 11 Jan 2018, 19:23:40

@timgeyssens thanks! That was the real issue, a year later.. :-)


Priority: Normal

Type: Bug

State: Closed

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted: Inline code

Affected versions: 7.5.3

Due in version:

Sprint:

Story Points:

Cycle: