none
Can I create multiple tables in an embedded dataset or a Stored Procedure of SSRS in SQL Server 2008 R2?

    Question

  • Urgent question:

    Can I create multiple tables in an embedded dataset or a Stored Procedure of SSRS in SQL Server 2008 R2?

    I am having an issue with showing data in SSRS report that is created by the final table (not intermediary).  In both scenarios:  Stored Procedure and embedded TSQL code in a dataset  - intermediary tables are created before the data is used by the SSRS report.  Is there an issue with creating intermediary tables, write to the final table, then showing data from the final table?   My task does call for creating intermediary tables, which are created depending upon the parameters passed from SSRS (selected by the user).  In this case, the user passes a Period parameter (date).

    If there is a known issue with any of the above - please advise on a work-around. 

    Example:

    SELECT ISBN,
                   Title,
                   Author
                   PublishDate
    INTO Table1 
    FROM TransTable

    SELECT *
    INTO Table2
    FROM Table1

    SELECT ISBN, AUTHOR
    INTO Final_Table
    FROM Table2
    WHERE PublishDate < GETDATE()

    SELECT * FROM Final_Table

    Thank you,

    Lenny

     


    Lenny Finkel
    Monday, August 29, 2011 5:11 PM

Answers

  • William,

    The issue that I was having worked.  But the only way it worked for me was: in the final step of the Stored Procedure - SELECT the data FROM the temp table - without an INTO. The result set was successfully passed to SSRS. 
    Thank you for your help.
    -Lenny

    Lenny Finkel
    • Marked as answer by Lenny F. _ Thursday, September 01, 2011 4:41 PM
    Thursday, September 01, 2011 4:41 PM

All replies

  • Yes, and no. While it's possible to create tables on the fly, you don't want to. What you want to do is create temporary work tables in TempDb. Simply add a "#" to the table name. It's also a good idea to drop the #temp table after use. Consider that Reporting Services will only consume the first rowset returned by a Stored Procedure. I also don't see why you're taking this approach--it can be done in a single step but I expect you have simplified this example...

    __________________________________________________________________
    William Vaughn
    Author, Mentor, Trainer, MVP
    Beta V Corporation
    William Vaughn's blog

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    “The Owl Wrangler” a fantasy fiction novel

    Please click the Mark as Answer button if a post solves your problem!

    Monday, August 29, 2011 7:06 PM
  • William,  you are correct - I have simplified my approach for the purpose of illustrating an example.  I have tried creating temporary tables (#) for intermediary and 'final' tables - instead of permanent tables -- the issue is the same.  The issue that I am noticing is that executing a Store Procedure, or embedded code that creates intermediary tables causes a problem, i.e., the result returned back to the SSRS report is blank (zero rows returned).  Once again, I have a need to create intermediary tables before I pull the data into the report from the Final table because I am creating a subset of data based on a user parameter (passed) by Period  - a given date.                                               Any other suggestions?        -Lenny     
    Lenny Finkel
    Monday, August 29, 2011 7:31 PM
  • Okay, have you tried executing the SP in SSMS? If this works, I suspect RS is being confused by the additional resultsets returned by that type of a query. Try using SET NOCOUNT ON to eliminate the extra resultsets. I still think it's entirely possible to create a filtering query that does not require temp tables--I do it all the time. Another approach is to return more rows and use a client-side report Filter expression to focus the rowset.

    __________________________________________________________________
    William Vaughn
    Author, Mentor, Trainer, MVP
    Beta V Corporation
    William Vaughn's blog

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    “The Owl Wrangler” a fantasy fiction novel

    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, August 30, 2011 4:29 PM
  • William,  I will try it out.  Thank you.
    Lenny Finkel
    Tuesday, August 30, 2011 7:02 PM
  • William,

    The issue that I was having worked.  But the only way it worked for me was: in the final step of the Stored Procedure - SELECT the data FROM the temp table - without an INTO. The result set was successfully passed to SSRS. 
    Thank you for your help.
    -Lenny

    Lenny Finkel
    • Marked as answer by Lenny F. _ Thursday, September 01, 2011 4:41 PM
    Thursday, September 01, 2011 4:41 PM