February 7, 2014 at 9:55 am
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.
February 7, 2014 at 2:04 pm
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.
February 10, 2014 at 5:27 am
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
February 13, 2014 at 8:21 am
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
March 21, 2014 at 4:09 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply