Running StoredProcess from a C# wrapper using API never getting response that it finished

[rhains]

We crated a C# wrapper that calls a SAS stored process that takes more than an hour. 

The stored process finished, and I can see that on the sas side, but it never returns a message back to the wrapper that it finishes.

Anyone have something working that you can share?

What the wrapper is doing is:

.

UserContext userContext = CreateUserContext(metadataServer, port);

StoredProcess storedProcess = new StoredProcess(userContext, storedProcLocation);

storedProcess.SetPromptValue("thread_id", thread_id);
storedProcess.SetPromptValue("connect_source", sourceDsn);
storedProcess.SetPromptValue("connect_destination", destDsn);
storedProcess.SetPromptValue("HIST_DATE", HistoryDate.ToString("MM/dd/yyyy"));
storedProcess.SetPromptValue("POS_DATE", PositionDate.ToString("MM/dd/yyyy"));
storedProcess.SetPromptValue("pRunType", pRunType);
storedProcess.SetPromptValue("p_model_type", modelType);

Execution execute = storedProcess.Execute();

return LogStoredProcessResult(execute, storedProcLocation);

When it runs Execute() it never returns.

Thanks

Richard

[AlanC]

Quick reply since I don't have code in front of me but make sure you close any open processes. I think there is a Close method.

[rhains]

Hi Alan, thanks for your answer.

When you say Close any Open process, do you mean in the SAS side? or in the API ?

Richard

[AlanC]

API. Should be a method there. Sorry, trying to help but in the dark a bit.

[rhains]

Thanks Alan. Here is the Method I am invoking. 

The SAS process is working fine, is this Method that Execute the SAS process, that does not pass to the next line

after

Execution execute = storedProcess.Execute(); 

when the STored Process takes long time to execute.

If I change the value on parmeter modelType to for example Factor, that makes the SAS stored process to compute less rows, and in that case the EXECUTE() method returns fine.

my problem is when the stored process takes more than an hour. It process on the SAS side but looks like the Execute method is timing out.

This is the code on my Method in the wrapper.

using System;
using System.Web.Services;
using SAS.BI.AuthenticationService.ClientUserContext;
using SAS.BI.StoredProcessService;
using SAS.BI.StoredProcessService.Exec;
using SAS.BI.StoredProcessService.IO;
using System.Configuration;
using System.Collections.Generic;

[WebMethod]

public bool RunStressGenDataModel(int HistoryOffsetDays, int PositionOffsetDays)
{
log.LogDebug("--------------------------------------------------------");
log.LogDebug("Starting RunGenModelStress");
string metadataServer="cpwnpsas01.corp.capitalpower.com";
string thread_id = DateTime.Now.ToString("yyyyMMddhhmmss");
string pRunType = "Daily";
string sourceDsn = "dsn=Allegro_Stress_Auth;Trusted_Connection=yes;database=allegro_stress";
string destDsn = "dsn=Allegro_Stress_Auth;Trusted_Connection=yes;database=allegro_stress";
DateTime HistoryDate = DateTime.Today.AddDays(HistoryOffsetDays);
DateTime PositionDate = DateTime.Today.AddDays(PositionOffsetDays);
string modelType = "_ALL_VALUES_";
int port = 8561;

UserContext userContext = CreateUserContext(metadataServer, port);

try
{
string storedProcLocation = ConfigurationManager.AppSettings["GenDataModelStress"].ToString();
StoredProcess storedProcess = new StoredProcess(userContext, storedProcLocation);
log.LogDebug(String.Format("RunGenModelStress using location of {0}", storedProcLocation));

/*
* If the parameter names in the Stored Proc ever change, you have to change them here as well
*/
storedProcess.SetPromptValue("thread_id", thread_id);
storedProcess.SetPromptValue("connect_source", sourceDsn);
storedProcess.SetPromptValue("connect_destination", destDsn);
storedProcess.SetPromptValue("HIST_DATE", HistoryDate.ToString("MM/dd/yyyy"));
storedProcess.SetPromptValue("POS_DATE", PositionDate.ToString("MM/dd/yyyy"));
storedProcess.SetPromptValue("pRunType", pRunType);
storedProcess.SetPromptValue("p_model_type", modelType);

Execution execute = storedProcess.Execute();

return LogStoredProcessResult(execute, storedProcLocation);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}

[AlanC]

Man, I double-posted something and it messed up a few posts so hopefully works right this time (and I am not a DA). 

Isolate the issue. Pull that code out to a Console app and try it there to make sure the timeout is not occurring on the web side. Start with that.

[rhains]

Thanks Alan. 

One last question. How do you know, looking in some logs in the SAS server, that a stored procedure finish processing. Do you know if SAS log something?

And it which log file.

Richard

[AlanC]

No, sorry. I rarely use stored procedures in SAS. I submit code directly and keep pgms locally or net accessible. I did find a few examples I have of StoredProcess submission. Here is one (old and using Framework, not Core):

StoredProcessService sp = SasCore.SasLanguageService.StoredProcessService;
sp.Repository = storedProcLibrary;
sp.Execute(storedProcedureName, newParms);
var sro = new SasRunObject();

SasCore.SasLanguageService.LineSeparator = "\r\n";
sro.SasList = SasCore.SasLanguageService.FlushList(SasCore.ListCharactersToReturn).Replace("\f", "");
sro.SasLog = SasCore.SasLanguageService.FlushLog(SasCore.LogCharactersToReturn).Replace("\f", "");

Again, make sure you clear things out t (null/Close/etc.) when done with them. Also, you may glean some info from my SGF paper on Lightweight SAS Web Services.

[AlanC]

Try using ALTLOG system option if you can or proc printto for log redirection. If the concern is with the storedproc. If you just want to submit SAS code on the server, consider just using the SASLanguageService vs the StoredProcess service.