[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

SSRS 2005 trigger report after table has been updated/changed

Hi Experts

I want to trigger a SSRS 2005 report when a table has been updated, how can I do this?  - I can do timed report updates but Believe I have to create a stored procedure to do this. Any help would be appricated
0
MrDavidThorn
Asked:
MrDavidThorn
1 Solution
 
lcohanDatabase AnalystCommented:
You can put a trigger on the table to fire ON UPDATE - but make sure is a speciffic update byt using IF UPDATED(col_name)... and start the job that refreshes the SSRS report. You can use the code below on the SSRS sql box to get the association between report name and job that triggers the SSRS execution or snapshot updated but keep in mind that every change/save you make to the report in ReportManager
will result in a new job name(GUID like) so you need to include some code like below in the trigger to get the jobname from the report name then use MSDB.[dbo].[sp_start_job] to fire that job.

select      distinct
            'ReportName' = c.name,
            'SQLJobName' = j.name
from ReportServer.dbo.Subscriptions s
      inner join ReportServer.dbo.Catalog c on c.ItemID = s.Report_OID
      inner join ReportServer.dbo.Users us on us.UserID = s.OwnerId
      inner join msdb.dbo.sysjobs j on j.job_id = (select job_id from msdb.dbo.sysjobsteps where command like '%'+CONVERT(nvarchar(128),s.Report_OID)+'%')
      inner join msdb.dbo.sysjobschedules js on j.job_id=js.job_id
      inner join msdb.dbo.sysschedules sc on js.schedule_id=sc.schedule_id

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now