v3.30 - Incorrect syntax near 'ProductLoadAllPaged'. when trying to view a product.

Upgraded 3.2 to 3.3 this morning and everything appeared to go well until I viewed a product and then i got the above error.
In the Admin site I also get a JQuery modal dialog that helpfully says 'Error Happened' when attempting to load the product list.

Any ideas?

Full Stack trace below...


Server Error in '/' Application.

Incorrect syntax near 'ProductLoadAllPaged'.

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: Incorrect syntax near 'ProductLoadAllPaged'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[SqlException (0x80131904): Incorrect syntax near 'ProductLoadAllPaged'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1767866
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5352418
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +244
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1691
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +61
   System.Data.SqlClient.SqlDataReader.get_MetaData() +90
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +365
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +1406
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +177
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +134
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41
   System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c) +9
   System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) +72
   System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) +355
   System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior) +166
   System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryInternal(String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters) +286
   System.Data.Entity.Core.Objects.<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__64() +45
   System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction(Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) +288
   System.Data.Entity.Core.Objects.<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__63() +170
   System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func`1 operation) +189
   System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably(String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters) +458
   System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery(String commandText, ExecutionOptions executionOptions, Object[] parameters) +61
   System.Data.Entity.Internal.<>c__DisplayClass14`1.<ExecuteSqlQuery>b__13() +144
   System.Data.Entity.Internal.LazyEnumerator`1.MoveNext() +45
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +381
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +58
   Nop.Data.NopObjectContext.ExecuteStoredProcedureList(String commandText, Object[] parameters) +331
   Nop.Services.Catalog.ProductService.SearchProducts(IList`1& filterableSpecificationAttributeOptionIds, Boolean loadFilterableSpecificationAttributeOptionIds, Int32 pageIndex, Int32 pageSize, IList`1 categoryIds, Int32 manufacturerId, Int32 storeId, Int32 vendorId, Int32 warehouseId, Int32 parentGroupedProductId, Nullable`1 productType, Boolean visibleIndividuallyOnly, Nullable`1 featuredProducts, Nullable`1 priceMin, Nullable`1 priceMax, Int32 productTagId, String keywords, Boolean searchDescriptions, Boolean searchSku, Boolean searchProductTags, Int32 languageId, IList`1 filteredSpecs, ProductSortingEnum orderBy, Boolean showHidden) +4837
   Nop.Services.Catalog.ProductService.SearchProducts(Int32 pageIndex, Int32 pageSize, IList`1 categoryIds, Int32 manufacturerId, Int32 storeId, Int32 vendorId, Int32 warehouseId, Int32 parentGroupedProductId, Nullable`1 productType, Boolean visibleIndividuallyOnly, Nullable`1 featuredProducts, Nullable`1 priceMin, Nullable`1 priceMax, Int32 productTagId, String keywords, Boolean searchDescriptions, Boolean searchSku, Boolean searchProductTags, Int32 languageId, IList`1 filteredSpecs, ProductSortingEnum orderBy, Boolean showHidden) +165
   Nop.Web.Controllers.CatalogController.Category(Int32 categoryId, CatalogPagingFilteringModel command) +5032
   lambda_method(Closure , ControllerBase , Object[] ) +146
   System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +157
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +27
   System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState) +22
   System.Web.Mvc.Async.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult) +29
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +32
   System.Web.Mvc.Async.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() +50
   System.Web.Mvc.Async.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41() +225
   System.Web.Mvc.Async.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41() +225
   System.Web.Mvc.Async.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41() +225
   System.Web.Mvc.Async.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41() +225
   System.Web.Mvc.Async.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41() +225
   System.Web.Mvc.Async.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41() +225
   System.Web.Mvc.Async.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41() +225
   System.Web.Mvc.Async.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41() +225
   System.Web.Mvc.Async.<>c__DisplayClass33.<BeginInvokeActionMethodWithFilters>b__32(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +34
   System.Web.Mvc.Async.<>c__DisplayClass2b.<BeginInvokeAction>b__1c() +26
   System.Web.Mvc.Async.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult) +100
   System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +27
   System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) +13
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +36
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +54
   System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +39
   System.Web.Mvc.Controller.<BeginExecute>b__15(IAsyncResult asyncResult, Controller controller) +12
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +28
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +54
   System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +29
   System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
   System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__5(IAsyncResult asyncResult, ProcessRequestState innerState) +21
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +36
   System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +54
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +31
   System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9514928
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.18446

I've restored my database from before the upgrade and re-ran the upgrade script, the issue persists.

Any help here would be great! :)

Are you running SQL Sever 2005? I was having the same same issue on a hosted SQL server 2005 db  after the update... so I did a fresh install (new empty database)  on 2005 and the error still happens... Then did a fresh install on a 2013 SQL express db locally and it works fine. So my conclusion (right now)  is it is 2005... of course it could be something else with my hosted environment.

Does it happen on every product?  Does it happen when you do a product search?
Can you run the SQL Profiler to see the actual call to sp?

It happens in a new database if you add a product and click on the "new products" link on the menu bar, or on the admin side if you go to catalog>products>manage products. basically when the ProductLoadAllPaged stored proc is called to display a list of products.

I played with it some more this morning and it is not an issue with the stored proc (which has not changed from version 3.2) but with the method ExecuteStoredProcedureList in \libraries\nop.data\NopObjectContext. If I take the code for ExecuteStoredProcedureList  from 3.2 and stick it in the 3.3 code base it fixes the error in the two scenarios above for the MSSQL 2005 database. I of course have no idea why the code was changed and if this 'fix' will break other parts of the code.

[email protected] wrote:
Do you have an opportunity to test it with other versions of MSSQL (2008 or 2012)? If yes, do you still experience this issue?


I don't have MSSQL 2005 installed (only 2008 and 2012). Could somebody else please test nopCommerce 3.30 with MSSQL 2005 and let me know the results?

[email protected] wrote:
Also please execute the following command over your SQL Server 2005 database in SSMS. Are you getting any errors?

declare @p29 nvarchar(max)
set @p29=NULL
declare @p30 int
set @p30=0
exec sp_executesql N'ProductLoadAllPaged @CategoryIds, @ManufacturerId, @StoreId, @VendorId, @WarehouseId, @ParentGroupedProductId, @ProductTypeId, @VisibleIndividuallyOnly, @ProductTagId, @FeaturedProducts, @PriceMin, @PriceMax, @Keywords, @SearchDescriptions, @SearchSku, @SearchProductTags, @UseFullTextSearch, @FullTextMode, @FilteredSpecs, @LanguageId, @OrderBy, @AllowedCustomerRoleIds, @PageIndex, @PageSize, @ShowHidden, @LoadFilterableSpecificationAttributeOptionIds, @FilterableSpecificationAttributeOptionIds output, @TotalRecords output',N'@CategoryIds nvarchar(4000),@ManufacturerId int,@StoreId int,@VendorId int,@WarehouseId int,@ParentGroupedProductId int,@ProductTypeId int,@VisibleIndividuallyOnly int,@ProductTagId int,@FeaturedProducts bit,@PriceMin decimal(29,0),@PriceMax decimal(29,0),@Keywords nvarchar(4),@SearchDescriptions bit,@SearchSku bit,@SearchProductTags bit,@UseFullTextSearch bit,@FullTextMode int,@FilteredSpecs nvarchar(4000),@LanguageId int,@OrderBy int,@AllowedCustomerRoleIds nvarchar(5),@PageIndex int,@PageSize int,@ShowHidden bit,@LoadFilterableSpecificationAttributeOptionIds bit,@FilterableSpecificationAttributeOptionIds nvarchar(max)  output,@TotalRecords int output',@CategoryIds=N'',@ManufacturerId=0,@StoreId=0,@VendorId=0,@WarehouseId=0,@ParentGroupedProductId=0,@ProductTypeId=NULL,@VisibleIndividuallyOnly=0,@ProductTagId=0,@FeaturedProducts=NULL,@PriceMin=NULL,@PriceMax=NULL,@Keywords=N'test',@SearchDescriptions=0,@SearchSku=1,@SearchProductTags=0,@UseFullTextSearch=0,@FullTextMode=0,@FilteredSpecs=N'',@LanguageId=0,@OrderBy=0,@AllowedCustomerRoleIds=N'1,2,3',@PageIndex=0,@PageSize=15,@ShowHidden=1,@LoadFilterableSpecificationAttributeOptionIds=0,@FilterableSpecificationAttributeOptionIds=@p29 output,@TotalRecords=@p30 output
select @p29, @p30

Running the sql above  returns the following on 2005

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ProductLoadAllPaged'.

(1 row(s) affected)


on 2012 (not 2013)  it successfully returns one row, which is what I currently have in that db

I currently don't have access to a 2008 db

Looks like one of performance optimizations done in version 3.30 cause with bug in SQL Server 2005. I've just created a work item.

P.S. Thinking whether we should support such old database version (2005) or drop its support. None of existing hosting companies have it installed today

My hosted environment db is indeed SQL 2005. Version 3.2 worked fine but 3.30 breaks it.