Plan, execute, measure and optimize customer experiences

Using some proccesses, in parallel, with &sqlouttable cause for 1012 error code

Reply
Occasional Learner
Posts: 1

Using some proccesses, in parallel, with &sqlouttable cause for 1012 error code

Hello,

 

I am using SAS Customer Intelligence Studio, version 5.4

 

I use precess node within the Customer Intelligence Studio with code such at the end of this message.

I have few equivlance proceeses (each one of them has differnet internal code) - about 7 process nodes.

While I am running the code with "Run Selected Node" button (i.e. manually) - for the whole Diagram - it works correctly.

While I am running the code with "Scheduled Diagram & Execute Now" option - I get error message: "Error in the stored process or called macro - Vendo Code: 1012". Also, this problem occure while I am scheduling the diagram for automatic/scheduled run.

The intresting issue here: evety time I am getting this error for another set of Process Nodes. It is not consistence.

 

One of my investigation assumption is: there is a problem to run parallel processes (they work in parallel because there is not direct flow connections between the different Process Nodes).

 

Do you have any idea for resolving this issue?

 

Regards,

Roy

Maybe, giving different output name?  If yes, how do I do it?

 

 

--------------- code sample ---------------------------------

%include "D:\SAS9_2\Config\&sasenv\SASApp\SASEnvironment\SASMacro\CodeNode_createSQLTables_Start.sas";

/*SQL*/
/* &sqlintable - INPUT Table*/
/* &sqlouttable - OUTPUT Table*/
proc sql;
%include "D:\SAS9_2\Config\&sasenv\SASApp\SASEnvironment\SASCode\Jobs\PROD_UTL_1a_SQLConnections.sas";

create table cmprodwr.&sqlouttable as
select * from connection to CMPRODWR
(
select m.X

from myTable m
inner join &sqlintable t on t.Y = m.Y

) ;
disconnect from CMPRODWR;
quit;

Contributor
Posts: 46

Re: Using some proccesses, in parallel, with &sqlouttable cause for 1012 error code

Hi,

 

Issues like that are nasty and sometimes complicated to resolve. Do you engage with SAS Technical Support on that already?  
A detailed and accurate log analysis is needed to say more.

 

You must first enable a detailed logging of your stored process execution. Some customers do this by setting MADebug macro variable, some by ramping up SAS Stored Process Server logconfig to DEBUG levels. SAS MA Administrator Guide said some words about controlling these logs.

 

In our company, our technical implementation team considers a best practice to use an explicit "proc printto" call in the beginning of each custom stored process code (in a shared %include section) to redirect its sas log to a separate file, one file per process execution. Like that:

 

 

%let LOG_DIR = /sasdata/sas/config/Lev1/Logs/custom_stp_logs;

option mprint mlogic symbolgen source source2 stimer notes;

data _null_;
        d = datetime();
        call symput('now_datetime', cats(put(datepart(d),  YYMMDDn8.), "_", put(hour(d), Z2.), put(minute(d), Z2.), put(second(d), Z2.)) );
        call symput('now_millis', put(round((d - floor(d))*1000, 1), Z3.));        
        call symput('MA_PROCESS_NAME', scan(symget('_PROGRAM'),-1,'/'));
run;

data _null_;
        put "Log redirected to: &LOG_DIR/&now_datetime.-&now_millis.-&SYSJOBID.-&MA_PROCESS_NAME..log";
run;

proc printto log = "&WORK_DIR/Logs/stp_logs/&now_datetime.-&now_millis.-&SYSJOBID.-&MA_PROCESS_NAME..log"; 
run;

 

 

I would also suggest first suggest that you checked your SAS Stored Process Server load balancing settings.  There is a SAS Note on that: http://support.sas.com/kb/40/567.html,  and I think SAS MA Administration Guide says the same. Default STP load balancing settings are just wrong for SAS MA, and this often lead to unpredictable results and preformance issues of many kinds. Make sure you don't have two stored processes executed concurrently by a single stored process worker at the same time.

 

Good luck!

-------
Dmitriy Alergant, Tier One Analytics
Contributor
Posts: 46

Re: Using some proccesses, in parallel, with &sqlouttable cause for 1012 error code

Also just check how do you assign &sqlintable and &sqlouttable macro variables. Do you make sure you're using unique names each time, even if two processes run at the same second?

-------
Dmitriy Alergant, Tier One Analytics
Ask a Question
Discussion stats
  • 2 replies
  • 202 views
  • 0 likes
  • 2 in conversation