Best way of getting notifications in SQL Server Reporting Services using Notification Services

Question

Is it possible to get notifications using SQL Server Reporting Services? Say for example I have a report that I want by mail if has for example suddenly shows more than 10 rows or if a specific value drop below 100 000. Do I need to tie Notification Services into it and how do I do that?

Please provide as much technical details as possible as I've never used Notification Services before.

Someone also told me that Notifications Services is replaced by new functionality in Reporting Services in Sql Server 2008 - is this the case?

Answer 1

I wouldn't go down the ntofications services route - it is pretty much a deprecated feature of SQL Server and even if it is around in future it will stagnate. So don't build a dependency on it.

Answer 2

Depending on your needs a data driven SSRS subscription to e-mail you the report would probably work. http://msdn.microsoft.com/en-us/library/ms159150(SQL.90).aspx

Answer 3

Sending mail using SSRS subscription to your data driven report

Answer 4

I'd agree with Simon re Notification Services

Also, data driven SSRS Subscriptions are not available unless you use Enterprise Edition (and isn't available if you use SharePoint Integrated Mode).

An alternate way would be to create an Agent job that runs a proc. The proc could check the conditions you require and kick off the subscription if they are met using:

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx'

Where the @EventData is the ID of the subscription in dbo.Subscriptions.

This will drop a row in [dbo].[Event]. The Service polls this table a few times a minute to kick off subscriptions.

Really, this isn't far from what happens when you set up a new Subscription, might even be easier to create a Subscription in the Report Server site, find which agent job was created (the ones with GUID names) and edit the T-SQL.

Hope this helps