Skip to content

Instantly share code, notes, and snippets.

@FembotDBA
Created June 12, 2013 23:22
Show Gist options
  • Save FembotDBA/5769987 to your computer and use it in GitHub Desktop.
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
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