Created
June 12, 2013 23:22
-
-
Save FembotDBA/5769987 to your computer and use it in GitHub Desktop.
Microsoft SQL Server 2005 e-mail if job completes but not the nightly one
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE [DATABASE NAME] | |
GO | |
/****** Object: StoredProcedure [etl].[spp_EmailETLComplete] Script Date: 06/12/2000 16:15:33 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE PROC [etl].[spp_EmailETLComplete] | |
AS | |
DECLARE @etljobtime DATETIME | |
SET @etljobtime = CONVERT(DATETIME,CONVERT(VARCHAR,GetDate(),101) + ' 03:00:00') | |
--current nightly job runs at 3am, I don't want to send an email to everyone when that job runs | |
DECLARE @originator VARCHAR(255) | |
DECLARE @origintime DATETIME | |
SET @origintime = (SELECT TOP(1) etl_run_date | |
FROM etl.etl_history ORDER BY id DESC) | |
DECLARE @elapsedtime VARCHAR(100) | |
SET @elapsedtime = CONVERT(VARCHAR(100),datediff(mi,@origintime,GETDATE())) | |
--PRINT @elapsedtime --for debugging | |
IF GETDATE() NOT BETWEEN @etljobtime AND DATEADD(MINUTE,45,@etljobtime) | |
--if this is called between 3am and 3:45am, don't send e-mail | |
BEGIN | |
--determine last user to run etl | |
SET @originator = (SELECT TOP(1) ETL_User | |
FROM etl.etl_history ORDER BY id DESC) | |
DECLARE @msgBody VARCHAR(max) | |
SET @msgBody = '<font face="Verdana, Arial, Helvetica, sans-serif" size="2">The ETL Process completed, Report cache cleared and intelligent cubes are refreshing currently. ' | |
SET @msgBody = @msgBody + 'Any reports accessing the cubes may need a few more minutes.<br><br>' | |
SET @msgBody = @msgBody + 'The ETL Request originated from the web Finance Tools Interface by ' + @originator + ' at ' + convert(varchar(100),@origintime) + ', and completed in ' + @elapsedtime + ' minutes.</font>' | |
EXEC msdb.dbo.sp_send_dbmail | |
@recipients='AD Distribution List', | |
--@recipients='testaccount@domain.com', --for debugging | |
@subject = 'ETL Run Notification', | |
@body = @msgBody, | |
@body_format = 'HTML', | |
@profile_name = 'MAIL PROFILE' | |
END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment