we are trying to use sas Stored Processes from VBA with IOM interface:
It seems to be ok but the password stored in metadata server is propagated and we don't want to store password in SAS Metadata.
Here is an example :
Dim obObjectFactory As New SASObjectManager.ObjectFactory
Dim spManager As New SASStoredProcessService.Manager
Dim spInstance As SASStoredProcessService.Instance
Dim spExecution As SASStoredProcessService.Execution
Dim spReader As SASStoredProcessService.StreamReader
obObjectFactory.SetMetadataFile "C:\Test\VBA\oms_serverinfo.xml", "", False
....and so on
3 questions :
-where is the complete documentation ?
-is IOM the best way to execute STP from VBA ?
-If yes, it is possible to force IOM objets to use the password write in the configuration file ? For the moment this password is used only for connection to metadata server. After that,the wrong password stored in metadata is propagated....
Some of the information is dated, but you may find the section on VBA helpful. The paper uses SAS/IntrNet as an example, but it can be extended to SAS Stored Processes because they are basically "The Son of SAS/IntrNet"(tm).
For example, this Excel macro, adapted from the paper (only changed the URL), runs the "Hello World" stored process sample and inserts the output into the current worksheet.
Dim currentCell As Range
Dim tempWorkbook As Workbook
Dim tempRange As Range
Set currentCell = Application.Workbooks(ActiveWorkbook.Name).ActiveSheet.Application.ActiveCell
Set tempWorkbook = Workbooks.Open("http://your-server:your-port/SASStoredProcess/do?_PROGRAM=/Samples/Stored Processes/Sample: Hello World")
Set tempRange = tempWorkbook.ActiveSheet.UsedRange
You will get prompted for a username and password in order to run the stored process. It look like Excel caches this information, so you should only have to enter it for the first stored process that you execute in a given workbook.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.