locked
Only Generate Email If Table Contains Data RRS feed

  • Question

  • I am wanting to fire-off an email with the failed jobs anytime they are deposited into a table.  My syntax fires off an email even when the table does not contain data, it just sends a blank email.  Can someone assist me so that this will only generate an email if teh table contains data?  

    if exists (Select  from FailedJobs)
    exec msdb.dbo.sp_send_dbmail
    @profile_name = 'DatabaseMail'
    @recipients = 'asdfasdfsdf@aafas.com'
    @from_address = 'asdfasdfacasca@cc.com'
    @query = 'Select * from failedjobs'
    @subject = 'List Of Failed Jobs'
    @attach_query_result_as_file = 1;

    Wednesday, July 1, 2015 3:28 PM

Answers

  • DECLARE @recordCount INT;
    SELECT  @recordCount = COUNT(*)
    FROM   FailedJobs
    
    IF @recordCount > 0
    BEGIN
    exec msdb.dbo.sp_send_dbmail
    @profile_name = 'DatabaseMail'
    @recipients = 'asdfasdfsdf@aafas.com'
    @from_address = 'asdfasdfacasca@cc.com'
    @query = 'Select * from failedjobs'
    @subject = 'List Of Failed Jobs'
    @attach_query_result_as_file = 1;
    END;
    
    



    Wednesday, July 1, 2015 3:50 PM

All replies

  • DECLARE @recordCount INT;
    SELECT  @recordCount = COUNT(*)
    FROM   FailedJobs
    
    IF @recordCount > 0
    BEGIN
    exec msdb.dbo.sp_send_dbmail
    @profile_name = 'DatabaseMail'
    @recipients = 'asdfasdfsdf@aafas.com'
    @from_address = 'asdfasdfacasca@cc.com'
    @query = 'Select * from failedjobs'
    @subject = 'List Of Failed Jobs'
    @attach_query_result_as_file = 1;
    END;
    
    



    Wednesday, July 1, 2015 3:50 PM
  • try this

    IF EXISTS(SELECT TOP 1 * FROM FailedJobs)
    BEGIN
    exec msdb.dbo.sp_send_dbmail
        @profile_name = 'DatabaseMail',
        @recipients = 'asdfasdfsdf@aafas.com',
        @from_address = 'asdfasdfacasca@cc.com',
        @query = 'Select * from failedjobs',
        @subject = 'List Of Failed Jobs',
        @attach_query_result_as_file = 1;
    END
    

    Wednesday, July 1, 2015 4:29 PM
  • This will do it for you.

    USE [YOUR_DB]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[Table_Name]
    
    AS
    
    BEGIN TRY
    
    INSERT [dbo].[Table_Production] 
    Select *
    FROM [dbo].[Table_Staging]
    
    SET NOCOUNT ON
    declare @RowCount as int 
    declare @EmailBody as varchar(1000)
    SELECT @RowCount = count(*) FROM [dbo].[Table_Staging] 
    set @EmailBody = 'Successfully Loaded TABLE' + cast(@RowCount as varchar(50)) + ' records loaded'
    
    IF @RowCount > 0
    BEGIN
    	exec msdb.dbo.sp_send_dbmail @profile_name = 'MailTest', 
    	 @recipients = 'your_name@firm.com',
    	 @subject = 'Successfully Production', 
    	 @body = @EmailBody,
    	 @body_format = 'HTML'
    END
    
    END TRY
    
    BEGIN CATCH
    	-- SELECT ERROR_NUMBER() AS ErrorNumber;
    	exec msdb.dbo.sp_send_dbmail @profile_name = 'MailTest', 
    	 @recipients = 'your_name@firm.com', 
    	 @subject = 'WARNING did NOT load Production', 
    	 @body = 'WARNING did NOT load Production + @@RowCount ', 
    	 @body_format = 'HTML'
    
    END CATCH;
    
    

    Regards.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, July 5, 2015 2:25 AM