Problem with calling stored process via ASP.NET Web service

[deleted_user]

We used this (http://support.sas.com/kb/25/959.html) article to guide us through building an ASP.NET web service using SAS 9.1.3 integration technologies API to call a stored process. Our web service has the following three simple steps -

1. Creates a temporary FileRef on FileService object & sends an XML file as the input to the TextStream. And finally set the LibRef using DataService object.
2. Executes the stored process using LanguageService.StoredProcessService.Execute (...)
3. Reads the output produced by the sproc which is in a SAS dataset.

To the best of our knowledge and from the ASP.NET webservice article mentioned above, we've configured the service and things look good. But here's the error we get on execution: (this is pulled from SAS logs)

ERROR: File WEBSVC.ICOM.DATA does not exist.

WEBSVC above is the name we assigned to the File in FileRef and to the library in LibRef. ICOM is the root node of the XML document which is streamed in. The stored procedure is coded to look at the dataset "WEBSVC.ICOM". We are not able to figure out what step we are missing here. It sure doesn't look like a whole to get this to work.

Here's how the input XML looks like -


Joe
2


Appreciate any thoughts/inputs,
Thanks,
Sudhir

Message was edited by: SDarbha Message was edited by: SDarbha

[deleted_user]

Alright, after little bit more reading and giving a closer look, I found the solution to the error. The problem is that the XML I'm sending in does not exactly match the format of the XML specified in the article. As you can see in my previous post, my input XML has a root node and bunch of child elements, where the root node is the name of the table and the child elements are the columns.

But it doesn't look like its enough - which means, there should be a root node for "dataset" name in the XML which contains multiple table nodes. So in summary, I think the format of the XML that's sending into the stream for stored process should be -



value1


value1

... (like this you can add multiple tables)
...
...


Once I added a root node to my XML, it started working as expected, which means the stored process was able to find the dataset WEBSVC.ICOM where ICOM my table name.

There's one more catch here - It worked first time and the second time it threw an error - "Invalid Logical Name". This was happening at the time of assigning FileRef with the name "WEBSVC". This is because the FileRef was assigned earlier is not currently available. So, this means once the sproc execution is done, we should release it for the next guy in the queue.

Once I used DeassignFileRef(...) and DeassignLibRef(..) methods on FileService and DataService respectively, the "invalid logical name" error went away.

I really hoped these important catches were explained well in the documentation. But I thought I would share it incase anyone else runs into this.

[Zach_sas]

Hi SDarbha,

Have you looked at using SAS BI Web Services to execute stored processes and return XML data? It hides all the implementation details of communicating with the SAS server for you. You can find more details for 9.13 at: http://support.sas.com/rnd/itech/doc9/dev_guide/websrvcs/index.html

Thanks,
-Zach

[deleted_user]

I think you are referring to XMLA web services right?

I don't think we currently have the license for it. Otherwise, we could try it out as well.

[Zach_sas]

Yes, I was referring to XMLA web services. It is part of Integration Technologies, so if you are using WorkspaceManager then you likely already have a license for BI Web Services. You can contact your SAS Representative for the download.

Thanks,
-Zach