SQL Server Performance

creating a report - stor proc with #temp table

Discussion in 'SQL Server 2005 Reporting Services' started by Mattie_Bala69, Feb 21, 2007.

  1. Mattie_Bala69 New Member

    Hey,
    I am trying to create a report with a stor procedure. the problem i have is this stor proc has some #temp tables that i have created to get to the result set and when i use the wizard to create the report it gives me an error saying invalid table #temp.
    eg i have it like this
    Select * into #temp1 from tablea
    Select * from #temp1
    Could some one help me please.
    Thank you,
    Mattie
  2. ranjitjain New Member

    Instead of Select * INTO, first you create the temp table and then use insert into that temp table.
    Like this:

    Create table #temp1(your_columns)
    Insert into #temp1 select * from tablea

    after this change run your report wizard and check whether this change is helping or not.
  3. Mattie_Bala69 New Member

    Hey,
    I tried it does not work.
    Thank you,
    Mattie
  4. Mattie_Bala69 New Member

    Hey,
    Its working now. I have no idea why it would not work the first time.
    Thank you soo much for your help.
    Mattie
  5. MichaelB Member

    It kinda sucks doesnt it? We do select into for speed... (load is not logged) and then we cant use it. By the way if you create the temp table then get the report working you can put it back to a select into once the report is attached to the proc properly! I do this so I can keep my speed. This was suposed to be fixed with SP2. I guess it will stay as a feature! Pitty, since Crystal can understand a select into...



    Michael
    MCDBA

    "The fear of the Lord is the beginning of knowledge,
    but fools despise wisdom and instruction." Proverbs 1:7
  6. Roji. P. Thomas New Member

  7. Adriaan New Member

    Problem with SELECT INTO is that you're locking the source table. This is what the CREATE TABLE with the INSERT INTO SELECT syntax helps to avoid.
  8. MichaelB Member

    true, but without the logging, select into will return much faster results. It has to be weighed on a case-by-case basis.

    Michael
    MCDBA

    "The fear of the Lord is the beginning of knowledge,
    but fools despise wisdom and instruction." Proverbs 1:7
  9. Adriaan New Member

    Well, if SELECT INTO sometimes works, sometimes doesn't work, then it isn't really an option, is it?
  10. Mattie_Bala69 New Member

    Hey Guys,
    I found out the report works but the i get warnings in Microsoft Visual Studio when i check in the report, it does not seem to recognize the field names in the stor proc. But strangly I can still run the report. and I can still deploy the project.
    I thought i will just let you all know. If someone knows a solution to get rid of the warnings please let me know.
    Thank you,
    Mattie
  11. MichaelB Member

    I found a solution, but it takes a bit of time

    1. change the proc to insert into a table variable.
    2. go through all the steps to create the report
    3. change the proc back to select -into and the report will still run fine.

    Michael
    MCDBA

    "The fear of the Lord is the beginning of knowledge,
    but fools despise wisdom and instruction." Proverbs 1:7

Share This Page