E-mail notification of scheduled activities outcome

[kakkaduan]

Hi All,

I have setup a "Publish All" macro in CAC which is basically just executing several other Publish Macros. The Publish All macro is scheduled in Cognos Connection to be executed each evening at 10. Every now and then the macro fails due to some of the subsequent macros failing, most of the times due natural causes. What I'd like to have is an notification sending me an E-mail preferably only when the scheduled execution is failed, but I guess I could live with an notification of each evenings execution.

The macro takes about 30 minutes to run so perhaps some sort of of a check could be triggered to run at 11 and then forward the result by mail somehow...

Is there an easy way to do this? What I could think of right away was to query the planning datastore and retrieve the macro runstateid and use database mail functionality to send the information to me. However if there is a more not so "backdoor" solution I'd be happy to use this instead.

Thanks in advance for your advice!

BR
Jakob

[ericlfg]

I believe there is a way to accomplish this, using an FM package with the P_Macro table, runstateid column, and Event Studio.  You would set an event as [runstateid] = 'CANCELLED' and the task would be to send an email notification.  You can then schedule this agent to trigger daily at 11pm (or whatever time you want)..

Hope this helps.

[kakkaduan]

Thanks ericlfg - helpful as always.

I did go ahead with the creation of a more SQL Server like approach. I created two stored procedures, one of them (CognosEmailnotification) which I setup a schedule for as an SQL Server Agent job to be run daily at 11 pm.
If anyone's interested, here's what the code looks like.


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[IsMacroComplete]
                      @MacroName NVARCHAR(200),
                      @IsComplete BIT OUTPUT,
                      @startdt char(29) OUTPUT,
                      @failurenote text OUTPUT
/*****************************************************************************
Author:           
Date:           2013-02-20
Description:   Retrieves last run status of the requested Macro.
Change log:
******************************************************************************/
AS

SET NOCOUNT ON

DECLARE @runstate NVARCHAR(500)


SELECT @runstate = runstateid, @startdt = startdt, @failurenote = failurenote from [PlanningStorec10].[dbo].[P_MACRO]
where macroname = @MacroName

IF (@runstate = 'COMPLETE')
           SET @IsComplete = 1
ELSE
           SET @IsComplete = 0


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[CognosEmailnotification]
                                 @MacroName NVARCHAR(200)

/*****************************************************************************
Author:           
Date:           2013-02-20
Description:   Sends an E-mail to support-team if the requested Macro wasn't completed succesfully at last run time.
Change log:
******************************************************************************/
AS

SET NOCOUNT ON

DECLARE @IsComplete BIT
DECLARE @p_body as nvarchar(max)
DECLARE @p_subject as nvarchar(max)
DECLARE @p_recipients as nvarchar(max)
DECLARE @p_profile_name as nvarchar(max)
DECLARE @startdt char(29)
DECLARE    @failurenote nvarchar(max)

EXEC IsMacroComplete @MacroName, @IsComplete OUTPUT, @startdt OUTPUT, @failurenote OUTPUT

SET @p_profile_name = 'General Administration Profile'
SET @p_recipients = 'email1@domain1.com'
SET @p_subject = 'The macro:'+@MacroName+' didnt execute succesfully '+SUBSTRING(CAST(@startdt as VARCHAR(50)),1,10)
SET @p_body = @failurenote

IF @IsComplete = 0

BEGIN

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = @p_profile_name,
  @recipients = @p_recipients,
  @body = @p_body,
  @body_format = 'HTML',
  @subject = @p_subject

END

[ericlfg]

Hey Jakob,

Looks good and thanks -- I've copied this for my own records. :)