Credits:
Author: Sorna Kumar Muthuraj
Date: 9 Jul 2011
Description
Retrieves a summary of the subscription notifications sent for one or many reports.
NOTE: It is assumed that the Report Server Database has its default name of 'ReportServer'
Code
Stored Procedure:
|
DROP PROC usp_GetReportNotificationSummary
GO
CREATE PROC usp_GetReportNotificationSummary @ReportName NVARCHAR(850) = NULL
AS
/****************************************************
Purpose: To retrieve a summary of the subscription notifications sent
for one or many reports.
Uses the Catalog, Notifications and ActiveSubscriptions tables
in the report server database (assumed to be called by its default
name of 'ReportServer')
Author: Sorna Kumar Muthuraj
History: 9 Jul 2011 - Initial Issue
*****************************************************/
SELECT
c.ItemID AS ReportID,
C.Name AS ReportName,
C.Path AS ReportFolder,
S.Description AS SubscriptionDescription,
N.NotificationEntered AS NotificationEntered,
A.TotalNotifications AS TotalNotifications,
A.TotalSuccesses AS TotalSuccesses,
A.TotalFailures AS TotalFailures
FROM ReportServer.dbo.Notifications N (NOLOCK)
INNER JOIN ReportServer.dbo.ActiveSubscriptions A
ON N.SubscriptionID = A.SubscriptionID
AND N.ActivationID = A.ActiveID
INNER JOIN ReportServer.dbo.Catalog C (NOLOCK)
ON C.ItemID = N.ReportID
INNER JOIN ReportServer.dbo.Subscriptions S (NOLOCK)
ON S.SubscriptionID = N.SubscriptionID
WHERE c.Name LIKE '%' + ISNULL(@ReportName, '') + '%'
ORDER BY
C.Name,
S.Description
|
