SQL Server Performance Forum – Threads Archive

creating a report – stor proc with #temp table

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

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.

Hey,
I tried it does not work.
Thank you,
Mattie

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

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

You can try setting SET FMTONLY ON, before executing the procedure and see if that helps.
Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

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.

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

Well, if SELECT INTO sometimes works, sometimes doesn’t work, then it isn’t really an option, is it?

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

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

]]>