Multiple Resultsets in DPE

Hi,
I'm writing a DPE, which returns a multiple resultsets(datatables) from a
extenal data source. I would like to map these to dataregions in the report.
How do i do this?
Or To make it simple, let us assume i have a stored procedure returning
multiple resultsets and how do i map these result sets to
dataregions(Table,List etc.,)
thanks
shankarYou cannot return multiple rowsets from a single query in the current
version.
You would need to break this into multiple queries, each of which returns a
single rowset.
In your case, you could add a parameter to the stored procedure to indicate
which rowset to return. Then have multiple data sets, each of which calls
the stored procedure with a different value for that parameter.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:uGNAmgzcEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> I'm writing a DPE, which returns a multiple resultsets(datatables) from a
> extenal data source. I would like to map these to dataregions in the
report.
> How do i do this?
> Or To make it simple, let us assume i have a stored procedure returning
> multiple resultsets and how do i map these result sets to
> dataregions(Table,List etc.,)
> thanks
> shankar
>|||SQL Server 2000 Reporting Services only supports one dataset pre data region
unless
the other data regions references are aggregated
(=First("Field!<Fieldname>.Value, "<DataRegionName>")>.
You will need to join your results in the query.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:uGNAmgzcEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> I'm writing a DPE, which returns a multiple resultsets(datatables) from a
> extenal data source. I would like to map these to dataregions in the
report.
> How do i do this?
> Or To make it simple, let us assume i have a stored procedure returning
> multiple resultsets and how do i map these result sets to
> dataregions(Table,List etc.,)
> thanks
> shankar
>

Multiple ResultSets

Hi
I have a stored procedure which returns mulitple resultsets
rs = call.executeQuery();
call.getMoreResults();
irs = call.getResultSet();
I want to pass these two resultsets to another method.
When i pass these two
i get error saying "ResultSet is closed" for the first resultset.
i came to know there is method in jdk1.4.2 which keeps the resultsets open.
"call.getMoreResults(Statement.Keep_Current_Result )"
But i tried to use that But again i have another problem
Im using WSAD5.1 which uses to jdk1.4.1
So i configured the setting in WSAD so that it uses jdk1.4.2.
So at compile time im not gettign any problem
But at runtime it says
"getMoreResults(int) is not supported by webshpere java.sql.Statement
Implementation"
Can you please help me regarding this..
Thanks in Advance
jaya nair wrote:

> Hi
> I have a stored procedure which returns mulitple resultsets
> rs = call.executeQuery();
> call.getMoreResults();
> irs = call.getResultSet();
> I want to pass these two resultsets to another method.
> When i pass these two
> i get error saying "ResultSet is closed" for the first resultset.
> i came to know there is method in jdk1.4.2 which keeps the resultsets open.
> "call.getMoreResults(Statement.Keep_Current_Result )"
> But i tried to use that But again i have another problem
> Im using WSAD5.1 which uses to jdk1.4.1
> So i configured the setting in WSAD so that it uses jdk1.4.2.
> So at compile time im not gettign any problem
> But at runtime it says
> "getMoreResults(int) is not supported by webshpere java.sql.Statement
> Implementation"
> Can you please help me regarding this..
> Thanks in Advance
Hi. By JDBC spec, getMoreResults() will close any previous
result set. You have to do whatever processing you want
with a given resultset before getting another from the same
statement.
Joe Weinstein at BEA Systems
|||Hi Joe,
I have a problem again while retriveing multiple results sets from SQLServer
stored procedure
I used call.execute(), then tried to retrive OUT parameters using
call.getString(1) , it works fine
later im trying to retrive multiple resultsets one after other.
As you said first i tried to retrieve first resultset using
rs1 = call.getResultSet();
and tried to retrieve second one
call.getMoreResults()
rs2 = call.getResultSet()
But im getting rs1 and rs1 are null. Im not getting resultsets
But when i execute this stored proc using Query Analyser. It returns both
resultsets and i can see the records.
But using jdbc im not able to retrieve.
Can you tell me why?
thanks in advance
sudha
"Joe Weinstein" wrote:

>
> jaya nair wrote:
>
> Hi. By JDBC spec, getMoreResults() will close any previous
> result set. You have to do whatever processing you want
> with a given resultset before getting another from the same
> statement.
> Joe Weinstein at BEA Systems
>

multiple resultsets

I am just having many problems trying to call a procedure with multiple
parameters in multiple datasets. The proc works fine in query analyzer with
each parameter specified. I've got all the fields in each of the datasets,
and I've drug them all into the report layout. right now, my rpt is failing
with this:
The value expression for the textbox 'fieldname' refers to the fields
'fieldname'.
Report item expressions can only refer to fields within the current data set
scope or,
if inside and aggregate, the specified data set scope.
Can anybody tell me how to get around this? each of the datasets, when run
individually, work fine. (using the ! exclamation to run) but i receive
this error for one of the datasets when trying to preview the entire report.
-- Lynn
LynnI fixed that error. Somehow the fields of dataset1 got into the fields of
dataset5. but, now i get this:
An error has occurred during report processing.
Annot read the next data row for the data set DataSet3.
There is insufficient result space to convert a money value to varchar.
Again, each procedure call works fine in QA.
-- Lynn
"Lynn" wrote:
> I am just having many problems trying to call a procedure with multiple
> parameters in multiple datasets. The proc works fine in query analyzer with
> each parameter specified. I've got all the fields in each of the datasets,
> and I've drug them all into the report layout. right now, my rpt is failing
> with this:
> The value expression for the textbox 'fieldname' refers to the fields
> 'fieldname'.
> Report item expressions can only refer to fields within the current data set
> scope or,
> if inside and aggregate, the specified data set scope.
> Can anybody tell me how to get around this? each of the datasets, when run
> individually, work fine. (using the ! exclamation to run) but i receive
> this error for one of the datasets when trying to preview the entire report.
> -- Lynn
> Lynn|||I fixed that error, too. Now the report returns. But no values are returned
for Dataset1, dataset3 and dataset5. And, dataset2 and dataset4 only contain
duplicates.
for example, dataset2 is supposed to be like this:
Symbol #Trades Volume Total $
..
...
...
but i get only this:
CRNX 213
It returns only Symbol and #trades, and it displays only one, replicated
over and over. Same for dataset4. It should be:
EndPoint #Trades Volume Total $
...
....
.....
but i get only 5 of these:
6BM6 2
Any direction?
-- Lynn
"Lynn" wrote:
> I fixed that error. Somehow the fields of dataset1 got into the fields of
> dataset5. but, now i get this:
> An error has occurred during report processing.
> Annot read the next data row for the data set DataSet3.
> There is insufficient result space to convert a money value to varchar.
> Again, each procedure call works fine in QA.
> -- Lynn
>
> "Lynn" wrote:
> > I am just having many problems trying to call a procedure with multiple
> > parameters in multiple datasets. The proc works fine in query analyzer with
> > each parameter specified. I've got all the fields in each of the datasets,
> > and I've drug them all into the report layout. right now, my rpt is failing
> > with this:
> >
> > The value expression for the textbox 'fieldname' refers to the fields
> > 'fieldname'.
> > Report item expressions can only refer to fields within the current data set
> > scope or,
> > if inside and aggregate, the specified data set scope.
> >
> > Can anybody tell me how to get around this? each of the datasets, when run
> > individually, work fine. (using the ! exclamation to run) but i receive
> > this error for one of the datasets when trying to preview the entire report.
> >
> > -- Lynn
> > Lynn

multiple resultset in the report

I have a stored procedure which returns multiplr resultsets ,and I'd like to
show it in different tabls in my report.Is it doable?
Thanks for your help.
RayRay, No, seperate result sets cannot be accessed within one dataset.
RS will always use the first resultset. You will either have to merge
the results into one set or create a split version of the SP, one SP
producing one set another SP to produce the other and use them in
seperate datasets.
Chris
ALI-R wrote:
> I have a stored procedure which returns multiplr resultsets ,and I'd
> like to show it in different tabls in my report.Is it doable?
> Thanks for your help.
> Ray