CDOSYS/Maintenance plan help please!

Hi there,
I have CDOSYS email working and correclty firing off emails for configured
alerts but what i'm trying to do is get CDOSYS to send an email when a
maintenance plan job successfully backups all user databases (we have
aproximately 16). what i'm after is the same information that sql mail used
to send:
JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
run on 19/11/2006 at 19:00:01
DURATION: 0 hours, 15 minutes, 1 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by Schedule 4
(Schedule 1). The last step to run was step 1 (Step 1).
the closest i can get to this is to use the following script as the next
step after the backups have completed:
DECLARE @.msg nvarchar(4000)
Set @.msg = REPLACE("Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG] ", "'", "") --'
EXEC sp_send_cdosysmail 'fromaddress',
'toaddress',
'subject line',
@.msg
but all this produces is the following:
Error:
Severity:
Date: 20061120
Time: 111358
Database:
Message:
i dont really want to create separate jobs and alerts for each individual
database so i was wondering if anyone had any ideas at all?!
thanks in advance
stuStu,
What you can do is the following :
In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
and associate the failure or the sucess of the maitenance plan step to the
respective step ( sucess or failure ).
Best Regards,
Paulo Condeça
"Stu" wrote:
> Hi there,
> I have CDOSYS email working and correclty firing off emails for configured
> alerts but what i'm trying to do is get CDOSYS to send an email when a
> maintenance plan job successfully backups all user databases (we have
> aproximately 16). what i'm after is the same information that sql mail used
> to send:
> JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
> run on 19/11/2006 at 19:00:01
> DURATION: 0 hours, 15 minutes, 1 seconds
> STATUS: Succeeded
> MESSAGES: The job succeeded. The Job was invoked by Schedule 4
> (Schedule 1). The last step to run was step 1 (Step 1).
>
> the closest i can get to this is to use the following script as the next
> step after the backups have completed:
> DECLARE @.msg nvarchar(4000)
> Set @.msg = REPLACE("Error: [A-ERR]
> Severity: [A-SEV]
> Date: [STRTDT]
> Time: [STRTTM]
> Database: [A-DBN]
> Message: [A-MSG] ", "'", "") --'
> EXEC sp_send_cdosysmail 'fromaddress',
> 'toaddress',
> 'subject line',
> @.msg
> but all this produces is the following:
> Error:
> Severity:
> Date: 20061120
> Time: 111358
> Database:
> Message:
> i dont really want to create separate jobs and alerts for each individual
> database so i was wondering if anyone had any ideas at all?!
> thanks in advance
> stu
>|||Paulo,
thank you for that, that has worked.
stu
"Paulo Condeça" wrote:
> Stu,
> What you can do is the following :
> In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
> and associate the failure or the sucess of the maitenance plan step to the
> respective step ( sucess or failure ).
> Best Regards,
> Paulo Condeça
> "Stu" wrote:
> > Hi there,
> >
> > I have CDOSYS email working and correclty firing off emails for configured
> > alerts but what i'm trying to do is get CDOSYS to send an email when a
> > maintenance plan job successfully backups all user databases (we have
> > aproximately 16). what i'm after is the same information that sql mail used
> > to send:
> >
> > JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
> > run on 19/11/2006 at 19:00:01
> > DURATION: 0 hours, 15 minutes, 1 seconds
> > STATUS: Succeeded
> > MESSAGES: The job succeeded. The Job was invoked by Schedule 4
> > (Schedule 1). The last step to run was step 1 (Step 1).
> >
> >
> > the closest i can get to this is to use the following script as the next
> > step after the backups have completed:
> >
> > DECLARE @.msg nvarchar(4000)
> > Set @.msg = REPLACE("Error: [A-ERR]
> > Severity: [A-SEV]
> > Date: [STRTDT]
> > Time: [STRTTM]
> > Database: [A-DBN]
> > Message: [A-MSG] ", "'", "") --'
> >
> > EXEC sp_send_cdosysmail 'fromaddress',
> > 'toaddress',
> > 'subject line',
> > @.msg
> >
> > but all this produces is the following:
> >
> > Error:
> > Severity:
> > Date: 20061120
> > Time: 111358
> > Database:
> > Message:
> >
> > i dont really want to create separate jobs and alerts for each individual
> > database so i was wondering if anyone had any ideas at all?!
> >
> > thanks in advance
> >
> > stu
> >|||Stu,
You know where to post, when you have any doubt.
Best Regards,
Paulo Condeça
"Stu" wrote:
> Paulo,
> thank you for that, that has worked.
> stu
>
> "Paulo Condeça" wrote:
> > Stu,
> >
> > What you can do is the following :
> > In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
> > and associate the failure or the sucess of the maitenance plan step to the
> > respective step ( sucess or failure ).
> >
> > Best Regards,
> >
> > Paulo Condeça
> >
> > "Stu" wrote:
> >
> > > Hi there,
> > >
> > > I have CDOSYS email working and correclty firing off emails for configured
> > > alerts but what i'm trying to do is get CDOSYS to send an email when a
> > > maintenance plan job successfully backups all user databases (we have
> > > aproximately 16). what i'm after is the same information that sql mail used
> > > to send:
> > >
> > > JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
> > > run on 19/11/2006 at 19:00:01
> > > DURATION: 0 hours, 15 minutes, 1 seconds
> > > STATUS: Succeeded
> > > MESSAGES: The job succeeded. The Job was invoked by Schedule 4
> > > (Schedule 1). The last step to run was step 1 (Step 1).
> > >
> > >
> > > the closest i can get to this is to use the following script as the next
> > > step after the backups have completed:
> > >
> > > DECLARE @.msg nvarchar(4000)
> > > Set @.msg = REPLACE("Error: [A-ERR]
> > > Severity: [A-SEV]
> > > Date: [STRTDT]
> > > Time: [STRTTM]
> > > Database: [A-DBN]
> > > Message: [A-MSG] ", "'", "") --'
> > >
> > > EXEC sp_send_cdosysmail 'fromaddress',
> > > 'toaddress',
> > > 'subject line',
> > > @.msg
> > >
> > > but all this produces is the following:
> > >
> > > Error:
> > > Severity:
> > > Date: 20061120
> > > Time: 111358
> > > Database:
> > > Message:
> > >
> > > i dont really want to create separate jobs and alerts for each individual
> > > database so i was wondering if anyone had any ideas at all?!
> > >
> > > thanks in advance
> > >
> > > stu
> > >