Cognos Impromptu 7 Scheduler

[galaxy0815]

Hi,

I have a Problem with the Impromptu Scheduler. It runs several Reports at different times. After a Report has run, It will be list up on the "Completed" site of the scheduler. The problem is that some reports have really run and for some nothing happens. But on the completed site there is allways written no errors. But there must be some errors because not all the reports run automatically (But they where listed on the completed site with no errors). Can somebody help me with this problem?

Is there for example an error logfile of impromptu? If yes, where?

Or has somebody a macro which creates such a file with all the errors that occur?

Thanks in advance for your help.

 

[drlex]

Galaxy,
In order to log problems, you might find it better to run reports by way of scheduled macros and log the problems therein. I log start and end time of each macro in a csv file, together with the error codes of any problems.

At the end of each day, a routine loads the contents of the csv file to a DB for archiving and clears the file.

Sample code:

Sub Main()
   Dim Logfile as string
   Logfile = "C:\MacroLog.csv"
   Open Logfile For APPEND As #1
   Write #1, Format(DATE,"yyyy-MM-dd"),Format(Time,"HH:mm:ss"), "<Macro Name> Start"
   Close #1
   on error goto errmes
   
   ' Code that runs the report
   
endmac:
   Open Logfile For APPEND As #1
   Write #1, Format(DATE,"yyyy-MM-dd"),Format(Time,"HH:mm:ss"), "<Macro Name> End"
   Close #1   
Exit Sub

ErrMes:
   ' code to send email
   Open Logfile For Append As #1
   Write #1, Format(DATE,"yyyy-MM-dd"),Format(Time,"HH:mm:ss"), "Error " & Err & " @ line: " & Erl & " - " & Error$ 
   Close #1   
   Resume endmac  
End Sub

soi la, soi carré

 

[galaxy0815]

Can you please show me the full code of your macro. I will see if I can use that or not.
(Thanks very much, I think this would help me to find the errors of the reports that do not run)

Where did you scheduled this macro? directly in the scheduler from Impromptu?

Thanks very much for your help

 

[drlex]

Galaxy
Macro is scheduled in Scheduler. You can, if you prefer, call it from Windows scheduler via a batch file:

C:
CD\Progra~1\Cognos\cer5\bin\
runmac32.exe "mymacro.mcx"

(macro placed in same path as executable)

The full macro you requested sight of:

Sub Main()
   
   'variables
   Dim strSubject as string
   Dim strbody as string
   Dim strTo as string
   Dim strprompt as string
   Dim strfileloc as string
   Dim strfilename as string
   Dim Logfile as string
   'objects
   Dim objImpApp As object
   Dim objImpRep As Object 
   Dim objOutlookEmail As Object
   '
   on error goto errmes
   '
   strfileloc = "K:\xl\Schemes\"
   strprompt = Format(CStr(CVar(Date)-1),"dddd, d Mmmm")
   strfilename = strfileloc + "Schemes for " & strprompt & ".xls"
   Logfile = "C:\MacroLog.csv"
   '
   Open Logfile For APPEND As #1
   Write #1, Format(DATE,"yyyy-MM-dd"),Format(Time,"HH:mm:ss"), "Nefarious Schemes Start"
   Close #1
   '
   Set objImpApp = CreateObject("CognosImpromptu.Application")
   objImpApp.OpenCatalog "\\Acmefile01\cognos\catalogues\sql.cat","Creator",,,,1
   Set objImpRep = objImpApp.OpenReport ("\\Acmefile01\cognos\reports\World Domination Plans.imr")
   Set objImpRep = objImpApp.ActiveDocument
   objImpRep.ExportExcelWithFormat strfilename
   objImpRep.CloseReport
   objImpApp.Quit
   Set objImpApp = Nothing
   Set objImpRep = Nothing
   '
endmac:
   Open Logfile For Append As #1
   Write #1, Format(DATE,"yyyy-MM-dd"),Format(Time,"HH:mm:ss"), "Nefarious Schemes End"
   Close #1   
Exit Sub
   '
ErrMes:
   strSubject = "Nefarious Schemes macro FAILURE"
   strBody = "Please note that the macro failed with error " & Err & " : " & Error$ & " which occurred at line: " & Erl
   strTo = "Dr.Lex@Acme.com"
   Set objOutlookEmail = CreateObject("CDONTS.NewMail")
   with objOutlookEmail
      .To = strTo
      .From = "TheBrain@Acme.com"
      .Subject = strSubject
      .Body = strBody
      .MailFormat = 0
      .Send
   End with
   set objOutlookEmail = nothing
   Open Logfile For Append As #1
   Write #1, Format(DATE,"yyyy-MM-dd"),Format(Time,"HH:mm:ss"), "Error " & Err & " @ line: " & Erl & " - " & Error$ 
   Close #1
   Resume endmac  
End Sub

soi la, soi carré

 

[galaxy0815]

Thanks very much. I will try if I get that worked.

Last 2 Questions:
Du you know a good archiv where you can find helpful macros for cognos impromptu?
Does the cognos impromptu scheduler only show error messages when for example a connection to the database fails or does it also show error messages when the creation of an .xls file fails on the harddrive?

Again, thanks very much for your help.

 

[drlex]

1. Look at the Samples folder in the path of your Cognos installation - there are sample macros to examine. See also the pdf documentation (Documentation folder) for macros and cognos script. The help file in the Macro editor appears to contain much of the pdf content, and has been of assistance to me.
2. As far as I know, the Cognos scheduler merely records whether a task has been run or not, and does not record error codes. That is why I suggested using a macro and writing error codes to a log file. The inability to find a file or write a file will be logged as errors; the failure of the DB when opening a catalogue may well 'hang' Impromptu and not trigger an error code. In my case, a separate, initial, routine checks the DB availability and connectivity prior to starting the Scheduler.

soi la, soi carré

 

[galaxy0815]

Sorry but I have one last Question:

Where do you schedule this macro in the scheduler?

When I schedule a report, I enter the path oh the report, name of the task, outputtype etc. The is also a field where you can attach a macro to that Task.
Is this the place where you insert the macro (so you insert this macro in each scheduled task) or can you schedule it in general that it works for all tasks?

Thanks for you help

 

[drlex]

[g]galaxy[/b],
Schedule macros in Scheduler as one would inserting a report task; just change the 'Files of Type' from 'Report' to 'Macro' in the drop-down list at the bottom of the 'File Name' window when inserting a new task.

I would recommend that you read 'schd.pdf' and 'schd_mac.pdf' which should be in the documentation folder of your install (default path likely to be C:\Program Files\Cognos\cer5\documentation, where cer5 is a v7.3 install).

soi la, soi carré

 

[galaxy0815]

Hello (derlex),

Your script was very good for me to understand how to produce such a macro.

But I have one problem with your script. It also does not save all the errors which could occur.

If for example the report(the .xls file) with the filename TestReport 2007.01.10.xls (this should be the file the marco produces) exists, and I open the file, and than I re-run my macro on the same day while I have the Report open, then there should be an error in saving the file because the macro tries to overwrite a file which is open in Excel This must produce an error because the saving is impossible.

But there is no errormessage in the csv File. So it does not produce the error in the csv-file.

Could it be that the macro does not catch the errors which have to do with file operations?

What I want is that for this example the macro should tell me in the csv file that it could not save the report (or it should show me the errormessage from the system)

I want really all errors to be catched in the csv file.

Could somebody (maybe derlex) help me with this?

Thanks a lot for your help in advance

 

[drlex]

galaxy,

You must have a slightly different configuation to me, as the macro is happy to overwrite an open Excel file. Indeed, the only warning I get is when I close the file, and Excel opens a window to inform me that

The file <name> may have been changed by another user since you last saved it. In that case, what do you 
O [u]S[/u]ave a copy
O [u]O[/u]verwrite changes
OK    Cancel

(grammar as displayed - bad Redmond!)

You might want to consider the use of the KILL command on the output file (and path) to test whether to proceed - if the file is open, then the macro will report an error 75 "Path/File access error".

soi la, soi carré

 

[galaxy0815]

Hello,

I do not really understand what you mean with this.

The macro cannot update a xls report which is open. (you can see that on the modified date of the xls file. The errorlog csv file says everything is ok, so no error logged, but the modiefied date of the xls file was not updated) So in my opinion this must produce an error. The thing I want is that all error should be catched by the macro (database errors,fileoperation errors etc.)

Otherwise I can not be sure for 100% that the report has really been updated.

How can I change the macro so that all errors will be catched?

And when does the macro run this part: on error goto errmes?
What is on error? Only a database error?

Thanks a lot for you help in advance.
This really helps me

 

[drlex]

galaxy,

I do not really understand what you mean with this.

I mean that I do not have the same problem as you do if the excel file is open when the macro writes to it. Whether this is due to the version of OS, Cognos or network, I obviously can't tell (and don't really want to investigate). I suspect it is because in testing, I am both the reader and the writer of the file, and so the OS allows it.

However, let's ignore that and look instead at a solution or work-around. If you believe that there is a chance that the file may be open at the time the macro is run, then the use of the KILL command would allow a test of whether the file is available for deletion prior to being written.

Since the KILL command on a 'locked-for-write' file will trigger an error, you can determine whether your ON ERROR statement results in an exit of the macro or perhaps saving with a slightly different file name. The exit or resumption can be determined by considering the error code - 75 in this case.

Alternatively, if your file name includes a date and a time in HH-mm-SS, you are likely to prevent the situation of generating the same file name ever occuring.

I'm afraid I can't answer a detailed question on Cognos applications and error codes; you might like to use the CognosScript Help file and type 'Trappable Errors' in order to view codes and some programming logic in trapping errors. If nothing else, it will explain better the suggestion of changing file names on an error 75 situation, as I describe above.

I'm sure that gives you enough to work on. Good luck.

soi la, soi carré