Execute SAS Server stored procedure from PowerShell

[MGOutside]

Trying to automate some of our processes and we need to run some SAS processes from command line.  Since we are running our code from SAS Server, we need to use PowerShell.  I have created a script than can successfully connect to the server, but I am not able to invoke the the stored procedure.  The methods for C# or VB don't always translate exactly, so I'm having an issue trouble shooting.

==================================================================

# create the Integration Technologies objects
$objFactory   = New-Object -ComObject SASObjectManager.ObjectFactoryMulti2
$objServerDef = New-Object -ComObject SASObjectManager.ServerDef
$objServerDef.MachineDNSName = "<<SERVERNAME>>" # SAS Workspace node
$objServerDef.Port           = 8561  # workspace server port
$objServerDef.Protocol       = 2     # 2 = IOM protocol
# Class Identifier for SAS Workspace (or some other ID)
$objServerDef.ClassIdentifier = "<<CLASSID>> "

try
{
    # create and connect to the SAS session
    $objSAS = $objFactory.CreateObjectByServer(
                    "SASApp",          # server name
                    $true,
                    $objServerDef,     # built server definition
                    "",   # user ID
                    ""       # password
                    )
     Write-Host "Connected to " $objServerDef.MachineDNSName
}
catch [system.exception]
{
  Write-Host "Could not connect to SAS session: " $_.Exception.Message
}

# local directory for downloaded file
$localPath = "c:\data"

# program to run
$program = "proc STP PROGRAM=`"/FOLDER/PROCEDURENAME`"; run;"

# run the program
$objSAS.LanguageService.Submit($program);

$objSAS.Close()

==============================================================

I'm getting the following error:

You cannot call a method on a null-valued expression.
At F:\Testautomate.ps1:44 char:1
+ $objSAS.LanguageService.Submit($program);
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Let me know if there is a better way or if I'm missing something here.

[LinusH]

What kind of processes are we talkong about?
Normally automation of SAS jobs are done via a scheduler (which in turn starts a SAS batch session with the SAS program name as a parameter). This is simplified by the Search server in your configuration directory.

[MGOutside]

Our process is to process data in SQL Server, then at a certain step we want our work in SAS to be done.  We are using Alteryx for the work flow and we want to kick off SAS at a certain point...  Hence we are trying to put a powershell script to kick off the server level SAS stored procedure.  I cannot seem to submit or activate the process...  I have also tried assiging the StoredProcessService as:

# run the program
$objProc = $objSAS.LanguageService.StoredProcessService;
$objProc.Repository = $repository
$objProc.Execute($procedure)

Defining the variables I get the following error:

Property 'Repository' cannot be found on this object; make sure it exists and is settable.
At F:\Testautomate2.ps1:45 char:1
+ $objProc.Repository = $repository
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound

You cannot call a method on a null-valued expression.
At F:\Testautomate2.ps1:46 char:1
+ $objProc.Execute($procedure)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Any help activating the stored procedure using Powershell would be appreciated.

[LinusH]

Not clear why you are exploiting SAS Stored Process for this task. As said, SASBatchServer would be my primary choice.

[MGOutside]

Odd you want me to use your solution.  Suffice it to say, it will not work as we have other processes on other platforms that are not timed.

If you only offer this as a solution, no need to reply again.  I need to get this to work as I am trying, so if this is a server setting that I need to get our IT team to modify or if I am invoking this incorrectly please let me know.

[Tom]

Is it just the call to PROC STP that is not working? Why not just start with some simpler SAS code first to make sure that you can actually connect to SAS?

[ChrisHemedinger]

I agree with @Tom - it looks like you might not have successfully connected to the SAS Workspace, as maybe the LanguageService property is null and that's what is causing the exception.  

You should be able to use the PowerShell ISE on your desktop to help debug your PowerShell script and see a bit of what's going on.

Chris