I created a new report in JasperSoft Studio (Which runs perfectly fine in preview mode, and in SQL Server Management Studio)..
However, I get an error when trying to run from Clarity Advanced Reporting.
Here is part of the Query that I believe is causing the issue (Removed rest due to containing secured information) :
SET NOCOUNT ON DECLARE @start_date DATE = 'Jan 1, 2011'; DECLARE @end_date DATE = dateadd(YEAR,40,@start_date); DECLARE @Nwkdy AS TABLE (dtday DATE); WITH AllDays AS ( SELECT @start_date AS DtDay , MONTH(@start_date) AS mnth , DAY(@start_date) AS dayofM , DATENAME(weekday, @start_date) AS dtname UNION ALL SELECT DATEADD(DAY, 1, DtDay) , MONTH(DATEADD(DAY, 1, DtDay)) AS mnth , DAY(DATEADD(DAY, 1, DtDay)) AS dayofM , DATENAME(weekday,DATEADD(DAY, 1, DtDay)) AS dtname FROM AllDays WHERE DtDay < @end_date ) INSERT INTO @Nwkdy SELECT DtDay FROM AllDays WHERE ( (mnth =1 AND dayofM = 1) -- new years OR (mnth =5 AND dayofM >= 25 AND dtname = 'Monday') -- memorial day OR (mnth =7 AND dayofM = 4) -- Indendence day OR (mnth =9 AND dayofM <= 7 AND dtname = 'Monday') -- Labour day OR (mnth =11 AND dayofM BETWEEN 22 AND 28 AND dtname = 'Thursday') -- Thanksgiving OR (mnth =12 AND dayofM = 25) -- Xmas ) OPTION (MAXRECURSION 0)
Any help would be appreciated.
AGAIN : This runs perfectly fine in JasperSoft Studio Preview AND SQL Server Management Studio.