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


DO not Email When there's no Data with two datasets


DO not Email When there's no Data with two datasets

Author
Message
eobiki10
eobiki10
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 369
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.
Kevin Galbreath-373973
Kevin Galbreath-373973
Mr or Mrs. 500
Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)

Group: General Forum Members
Points: 542 Visits: 1368
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.
eobiki10
eobiki10
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 369
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
eobiki10
eobiki10
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 369
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
eobiki10
eobiki10
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 369
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
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