How to configure Stored Procedure with Temporary Tables for use with SqlDataSource component

HOW-TO

Configure Stored Procedure with Temporary Tables for use with SqlDataSource component

DESCRIPTION

The SqlDataSource uses the standard ADO.NET data provider to load the report data and metadata. In case the stored procedure uses temporary tables the data provider fails to retrieve the schema.

Before SQL Server 2016, the mean for obtaining the data schema of a temporary table is the FMTONLY setting.

As of SQL Server 2016 Temporary Tables can be replaced with the better performing Memory-Optimized Tables. This includes mechanism allowing you to get the schema of temporary objects correctly. The FMTONLY setting is deprecated. For more details, please check Using Memory-Optimized Tables to Replace SQL Server Temp Tables and Table Variables.


SOLUTION

• For MSSQL engines before SQL Server 2016.
  In order to use stored procedures containing temporary tables, you have to turn off FMTONLY in a seemingly never executed statement. Thus the data schema of temporary tables can be read by the SqlDataSource Wizard as each state of IF-ELSE statements is evaluated when the SQL query is run by the SqlDataSource Wizard. The check will be skipped when the same SQL query is used by calling application at run-time.
  
  For example consider the following SQL CREATE stored procedure statement:
  USE AdventureWorks

  GO

  CREATE PROCEDURE dbo.Demo

       

  AS

  BEGIN

      SET NOCOUNT ON;

       IF 1=0 BEGIN

         SET FMTONLY OFF

       END

      

      CREATE TABLE #Temp

      (

          ProductID   integer NOT NULL,

          Name        nvarchar(50) COLLATE DATABASE_DEFAULT NOT NULL

      );

      

      INSERT INTO #Temp

          ([ProductID], [Name])

      SELECT

          p.[ProductID], p.[Name]

      FROM Production.Product AS p

       

      SELECT

          t.[Name], t.[ProductID]

      FROM #Temp AS t

      

      DROP TABLE #Temp;

  END;

  

• For MSSQL Engines after SQL Server 2016 (even 2014)
  You can use memory optimized tables, where the data schema can be obtained correctly when you work with SQL Server 2016.
  USE AdventureWorks

  GO

  CREATE TYPE MemoryOptTabVar as TABLE (

     [ProductID] integer NOT NULL ,

     [Name] nvarchar(50) NULL,

     INDEX IX_MemoryOptTabVar_ID NONCLUSTERED ([ProductID])

  ) WITH (MEMORY_OPTIMIZED = ON)

  GO

   

  CREATE PROCEDURE dbo.MemoryOptTableVarExample

  AS

  BEGIN

  SET NOCOUNT ON;

      DECLARE @MemOptVarTab MemoryOptTabVar ;

         INSERT @MemOptVarTab ([ProductID], [Name])

      SELECT

          p.[ProductID], p.[Name]

      FROM Production.Product AS p

         SELECT * FROM @MemOptVarTab

  END

  GO
  
  
  

If you consider working with SQL Server 2016, you may also find interesting Faster temp table and table variable by using memory optimization.