Something like Data Driven Subscriptions SSRS Stan

问题:

I'm fairly new to MSSQL and SSRS.

I'm trying to create a data driven subscription in MSSQL 2008 Standard SSRS that does the following: I need to send out/email which includes report to my users just in case the report has data in it. This would use the list of recipients and must work for multiple results with multiple email address's.

The way I'm thinking of doing this is making a subscription based on result of the query. If query doesn't return any result then email wouldn't go out. But if a result is found then email with report will be sent out.

Any suggestions on how to that or if you can suggest something that already out there on the internet with a description?

Also in the link below I found similar to what I need, but I have difficulties with writing RunADDR. Any help is greatly appreciated. Mike Christie solution: Data Driven Subscriptions SSRS Standard Edition 2008

回答1:

You could create a sql agent job that executes a regular subscription. Look up the subscription Id and in your SQL Agent job write a query to the tune of

if exists (select * from Table)
BEGIN
EXECUTE msdb.dbo.sp_start_job 'subscription_job_ID_here'
END

This would send out the non data driven subscription if there is data on the table for the if exists and not if there is no data.

回答2:

You can use a data-driven subscription to do what you want. The data-driven query would be something like:

IF EXISTS (<your report dataset>)
    SELECT 'recipient emails' as ToAddress
    FROM MyEmailList
ELSE
    SELECT 'no one' as ToAddress

Then in the email settings you select this column for the "TO:" address. Of course you can have multiple emails and any other parameters you want to add.