SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Driven Subscription Error & Failures URGENT Help needed.


Data Driven Subscription Error & Failures URGENT Help needed.

Author
Message
Knives85
Knives85
Old Hand
Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)

Group: General Forum Members
Points: 351 Visits: 322
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 Smile


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


Thom A
Thom A
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82523 Visits: 21130
You say "This error means nothing". What is "this" error? Smile

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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Knives85
Knives85
Old Hand
Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)

Group: General Forum Members
Points: 351 Visits: 322
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
Sue_H
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72635 Visits: 14933
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
sql Sarah
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1683 Visits: 609
I have a feeling that it doesn't like the subquery. Maybe this would work?

SET DATEFORMAT DMY

Declare @LatestDay datetime

,@LatestWeekStart datetime

,@LatestWeekEnd datetime

,@PreviousWeekStart datetime

,@PreviousWeekEnd datetime

,@ChartOffSetStart datetime

,@ChartOffSetEnd datetime

,@YTDStart datetime

,@YTDEnd datetime


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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search