executing a store procedure from DBRead step

Version 1
    This document was generated from CDN thread

    Created by: Javier Glaz on 20-01-2014 06:50:30 AM
    The costumer has a UCCX 9.0.2SU1 Premium integrated with a MSSQL DB. We
    have already done some tests with standard SQL queries through DBRead
    and DBGet steps in a script and everything works fine.

    The problem is that, the main script that is going to be in production
    soon, has to query a DB with more than 50 million records. The query
    gets slow in this case, so the costumer decided to write
    a store procedure in the DB and execute it through a DBRead Step.

    At first, we excecuted the Store Procedure, and, although it gave a
    "Number of rows returned" = 1, there was no table/view to select the
    results from. So we decided to try creating a temporal table.
    You can see this in the screenshot. Once again, the temporal table does not appear in the table/view selection below.

    So the question would be, how should the result be retrieved from
    the store procedure? What are we doing wrong? I have seen some solutions
    with Java, but we are trying to avoid those.

    Subject: RE: executing a store procedure from DBRead step
    Replied by: Steven Pawlak on 20-01-2014 08:40:38 AM
    Javier,

    The View/Table in the GET step is used to type the values. You might be able to create a VIEW and use that just to get the field names and types of the output of the SP. However, since you have PRE you could also write a simple web service (just a page that handles the form post and sends back XML) in ASP or whatever and have that do the SP call and unload the values into a string or simple XML that you can pass back to the Script using CREATE URL Document step. This is how I get around the SP block that cisco has. 

    Subject: RE: executing a store procedure from DBRead step
    Replied by: Javier Glaz on 22-01-2014 10:59:42 AM
    Steven Pawlak:
    Javier,

    The View/Table in the GET step is used to type the values. You might be able to create a VIEW and use that just to get the field names and types of the output of the SP. However, since you have PRE you could also write a simple web service (just a page that handles the form post and sends back XML) in ASP or whatever and have that do the SP call and unload the values into a string or simple XML that you can pass back to the Script using CREATE URL Document step. This is how I get around the SP block that cisco has. 
    Hi Steven, 

    Thanks a lot for your quick answer. The problem I am having is that I cannot select the temporal table or view I am creating to retrieve the results. Although I know the SP runs, because the result line show with a "1". 
    Regarding the other solution you mentioned, do you have any example code I can use to start testing it? 

    Thanks in advance, 
    Javier