Macro Hangs

[SymbionA]

I have DTS package that calls a Macro detailed below.

-----------------------------------------
'File Constants
Global Const ReportName = "AM_AC1_pdf"
Global Const ReportInputPath = "d:\gains\erm\mayne\reports\input"
Global Const ReportOutputPath = "e:\reports\PLANNER_AC1"
Global Const GAINSCatalogPath = "d:\gains\erm"
Global Const GAINSCatalog = "gainssql2005.cat"
Global Const dbUserId = "sa"
Global Const dbUserPwd = "sa"

Sub Main()

Dim objImpApp as Object
Dim objImpRep as Object
Dim objPDFPub as Object
Set objImpApp = CreateObject("Impromptu.Application")
objImpApp.Visible 0
objImpApp.OpenCatalog GAINSCatalogPath & "\" & GAINSCatalog , "Creator", , dbUserId, dbUserPwd
Set objImpRep = objImpApp.OpenReport (ReportInputPath &"\"& ReportName & ".imr")
objImpRep.Reexecute
Set objPDFPub = objImpRep.PublishPDF
objPDFPub.Publish ReportOutputPath &"\"& ReportName &""+ "_"+ mid$(date$,7,4)+ mid$(date$,1,2)+ mid$(date$,4,2) +".pdf"
objImpRep.CloseReport
objImpApp.Quit
Set objImpApp = Nothing
End Sub

-----------------
However, the process hangs, I have tried debuggin this and it tells me that "This action cannot be completed because the other program is busy...."

I think there is a problem with the CreateObject... I have tried "Set objImpApp = CreateObject("CognosImpromptu.Application")" but the same thing happens.

What is wrong?

Thanks for your help in advance

 

[billnted]

You debugging with f8 step-through? stick some error trapping in the macro - 'on error' - and that oughta help. Otherwise - got Impromptu open already in single session? Got two versions installed? Not registered inmpromptu in config. manager? Tried putting .cer4 (or whatcha version) after 'Application' in the Createobject call?
l8rs

 

[SymbionA]

Thanks for your reply.

Not at work now, however, I did put it through debug and it stuck on

Set objImpApp = CreateObject("Impromptu.Application")

In the debug window it give me the message that i can described above. Not sure how I tackle your suggestions of

"got Impromptu open already in single session? Got two versions installed? Not registered inmpromptu in config. manager?"

Perhaps you will elaborate on these points on how I can do these checks?

Many thanks, this problem has been buggin me for days!!

 

[billnted]

r-i-i-i-ght!

*deep breath*

If your macro fails, it's coz it's not opening impromptu. This could be for a number of reasons.
- you might have impromptu set to single session and a session open (perhaps invisible - check task manager). See the "read me" about switching single to multiple sessions and then type C:\Progra~1\Cognos\cer4\bin\ImpAdmin.exe /MI on a DOS prompt. (cer3 for 7.1,cer4 for 7.3 and so on)

- you have two versions of impromptu installed on your 'box. System can't figure out which one to open - cue Spock-like logical indecision and freeze. Uninstall the obsolete one.

- you haven't 'registered' impromptu by running Cognos configuration manager, so the Registry is missing the link that allows OLE operation to work as expected. Run Cognos configuration manager or at DOS prompt, type C:\Progra~1\Cognos\cer4\bin\ImpAdmin.exe /regserver (assuming you've got admin ver of Impromptu 7.3)

and exhale.
l8rs.

 

[SymbionA]

Thank you for your response.

Indeed we had have it at single user session and I now have changed it to multi session.

We have only one version of Impromptu loaded on the machine

I have run Cognos Configuration Manager.

I still have the problem, at this stage I am wondering if the registry is corrupt and causing the server is busy error loop. I may have to re-install!

Any other suggestions?

Thanks!

 

[richardh9920]

I would suggest that you use anything (that's ANYTHING) other than the Cognos Script Editor to debug your macro.

If you have it you can use Visual Basic (6.0 works fine and is 100% compatible). Failing that create a macro in MS-Excel and cut and paste your script into it.

The reason you are better off using a non-Cognos environment is that you will get better error messages and much better stability.

As for your problem, it sounds like it is doing something or it would fail with an automation error. I would switch to "objImpApp.Visible 1" so you can see what is really happening.

Also, I have never come across a Reexecute method to run a report. I think I always use RetrieveAll.

 

[SymbionA]

I am using Excel and created the Macro on my PC not the server.

Of course ActiveX component is not located on my PC for Impromptu and the server has no applications installed.

Is there a way round it?

 

[SymbionA]

Thanks!

I am using Excel and created the Macro on my PC not the server.

Of course ActiveX component is not located on my PC for Impromptu and the server has no applications installed.

Is there a way round it?

 

[drlex]

SymbionA
I note that you state that this is a DTS package, so I take it that you are running SQL server. Using a copy of SS 2000 (patched to date), I tried a simple package with an ActiveX Task like so:

Function Main()
	Dim objImpApp 
	Dim objImpRep
	Dim objPDFPub 
	Set objImpApp = CreateObject("Impromptu.Application")
	objImpApp.Visible 1
	Set objImpRep = objImpApp.OpenReport ("c:\report.imr")
	objImpRep.RetrieveAll
	Set objPDFPub = objImpRep.PublishPDF
	objPDFPub.Publish "C:\report.pdf"
	objImpRep.CloseReport
	objImpApp.Quit
	Set objImpApp = Nothing
	Main = DTSTaskExecResult_Success
End Function

Works for both manual and automatic (scheduled) run.

Have you tried a stand-alone simple macro to run in Cognos Script editor? If that fails (and the Cognos configuration has been applied), then it sounds as though there's either an install issue on Impromptu or there's a policy in effect preventing OLE operation on the machine.
If not, check that the version of SQL server is patched and fully up-to-date.
lex

soi la, soi carré

 

[griffindm]

Try changing your line in question to:

Set objImpApp = CreateObject("CognosImpromptu.Application")

And see if that fixes your problem.

The support for a registry call using the syntax you have in your current line ended with Impromptu version 6.

Hope this helps,

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
Magic with Data
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[SymbionA]

Thanks all.

griffindm , I have tried CreateObject("CognosImpromptu.Application") (see very top of this thread) however the same thing happens, However, I will update the scripts to reflect the change you described.

drlex, Cognos Configuration has been applied, I am migrating all the scripts to test server and pointing Test to the LIVE database to create a "LIVE" environment. I will then re-install Cognos on the LIVE server, I will let you know how this goes.

It does seem to be a registry issue, any of you had a "Server Busy" loop before?

 

[SymbionA]

Before I re-install, I have migrated the scripts etc., to a test server.

I want the test server to point to the LIVE data set so that we can still run some reports.

I have mapped the drive of where the catalog sits of the LIVE dataset so my mapping is to H: drive - "\\servername\gains"

Now, I run my script below (notice the catalogPath points to the mapped drive)

'File Constants
Global Const ReportName = "AM_Overdue_print"
Global Const ReportInputPath = "e:\gains\erm\mayne\reports\input"
Global Const ReportOutputPath = "e:\gains\erm\mayne\reports\output"
Global Const GAINSCatalogPath = "h:\erm"
Global Const GAINSCatalog = "gainssql2005.cat"
Global Const dbUserId = "sa"
Global Const dbUserPwd = "sa"

Sub Main()

Dim objImpApp as Object
Dim objImpRep as Object
Set objImpApp = CreateObject("Impromptu.Application")
objImpApp.Visible 0
objImpApp.OpenCatalog GAINSCatalogPath & "\" & GAINSCatalog , "Creator", , dbUserId, dbUserPwd
Set objImpRep = objImpApp.OpenReport (ReportInputPath &"\"& ReportName & ".imr")
objImpRep.Print

objImpRep.CloseReport
objImpApp.Quit
Set objImpApp = Nothing
End Sub


However, I get the error message "Unable to open this catalog h:\erm\gainssql2005.cat"

There are no access issues from this server since I get see the file through explorer. Any ideas why I get this error?

Thanks in advance

 

[drlex]

SymbionA,
You may be getting this error as the drive mapping may not be the same for all profiles on the server. For DTS jobs, the profile of the creator is usually different to that of the SQLServer agent through which automated jobs are run.

Have you tried putting in the full UNC path ("\\servername\gains" instead of the drive letter for the variable "GAINSCatalogPath"?

soi la, soi carré

 

[Yomet]

Hi,

I have the same issue as the original post in this thread, i.e. when I run my CognosScript through the Script Editor I get the "This action cannot be completed because the other program is busy...." error message.

After a bit of debugging I have the following information:
- The script connects to Impromptu and opens the catalog just fine
- It opens and exports the reports just fine

However, when the report it BIG (2-3 min to get initial data set) it hangs. It seems that the editor has a 'timeout' of around 30s that it will wait for a command to complete before it just goes on to the next one. I noticed this when the error happened just after an ExportToASCII statement and I could see the export file size growing when the error came, after I waited for the export file to stop growing and hit the "retry" button everything worked fine.

Someone, richardh9920, said to try running the script from VB so I tried it in Access 2003 and everything works flawlessly, just remember to add a reference to "Impromptu Client Type library". Big cudos to richardh9920 for that work-around (or solution).

Now my question is - does anyone know if there actually is a timeout in the Script Editor? If so, can I change it?

Another, quick, question: in this code

      Set objImpRep = objImpApp.OpenReport(strReportPath & "ACTIFS.imr", SegPer)
      objImpRep.RetrieveAll
      objImpRep.ExportASCII strReportPath & "ACTIFS.csv", True, True, ";"
      objImpRep.CloseReport

Do I need the .RetrieveAll before the Export or will the Export automatically retrieve all rows?

Happy for any help you can get me

Thanks

Yomet