Get an SSRS report to trigger a table update on completion
My problem: I want a weekly report to check a table for any records that have a flag set to say there has been an update since it last ran and to note the change that was made. On completion I would like the flag to be reset so that the next time it runs, that same change is not reported again. What I've done so far is find the job created by the subscription for that report that is emailing it to the appropriate users and add a second step that invokes an SP that resets all the flags. This is a very flimsy workaround because any time a user makes a change to that subscription to, say, add additional recipients, my update step gets lost. So can anyone think of a better way that I can invoke my reset after the snapshot is taken and the email is sent?Nick Ryan SQL Server Developer OnePath (NZ) Lts
July 1st, 2011 12:57am

Why dont you create a stored procedure that gets called when running the report, and use the stored procedure to do the table update when it completes
Free Windows Admin Tool Kit Click here and download it now
July 1st, 2011 1:30am

I have an idea. Create a new Job that polls ReportServer.dbo.ExecutionLog2 view for the report execution. If it finds an entry then it triggers your reset SP. Or You can create a trigger that does the work for you select * from reportServer.dbo.ExecutionLog2 where RequestType='Subscription' order by 1 desc Spandan
July 1st, 2011 2:10am

The solution provided by ClareMoore is the correct way to implement your requirement.. Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA Blog: Mr. Wharty's Ramblings MC ID: Microsoft Transcript Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
Free Windows Admin Tool Kit Click here and download it now
July 2nd, 2011 12:58am

Hey Jeff, You are right that ClareMoores solution is correct. What if report rendering got timed out? The stored procedure returned data and reset the flags but user was unable to see the report. I had faced this timeout problem on my UAT SSRS server. SP returned 300,000 records in 10 secs. But while rendering it got timed out. There were no expression in report only simple data dump. Spandan Buch
July 3rd, 2011 7:59am

Thanks very much. I had thought that I couldn't put the flag reset process in the same SP as the one that provides the data for the report but I see that the SELECT followed by the UPDATE does exactly what I want. Easy when you know how. Cheers, NickNick Ryan SQL Server Developer OnePath (NZ) Lts
Free Windows Admin Tool Kit Click here and download it now
July 3rd, 2011 5:30pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics