I'm trying to work around the fact that SSRS will send scheduled report emails even if there is no data in the report. I thought that I might be able to use the ReportExecution2005 to execute (render) the report and then send an email if the row count is 开发者_JAVA百科greater than zero. Is this possible?
First I published the report then I created the subscription to fire only once. Once that was completed I grabbed the job name for that report subscription. Then I created a stored procedure that checked for record count and if there were records detected it fired the msdb..sp_start_job @job_name = '' which emailed me and our technical support staff. So in a nutshell... it would only notify me if and when records were detected.
DECLARE @cnt smallint
SELECT @cnt = COUNT(FieldName) FROM TableName
IF (@cnt) > 0
BEGIN
exec msdb..sp_start_job @Job_Name = 'E1F4B93A-8578-490D-BD15-3C989C3BE4F3'
END
ELSE
BEGIN
PRINT 'No Records Found!'
END
Here is a link to my post that has a query that will assist with grabbing the right job name. It works for 2005 & 2008, but you'll need to modified it if your using a named instance. http://ayesamson.com/category/sql-server/2008/reporting-services-2008/subscriptions/
Hope this helps. We use standard edition of SQL Server 2005 & 2008/R2
精彩评论