Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 Reporting Services Development
 Multiple result sets not displaying.

Author  Topic 

Brutoxx
Starting Member

21 Posts

Posted - 2004-11-10 : 16:27:44
I created a report that runs a StoredProcedure which returns 3 results sets.

My report consists of a table.

My problem is when I run the report it only displays one result set, juts the first one.

Please let me know if any of you guys know what I am doing wrong, thank you!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-10 : 16:29:22
Are the columns the same in all 3 result sets? If so, just UNION them together in the stored procedure. If not, then you'll need to create 3 stored procedures instead of the 1 for RS. Then you'd use 3 tables in RS, one that references each sproc.

Tara
Go to Top of Page

Brutoxx
Starting Member

21 Posts

Posted - 2004-11-10 : 16:36:46
Thank you for such a fast reply.

The columns are not the same.

Is that really the only way out? I have about 10 sprocs and rewriting those would be a pain.


Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-10 : 16:49:17
Yes. Stored procedures really shouldn't be designed so that they return more than one result set anyway. IMO of course. I wonder what others think.

Tara
Go to Top of Page

Brutoxx
Starting Member

21 Posts

Posted - 2004-11-10 : 16:55:28
I guess I have to break it into 3 sp's...

I just can't conceive the idea that microsoft would not think about that, since multiple result sets on stored procedures are very common.

Have a nice day!
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-10 : 17:49:23
Stored procedures are one thing. Reports that utilize multiple result sets are always written in a report-subreport hierarchy. I don't know of any reporting product that supports multiple results sets in one procedure call, especially those with completely different structures. Crystal Reports doesn't support it, for example.
Go to Top of Page
   

- Advertisement -