SQL Server Performance Forum – Threads Archive
Multiple Datasets against OracleI am new to Reporting Services and am running into an interesting problem. I want to do the following against an Oracle DB. I have complex logic than needs to be done on some data before the report is created. I created an Oracle Stored Procedure to populate an Oracle Temp Table with data. I want to first execute the Stored Procedure to generated a set of data and then run a query against it. The way Oracle Temp Tables work is that the data goes away after the session is ended. Therefore I need to execute the stored procedure first then run the sql statement against it. I can create a Reporting Services dataset as a Stored Procedure and execute it successfully. I can create a sql statement against the temp table and it will execute but come back with no data. My problem could be one of two things or a combination of them. 1) The two datasets are executing via different sessions. This means that the data is not available for the SQL statement to query. AND/OR 2) The stored procedure is not running and completing before the SQL statement runs.
1) Where can I (if possible) set execution precedent for the datasets?
2) Can I force the dataset execution to occur within a single Oracle session? Thank You, Mike La Pointe
No expert on Oracle but if there is something similair to the temp tables in SQL Server (#local and ##global) you might have the option to make a global temp table that is accessible from different sessions.
quote:Originally posted by Argyle No expert on Oracle but if there is something similair to the temp tables in SQL Server (#local and ##global) you might have the option to make a global temp table that is accessible from different sessions.
The session thing is only 1/2 of the issue. Can I execute one of my Datasets first and not have the second one execute until the completion of the first?
Have you referred to the Reporting Services newsgroups set by Microsoft for this query and also I believe posting the question in Oracle forums will fetch you required information. HTH Satya SKJ
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.