SQL Jobs Failed in the Past 24 hours

----- SQL Jobs Failed in the Past 24 hours.
----- This will create a SQL Job that will send out an email with a list of Jobs that have failed over the past 24 hours.
/*

PLEASE SET VALUES FOR THE FOLLOWING PARAMETERS:
@SMTPServerIP = ''SMTP-SERVER-IP-ADDRESS''
@FromEmailAddress= ''FROM-EMAIL-ADDRESS''
@FromName = ''FROM-NAME''
@EmailSubject = ''Jobs failed on SQL Server'' - Change this to what you want it to be.
@ToEmailAddress = ''TO-EMAIL-ADDRESS'' - Semi-colon seperated string
@AttachmentFile = ''C:\jobs.txt'' - Make sure this path exists

*/

USE msdb

GO

BEGIN TRANSACTION

DECLARE
@JobID BINARY(16)
, @ReturnCode INT

SELECT
@ReturnCode = 0

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT
@JobID = job_id
FROM
msdb.dbo.sysjobs
WHERE
(name = N'JobsFailedToday')

IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''JobsFailedToday'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'JobsFailedToday'
SELECT
@JobID = NULL
END

BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID OUTPUT
, @job_name = N'Jobs Failed Today'
, @owner_login_name = N'sa'
, @description = N'List of all SQL Jobs that failed today.'
, @category_name = N'[Uncategorized (Local)]'
, @enabled = 1
, @notify_level_email = 0
, @notify_level_page = 0
, @notify_level_netsend = 0
, @notify_level_eventlog = 2
, @delete_level= 0

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID
, @step_id = 1
, @step_name = N'Jobs Failed Today'
, @command = N'-- ALL SQL Code
DECLARE
@Month varchar(2)
, @LDate varchar(15)

SELECT
@Month = CONVERT(varchar, month(GETDATE()))

IF LEN(@Month) < 2
BEGIN
SELECT
@LDate = CONVERT(varchar, YEAR(GETDATE())) + ''0'' + CONVERT(varchar, month(GETDATE())) + CONVERT(varchar, day(GETDATE()))
END
ELSE
BEGIN
SELECT
@LDate = CONVERT(varchar, YEAR(GETDATE())) + CONVERT(varchar, month(GETDATE())) + CONVERT(varchar, day(GETDATE()))
END

----- Send Email from SQL Server without using Outlook
----- Step 1: DECLARE variables
DECLARE
@SMTPServerIP varchar(16)
, @FromEmailAddress varchar(100)
, @FromName varchar(100)
, @ToEmailAddress varchar(300)
, @SQL varchar(8000)
, @SQLQuery varchar(2000)
, @EmailSubject varchar(1000)
, @AttachmentFile varchar(200)

----- Step 2: SET value for variables
SELECT
@SMTPServerIP = ''SMTP-SERVER-IP-ADDRESS''
, @FromEmailAddress= ''FROM-EMAIL-ADDRESS''
, @FromName = ''FROM-NAME''
, @EmailSubject = ''Jobs failed on SQL Server''
, @ToEmailAddress = ''TO-EMAIL-ADDRESS'' ----- Semi-colon seperated string
, @AttachmentFile = ''C:\jobs.txt'' ----- Local Path to SQL Server

----- Step 3: Define query to be run for Email message if needed
SELECT
@SQLQuery = ''SELECT Name AS JobName FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B '' +
'' WHERE A.job_id = B.job_id AND B.last_run_outcome = 0 AND last_run_date = '' + CONVERT(varchar, @LDate)

----- Step 4: Define final SQL Statement
SELECT
@SQL = ''osql -E-w500 -Q "'' + @SQLQuery + ''" -o "C:\Jobs.txt"''

----- Step 5: EXECUTE SQL Statement
EXEC master.dbo.xp_cmdshell @SQL

----- Step 6: Send File only if there is some data in the file
----- Step 6:a: Create temporary table that will hold details for xp_getfiledetails results
CREATE TABLE #FileDetails (
AlternateName varchar(33)
, Size int
, CreationDate int
, CreationTime int
, LastWrittenDate int
, LastWrittenTime int
, LastAccessedDate int
, LastAccessedTime int
, Attributes int
)

----- Step 6:b: INSERT data in temporary table
INSERT INTO #FileDetails
EXEC master.dbo.xp_getfiledetails ''C:\jobs.txt''

IF (SELECT Size FROM #FileDetails) > 0
BEGIN
----- Step 6:c: Send Email with File
EXEC master.dbo.xp_SMTP_SendMail
@From = @FromEmailAddress
, @From_Name = @FromName
, @To = @ToEmailAddress
, @Subject = @EmailSubject
, @MessageFile = @AttachmentFile
, @Server = @SMTPServerIP
END
ELSE
BEGIN
----- Step 6:d: Send Email with File
EXEC master.dbo.xp_SMTP_SendMail
@From = @FromEmailAddress
, @From_Name = @FromName
, @To = @ToEmailAddress
, @Subject = @EmailSubject
, @Message = ''No Jobs Failed Today''
, @Server = @SMTPServerIP
END

----- Step 6:e: DROP temporary table
DROP TABLE #FileDetails


GO '
, @database_name = N'master'
, @server = N''
, @database_user_name = N''
, @subsystem = N'TSQL'
, @cmdexec_success_code = 0
, @flags = 0
, @retry_attempts = 0
, @retry_interval = 1
, @output_file_name = N''
, @on_success_step_id = 0
, @on_success_action = 1
, @on_fail_step_id = 0
, @on_fail_action = 2

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_update_job
@job_id = @JobID
, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID
, @name = N'Jobs Failed Today'
, @enabled = 1
, @freq_type = 8
, @active_start_date = 20060101
, @active_start_time = 74500
, @freq_interval = 127
, @freq_subday_type = 1
, @freq_subday_interval = 0
, @freq_relative_interval = 0
, @freq_recurrence_factor = 1
, @active_end_date = 99991231
, @active_end_time = 235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID
, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

COMMIT TRANSACTION

GOTO EndSave
QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave: