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

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

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

/****** 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

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)

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

Many thanks!