BookmarkSubscribeRSS Feed
Jchapman
Calcite | Level 5

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?

3 REPLIES 3
Jchapman
Calcite | Level 5

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
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
jakarman
Barite | Level 11

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?

---->-- ja karman --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1514 views
  • 0 likes
  • 3 in conversation