1 Reply Latest reply: Sep 4, 2007 8:06 AM by 3004 RSS

    Problems with StoredProcedures that use INSERT/DELETE statements

      <p>Hello</p><p> </p><p>I am using Hyperion Intelligence explorer 8.5, and the databasesource is a MS SQL Server 2000 source connected via ODBC.</p><p> </p><p>I have this problem: when I use, in a Query section, a storedprocedure that (in its code) uses only SELECT statements I get theresult of the query in the Results section, but when I use astoredprocedure that does some work (and executes INSERT or DELETEor other SQL statements) and ends executing a SELECT statement inorder to return data to the caller Hyperion hangs.</p><p>I mean: first I select the stored procedure(Query\StoredProcedure... men&ugrave<img src="i/expressions/face-icon-small-wink.gif" border="0">, than I start it using theProcess command in Hyperion.</p><p>If the storedproc contains only SELECT statements I get theresults, but if it contains INSERT or DELETE (and the laststatement is a SELECT) Hyperion does not return any data and if Itry to repeat the Process command I get an error that tells"the connection is busy with results from anotherhstmt".</p><p>Any suggestions? Did you ever use (successfully <img src="i/expressions/face-icon-small-wink.gif" border="0">)storedprocedures that process data (by INSERT or DELETEstatements) and then return the result with a SELECT statement?</p><p>Thank you for your help</p><p> </p>