Solved

How can I pass 3 #temp tables from a sp to SSRS to use to create 3 tables?

Posted on 2010-11-18
9
417 Views
Last Modified: 2012-06-21
Hi, I'm using sql 2005.  Can this be done?  Can someone show me an example?  Thank you.  
0
Comment
Question by:lapucca
9 Comments
 
LVL 10

Accepted Solution

by:
itcouple earned 500 total points
ID: 34170968
Hi

SSRS accepts only one Stored procedure result per dataset. (Microsoft was suggested to change that using microsoft connect website).

So your options are:
1) Create 3 Sps
2) Expand Sp to pass extra parameter which will identify which temp table you want to use.
3) If the structure of the temp tables is exactly the same you can add extra table (table1, table2 , table2) and union them and in SSRS in each dataset just filter based on the extra column.

Hope that helps
Emil
0
 
LVL 13

Expert Comment

by:devlab2012
ID: 34171131
create three different datasets in SSRS for three different database tables.
You can get different #temp tables from one sp using some parameters or create three different sp.
0
 

Author Comment

by:lapucca
ID: 34175153
The 3 temp tables are created in one stored procedure.  The 3 tables are related in creating them so it won't make sense and cannot break them down to 3 separate sp.  I currently have 3 select statment at the end of the sp and when creating reports it only detects 1.  Where can I see example on using parameters to use these 3 temp tables in 1 sp?  Thank.
0
 
LVL 4

Expert Comment

by:BostonMA
ID: 34175230
In order to give you the best solution, can you explain why do you need to display the results of the temp table in your report?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:lapucca
ID: 34175579
I'm doing a differences report.  Table one shows entries that are in our production side but not in the remote side.  The 2nd is the opsite of that, it shows what's in the remote but not in the production.  The 3rd shows the entries that exist in both but there are discrepencies in a couple of the colums.  Thaks.
0
 

Author Comment

by:lapucca
ID: 34175608
Oh, and I want to output 3 different tables in the report for these 3 temp tables. Thanks.
0
 

Author Comment

by:lapucca
ID: 34175708
Each table would have their own column header since they all have some common columns but definitely some different ones too.  The 3rd one has a lot more columns than the previous 2.  Thanks.
0
 
LVL 4

Expert Comment

by:BostonMA
ID: 34190229
Per devlab2012 comments, the best solution would probably be to use dynamic sql to conditionally output the temp table based on the parameter you pass the stored procedure.  See this page as an example
http://www.mssqltips.com/tip.asp?tip=1160


0
 

Author Closing Comment

by:lapucca
ID: 34194140
Thanks everyone.  Yeah, creating the tables to the database then delete them is not going to work.  Users who run the report will not have these type of permissions.  I guess I would just need to have 4 (yeah, increase now to 4) sp.  In each of the sp, the 2 temp tables would have to be re-created which is horrible performance and very in-efficient.  However, that is the limit of working with SSRS.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql help 5 52
Problem with SqlConnection 4 157
SQL query to summarize items per month 5 52
How to simplify my SQL statement? 14 50
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now