SSRS Dataset using #Temp Tables

Question

I could have sworn I have done this before.

I have multiple Datasets in my SSRS .rdl. The first Dataset creates the #Temp Tables which subsequent Sub-Reports and SQL Server Stored Procedures and Datasets utilize. Also, utilizing the

Use single transaction when processing the queries

I have included the following in the SQL Server Stored Procedures...

	--	This faux IF Statement exists when the SQL Server Stored Procedure utilizes #Temporary Tables.

	--	This allows any remote service call to this SQL Server Stored Procedure, like Microsoft Visual

	--	Studio. FMTONLY defaults to ON and a remote call has a "weird" behavior of ignoring conditional

	--	statements such as and If (condition), It looks through all logic to give all possible result

	--	sets that could be returned from the Stored Procedure. This is why it is able to break through

	--	the IF 1=0 logic and turn itself off. That being said, when this is set to off, the

	--	creation of the table adapter must run the whole SQL Server Stored Procedure before it can get

	--	column names. So FMTONLY is	pretty much just a setting so an application can 'LOOK' through a

	--	section of code and find returnable column names without returning the data. There is a problem

	--	with using #TempTables because in order for them to be seen by the scan done by FMTONLY, the code

	--	to create them must actually be ran, hence why the use of the statement 

	--		" IF (1=0) BEGIN SET FMTONLY OFF END ".

	--	This would allow the entire SQL Server Stored Procedure to run even if the SQL Server Stored

	--	Procedure was read with FMTONLY initially set to ON.

	IF 1 = 0

		BEGIN

			SET FMTONLY OFF

			;

		END

		;
```Which should allow Microsoft Visual Studio and the Dataset to recognize the #Temp Tables.

For the Second Dataset which utilizes #Temp Tables created by the first Dataset, I have its Query type as ***Text*** with the following

EXECUTE [CLM_0014].[DeniedReasons] @StartDate_In,

```sql
									@EndDate_In,

									@BeneficiaryType_In
```WITH RESULT SETS

```sql
(

	(

		[ReportSection] VARCHAR(30),

		[RowNumber] INTEGER,

		[ServiceCenterID] VARCHAR(4),

		[PlanName] VARCHAR(4),

		[ReportBeginDate] DATE,

		[ReportEndDate] DATE,

		[ClaimType] VARCHAR(20),

		[DenialCategory] VARCHAR(30),

		[ClaimExplanation] VARCHAR(255),

		[ClaimExplanationCount] INTEGER,

		[ClaimAmount] MONEY

	)

)
```;

Microsoft Visual Studio then throws me an error message...

***Microsoft SQL Report Designer***

***Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct***

And ***<Click>*** on ***Details>>***

***Invalid object name '#Temp_Table_Claim'.***

***EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time***

I could have sworn I have done this in the past...re-use a #Temporary Table being defined and created in a previous Dataset.

At this point, I am changing to Permanent ##Temporary Tables which doesn't really lend itself to multiple people running the report. Not a very good solution. And creating Staging Tables is not really an option...just too much over head.

Can anyone suggest how I might be able to get around this and re-use #Temporary Tables that have been created in a previous Dataset?

Thanks in advance for your review and am hopeful for a reply.

Answer 1

Hi @Bobby P

If the dataset is not update frequently, why not use staging table?

Best regards,

Cosmog Hong

Answer 2

The first Dataset creates the #Temp Tables

Why are you so sure that SSRS execute the dataset in a specific order?

And keep in mind, if SSRS close the connect to database & reopen it, then temp table gets automatically dropped.

Better use a permantent table.