I am trying to create an SSRS report, My DBA won't allow me to use stored procedures. In the Dataset Query Section I am trying to create a temp table to hold what I need to run the report.The query works in SQL 2008 with no issues but when I try to put it in the Dataset Query Section I get an error that tells me that Create Table is not supported. Is there a way around this and if so how? Thanks

you cannot create a temp table in SSRS Query Section. Try to create a view and dump your data into it and use that view as your dataset. Is there any complication reasons for not using the SP ??Dasari

Hi KariP6770, It is definitely possible to use temp tables in Reporting Services. How are you creating the report? If you are using the wizard, when you reach the window where you write your T-SQL code, write a simple SELECT statement such as SELECT TOP 10 * FROM tblProduct (without using any Temp table). Once you are done, go back to your data set and write your code using Temp tables. Hope this helps, please feel free to ask if you have any questions.

Hello Kari, It is 100% doable, Can you please paste us Error message for further analysis? Also CTE can be used same as Temp Tables & Table Variables. Thanks KumarKG, MCTS

Hi KariP6770, It is completely ok to create a temp table in the dataset query section of SQL Server Reporting Services (SSRS). As _Kumar mentioned, we can use a #temp table or @table variable. SQL statements for creating the temporary table using create table statement: Create table #MyTempTable(col1 int) Insert into #MyTempTable (col1) select col1 from <TargetTableName> The code using a table variable might like below: Declare @MyTempTable(col1 int) insert into @MyTempTable(col1) select col1 from <TargetTableName> If it still cannot work fine with either way, could you please provide me with the original SQL statement and the details of the error? More information about Decleare @Local_Variable, please refer: http://msdn.microsoft.com/en-us/library/ms188927(v=SQL.100).aspx. Thanks, Lola Wang Please remember to mark the replies as answers if they help.