I have been working for days to translate a report out of an old system and in SQL reporting services. Getting the basic code down to get the required data was easy, however getting it to work in reporting services has turned into a nightmare. Why, because I have been told that SQL reporting services does not allow temporary tables...HUH! Ok, so how am I supposed to take three data queries and munge them together into a report. Here is the query that does work, can anyone give me an idea of how to make this work given the limitations I have run up against. I have already thought of using a store procedure, but we have ruled that out since would likely have to do it via linked servers, which would be expensive. We thought of having it just create and then link real tables and then delete them...not sure thats going to work, and again probably talking linked servers to get that to work. Code: select distinct al3.asg_location into ##tmp1 from dbo.probsummarym2 AL3 inner join dbo.probsummarym1 AL1 on AL3.number=AL1.number where AL1.assignment='international client services' AND AL1.status='Closed' AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'} and al3.asg_location is not null SELECT AL3.asg_location as asg_location, Count (AL3.resolve_met) as met_sla into ##tmp2 FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3 WHERE (AL3.number=AL1.number) AND (AL1.severity_code<>'Scheduled' AND AL1.status='Closed' AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'} AND AL3.resolve_met='t' AND AL1.assignment='international client services' ) GROUP BY AL3.asg_location SELECT AL3.asg_location as asg_location, Count (AL1.status) as sch_closed into ##tmp3 FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3 WHERE (AL3.number=AL1.number) AND (AL1.assignment='international client services' AND AL1.severity_code='Scheduled' AND AL1.status='Closed' AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'} ) GROUP BY AL3.asg_location SELECT AL3.asg_location as asg_location, Count (AL1.status) as unsch_closed into ##tmp4 FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3 WHERE (AL3.number=AL1.number) AND (AL1.assignment='international client services' AND AL1.severity_code<>'Scheduled' AND AL1.status='Closed' AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'} ) GROUP BY AL3.asg_location select ##tmp1.asg_location, ##TMP3.sch_closed, ##tmp2.met_sla, ##tmp4.unsch_closed from ##tmp1 left outer join ##tmp2 on ##tmp1.asg_location = ##tmp2.asg_location left outer join ##tmp3 on ##tmp1.asg_location = ##tmp3.asg_location left outer join ##tmp4 on ##tmp1.asg_location = ##tmp4.asg_location group by ##tmp1.asg_location, ##TMP3.sch_closed, ##tmp2.met_sla, ##tmp4.unsch_closed order by ##tmp1.asg_location drop table ##tmp1, ##tmp2, ##tmp3, ##tmp4

Temporary tables should work in Reporting Services. I tried with the following query, and it worked as expected in SSRS 2005.SELECT OrderID, ProductName, Quantity, Products.UnitPrice into ##tmp1FROM [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductIDSELECT ##tmp1.*, OrderDate into ##tmp2FROM Orders INNER JOIN ##tmp1 ON ##tmp1.OrderID = Orders.OrderIDSELECT * from ##tmp2 DROP TABLE ##tmp1, ##tmp2

Are you sure because in my case every time I use temp table in store procedure it won't show up in dataset windows I tried all tricks none of em worked

When I plug the query into the report wizard I get the following: There is an error in the query. Invalid object name '##tmp1'.Invalid object name '##tmp2'.Invalid object name '##tmp3'.Invalid object name '##tmp4'.

Have you tried using the temp tables as subselects? SELECT tmp1.asg_location, TMP3.sch_closed, tmp2.met_sla, tmp4.unsch_closed FROM (select distinct al3.asg_location from dbo.probsummarym2 AL3 inner join dbo.probsummarym1 AL1 on AL3.number=AL1.number where AL1.assignment='international client services' AND AL1.status='Closed' AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'} and al3.asg_location is not null) tmp1 INNER JOIN (SELECT AL3.asg_location as asg_location, Count (AL3.resolve_met) as met_sla FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3 WHERE (AL3.number=AL1.number) AND (AL1.severity_code<>'Scheduled' AND AL1.status='Closed' AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'} AND AL3.resolve_met='t' AND AL1.assignment='international client services') GROUP BY AL3.asg_location) tmp2 ON tmp1.asg_location = tmp2.asg_location INNER JOIN (SELECT AL3.asg_location as asg_location, Count (AL1.status) as sch_closed into ##tmp3 FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3 WHERE (AL3.number=AL1.number) AND (AL1.assignment='international client services' AND AL1.severity_code='Scheduled' AND AL1.status='Closed' AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'} GROUP BY AL3.asg_location) tmp3 ON tmp1.asg_location = tmp3.asg_location left outer join ( SELECT AL3.asg_location as asg_location, Count (AL1.status) as unsch_closed FROM dbo.probsummarym1 AL1, dbo.probsummarym2 AL3 WHERE (AL3.number=AL1.number) AND (AL1.assignment='international client services' AND AL1.severity_code<>'Scheduled' AND AL1.status='Closed' AND AL1.close_time BETWEEN {ts '2006-05-28 00:00:00.000'} AND {ts '2006-06-04 00:00:00.000'} ) GROUP BY AL3.asg_location) tmp4 on tmp1.asg_location = tmp4.asg_location group by tmp1.asg_location, TMP3.sch_closed, tmp2.met_sla, tmp4.unsch_closed order by tmp1.asg_location Formatting sucks, but this gives the idea.

Ok, The replies got my Brain kick started, I've got it figured out now. The Report Wizard, doesn't like Temp tables, and for the record neither does the stright report builder. The rebuilder deals with this by use of the refreshfields button. When the refresh takes place it runs the query and is able to figure out the fields that it needs to allow the rest of the build to work. The Wizard doesn't have this button, and so trips on the Temp tables. There is a easy work around however. When you reach the dataquery window in the report wizard. Don't put in the whole query with all the temp tables and such. Instead put in a very simple query that just selects out the fields names and sets them null. so something like select field1 as null, field2 as null.....etc Then after the wizard finishes, do to the data query put in your real query, and hit the updaye fields button.... You should be good to go...and you get the nice pre-setup/formated report tables that the wizard generates as well...which was of course the real goal!

Have you tried using table variables instead of temp tables? Jarret

Thanks a lot. I was stuck with a query using temp tables acessing linked servers and you saved me. The dummySELECT statement did not work though. I used something like select Char_Field1='',Char_Field2 ='',Numeric_Field = 0 Thanks again

Using a Common Table Expression (CTE) may be another option that people want to explore as an alternative to temp tables. WIth a CTE you don't have to worry about disposing of your temp tables at the end of the query. You can multiple CTE's togeter just like you would tables.

http://dineshasanka.spaces.live.com/blog/cns!22A79FCE82651673!428.entry

Try wrapping a BEGIN and END tag around the code - I had the same problem, and that solved it for me.

Have you tried to use variable table? DECLARE @phone TABLE (Employee varchar(10) COLLATE database_default) insert into @phone (Employee) select Phone from someothertable

Here's the alternative that you're looking for.Place SET FMTONLY OFFat the end of the query.For example.SELECT name, addressINTO #tempFROM CustomerSET FMTONLY OFFSELECT * FROM #tempWhat this does is that it'll run the query and return the data fields without displaying the results. You would then run the SELECT statement to see the results

Here's the alternative that you're looking for.Place SET FMTONLY OFFat the end of the query.For example.SELECT name, addressINTO #tempFROM CustomerSET FMTONLY OFFSELECT * FROM #tempWhat this does is that it'll run the query and return the data fields without displaying the results. You would then run the SELECT statement to see the results I'm creating the query with Temp tablesfrom theRDL file with command type = Text, instead of as a stored proc.I set"Set FMTONLY OFF"on the first line of my query, instead of after creating the #temp. It also worked. Thanks.

Hi allYou do not need all those !!!Temp tables works fine on SSRS, without any complex,So easy,You have just to follow the following steps in SSRSThe steps are here : http://www.dbasesolution.com/forum/SSRS/forumlist.asp?forum_id=670mnaouar Ben Khelifa

Ok, so how am I supposed to take three data queries and munge them together into a report. I have already thought of using a store procedure, but we have ruled that out since would likely have to do it via linked servers, which would be expensive. We thought of having it just create and then link real tables and then delete them...not sure thats going to work, and again probably talking linked servers to get that to work. Halpo, You mention linkedservers, is this because the three queries used to create the temp tables are on three different database servers? If that's the case, then you're correct, SSRS won't save temp datasets and merge them into memory for you. If you can'trun thedirectly on the system that's your datasource, it won't work in reporting services.Garth H MCTS: SQL 2008 BI http://bitinkering.spaces.live.com/

I keep getting the same error too... once i get that i error hit ok and then go to Report dataset and right click and hit refresh and then i get the prompt to enter the parameters and once i enter them everything works fine...Hope this helpsthanks,Karen

THE FMTONLY OFF saved me lot of time. But I am not what it means and why it doesn't work when i use it as a first line in my stored proc( before creating temp table)

Hi all You do not need all those !!! Temp tables works fine on SSRS, without any complex, So easy, You have just to follow the following steps in SSRS The steps are here : http://www.dbasesolution.com/forum/SSRS/forumlist.asp?forum_id=670 mnaouar Ben Khelifa The link doesn't exist anymore.

I've found this issue exists with the SCCM based "reporting services" SSRS integration. My workaround is to create the rdl's completely independent of SCCM.