Dave.
I was having an issue with products not sorting correctly. I tracked it down to it using the ModifiedDate as a default, which is strange because it was only doing it in my live system, and not dev.
I could only see it happening with the CatId returned wasnt' numeric.
I then went to show someone what I was doing to date, and it crashed. It crashes in Microsoft Edge for me, but if I use Chrome it works OK. It wasn't working for my supplier i Chrome either.
From my previous time, I had setup a debug log in the stored procedures, so I ran the pageload in Edge, then in Chrome.
Edge produced this SQL (which fails).
DECLARE @recct int SET @recct = 3 DECLARE @lbound int, @ubound int SET @lbound = 0 SET @ubound = @lbound + 76 IF @lbound >= @recct BEGIN SET @ubound = @recct + 1 SET @lbound = @ubound - (76) END SELECT NB1.[ItemId] ,ISNULL(NB2.[XMLData],NB1.[XMLData]) as [XMLData] ,ISNULL(NB2.[Lang],ISNULL(NB1.[Lang],'')) as [Lang] ,NB1.[PortalId] ,NB1.[ModuleId] ,NB1.[TypeCode] ,NB1.[GUIDKey] ,NB1.[ModifiedDate] ,NB1.[TextData] ,NB1.[XrefItemId] ,NB1.[ParentItemId] ,NB1.[UserId] FROM ( SELECT ROW_NUMBER() OVER( order by NB4.[XMLdata].value('(genxml/sort)[1]','int'), NB3.productname ) AS row, NB1.[ItemId] ,ISNULL(NB2.[XMLData],NB1.[XMLData]) as [XMLData] ,ISNULL(NB2.[Lang],ISNULL(NB1.[Lang],'')) as [Lang] ,NB1.[PortalId] ,NB1.[ModuleId] ,NB1.[TypeCode] ,NB1.[GUIDKey] ,NB1.[ModifiedDate] ,NB1.[TextData] ,NB1.[XrefItemId] ,NB1.[ParentItemId] ,NB1.[UserId] FROM dbo.[NBrightBuy] as NB1 left join dbo.[NBrightBuyIdx] as NB3 on NB3.ItemId = NB1.ItemId and NB3.[Lang] = 'en-AU' left join dbo.[NBrightBuyLang] as NB2 on NB2.ParentItemId = NB1.ItemId and NB2.[Lang] = 'en-AU' left join dbo.[NBrightBuy] as NB4 on (NB4.TypeCode = 'CATXREF' or NB4.TypeCode = 'CATCASCADE' ) and NB4.ParentItemId = NB1.ItemId and NB4.XrefItemId = Order by WHERE NB1.TypeCode = 'PRD' and (NB1.PortalId = '1' or NB1.PortalId = '-1') and (NB1.ModuleId = '483' or NB1.ModuleId = '-1') and NB1.[ItemId] in (select parentitemid from dbo.[NBrightBuy] where (typecode = 'CATCASCADE' or typecode = 'CATXREF') and XrefItemId = 27) and (NB3.Visible = 1) ) AS NB1 left join dbo.[NBrightBuyIdx] as NB3 on NB3.ItemId = NB1.ItemId and NB3.[Lang] = 'en-AU' left join dbo.[NBrightBuyLang] as NB2 on NB2.ParentItemId = NB1.ItemId and NB2.[Lang] = 'en-AU' left join dbo.[NBrightBuy] as NB4 on (NB4.TypeCode = 'CATXREF' or NB4.TypeCode = 'CATCASCADE' ) and NB4.ParentItemId = NB1.ItemId and NB4.XrefItemId = Order by WHERE row > @lbound AND row < @ubound
Error is:
Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'Order'. Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'and'.
Chrome has this SQL
DECLARE @recct int SET @recct = 3 DECLARE @lbound int, @ubound int SET @lbound = 0 SET @ubound = @lbound + 76 IF @lbound >= @recct BEGIN SET @ubound = @recct + 1 SET @lbound = @ubound - (76) END SELECT NB1.[ItemId] ,ISNULL(NB2.[XMLData],NB1.[XMLData]) as [XMLData] ,ISNULL(NB2.[Lang],ISNULL(NB1.[Lang],'')) as [Lang] ,NB1.[PortalId] ,NB1.[ModuleId] ,NB1.[TypeCode] ,NB1.[GUIDKey] ,NB1.[ModifiedDate] ,NB1.[TextData] ,NB1.[XrefItemId] ,NB1.[ParentItemId] ,NB1.[UserId] FROM ( SELECT ROW_NUMBER() OVER( Order by ModifiedDate DESC ) AS row, NB1.[ItemId] ,ISNULL(NB2.[XMLData],NB1.[XMLData]) as [XMLData] ,ISNULL(NB2.[Lang],ISNULL(NB1.[Lang],'')) as [Lang] ,NB1.[PortalId] ,NB1.[ModuleId] ,NB1.[TypeCode] ,NB1.[GUIDKey] ,NB1.[ModifiedDate] ,NB1.[TextData] ,NB1.[XrefItemId] ,NB1.[ParentItemId] ,NB1.[UserId] FROM dbo.[NBrightBuy] as NB1 left join dbo.[NBrightBuyIdx] as NB3 on NB3.ItemId = NB1.ItemId and NB3.[Lang] = 'en-AU' left join dbo.[NBrightBuyLang] as NB2 on NB2.ParentItemId = NB1.ItemId and NB2.[Lang] = 'en-AU' WHERE NB1.TypeCode = 'PRD' and (NB1.PortalId = '1' or NB1.PortalId = '-1') and (NB1.ModuleId = '483' or NB1.ModuleId = '-1') and NB1.[ItemId] in (select parentitemid from dbo.[NBrightBuy] where (typecode = 'CATCASCADE' or typecode = 'CATXREF') and XrefItemId = 27) and (NB3.Visible = 1) ) AS NB1 left join dbo.[NBrightBuyIdx] as NB3 on NB3.ItemId = NB1.ItemId and NB3.[Lang] = 'en-AU' left join dbo.[NBrightBuyLang] as NB2 on NB2.ParentItemId = NB1.ItemId and NB2.[Lang] = 'en-AU' WHERE row > @lbound AND row < @ubound
Which runs, but is using the Modified date.
I had changed the setting to a static list to see if that would use a different path and resolve my issue.
What is strange is that using a different browser generates different SQL!
Changing the settings so not to use Static, stops the SQL crash.
Now when running on Edge I get
DECLARE @recct int SET @recct = 3 DECLARE @lbound int, @ubound int SET @lbound = 0 SET @ubound = @lbound + 76 IF @lbound >= @recct BEGIN SET @ubound = @recct + 1 SET @lbound = @ubound - (76) END SELECT NB1.[ItemId] ,ISNULL(NB2.[XMLData],NB1.[XMLData]) as [XMLData] ,ISNULL(NB2.[Lang],ISNULL(NB1.[Lang],'')) as [Lang] ,NB1.[PortalId] ,NB1.[ModuleId] ,NB1.[TypeCode] ,NB1.[GUIDKey] ,NB1.[ModifiedDate] ,NB1.[TextData] ,NB1.[XrefItemId] ,NB1.[ParentItemId] ,NB1.[UserId] FROM ( SELECT ROW_NUMBER() OVER( order by NB4.[XMLdata].value('(genxml/sort)[1]','int'), NB3.productname ) AS row, NB1.[ItemId] ,ISNULL(NB2.[XMLData],NB1.[XMLData]) as [XMLData] ,ISNULL(NB2.[Lang],ISNULL(NB1.[Lang],'')) as [Lang] ,NB1.[PortalId] ,NB1.[ModuleId] ,NB1.[TypeCode] ,NB1.[GUIDKey] ,NB1.[ModifiedDate] ,NB1.[TextData] ,NB1.[XrefItemId] ,NB1.[ParentItemId] ,NB1.[UserId] FROM dbo.[NBrightBuy] as NB1 left join dbo.[NBrightBuyIdx] as NB3 on NB3.ItemId = NB1.ItemId and NB3.[Lang] = 'en-AU' left join dbo.[NBrightBuyLang] as NB2 on NB2.ParentItemId = NB1.ItemId and NB2.[Lang] = 'en-AU' left join dbo.[NBrightBuy] as NB4 on (NB4.TypeCode = 'CATXREF' or NB4.TypeCode = 'CATCASCADE' ) and NB4.ParentItemId = NB1.ItemId and NB4.XrefItemId = 27 WHERE NB1.TypeCode = 'PRD' and (NB1.PortalId = '1' or NB1.PortalId = '-1') and (NB1.ModuleId = '483' or NB1.ModuleId = '-1') and NB1.[ItemId] in (select parentitemid from dbo.[NBrightBuy] where typecode = 'CATXREF' and XrefItemId = 27) and (NB3.Visible = 1) ) AS NB1 left join dbo.[NBrightBuyIdx] as NB3 on NB3.ItemId = NB1.ItemId and NB3.[Lang] = 'en-AU' left join dbo.[NBrightBuyLang] as NB2 on NB2.ParentItemId = NB1.ItemId and NB2.[Lang] = 'en-AU' left join dbo.[NBrightBuy] as NB4 on (NB4.TypeCode = 'CATXREF' or NB4.TypeCode = 'CATCASCADE' ) and NB4.ParentItemId = NB1.ItemId and NB4.XrefItemId = 27 WHERE row > @lbound AND row < @ubound
Which is what I expect.