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

BORNXenon wrote:
What hosting are you using?

Pipeten. I have a Multihost package with them, but once I've finished my Nop site, I'll be moving to a Cloud server package so that I can have Full Trust and SSL certs, etc. The db will however still be SQL 2005 as they host the databases on dedicated servers in order to keep the web servers running as quick as possible.

So, is there a temp fix for this until v3.40?
Is it safe to add in the 3.2 code as kurt suggests, or will it break something else?

BORNXenon wrote:
Please see changeset fe73120da4b6. Just revert back this changeset (simply replace /Libraries/Nop.Data/NopObjectContext.cs file with an old one from 3.20) in order to get SQL Server 2005 support

Cheers Andrei, I'll give it a go.

I can confirm this "error happened" issue occurs when browsing products in the Admin area with SQL Server 2005 (and can further confirm that there are other NOP customers using this version of SQL Server  ;-).  

I can also confirm that the recommended fix works.  Reverting the "ExecuteStoredProcedureList" method back to the Nop 3.2 version indeed fixes the problem.

I have executed above sql in SQL Server 2005 and  2008R2 and got same error message as like..
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)

It's seems above syntax is not supported by SQL Server 2008R2 and lower version.
This error occurred when ExecuteStoredProcedureList function called in code base.



Most possible solution is:
Add Exec keyword just before  ProductLoadAllPaged in ProductServices.cs file.

var products = _dbContext.ExecuteStoredProcedureList<Product>(
                    "Exec ProductLoadAllPaged",
                    pCategoryIds,
                    pManufacturerId,
                    pStoreId,
                    pVendorId,
                    pWarehouseId,
                    pParentGroupedProductId,
                    pProductTypeId,
                    pVisibleIndividuallyOnly,
                    pProductTagId,
                    pFeaturedProducts,
                    pPriceMin,
                    pPriceMax,
                    pKeywords,
                    pSearchDescriptions,
                    pSearchSku,
                    pSearchProductTags,
                    pUseFullTextSearch,
                    pFullTextMode,
                    pFilteredSpecs,
                    pLanguageId,
                    pOrderBy,
                    pAllowedCustomerRoleIds,
                    pPageIndex,
                    pPageSize,
                    pShowHidden,
                    pLoadFilterableSpecificationAttributeOptionIds,
                    pFilterableSpecificationAttributeOptionIds,
                    pTotalRecords);

It would produce below sql and execute without any syntax error.

declare @p29 nvarchar(max)
set @p29=NULL
declare @p30 int
set @p30=0
exec sp_executesql N'exec 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

See this post of mine. SQL Server 2005 is not supported by the latest versions.

To expand on this further... Microsoft mainstream support for SQL 2005 has been officially over since 2011.  SQL 2005 (extended support) is valid until 2016.  During the time while a product is in extended support mode, ideally you would "bail" as soon as possible and go for an upgrade. There is no point in trying to hold onto products past their mainstream support.
In case people are curious... pretty good article on  SQL Support timelines here

This bug is relevant to
https://www.nopcommerce.com/boards/t/31887/v-340-do-not-show-products-on-admin-panel.aspx