Database mail config of sqlserver 2000 using facility of sqlserver 2005

To configure database mail if any of ur jobs in sqlserver 2000 fails using the facility of sqlserver 2005

First step is to run this script in sqlserver 2005

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Job alert Account',
@description = 'Job Alert',
@email_address='MailID',
@display_name = 'Job Status Alert',
@username='From Mail_ID',
@password='password',
@mailserver_name = 'Ip address of the mail server'


EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Job alert',
@description = 'Job alert mail account'


EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Job alert',
@account_name = 'Job alert Account',
@sequence_number = 1


EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Job alert',
@principal_name = 'public',
@is_default = 1 ;

declare @body1 varchar(100)
set @body1 = 'Server :'+ ' Email Alert '
EXEC msdb.dbo.sp_send_dbmail @recipients=' To MailId',
@subject = 'Job Failed Mail Status',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT * from databasename.dbo.Temp_Job_Fail' ,
@attach_query_result_as_file = 1 ;

Second step

Created two tables on 2000 and 2005 server ---called--Temp_Job_Fail

Third step

Create a Link Server so that the data from the table(in sqlserver 2000) can be inserted into the table(in sqlserver 2005)

Fourth Step

Paste the below given query on last step or any step of the job as per the requirement of the each job on sqlserver 2000

insert into Temp_Job_Fail
select b.name,b.originating_server,a.run_date,a.message,a.step_name from msdb.dbo.sysjobhistory a,msdb.dbo.sysjobs b
where a.job_id = b.job_id and
a.message like '%Error%' and
a.run_date = convert(varchar(10),getdate(),112)
and a.message not like '%DBCC printed error messages%'

(INSERT INTO [Linked Server].Databasename.DBO.Temp_Job_Fail---2005 server
SELECT * FROM Temp_Job_Fail -- 2000 server)

Fifth Step

Create a trigger on server 2005--- to run the Job on table Temp_Job_Fail

CREATE TRIGGER trg_Insert_Temp_Job_Fail ON Temp_Job_Fail AFTER INSERT AS
BEGIN
if (SELECT NAME from DATABASENAME.dbo.Temp_Job_Fail)is not null
begin
EXEC msdb.dbo.sp_start_job @job_name = 'MailSendingForJobFailedin2000'
end
else
Print 'Issue'

END

Sixth Step

Create a job on sqlserver 2005 which will send the mail.
On the First step of the job Copy this query

declare @body1 varchar(100)
set @body1 = 'Server :'+ ' Email Alert '
EXEC msdb.dbo.sp_send_dbmail @recipients=' To MailId',
@subject = 'Job Failed Mail Status in sqlserver 2000',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT * from databasename.dbo.Temp_Job_Fail' ,---output of the query will be send in your mail
@attach_query_result_as_file = 1 ;

On the Second step copy this query

Truncate table DATABASENAME.dbo.Temp_Job_Fail