WRS - Instability

[Eric_WOLFF]

Hi,

My problem is :
- We execute a WRS on SAS Portal and sometimes the execution failed.

The Report is associate with a IMS. This IMS is associate with a STP who build the table (on a work library) to display.

I think that sometimes WRS execute his syntax SQL before the STP's execution.

I don't find any hotfix to resolve this problem.
So...i'm then only one ? A solution exist ? My english is good ?

Thanks,

P.S : Windows NT, SAS 9.1.3 SP4, Tomcat, WRS 3.1

[Jenn2]

Hello Eric,
When you say "execute WRS on SAS Portal" do you mean that you are trying to view a WRS report posted on the portal? Also, can you elaborate on what you mean by "I t hink that sometimes WRS execute his syntax SQL before the STP's execution?" What makes you think that? I'm curious about the clues you may be observing...maybe they can help us understand what is wrong.

[Eric_WOLFF]

On the SAS Portal, we use the Collection Portlet to execute a report created by WRS.

The particularity of this report is : the associated IMS is dependant of a STP.
The STP create the table (on a temporary work), this table is used by IMS.

The report execution work (9 times on 10) but sometimes we have the error "table not find".

Now, the french support SAS say to use this hotfix :
http://ftp.sas.com/techsup/download/hotfix/ims31.html

I try after my vacation and i will tell you the result :).

[deleted_user]

Hi Eric!

I also tried creating a WRS Report with stored process integrated in my information map 2 months ago, and I also got a similar error. If I remember it correctly, my problem then was not on the information map but on the execution of the stored process. To be specific, my problem then was just the authorization on the source dataset that is why there is no WORK table created, and the error manifested on the InfoMap, then translates to the Report. Maybe you can look at the log of your STP why it failed to create the WORK table first 🙂

Anyway, hope you have a good vacation. Happy holidays 🙂

[Eric_WOLFF]

Thanks for your response.
But for my case, in IMS it's work perfectly.

[Jenn2]

Eric,
Did you have any success with the hotfix? You wrote the following before the holiday:

"Now, the french support SAS say to use this hotfix :
http://ftp.sas.com/techsup/download/hotfix/ims31.html

I try after my vacation and i will tell you the result ."

[AngelaHall]

Eric,
I ran into a similar error when using STP as my query generator in an IMAP. Included is my blog post last summer with some must dos:
http://sas-bi.blogspot.com/2009/06/utilizing-sql-pass-thru-within-sas.html

> Must have *ProcessBody; in the top of the program (This is created by Enterprise Guide automatically)
> Must reassign the table location libref to libname BIOUT (work);
> Must be the same libref of the Metadata Library used in Info Map

Otherwise sometimes the work location is locked from other use and that is why it worked on one occasion and not on another.

Let me know if this helps.

~ Angela Hall
http://sas-bi.blogspot.com

[Eric_WOLFF]

"Otherwise sometimes the work location is locked from other use and that is why it worked on one occasion and not on another.
"

Exactly. It's a work lock problem.

[AngelaHall]

Right, so then this isn't instability. It's an implementation issue. You must 'trick' the SAS Stored Process into thinking the table is a defined location in the SAS Metadata by then reassigning that libref to WORK in the code.

That way - WORK is being defined with each execution of SAS Stored Process - which is 1 per user interaction.

Here is an overview of the process.
1. Define a SAS (BASE) Library in SAS Management Console that points to somewhere on the system.
2. Create a small table in this physical location with the columns/formats etc. (This will be deleted later. Just used to define the metadata.)
3. Import the table metadata into SAS Library defined in Step 1.
4. Delete the table created in Step 2.
5. In the Information Map, use this table metadata created in Step 3 above as the source.
6. In the SAS Stored Process, add the statement
libname libref (WORK);
where libref completely matches the libref you gave the SAS Library in Step 1.

~ Angela Hall
http://sas-bi.blogspot.com

[Eric_WOLFF]

Of course, we have always make this six steps.

So, normaly one execution of WRS = one new subtree on SASWORK.

It's why i don't understand the correlation between the SASWORK's delete and my WRS's report execute correctly all the time.

[Eric_WOLFF]

Hi,
With the vacation i forgot to answer.
So the hot fix don't resolve the problem.
The solution is....clean the work !!!
I don't understand the correlation but it's work.