Gold R6 Could Not Execute Query errors

[AbleMods]

We're seeing a sudden increase in Could Not Execute Query errors on a Gold R6 store. Probably due to higher shopping volume, but it's a concern regardless.

Any thoughts how to fix?

Here's the error message:

An error has occured at http://www.badideatshirts.com/category. ... isfeatured desc, orderby asc, name asc&c=52&ps=12&p=8
Exception: Exception of type 'System.Web.HttpUnhandledException' was thrown. Stack Trace: at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.category_aspx.ProcessRequest(HttpContext context) in c:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\a3abb1a9\12748306\App_Web_qfsqbyw3.9.cs:line 0 at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) Inner Exception: could not execute query [ SELECT P.ProductId AS ProductId, COUNT(OI.ProductId) AS OrderCount FROM ac_OrderItems AS OI RIGHT OUTER JOIN ac_Products AS P ON OI.ProductId = P.ProductId WHERE P.ProductId IN (69,903,2577,1854,2456,2423,393,2540,1021,2601,1387,3134,3228,3229,3230,24,2897,171,3234,3064,3094,3237,3240,3241,30,46,3048,85,1191,3097,2678,133,140,1728,2418,2264,2156,2415,3227,1850,2839,3131,2551,3246,3032,2957,3107,2636,129,2270,2803,2556,2178,2349,2506,2372,2493,2519,467,2890,610,3051,2857,2945,2879,2982,724,3175,727,2481,740,2164,2508,2282,2366,2988,899,2546,923,2158,3075,3029,962,2451,2899,2244,1016,2972,1064,1101,2880,1129,2165,1140,1581,2463,2516,1592,2553,563,1647,2644,2717,3247,5,3087,2445,726,75,3169,136,1844,187,246,1855,2311,670,530,3037,565) GROUP BY OI.ProductId, P.ProductId ORDER BY OrderCount DESC ] [SQL: SELECT P.ProductId AS ProductId, COUNT(OI.ProductId) AS OrderCount FROM ac_OrderItems AS OI RIGHT OUTER JOIN ac_Products AS P ON OI.ProductId = P.ProductId WHERE P.ProductId IN (69,903,2577,1854,2456,2423,393,2540,1021,2601,1387,3134,3228,3229,3230,24,2897,171,3234,3064,3094,3237,3240,3241,30,46,3048,85,1191,3097,2678,133,140,1728,2418,2264,2156,2415,3227,1850,2839,3131,2551,3246,3032,2957,3107,2636,129,2270,2803,2556,2178,2349,2506,2372,2493,2519,467,2890,610,3051,2857,2945,2879,2982,724,3175,727,2481,740,2164,2508,2282,2366,2988,899,2546,923,2158,3075,3029,962,2451,2899,2244,1016,2972,1064,1101,2880,1129,2165,1140,1581,2463,2516,1592,2553,563,1647,2644,2717,3247,5,3087,2445,726,75,3169,136,1844,187,246,1855,2311,670,530,3037,565) GROUP BY OI.ProductId, P.ProductId ORDER BY OrderCount DESC] Inner Exception Stack Trace: at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List() at AbleCommerce.ConLib.CategoryGridPage.GetSortedProducts(IList`1 products) in d:\Websites\BadIdeaTShirts\ConLib\Custom\CategoryGridPage.ascx.cs:line 346 at AbleCommerce.ConLib.CategoryGridPage.BindProductList() in d:\Websites\BadIdeaTShirts\ConLib\Custom\CategoryGridPage.ascx.cs:line 318 at AbleCommerce.ConLib.CategoryGridPage.BindSearchResultsPanel() in d:\Websites\BadIdeaTShirts\ConLib\Custom\CategoryGridPage.ascx.cs:line 292 at AbleCommerce.ConLib.CategoryGridPage.BindPage() in d:\Websites\BadIdeaTShirts\ConLib\Custom\CategoryGridPage.ascx.cs:line 139 at AbleCommerce.ConLib.CategoryGridPage.Page_PreRender(Object sender, EventArgs e) in d:\Websites\BadIdeaTShirts\ConLib\Custom\CategoryGridPage.ascx.cs:line 239 at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Inner Exception: could not execute query [ SELECT P.ProductId AS ProductId, COUNT(OI.ProductId) AS OrderCount FROM ac_OrderItems AS OI RIGHT OUTER JOIN ac_Products AS P ON OI.ProductId = P.ProductId WHERE P.ProductId IN (69,903,2577,1854,2456,2423,393,2540,1021,2601,1387,3134,3228,3229,3230,24,2897,171,3234,3064,3094,3237,3240,3241,30,46,3048,85,1191,3097,2678,133,140,1728,2418,2264,2156,2415,3227,1850,2839,3131,2551,3246,3032,2957,3107,2636,129,2270,2803,2556,2178,2349,2506,2372,2493,2519,467,2890,610,3051,2857,2945,2879,2982,724,3175,727,2481,740,2164,2508,2282,2366,2988,899,2546,923,2158,3075,3029,962,2451,2899,2244,1016,2972,1064,1101,2880,1129,2165,1140,1581,2463,2516,1592,2553,563,1647,2644,2717,3247,5,3087,2445,726,75,3169,136,1844,187,246,1855,2311,670,530,3037,565) GROUP BY OI.ProductId, P.ProductId ORDER BY OrderCount DESC ] [SQL: SELECT P.ProductId AS ProductId, COUNT(OI.ProductId) AS OrderCount FROM ac_OrderItems AS OI RIGHT OUTER JOIN ac_Products AS P ON OI.ProductId = P.ProductId WHERE P.ProductId IN (69,903,2577,1854,2456,2423,393,2540,1021,2601,1387,3134,3228,3229,3230,24,2897,171,3234,3064,3094,3237,3240,3241,30,46,3048,85,1191,3097,2678,133,140,1728,2418,2264,2156,2415,3227,1850,2839,3131,2551,3246,3032,2957,3107,2636,129,2270,2803,2556,2178,2349,2506,2372,2493,2519,467,2890,610,3051,2857,2945,2879,2982,724,3175,727,2481,740,2164,2508,2282,2366,2988,899,2546,923,2158,3075,3029,962,2451,2899,2244,1016,2972,1064,1101,2880,1129,2165,1140,1581,2463,2516,1592,2553,563,1647,2644,2717,3247,5,3087,2445,726,75,3169,136,1844,187,246,1855,2311,670,530,3037,565) GROUP BY OI.ProductId, P.ProductId ORDER BY OrderCount DESC] Inner Exception Stack Trace: at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List() at AbleCommerce.ConLib.CategoryGridPage.GetSortedProducts(IList`1 products) in d:\Websites\BadIdeaTShirts\ConLib\Custom\CategoryGridPage.ascx.cs:line 346 at AbleCommerce.ConLib.CategoryGridPage.BindProductList() in d:\Websites\BadIdeaTShirts\ConLib\Custom\CategoryGridPage.ascx.cs:line 318 at AbleCommerce.ConLib.CategoryGridPage.BindSearchResultsPanel() in d:\Websites\BadIdeaTShirts\ConLib\Custom\CategoryGridPage.ascx.cs:line 292 at AbleCommerce.ConLib.CategoryGridPage.BindPage() in d:\Websites\BadIdeaTShirts\ConLib\Custom\CategoryGridPage.ascx.cs:line 139 at AbleCommerce.ConLib.CategoryGridPage.Page_PreRender(Object sender, EventArgs e) in d:\Websites\BadIdeaTShirts\ConLib\Custom\CategoryGridPage.ascx.cs:line 239 at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Page.ProcessRequestMain(B

[AbleMods]

Here's same error on product page instead of category page:

An error has occured at http://www.badideatshirts.com/product.a ... uctid=2711
Exception: Exception of type 'System.Web.HttpUnhandledException' was thrown. Stack Trace: at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.product_aspx.ProcessRequest(HttpContext context) in c:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\a3abb1a9\12748306\App_Web_qfsqbyw3.34.cs:line 0 at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) Inner Exception: could not execute query [ SELECT this_.OptionChoiceId as OptionCh1_60_0_, this_.OptionId as OptionId60_0_, this_.Name as Name60_0_, this_.ThumbnailUrl as Thumbnai4_60_0_, this_.ImageUrl as ImageUrl60_0_, this_.PriceModifier as PriceMod6_60_0_, this_.CogsModifier as CogsModi7_60_0_, this_.WeightModifier as WeightMo8_60_0_, this_.SkuModifier as SkuModif9_60_0_, this_.Selected as Selected60_0_, this_.OrderBy as OrderBy60_0_ FROM ac_OptionChoices this_ WHERE this_.OptionChoiceId in (SELECT distinct this_0_.Option1 as y0_ FROM ac_ProductVariants this_0_ WHERE this_0_.ProductId = @p0 and this_0_.Available = @p1 and (this_0_.InStock > @p2 or this_0_.AvailabilityDate > @p3)) ] Name:cp0 - Value:2711 Name:cp1 - Value:True Name:cp2 - Value:0 Name:cp3 - Value:12/16/2015 10:14:54 PM [SQL: SELECT this_.OptionChoiceId as OptionCh1_60_0_, this_.OptionId as OptionId60_0_, this_.Name as Name60_0_, this_.ThumbnailUrl as Thumbnai4_60_0_, this_.ImageUrl as ImageUrl60_0_, this_.PriceModifier as PriceMod6_60_0_, this_.CogsModifier as CogsModi7_60_0_, this_.WeightModifier as WeightMo8_60_0_, this_.SkuModifier as SkuModif9_60_0_, this_.Selected as Selected60_0_, this_.OrderBy as OrderBy60_0_ FROM ac_OptionChoices this_ WHERE this_.OptionChoiceId in (SELECT distinct this_0_.Option1 as y0_ FROM ac_ProductVariants this_0_ WHERE this_0_.ProductId = @p0 and this_0_.Available = @p1 and (this_0_.InStock > @p2 or this_0_.AvailabilityDate > @p3))] Inner Exception Stack Trace: at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session) at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) at NHibernate.Impl.CriteriaImpl.List(IList results) at NHibernate.Impl.CriteriaImpl.List[T]() at CommerceBuilder.Products.OptionChoiceRepository.GetAvailableChoicesFromInventory(Int32 productId, IDictionary`2 selectedChoices, Int32 targetOptionVariantIndex) at CommerceBuilder.Products.OptionChoiceRepository.GetAvailableChoices(Int32 productId, Int32 targetOptionId, IDictionary`2 selectedChoices) at CommerceBuilder.Products.OptionChoiceDataSource.GetAvailableChoices(Int32 productId, Int32 targetOptionId, IDictionary`2 selectedChoices) at AbleCommerce.ConLib.BuyShirtPage.BuildProductOptions(Int32 selectedOptionIndex) in d:\Websites\BadIdeaTShirts\ConLib\Custom\BuyShirtPage.ascx.cs:line 993 at AbleCommerce.ConLib.BuyShirtPage.Page_Init(Object sender, EventArgs e) in d:\Websites\BadIdeaTShirts\ConLib\Custom\BuyShirtPage.ascx.cs:line 89 at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.AddedControl(Control control, Int32 index) at CommerceBuilder.UI.WebControls.HtmlContainer.CreateChildControls() at System.Web.UI.Control.EnsureChildControls() at CommerceBuilder.UI.WebControls.HtmlContainer.Page_InitComplete(Object sender, EventArgs e) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Inner Exception: could not execute query [ SELECT this_.OptionChoiceId as OptionCh1_60_0_, this_.OptionId as OptionId60_0_, this_.Name as Name60_0_, this_.ThumbnailUrl as Thumbnai4_60_0_, this_.ImageUrl as ImageUrl60_0_, this_.PriceModifier as PriceMod6_60_0_, this_.CogsModifier as CogsModi7_60_0_, this_.WeightModifier as WeightMo8_60_0_, this_.SkuModifier as SkuModif9_60_0_, this_.Selected as Selected60_0_, this_.OrderBy as OrderBy60_0_ FROM ac_OptionChoices this_ WHERE this_.OptionChoiceId in (SELECT distinct this_0_.Option1 as y0_ FROM ac_ProductVariants this_0_ WHERE this_0_.ProductId = @p0 and this_0_.Available = @p1 and (this_0_.InStock > @p2 or this_0_.AvailabilityDate > @p3)) ] Name:cp0 - Value:2711 Name:cp1 - Value:True Name:cp2 - Value:0 Name:cp3 - Value:12/16/2015 10:14:54 PM [SQL: SELECT this_.OptionChoiceId as OptionCh1_60_0_, this_.OptionId as OptionId60_0_, this_.Name as Name60_0_, this_.ThumbnailUrl as Thumbnai4_60_0_, this_.ImageUrl as ImageUrl60_0_, this_.PriceModifier as PriceMod6_60_0_, this_.CogsModifier as CogsModi7_60_0_, this_.WeightModifier as WeightMo8_60_0_, this_.SkuModifier as SkuModif9_60_0_, this_.Selected as Selected60_0_, this_.OrderBy as OrderBy60_0_ FROM ac_OptionChoices this_ WHERE this_.OptionChoiceId in (SELECT distinct this_0_.Option1 as y0_ FROM ac_ProductVariants this_0_ WHERE this_0_.ProductId = @p0 and this_0_.Available = @p1 and (this_0_.InStock > @p2 or this_0_.AvailabilityDate > @p3))] Inner Exception Stack Trace: at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session) at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) at NHibernate.Impl.CriteriaImpl.List(IList results) at NHibernate.Impl.CriteriaImpl.List[T]() at CommerceBuilder.Products.OptionChoiceRepository.GetAvailableChoicesFromInventory(Int32 productId, IDictionary`2 selectedChoices, Int32 targetOptionVariantIndex) at CommerceBuilder.Products.OptionChoiceRepository.GetAvailableChoices(Int32 productId, Int32 targetOptionId, IDictionary`2 selectedChoices) at CommerceBuilder.Products.OptionChoiceDataSource.GetAvailableChoices(Int32 productId, Int32 targetOptionId, IDictionary`2 selectedChoices) at AbleCommerce.ConLib.BuyShirtPage.BuildProductOptions(Int32 selectedOptionIndex) in d:\Websites\BadIdeaTShirts\ConLib\Custom\BuyShirtPage.ascx.cs:line 993 at AbleCommerce.ConLib.BuyShirtPage.Page_Init(Object sender, EventArgs e) in d:\Websites\BadIdeaTShirts\ConLib\Custom\BuyShirtPage.ascx.cs:line 89 at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.

[jmestep]

Joe, no solution, but I have seen could not execute query errors on several sites. I just never had to track it down since the client wasn't reporting issues.

[roadkill]

Here is another one:

An error has occured at http://www.badideatshirts.com/Errors/Pa ... source.axd
Exception: query did not return a unique result: 2 Stack Trace: at NHibernate.Impl.AbstractQueryImpl.UniqueElement(IList list) at NHibernate.Impl.CriteriaImpl.UniqueResult[T]() at CommerceBuilder.Users.UserRepository.LoadForUserName(String userName, Boolean createMissing) at CommerceBuilder.Users.UserDataSource.LoadForUserName(String userName, Boolean createMissing) at CommerceBuilder.Services.Membership.WebUserLocator.Locate() at CommerceBuilder.Common.AbleContext.get_User() at CommerceBuilder.Services.PageTracker.Track(HttpApplication application, HttpContext context) at CommerceBuilder.Services.HttpModule.a(Object A_0, EventArgs A_1) at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

[Katie]

Hi,

It can be really difficult to diagnose a problem with only the error message. It would be helpful to see how this product is configured. A lot of things can come into play like Kits, Options, Discounts, etc. Can you reproduce the issue yourselves, or are you just seeing these in the error log?

Gold R6 is over 2 years old now. We've made a lot of changes and improvements since then. In Gold R8, many of the changes were focused on improving the Nhibernate queries.

http://help.ablecommerce.com/index.htm# ... _to_r8.htm

Thanks,
Katie