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

Destination directory for SQL Server 2012 is greyed out

.\setup.exe /Action=Install /INSTANCEDIR="D:\Microsoft SQL Server"

Here is the list of SQL Server 2012 setup paramters:

https://technet.microsoft.com/en-us/library/ms144259(v=sql.110).aspx