SQL Server Performance

Proc for detecting Reporting Services email subscription failures

Discussion in 'Contribute Your SQL Server Scripts' started by JonM80, Jul 8, 2008.

  1. JonM80 New Member

    Hey Guys,
    I have a job that runs this Proc every morning. Not sure if anyone has come up with a better way to detect failures but, here is my solution. You'll need to enable xp_cmdshell. Hope this can help someone.
    USE [ReportServer]
    GO
    /****** Object: StoredProcedure [dbo].[CheckForSubFailures] Script Date: 07/08/2008 13:15:03 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: JonM80
    -- Create date: 06/08/07
    -- Description: Check For Subscription Failures, Every Morning if they exist, Then send Description(s) in email
    -- =============================================
    CREATE PROCEDURE [dbo].[CheckForSubFailures]
    -- Add the parameters for the stored procedure here

    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    if exists(select usr.UserName,cat.[Name] as ReportName,sub.Description,
    cat.[Path] as ReportPath from Subscriptions sub
    inner join Users usr on Sub.OwnerId = usr.userID
    inner join [catalog] cat on cat.ItemID = sub.Report_OID
    where sub.LastStatus like 'Failure%' and sub.InactiveFlags = '0' )
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ReportManagerBot',
    @recipients = 'email-addresses',
    @query = 'select ReportServer.dbo.Users.UserName,
    ReportServer.dbo.[Catalog].[Name] as ReportName,ReportServer.dbo.Subscriptions.Description,
    ReportServer.dbo.[Catalog].[Path] as ReportPath
    from ReportServer.dbo.Subscriptions
    inner join ReportServer.dbo.Users on
    ReportServer.dbo.Subscriptions.OwnerId = ReportServer.dbo.Users.userID
    inner join ReportServer.dbo.[Catalog] on
    ReportServer.dbo.[Catalog].ItemID = ReportServer.dbo.Subscriptions.Report_OID
    where ReportServer.dbo.Subscriptions.LastStatus like ''Failure%'' and ReportServer.dbo.Subscriptions.InactiveFlags = ''0''',
    @subject = 'Some Reporting Services Subscriptions Have Failed',
    @attach_query_result_as_file = 0 ;

    END
  2. satya Moderator

    Appreciate your idea to share the solution, moving to appropriate section.

Share This Page