August 22, 2007 at 7:22 am
I have a report created using MS SQL Server 2005 reporting services and deployed to the report server. I added a subscription for the report to be scheduled to run and emailed to someone on a regular basis. But now we want the report emailed ONLY when there is data returned. If no data is returned after the report runs then don't email it.
Does anyone know how to do that?
Thanks!
September 24, 2007 at 9:23 am
Hi Jenny,
Have you solved this ? Have the same need (to email a report ONLY when there is data).
Thanks, Steve.
September 25, 2007 at 2:07 am
I've done this by basing the subscription query on a table that gets updated by the stored procedure on which the report is based.
September 25, 2007 at 6:57 am
See the thread at http://www.sqlservercentral.com/Forums/Topic402212-150-1.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 25, 2007 at 9:00 am
That's pretty much what I did.
I add a subscription for the report to be emailed to an email address and schedule the report to run once. Query the table on report server to get the ScheduleID. And then create a sp like this:
CREATE PROC p_RunJobOnlyWhenThereIsData
AS
exact report query here
...
if @rowcount > 0
begin
exec msdb..sp_start_job @job_name = 'D7BADF75-A9BC-487B-B874-2BEF2F4A7E0A'
end
GO
Then create a SQL Server agent job to run this script (sp) once a week. So if there is data returned the report will run and be emailed to the person with that address and if there is no data returned the report will not run based on the condition set in the sp.
The only problem I have is that since the query for the report talks to a database on a sql server so I have to add linked server from report server to that database server. But it seems like I can't use variables in the query if it runs against a linked server. I got a message like "Internal SQL server error." when I execute this query (the query for report):
declare @ndate int
select @ndate = max(ndate) from darius.autocount.dbo.ac2007
select l.tstate, count(distinct a.tstate) as statecount
from darius.Autocount.dbo.lk_tstate_list l
left join darius.Autocount.dbo.ac2007 a
on l.tstate = a.tstate
and a.ndate= @ndate
group by l.tstate
having count(distinct a.tstate) < 1
order by 2
I don't know why. If I can solve this part then it will be perfect.
Thanks!
September 25, 2007 at 9:15 am
Jenny,
Can you successfully run the query against the linked server without using variables? What version of SQL Server is each server? There are issues with linked servers between 2005 and 2000.
Lastly, I have found that it is usually more efficient to create a stored procedure on the linked server and call that. This guarantees the processing is all done on the linked server and only passes back the data needed on the original server. So I would create a stored procedure on the liked server like:
Create Procedure usp_statecounts
AS
declare @ndate int
select @ndate = max(ndate) from autocount.dbo.ac2007
select l.tstate, count(distinct a.tstate) as statecount
from Autocount.dbo.lk_tstate_list l
left join Autocount.dbo.ac2007 a
on l.tstate = a.tstate
and a.ndate= @ndate
group by l.tstate
having count(distinct a.tstate) < 1
order by 2
Then in p_RunJobOnlyWhenThereIsData I would call darius.autocount.dbo.usp_statecounts
I usually don't use the column # in my order by either. I would use order by statecount.
Please post your results.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 25, 2007 at 9:41 am
Jack,
Yes I can successfully run the query against the linked server without using variables.
Yes the database server is 2000 and the report server is 2005. That explains why. And I agree with your way which would solve my problem and is more efficient as well.
Thanks!
September 26, 2007 at 5:41 am
You can actually do this through reporting services directly. The data-driven subscriptions allow you to specify a list of recipients (and other options such as parameter values) through a query. You then specify a schedule in which the subscription will run the query and then distribute a report for each row returned in the query.
The trick is, if the query returns no results, the report will not be sent. So, if your recipient list query only returns records if the report will return records (using an IF EXISTS, or some other way of checking), you can essentially get the data driven subscription to not send out any reports when they would be blank.
The nice thing about doing this is that you do not have to manually create SQL agent jobs and contend with making changes to a job that was created by reporting services.
September 26, 2007 at 6:32 am
Michael Earl (9/26/2007)
You can actually do this through reporting services directly. The data-driven subscriptions allow you to specify a list of recipients (and other options such as parameter values) through a query. You then specify a schedule in which the subscription will run the query and then distribute a report for each row returned in the query.The trick is, if the query returns no results, the report will not be sent. So, if your recipient list query only returns records if the report will return records (using an IF EXISTS, or some other way of checking), you can essentially get the data driven subscription to not send out any reports when they would be blank.
The nice thing about doing this is that you do not have to manually create SQL agent jobs and contend with making changes to a job that was created by reporting services.
Michael,
This is an excellent idea! I thought about using a data-driven subscription in my case, but did not consider putting an "If exists" in that query. Of course you have to be using Enterprise Edition in order to use Data-Driven subscriptions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 26, 2007 at 7:27 am
Yes I know data-driven subscription can handle this. But just because we do not have Enterprise edition of Reporting Services (we use Standard Edition) we have to come up with an alternate way.
September 26, 2007 at 7:36 am
Yup - we have all worked for the cheap company that refuses to pony up the coin for the correct edition of SQL.
I hope they don't ask you to create your own clustering failover solution or some way to do online re-indexing in standard edition.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply