Error: Incorrect syntax near the keyword 'SELECT'. Incorrect syntax near ')'.

Posted: November 08, 2018 at 6:07 AM Quote #214069
Can Anyone please help me identify what this error is and how to fix it. My log is creating hundreds of these and i think it is causing my website homepage to randomly crash and not load back. It shows nopcommerce error page instead. The only way to get homepage working after that is to manually enter into admin page via link and restart.

Im using NopCommerce Version:4.10
With Seven Spikes Motion Theme

Log level:  Error

Short message:  
Incorrect syntax near the keyword 'SELECT'.
Incorrect syntax near ')'.

Full message:  
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'SELECT'.
Incorrect syntax near ')'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Nop.Services.News.NewsService.GetAllNews(Int32 languageId, Int32 storeId, Int32 pageIndex, Int32 pageSize, Boolean showHidden)
   at Nop.Web.Factories.NewsModelFactory.<PrepareHomePageNewsItemsModel>b__16_0() in C:\andrei\nopcommerce\sources\src\Presentation\Nop.Web\Factories\NewsModelFactory.cs:line 165
   at Nop.Core.Caching.MemoryCacheManager.Get[T](String key, Func`1 acquire, Nullable`1 cacheTime) in C:\andrei\nopcommerce\sources\src\Libraries\Nop.Core\Caching\MemoryCacheManager.cs:line 152
   at Nop.Web.Factories.NewsModelFactory.PrepareHomePageNewsItemsModel() in C:\andrei\nopcommerce\sources\src\Presentation\Nop.Web\Factories\NewsModelFactory.cs:line 163
   at Nop.Web.Components.HomepageNewsViewComponent.Invoke() in C:\andrei\nopcommerce\sources\src\Presentation\Nop.Web\Components\HomepageNews.cs:line 24
   at lambda_method(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.ViewComponents.DefaultViewComponentInvoker.InvokeSyncCore(ObjectMethodExecutor executor, ViewComponentContext context)
   at Microsoft.AspNetCore.Mvc.ViewComponents.DefaultViewComponentInvoker.InvokeAsync(ViewComponentContext context)
   at StackExchange.Profiling.Internal.ProfilingViewComponentInvoker.InvokeAsync(ViewComponentContext context) in C:\projects\dotnet\src\MiniProfiler.AspNetCore.Mvc\Internal\ProfilingViewComponentInvoker.cs:line 30
   at Microsoft.AspNetCore.Mvc.ViewComponents.DefaultViewComponentHelper.InvokeCoreAsync(ViewComponentDescriptor descriptor, Object arguments)
   at AspNetCore.Themes_Motion_Views_Home_Index.ExecuteAsync() in E:\SLS\nopCommerce_4.10_NoSource\Themes\Motion\Views\Home\Index.cshtml:line 18
   at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageCoreAsync(IRazorPage page, ViewContext context)
   at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageAsync(IRazorPage page, ViewContext context, Boolean invokeViewStarts)
   at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderAsync(ViewContext context)
   at Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ViewContext viewContext, String contentType, Nullable`1 statusCode)
   at Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ActionContext actionContext, IView view, ViewDataDictionary viewData, ITempDataDictionary tempData, String contentType, Nullable`1 statusCode)
   at Microsoft.AspNetCore.Mvc.ViewFeatures.ViewResultExecutor.ExecuteAsync(ActionContext context, ViewResult result)
   at Microsoft.AspNetCore.Mvc.ViewResult.ExecuteResultAsync(ActionContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeResultAsync(IActionResult result)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResultFilterAsync[TFilter,TFilterAsync]()
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResultExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeResultFilters()
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResourceFilter()
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()
   at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
   at StackExchange.Profiling.MiniProfilerMiddleware.Invoke(HttpContext context) in C:\projects\dotnet\src\MiniProfiler.AspNetCore\MiniProfilerMiddleware.cs:line 94
   at Nop.Services.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) in C:\andrei\nopcommerce\sources\src\Libraries\Nop.Services\Authentication\AuthenticationMiddleware.cs:line 79
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
   at Nop.Core.Http.InstallUrlMiddleware.Invoke(HttpContext context, IWebHelper webHelper) in C:\andrei\nopcommerce\sources\src\Libraries\Nop.Core\Http\InstallUrlMiddleware.cs:line 51
   at Nop.Core.Http.KeepAliveMiddleware.Invoke(HttpContext context, IWebHelper webHelper) in C:\andrei\nopcommerce\sources\src\Libraries\Nop.Core\Http\KeepAliveMiddleware.cs:line 50
   at Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.Invoke(HttpContext context)
ClientConnectionId:c5cb6f6d-3040-4b6b-b1ea-9dbf280cf2c4
Error Number:156,State:1,Class:15


IP address:  
202.88.250.117

Customer:  Guest

Page URL:  
http://sls.millinfo.net/

Referrer URL:  

Created on:  
11/8/2018 2:40:31 AM


Thanks,
Varun
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Posted: November 08, 2018 at 7:52 AM Quote #214076
What version of SQL Server are you using?
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
www.noptools.com
Posted: November 08, 2018 at 8:03 AM Quote #214078
New York wrote:
What version of SQL Server are you using?


Hi,
Thanks for the quick reply.

Im using SQL Server 2008 (10.0.5538.0)

Thanks,
Varun
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Posted: November 09, 2018 at 3:38 AM Quote #214099
varunnaresh wrote:
SQL Server 2008 (10.0.5538.0)

So do you have "UseRowNumberForPaging" enabled in appsettings.json?
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Interested in the dedicated Premium support services provided by core developers? Please visit http://www.nopcommerce.com/supportservices.aspx

Regards,
Andrei Mazulnitsyn
Posted: November 09, 2018 at 4:08 AM Quote #214100
a.m. wrote:
SQL Server 2008 (10.0.5538.0)
So do you have "UseRowNumberForPaging" enabled in appsettings.json?


Yes.

    //Enable for backwards compatibility with SQL Server 2008 and SQL Server 2008R2
    "UseRowNumberForPaging": true,
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Posted: November 09, 2018 at 5:26 AM Quote #214103
I'm not sure but looks like EF has some issues with SQL Server 2008 and this setting. Because we experience it on our web server as well periodically. I've seen several similar reports and in all cases people used SQL Server 2008
This post/answer is useful
1
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Interested in the dedicated Premium support services provided by core developers? Please visit http://www.nopcommerce.com/supportservices.aspx

Regards,
Andrei Mazulnitsyn
Posted: November 09, 2018 at 5:31 AM Quote #214104
a.m. wrote:
I'm not sure but looks like EF has some issues with SQL Server 2008 and this setting. Because we experience it on our web server as well periodically. I've seen several similar reports and in all cases people used SQL Server 2008


I see. Thank you so much for your quick reply and information.
I will keep that in mind and see if i can transfer my website db to a newer SQL server.

Thank you so much again. At Least now i know what i can do to prevent it.

Thanks,
Varun
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Posted: November 09, 2018 at 5:47 AM Quote #214106
a.m. wrote:
I'm not sure but looks like EF has some issues with SQL Server 2008 and this setting. Because we experience it on our web server as well periodically. I've seen several similar reports and in all cases people used SQL Server 2008


Does the same issue exist on SQL Server 2008 R2 ?
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Posted: November 09, 2018 at 9:43 AM Quote #214114
varunnaresh wrote:
Does the same issue exist on SQL Server 2008 R2 ?

Yes. Actually we had this issue on SQL Server 2008 R2
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Interested in the dedicated Premium support services provided by core developers? Please visit http://www.nopcommerce.com/supportservices.aspx

Regards,
Andrei Mazulnitsyn
Premium support services
  • Dedicated premium support services provided by core developers are intended for persons who run mission critical websites, work on projects with tight deadlines, or want to get dedicated support.
Professional services
  • Want to open a new store? Want to take your store to the next level? Need a custom extension? We can customize nopCommerce to fit your store perfectly. Request a quote to get started.