SQL Reporting Services - SP with Temp Table

Posted on 2005-05-05
Last Modified: 2012-06-22

Is it possible to create a report using the report wizard that will return the results from a Stored Procedure that contains a temporary table?

I have tried this but I get an error message at the "Design the Query" stage of the Wizard......

"There is an error in the query. Invalid object name '#Temp'.

I also get the same message if I try to display the estimated execution plan in Query Analyser.

Thanks in advance,

Question by:ive5005s
    LVL 7

    Accepted Solution

    Can;t use the Report Wizard.  You have to create the report without using the wizard, meaning you have to manually define all of your fields.

    Author Comment

    Thanks ABaruh,

    Please could you point me to some resources that will explain how I to do this?

    LVL 7

    Expert Comment

    When you open a new project in Visual Studio, you have two choices:

    Report Project Wizard
    Report Project

    choose the latter.
    LVL 7

    Expert Comment

    I did answer his question about why he was getting the error message - you cannot use the Report Wizard if the SQL stored proc in the dataset has temp tables.  That should be worth getting the solution points.

    Author Comment

    Thanks ABaruh.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
    Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    732 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

    22 Experts available now in Live!

    Get 1:1 Help Now