Generating Replication alerts using SMTP.

I cannot use SQL Mail due to the requirement of outlook client installation
on the server (that I cannot do). The other option is to use SMTP.
I have tried to use xp_smtp_sendmail by adding tasks in the system generated
jobs but it doesn't seem to work.
Anyone any ideas?
Regards,
MZeeshan
Ive used it just fine. I made these procs because I needed to add a job step
named "Alert" to every job on every server that I have. Keep in mind this
will change the way your job steps are run, and may not be applicable to all
jobs. (Say if they have 5 steps, and steps 1 - 4 are Quit with Failure when
the step fails. )
Use msdb
go
ALTER procedure admin_AddAlertJobStep
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.command varchar(256)
set @.command = 'exec admin_AlertFailedJob '''+ @.JobName
+''','''+@.Recipient+''''
exec sp_add_jobstep @.job_name = @.JobName
, @.step_name = 'Alert'
, @.command = @.Command
, @.on_success_action = 2
, @.on_fail_action = 2
declare @.StepId int
set @.StepId = (select max(step_id) - 1 from SysJobSteps sjs
inner join SysJobs sj on sjs.job_id = sj.job_id
where sj.Name = @.JobName)
exec sp_update_jobstep
@.job_name = @.JobName
, @.step_id = @.StepId
, @.on_fail_action = 3
use master
go
ALTER procedure admin_AlertFailedJob
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.rc int
declare @.ServerName varchar(256)
set @.ServerName = (select @.@.ServerName)
set @.JobName = @.JobName + ' failed!'
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.ServerName
,@.TO = @.Recipient
,@.Subject = @.JobName
,@.server = N'address'
,@.Port = 25
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:50D1D59D-BA02-46A3-96FE-1770B00E15D8@.microsoft.com...
>I cannot use SQL Mail due to the requirement of outlook client installation
> on the server (that I cannot do). The other option is to use SMTP.
> I have tried to use xp_smtp_sendmail by adding tasks in the system
> generated
> jobs but it doesn't seem to work.
> Anyone any ideas?
> --
> Regards,
> MZeeshan

Generating Replication alerts using SMTP.

I cannot use SQL Mail due to the requirement of outlook client installation
on the server (that I cannot do). The other option is to use SMTP.
I have tried to use xp_smtp_sendmail by adding tasks in the system generated
jobs but it doesn't seem to work.
Anyone any ideas?
--
Regards,
MZeeshanIve used it just fine. I made these procs because I needed to add a job step
named "Alert" to every job on every server that I have. Keep in mind this
will change the way your job steps are run, and may not be applicable to all
jobs. (Say if they have 5 steps, and steps 1 - 4 are Quit with Failure when
the step fails. )
Use msdb
go
ALTER procedure admin_AddAlertJobStep
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.command varchar(256)
set @.command = 'exec admin_AlertFailedJob '''+ @.JobName
+''','''+@.Recipient+''''
exec sp_add_jobstep @.job_name = @.JobName
, @.step_name = 'Alert'
, @.command = @.Command
, @.on_success_action = 2
, @.on_fail_action = 2
declare @.StepId int
set @.StepId = (select max(step_id) - 1 from SysJobSteps sjs
inner join SysJobs sj on sjs.job_id = sj.job_id
where sj.Name = @.JobName)
exec sp_update_jobstep
@.job_name = @.JobName
, @.step_id = @.StepId
, @.on_fail_action = 3
use master
go
ALTER procedure admin_AlertFailedJob
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.rc int
declare @.ServerName varchar(256)
set @.ServerName = (select @.@.ServerName)
set @.JobName = @.JobName + ' failed!'
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.ServerName
,@.TO = @.Recipient
,@.Subject = @.JobName
,@.server = N'address'
,@.Port = 25
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:50D1D59D-BA02-46A3-96FE-1770B00E15D8@.microsoft.com...
>I cannot use SQL Mail due to the requirement of outlook client installation
> on the server (that I cannot do). The other option is to use SMTP.
> I have tried to use xp_smtp_sendmail by adding tasks in the system
> generated
> jobs but it doesn't seem to work.
> Anyone any ideas?
> --
> Regards,
> MZeeshan

Generating Replication alerts using SMTP.

I cannot use SQL Mail due to the requirement of outlook client installation
on the server (that I cannot do). The other option is to use SMTP.
I have tried to use xp_smtp_sendmail by adding tasks in the system generated
jobs but it doesn't seem to work.
Anyone any ideas?
Regards,
MZeeshanIve used it just fine. I made these procs because I needed to add a job step
named "Alert" to every job on every server that I have. Keep in mind this
will change the way your job steps are run, and may not be applicable to all
jobs. (Say if they have 5 steps, and steps 1 - 4 are Quit with Failure when
the step fails. )
Use msdb
go
ALTER procedure admin_AddAlertJobStep
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.command varchar(256)
set @.command = 'exec admin_AlertFailedJob '''+ @.JobName
+''','''+@.Recipient+''''
exec sp_add_jobstep @.job_name = @.JobName
, @.step_name = 'Alert'
, @.command = @.Command
, @.on_success_action = 2
, @.on_fail_action = 2
declare @.StepId int
set @.StepId = (select max(step_id) - 1 from SysJobSteps sjs
inner join SysJobs sj on sjs.job_id = sj.job_id
where sj.Name = @.JobName)
exec sp_update_jobstep
@.job_name = @.JobName
, @.step_id = @.StepId
, @.on_fail_action = 3
use master
go
ALTER procedure admin_AlertFailedJob
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.rc int
declare @.ServerName varchar(256)
set @.ServerName = (select @.@.ServerName)
set @.JobName = @.JobName + ' failed!'
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.ServerName
,@.TO = @.Recipient
,@.Subject = @.JobName
,@.server = N'address'
,@.Port = 25
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:50D1D59D-BA02-46A3-96FE-1770B00E15D8@.microsoft.com...
>I cannot use SQL Mail due to the requirement of outlook client installation
> on the server (that I cannot do). The other option is to use SMTP.
> I have tried to use xp_smtp_sendmail by adding tasks in the system
> generated
> jobs but it doesn't seem to work.
> Anyone any ideas?
> --
> Regards,
> MZeeshan

generate a email using smtp server (was "Help with query....")

Help with query: I currently cannot be alerted by SQL Mail so I would like to take the script that was generated by SQL server and using the store procedure sp_sqlsmtpmail to generate a email using smtp server to alert me. The store procedure does work. I would like to know if this is possible.

Thanks

Lystra

-- Script generated on 1/21/2005 10:04 AM
-- By: MAMSI\sa
-- Server: (local)

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Demo: Full tempdb'))
-- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @.name = N'Demo: Full tempdb'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @.name = N'Demo: Full tempdb', @.message_id = 9002, @.severity = 0, @.enabled = 1, @.delay_between_responses = 10, @.include_event_description_in = 5, @.database_name = N'tempdb', @.category_name = N'[Uncategorized]'

Then

Exec sp_sqlsmtpmail
@.vcTo = 'lwilliams@.Huc.com',
@.vcBody ='Check out problem Immediataly.',
@.vcSubject ='DOCSITE01FDK - Full Tempdb Log'

ENDYou can have the alert execute a job that has the call to sp_sqlsmtpmail in one of it's steps.|||Great idea, thanks.

Lystra