I was trying to find a alert which could fire immediately upon any of the job failure in the server to send an email. Unfortunately I couldn’t find any the alert related to this. So I thought of writing a DML trigger which should fire immediately when an entry is inserted in sysjobhistory table on msdb database. The next step is that the trigger should send a mail when there is failure in the job, hence I’ve used run_status column in the table, as all the DBA’s know when this column has a value 0 then it states the job has been failed. I’ve tried this script in SQL Server greater than version 2000 however it’s not working on it. This script works only on SQL Server 2000, still some of them are still working on SQL 2000 so I thought this script has still value. This DML trigger will send an email upon any of the job failure.

Applies To

• SQL Server 2000

Script

In the script below make sure to change the mail address used in the recipients field. You also need SMTP mail to be configured in SQL Server to work however you can modify the script to include CDO messaging.

/*

Written By : Vidhya Sagar

www.sql-articles.com

*/

USE MSDB

GO

CREATE TRIGGER jobfailurenotify

ON sysjobhistory

FOR INSERT

AS

DECLARE @failed TINYINT, @subject VARCHAR(300), @body VARCHAR(500)

SELECT @failed = COUNT(*) from inserted where run_status=0

IF @failed > 0

BEGIN 

    SELECT @subject=name from msdb.dbo.sysjobs where job_id in (select distinct(job_id) from inserted where run_status=0)

    SET @subject=@subject + ' job failed on ' + CONVERT(VARCHAR(25), GETDATE())

    SET @body=@subject + '. Please look in to this. Server Name : ' + CONVERT(VARCHAR(25),SERVERPROPERTY('SERVERNAME'))

    EXEC master.dbo.xp_sendmail

    @recipients     = N'kvs1983@gmail.com',

    @subject        = @subject,

    @message           = @body

END

ELSE 

BEGIN

    PRINT 'No job failures found'

END

Sample Mail Output

From:    testing@gmail.com

Sent:    Monday, March 02, 2009 6:50 PM

To:    kvs1983@gmail.com

Subject:    TESTING job failed on Mar  2 2009  6:50PM

TESTING job failed on Mar  2 2009  6:50PM. Please look in to this. Server Name : SAGARSYS

If you have any concerns or queries regarding this script. Just post it out in our Forums Section.

I started side by side upgrading SQL 2000 to SQL 2005, at that time I need to detach all the user databases from the source server and need to attach the same in destination server. Source server has almost 80 user databases and I feel bored to detach all the database one by one which is time consuming too.. Hence I thought of writing a script which will help me to detach the user databases in a single stretch. I know that someone will face this situation, hence I’m posting my script here which will help you!!

/*

Script to detach all user databases

Written by Vidhya Sagar

www.sql-articles.com

*/

set nocount on

declare @dbname as varchar(80)

declare @server_name as varchar(20)

select @server_name = @@servername

declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in ('model','master','msdb','tempdb','distribution','repldata')

open rs_cursor

Fetch next from rs_cursor into @dbname

IF @@FETCH_STATUS <> 0

PRINT 'No User databases found!!!'

WHILE @@FETCH_STATUS = 0

BEGIN

Declare @exec varchar(8000)

Set @exec= 'DECLARE @spidstr varchar(8000)

DECLARE @ConnKilled smallint

SET @ConnKilled=0

SET @spidstr = ''''

SELECT @spidstr=coalesce(@spidstr,'','' )+''kill ''+convert(varchar, spid)+ ''; ''

FROM master..sysprocesses WHERE dbid=db_id('''+@dbname+''')

IF LEN(@spidstr) > 0

BEGIN

EXEC(@spidstr)

SELECT @ConnKilled = COUNT(1)

FROM master..sysprocesses WHERE dbid=db_id('''+@dbname+''')

END'+char(10)+';'+char(10)+

'Exec sp_detach_db ' + @dbname

Exec (@exec)

print 'Detach of ' + upper(@dbname) + ' database successfully completed'

print ''

FETCH NEXT FROM rs_cursor INTO @dbname

END

CLOSE rs_cursor

deallocate rs_cursor

print ' '

print upper(@server_name) + '--> All User databases successfully detached'

Sample Output:

Detach of NORTHWIND database successfully completed

Detach of PUBS database successfully completed

SAGARSYS\SQL2K–> All User databases successfully detached

Usage:

Copy the above script and execute it, this will automatically detach all the user databases and you will receive the output as above!!

If you have any concurrence or discussion in the script above, lets discuss on SQL-Articles Forums section.