Vulnerability in EPiServer.Forms

Try our conversational search powered by Generative AI!

Exception in OrderContext.Current.FindPurchaseOrdersByStatus

Vote:
 

Hi,

After updating my site from v11.2.2 to v11.3.0, OrderContext.Current.FindPurchaseOrdersByStatus started throwing an exception.

It says {"Exception in ecf_Search_PurchaseOrder: "} with an Inner Exception of {"Incorrect syntax near 'OFFSET'.\r\nInvalid usage of the option NEXT in the FETCH statement."}. Is this a known error or could it be that something went wrong during the update?

I've run Update-EPiDatabase without errors and everything else seems to be working fine.

#185309
Nov 15, 2017 10:00
Vote:
 

Hi,

It might be a bug in our side. Can you post the complete SP call (with parameters) which caused the error? We will look into it asap

#185313
Nov 15, 2017 10:26
Vote:
 

Copy pasted from SQL Server Profiler:

declare @p8 int
set @p8=0
exec ecf_Search_PurchaseOrder @SQLClause=N'(Status = ''OnHold'')',@MetaSQLClause=N'',@OrderBy=N'',@Namespace=N'Mediachase.Commerce.Orders',@Classes=N'PurchaseOrder',@StartingRec=0,@NumRecords=2147483647,@RecordCount=@p8 output
select @p8

#185315
Nov 15, 2017 10:41
Vote:
 

I tried that and it works well for me (even with different status). Can you post the content of your ecf_OrderSearch here?

#185317
Nov 15, 2017 10:49
Vote:
 

/****** Object: StoredProcedure [dbo].[ecf_OrderSearch] Script Date: 11/15/2017 10:50:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ecf_OrderSearch]
(
@SQLClause nvarchar(max),
@MetaSQLClause nvarchar(max),
@OrderBy nvarchar(max),
@Namespace nvarchar(1024) = N'',
@Classes nvarchar(max) = N'',
@StartingRec int,
@NumRecords int,
@RecordCount int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @query_tmp nvarchar(max)
DECLARE @FilterQuery_tmp nvarchar(max)
DECLARE @TableName_tmp sysname
DECLARE @SelectMetaQuery_tmp nvarchar(max)
DECLARE @FromQuery_tmp nvarchar(max)
DECLARE @FullQuery nvarchar(max)
DECLARE @SelectQuery nvarchar(max)
DECLARE @CountQuery nvarchar(max)
-- 1. Cycle through all the available product meta classes
--print 'Iterating through meta classes'
DECLARE MetaClassCursor CURSOR READ_ONLY
FOR SELECT TableName FROM MetaClass
WHERE Namespace like @Namespace + '%' AND ([Name] in (select Item from ecf_splitlist(@Classes)) or @Classes = '')
and IsSystem = 0
OPEN MetaClassCursor
FETCH NEXT FROM MetaClassCursor INTO @TableName_tmp
WHILE (@@fetch_status = 0)
BEGIN
--print 'Metaclass Table: ' + @TableName_tmp
set @Query_tmp = 'select META.ObjectId as ''Key'' from ' + @TableName_tmp + ' META'

-- Add meta Where clause
if(LEN(@MetaSQLClause)>0)
set @query_tmp = @query_tmp + ' WHERE ' + @MetaSQLClause
if(@SelectMetaQuery_tmp is null)
set @SelectMetaQuery_tmp = @Query_tmp;
else
set @SelectMetaQuery_tmp = @SelectMetaQuery_tmp + N' UNION ALL ' + @Query_tmp;
FETCH NEXT FROM MetaClassCursor INTO @TableName_tmp
END
CLOSE MetaClassCursor
DEALLOCATE MetaClassCursor
-- Create from command
SET @FromQuery_tmp = N' INNER JOIN (select distinct U.[KEY] from (' + @SelectMetaQuery_tmp + N') U) META ON OrderGroup.[OrderGroupId] = META.[KEY] '
set @FilterQuery_tmp = N' WHERE 1=1'
-- add sql clause statement here, if specified
if(Len(@SQLClause) != 0)
set @FilterQuery_tmp = @FilterQuery_tmp + N' AND (' + @SqlClause + ')'

if(Len(@OrderBy) = 0)
begin
set @OrderBy = ' OrderGroupId DESC'
end
set @SelectQuery = N'SELECT OrderGroupId' +
' FROM dbo.OrderGroup OrderGroup ' + @FromQuery_tmp + @FilterQuery_tmp + ' ORDER BY ' + @OrderBy +
' OFFSET ' + cast(@StartingRec as nvarchar(50)) + ' ROWS ' +
' FETCH NEXT ' + cast(@NumRecords as nvarchar(50)) + ' ROWS ONLY ;';
set @CountQuery= N'SET @RecordCount= (SELECT Count(1) FROM dbo.OrderGroup OrderGroup ' + @FromQuery_tmp + @FilterQuery_tmp +');';
set @FullQuery = @CountQuery+ @SelectQuery;
--print @FullQuery
exec sp_executesql @FullQuery, N'@RecordCount int output', @RecordCount = @RecordCount OUTPUT
SET NOCOUNT OFF
END

#185319
Nov 15, 2017 10:58
Vote:
 

Ah, what version of SQL Server are you running? Commerce 11 requires SQL Server 2012 and up (and that's the minimum version to support FETCH NEXT)

#185323
Nov 15, 2017 11:28
Vote:
 

Oh, that explains it. I'm currently running 2008 R2. Guess it's upgrade time. :)

Many thanks!

#185324
Nov 15, 2017 11:31
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.