SQL Email Alert Example

USE [cabinet]

GO

 

/****** Object:  StoredProcedure [dbo].[sp_send_AuditHistoryHasValuesEmail_email]    Script Date: 7/27/2016 8:20:28 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

ALTER procedure [dbo].[sp_send_CabinetLogFillAlert_email]

as

Begin

 

declare @recordCount int

 

DECLARE @temptable TABLE(

LogDate DATETIME,

ProcessInfo NVARCHAR(MAX),

[Text] NVARCHAR(MAX)

)

 

INSERT INTO @temptable

Exec sp_readerrorlog 0,1

 

--DROP TABLE @temptable

 

 

select @recordCount = isnull(count([Text]), 0)

FROM @temptable where [Text] like '%The transaction log for database ''cabinet'' is full%%' and  DATEDIFF(HOUR,[LogDate], GETDATE()) <= 1;

 

 

IF (@recordCount > 0)

begin

 

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'MAILSERVER',

    @recipients = 'DATA_Alert@crouse.org',

    @query = 'select @recordCount = isnull(count(*), 0)

FROM @temptable where [Text] like ''%The transaction log for database ''cabinet'' is full%%'' and  DATEDIFF(HOUR,[LogDate], GETDATE()) <= 1 ORDER BY [LogDate];',

      @subject = 'Cabinet is FULL Email ',

       @Body = 'See if this is causing an issue..... ' ,

    @attach_query_result_as_file = 1 ;

 

End/*

else

begin

 

      EXEC msdb.dbo.sp_send_dbmail

      @profile_name = 'MAILSERVER',

       @recipients = 'SQLAlertManageengine@crouse.org',

            @BODY = 'No data returned ',

            @subject = 'AuditHistory Email'

 

End*/

End;

 

GO