Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DO not Email When there's no Data with two datasets Expand / Collapse
Author
Message
Posted Friday, February 7, 2014 9:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:51 AM
Points: 80, Visits: 332
Hi All,

I have a report containing two tablixes from two datasets. A standard subscription was initially created on this report but I have noticed recently that it emails out even when the report does not contain any data. How do I stop this? I have read suggestions that this can only be achieved using a data driven subscription but am little bit confused on how to define the query that will drive this data driven subscription based on the fact that the report has two datasets in it. Any help will be appreciated.
Post #1539269
Posted Friday, February 7, 2014 2:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:42 AM
Points: 455, Visits: 1,055
Data driven subscriptions also require the enterprise edition of SQL Server.

This is a bit of a roundabout solution but what I usually do is...

1.) Create a subscription for the report that is scheduled to only run once in the past. This way it will never actually be triggered by SSRS.

2.) All subscriptions in SSRS are actually SQL Server jobs. So I create a job on the SQL Server where the SSRS databases are located that runs the same SQL as the report and checks to see if any rows are returned. If there are then the job starts the job that equates to the SSRS subscription.

You can find the job that equates to the subscription by querying dbo.Subscriptions in the ReportServer database.
Post #1539393
Posted Monday, February 10, 2014 5:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:51 AM
Points: 80, Visits: 332
Thanks Kevin, this is a little bit simpler.

I am testing the job at the moment to see how it works out.

Below is my SQL in the job step

IF @@rowcount > 0

BEGIN

EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData= '73a492d4-a44c-48ec-b13f-781c6d61e313' -- subscriptionID

END
Post #1539705
Posted Thursday, February 13, 2014 8:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:51 AM
Points: 80, Visits: 332
Hi ,

The code that I used in the job did not work for me. Can you let me know the code to check if any rows are returned.

Thanks,

EO
Post #1541232
Posted Friday, March 21, 2014 4:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:51 AM
Points: 80, Visits: 332
Saw this my posting and decided to put the solution that worked for me.

I created the subscription normal and putting an end date to the subscription because it wil now be triggered by the job. Then created a job using the same subscription name so I can track my job easily. Then using the code in the job step.

IF Exists (My SQl Code here which is the same code that I used in the report)

BEGIN


EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData= '08F7299B-ED9A-4F30-A06B-B2A5A8F3D608' -- subscription Id

END

To easily get the subscription Id of my report and all other subscription ids.

I created a report using this code

SELECT name
,subscriptionid
,itemid
,ExtensionSettings

FROM ReportServer.dbo.Subscriptions s
INNER JOIN ReportServer.dbo.[Catalog] c ON c.ItemID = s.Report_OID
WHERE Deliveryextension = 'Report Server Email'
ORDER BY name


Hope this helps someone.

EO





Post #1553392
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse