Data Driven Subscription Error & Failures URGENT Help needed.

Question

Data Driven Subscription Error & Failures URGENT Help needed.

Knives85

SSChasing Mays

Points: 626
More actions
December 13, 2017 at 4:02 am

#402746

Hello
I have an issue with a Data-Driven Subscription (DDS) and I've exhausted all Avenues in trying to resolve it, I'm on the verge of tears and your help would be greatly appreciated.
Basically I have a Report that takes 8 Data Parameters, I have set up a data-driven subscription that is powered by a script that pulls 8 dates from my Calendar table, these are returned on a single row. I am then going through the DDS wizard and setting all the columns to the appropriate parameters and running the subscription. The problems now start. The Subscription isn't running and the Status is just showing Done: 1 processed of 1 total; 1 errors. This error means nothing and when I'm looking online all I'm getting is go to the Trace Log on the RS Server and take a look. Unfortunately, I don't have access to the reporting server to check the logs, is there another way I can check them to find out the error?
I have also taken the dates generated by my script and manually copied them into the parameters, when I do this it the DDS works fine and generates a report; this makes me think its something to do with how my script is generating the dates in the first place, I have tried casting as DateTime to be in the safe and I've output them into a table and checked they're column type and they are showing as DateTime.
Below is the Calendar script that's powering my DDS. If you can help I'd love you forever 🙂
SET DATEFORMAT DMY
SELECT LatestDay = CAST(LatestDay AS DATETIME), LatestWeekStart = CAST(LatestWeekStart AS DATETIME), LatestWeekEnd = CAST(LatestWeekEnd AS DATETIME), PreviousWeekStart = CAST(PreviousWeekStart AS DATETIME), PreviousWeekEnd = CAST(PreviousWeekEnd AS DATETIME), ChartOffSetStart = CAST(ChartOffSetStart AS DATETIME), ChartOffSetEnd = CAST(ChartOffSetEnd AS DATETIME), YTDStart = CAST(YTDStart AS DATETIME), YTDEnd = CAST(YTDEnd AS DATETIME)
FROM ( SELECT LatestDay = (SELECT MIN(Cal_Date) FROM tblDimCalendar cal WHERE Cal_Date = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()-2), 0)), LatestWeekStart = (SELECT MIN(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinWeek = 0), LatestWeekEnd = (SELECT MAX(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinWeek = 0 AND Cal_Date <= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()-2), 0)), PreviousWeekStart = (SELECT MIN(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinWeek = -1), PreviousWeekEnd = (SELECT MAX(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinWeek = -1), ChartOffSetStart = (SELECT MIN(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinWeek = -6), ChartOffSetEnd = (SELECT MAX(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinWeek = -1), YTDStart = (SELECT MIN(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinYear = 0), YTDEnd = (SELECT MAX(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinYear = 0 AND Cal_Date <= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()-2), 0)) ) qry

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

You say "This error means nothing". What is "this" error? 🙂

Also, don't forget, we can't run your SQL, we don't have access to you server or data, so we don't know the output of your above query.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Knives85 SSChasing Mays Points: 626 More actions · Answer 2

Thanks for the quick response. The error message is

Done: 1 processed of 1 total; 1 errors (see screenshot below)

Also, this is the output of my Calendar Code

Do you need anything else ???

Thanks a lot

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Knives85 - Wednesday, December 13, 2017 4:14 AM
Thanks for the quick response. The error message is
Done: 1 processed of 1 total; 1 errors (see screenshot below)
Also, this is the output of my Calendar Code
Do you need anything else ???
Thanks a lot

You might be able to get some information if you have access to the ExecutionLog3 view - it would at least tell you row count, time data retrieval and processing. But you really need to check the Reporting Services log to see if you can find more details.

Sue

sql Sarah SSCrazy Points: 2081 More actions · Answer 4

I have a feeling that it doesn't like the subquery. Maybe this would work?

SET DATEFORMAT DMY

Declare @LatestDaydatetime

,@LatestWeekStartdatetime

,@LatestWeekEnddatetime

,@PreviousWeekStart datetime

,@PreviousWeekEnddatetime

,@ChartOffSetStart datetime

,@ChartOffSetEnddatetime

,@YTDStartdatetime

,@YTDEnddatetime

SELECT

@LatestDay = (SELECT MIN(Cal_Date) FROM tblDimCalendar cal WHERE Cal_Date = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()-2), 0)),

@LatestWeekStart = (SELECT MIN(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinWeek = 0),

@LatestWeekEnd = (SELECT MAX(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinWeek = 0 AND Cal_Date <= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()-2), 0)),

@PreviousWeekStart = (SELECT MIN(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinWeek = -1),

@PreviousWeekEnd = (SELECT MAX(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinWeek = -1),

@ChartOffSetStart = (SELECT MIN(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinWeek = -6),

@ChartOffSetEnd = (SELECT MAX(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinWeek = -1),

@YTDStart = (SELECT MIN(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinYear = 0),

@YTDEnd = (SELECT MAX(Cal_Date) FROM tblDimCalendar cal WHERE Cal_RelFinYear = 0 AND Cal_Date <= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()-2), 0))

SELECT

LatestDay= @LatestDay

LatestWeekStart= @LatestWeekStart

LatestWeekEnd= @LatestWeekEnd

PreviousWeekStart= @PreviousWeekStart

PreviousWeekEnd= @PreviousWeekEnd

ChartOffSetStart= @ChartOffSetStart

ChartOffSetEnd= @ChartOffSetEnd

YTDStart= @YTDStart

YTDEnd= @YTDEnd