开发者

ReportExecution2005 - Can you get the row count for a rendered report?

开发者 https://www.devze.com 2023-01-27 03:00 出处:网络
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

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

0

精彩评论

暂无评论...
验证码 换一张
取 消