2 Replies Latest reply: Aug 12, 2011 12:33 AM by 863034 RSS

    xmlviewservice.executeSQLQuery  TIME OUT ERROR

    863034
      Hello OBIEE Experts,

      I got stucked with the command line xmlviewservice.executeSQLQuery(...) method of OBIEE as operation time out. Actuall i am writing dot net source code to retrive the xml result by passing the query created in Oracle BI Answer page. This method runs fine when running a very simple query returning only two rows. However when i set to use a query that takes around 10 min running in OBIEE answer section, the above method fails. It returns an error Operation Time out. I have even try setting sawservice & xmlviewservice timeout but no use.

      Is there any way to handle sql execution time using OBIEE SAW web service functionality ? Please find below code that i have used.



      //**********************************************
      Imports saw = Obiee_Services.web_Reference
      //**********************************************

      Dim sawservice As New saw.SAWSessionService
      Dim sessionid = sawservice.logon("xxx", "xxx")
      sawservice.Timeout = 60000

      'Dim curUser As String = sawservice.getCurUser(sessionid)
      'Dim repref As New saw.ReportRef()
      Dim xmlviewservice As New saw.XmlViewService
      Dim xmlQueryExecutionOption As New saw.XMLQueryExecutionOptions
      xmlQueryExecutionOption.maxRowsPerPage = 10
      xmlQueryExecutionOption.refresh = True


      Dim repid As String = "Report1"
      Dim repParams As New saw.ReportParams
      Dim repHtmlOP As New saw.ReportHTMLOptions

      Dim QueryResult As New saw.QueryResults
      xmlviewservice.Timeout = 60000
      QueryResult = xmlviewservice.executeSQLQuery(qry, Obiee_Services.web_Reference.XMLQueryOutputFormat.SAWRowsetData, xmlQueryExecutionOption, sessionid)
      Dim rstring As String = QueryResult.rowset
      Dim i = WriteToFile("text.xml", rstring)


      Regards,
      Rajen Singh
        • 1. Re: xmlviewservice.executeSQLQuery  TIME OUT ERROR
          Christian Screen
          Rajen,

          If that is your code, I don't see where you are logging out after the QueryResult.rowset call.

          Also, Are you 100% that your attempt to use an I/O operation with the WriteToFile() function that you are calling isn't causing your issue?

          Try to comment out the WriteToFile() call and just write the QueryResult.rowset to the console/system output to see if the recordset XML is actually getting retrieved. Even long running queries should return some information.

          In addition, test your query in OBI Presentation Services. If the query is taking 20 minutes to run in presentation services or it timesout there you may just want to update the query so that it returns less records, etc. Whatever affects you receive in presentation services in regards to timeouts, etc. are basically the same as those seen when calling the web services.
          • 2. Re: xmlviewservice.executeSQLQuery  TIME OUT ERROR
            863034
            Thank you for the reply.

            I do agree that i have omitted logging out command line and WriteToFile() definition. The below executesqlquery command is a syncronous call. I do not think it will be a problem with the next two or three lines. Below mentioned command line will not be released by the compiler until and unless it is completed. MOreover i am sure that the two mentioned command line does not have to do much on the issue becouse the error message is associated with below line and also works fine on simple query.

            QueryResult = xmlviewservice.executeSQLQuery(qry, Obiee_Services.web_Reference.XMLQueryOutputFormat.SAWRowsetData, xmlQueryExecutionOption, sessionid)

            I have tested other work around to resolve the current error i.e "Operation Time Out" error atleast(applying other configuration setup and increasing timeout properties). Having resolved sucessfully the next issue is on the below line. Blank rowset is returned each time. I can get an ID on QueryResult.QueryId and QueryResult.Finished=false.

            Dim rstring As String = QueryResult.rowset

            Finally, in OBI Presentation Services, the query runs fine in a specific time.