Email Subscription Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Email Subscription Problem

Author

Topic

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-09-01 : 08:17:43

Hello All Experts,

I have created email subscription for one report, which is working properly. I have scheduled it on everyday,its automatically send emails to the concern email id on everyday basis.

Now is there any way suppose when reports get executed and reports comes blank then its shouldnt send mail on that day. Is it possible or not? Because currently the report is coming blank then also its sending blank report to the concern email id.

Please guide me.

Regards,
AKM

theboyholty
Posting Yak Master

226 Posts

Posted - 2010-10-20 : 05:54:48

I would advise using a data-driven subscription. That way you can contsruct the email addresses for the recipients on the fly and if there's no data, no email address is created and it doesn't send:

I'll give an example.
Say your main report query is generating a list of new clients for the day and goes something like:

SELECT ClientName,Postcode,ContractValue
FROM Clients
WHERE SignDate = GETDATE()

Your subscription query could be something like
SELECT 'abhit.kumar@yourplace.com' as EmailAddress
FROM Clients
WHERE SignDate = GETDATE()

So if there's data, it creates an email address and if there's no data, it doesn't so the subscription fails.

From the users point of view, this simply means that they receive no email when there's no data.

I use something similar to generate a list of specific types of calls received in the last hour and my bosses don't want to receive 24 emails a day when only five or six of them might contain data.

In practice, when setting up your subscription, select data-driven subscription and most of it is fairly intuitive, but when it comes to entering a To Email Address, choose 'Get data from a Database', select EmilAddress from your query (which you pasted into the query window - You can use an SP with the command EXEC SPName) and you're onto a winner.

You can have as many email addresses as you want and also change any setting as to the users preference e.g. date parameters, export format etc.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources