Problem with multiple running

[Jchapman]

I have a web process that essentially has a pre-processing and post processing step to check for concurrently running instances.

The pre-processing writes a line to a SQL dbms with the name of the process, time it was executed, etc with null values for execution time and a return code.

Post-processing occurs after stored process runs and populates the execution time and return code.

Before pre-processing occurs the web process counts the number of null valued return code. However, when running as a stored process the count always returns 0. I first confirmed this works perfectly in base SAS. I then confirmed that within SQL management studio the count works and gives 1 when running two instances of the stored process starting with one, then the other some seconds later.

MPRINT(WPSQLCHECKRUN): *check rows for null RC;

MPRINT(WPSQLCHECKRUN): proc sql noprint;

MPRINT(WPSQLCHECKRUN): connect to odbc as mydb(dsn=test user=nope password="noway");

MPRINT(WPSQLCHECKRUN): select rows into :rowCount from connection to mydb( select count(case when rc is null then 1 else null end) as rows from test.dbo.new );

MPRINT(WPSQLCHECKRUN): disconnect from mydb;

MPRINT(WPSQLCHECKRUN): quit;

MLOGIC(WPSQLCHECKRUN): %PUT &rowcount

0

How do I resolve this problem? What is causing it in the first place?

[Jchapman]

Ah. Only one stored process is running at a time. The other is kept in wait. How do I allow the same stored process to be run concurrently?

[Quentin]

Which server are these stored processes run on?  Pooled workspace server?  Stored process server?  I think you would need to check the setup to make sure they are configured to allow multiple stored processes to run at the same time.  I think even with a pooled server, you can end up with jobs lining up if other users have multiple sessions going.  Also, make sure your stored processes take long enough that they are both trying to run concurrently.  Maybe put a sleep() function in both.

[jakarman]

Please rethink on all services behavior.

- The workspace server is commonly seen as a single personal user process. With Eg 5.1 sas 9.3 this assumption is wrong as the workspace server can run many processes parallel. This can be seen by the nested SAS work naming physical naming (properties).

- The stored process server is running from a startup point and then serving a lot of subprocesses. The number of process is part of the SP configuration.

   You need a multibridge definition when having multiple SP's  SAS(R) 9.4 Intelligence Platform: Application Server Administration Guide
   As Quentin already stated testing concurrency runs is also having sure a process is running log enough you can see that happening.  

For what you are doing with CPU IO and other information on per-process is already available as standard tools

- SAS(R) 9.4 Interface to Application Response Measurement (ARM): Reference

- SAS(R) 9.4 Intelligence Platform: Middle-Tier Administration Guide, Second Edition (logging midtier)

- SAS(R) 9.4 Logging: Configuration and Programming Reference, Second Edition

Why develop something that is already there?