SSRS 2005 trigger report after table has been updated/changed

Posted on 2011-10-26
Last Modified: 2012-05-12
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
Question by:MrDavidThorn
    1 Comment
    LVL 39

    Accepted Solution

    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' =,
                'SQLJobName' =
    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


    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
    Introduction Earlier I wrote an article about the new lookup functions ( that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now