Stored Process Error - Lock Not Available

[cbrotz]

I am getting the following error in SAS EG when I run a stored process from a VB Script.  If I run it manually, it runs fine.  Any ideas on how set this up properly would be appreciated.

ERROR: A lock is not available for TEMP.V_PRM_DATA

Thank you

[SASKiwi]

I would compare the definition of the TEMP library between your two modes of operation. Storing this table in your WORK library will prevent this error as each SAS session is allocated a unique WORK library.

[cbrotz]

I was originally running this to store in the WORK. Library and could not use the results the next step in the EG session.

The work library does not save evidently:

No, because the work session disappears when the stored process is done. You can write the file to a temp or perhaps rather than a stored process use a %INCLUDE to call the program instead.

The TEMP library works.....sometimes. 

[SASKiwi]

Try clearing the TEMP libname in your EG session after using it, then reassign it in your Stored Process session. EG sessions run under your user account, Stored Process sessions run under a shared service user account.

You will still get the lock problem if you run two Stored Process sessions at the same time.

[cbrotz]

I see code for  "libname_all_clear;"   Is that what you are referring to?

How do I assign a library?  I know we used to use libname statements.  Is it something like that?

Thanks,

[FredrikE]

The problem is that sas data set can only be written or updated by one user at a time, otherwise you get the lock error message.

One way to solve this is to name the dataset with a time stamp or user name so every dataset is unique.

The easiest is to add the user name as a suffix. I don' remember which macro variable that are automatic in the stored process but one of _METAUSER, SYSUSERID SASUSER should work:

data test&_METAUSER;
a = 10;
run;

//Fredrik

[cbrotz]

I am pretty sure I am the only one creating stored processes in our system...I am definitely not running the same stored process from two different SAS EG sessions at the same time.   Something else seems like it is not working right.  It seems more like once the stored process runs, it does not free up the connection to that file even though it is a temp file. Hours later, it is still locked. The naming idea might work.  I will give that a try.

[cbrotz · Accepted solution]

What was happening is that my stored process to create a file in a SAS EG session was running exactly the same time as the code.  I thought the code would "wait"  but I guess it does not work that way.  I used the following code solution to "sleep" the program.  It is not exactly what I was looking for but it will work.

Re: Sleep Question

 KevinC

‎02-07-2013 06:27 PM

Look for the CALL SLEEP function instead of trying to guess how long a loop takes.

Data _null_;

     call sleep (600,1); /* 600 seconds*/

run;

[cbrotz · Accepted solution]

Look for the CALL SLEEP function instead of trying to guess how long a loop takes.

Data _null_;

     call sleep (600,1); /* 600 seconds*/

run;