No fields for dynamic SQL stored procedure in SSRS

Question

I have the following SP which works correctly when ran on its own:

USE [Orders]
GO
SET FMTONLY OFF; 

CREATE PROCEDURE [dbo].[Get_Details_by_Type]

@isArchived varchar(10),
@Type varchar(50)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    declare @sqlQuery nvarchar(max)
          IF(@isArchived = 'ALL')
            BEGIN
                set @sqlQuery  = 'SELECT *  FROM [dbo].[Orders] 
              WHERE ' + @Type + ' != € 
                ORDER BY [IDNumber]'
                exec sp_executesql @sqlQuery
            END
        ELSE
            BEGIN
            set @sqlQuery  = 'SELECT * FROM [dbo].[Orders] 
          WHERE ' + @Type + ' != € AND [isArchived] = ' + @isArchived + ' ORDER BY [IDNumber]'
            exec sp_executesql @sqlQuery
        END
END
SET FMTONLY ON; 

The problem I'm having is that when I add a DataSet for a SSRS report, it pulls no fields/columns in the Fields section. I'm guessing it's due to the dynamic SQL?

How can I resolve that?

Answer 1

The Problem
Stored procs which contain Dynamic Sql and Temp tables are the bane of wizards like SSRS and ORM generators like Linq2SQL and EF reverse engineering tools.

This is because the tools SET FMTONLY ON; (or more recently, sp_describe_first_result_set) prior to running the PROC, in order to derive the resultset schema produced by the PROC so that mappings for the ReportViewer UI can be generated. However, neither FMTONLY ON nor sp_describe_first_result actually execute the PROC.

e.g. the tool will do something like:

SET FMTONLY ON;
EXEC dbo.MyProc NULL;

Some Workarounds:

• Manually editing the RDL / RDLC file to insert the actual result set column names and types.
• Temporarily dropping the real proc and substituting it with one which returns a data set of zero or more rows with the actual data types and column names returned by the real proc, running the wizard, and then reverting the real proc.
• Adding SET FMTONLY OFF; as the first line in the PROC - this will force execution of the PROC (although your proc may fail because of null or dummy parameters passed in by the tool). Also, FMTONLY is being deprecated
• At the start of the proc, adding a dummy statement which returns the actual schema(s) of the result set(s), wrapped in a conditional branch which never gets executed.

Here's an example of the last hack:

CREATE PROCEDURE [dbo].[Get_Details_by_Type]
  @isArchived varchar(10),
  @Type varchar(50)
AS
BEGIN
   -- For FMTONLY ON tools only
   IF 1 = 2
     BEGIN
       -- These are the actual column names and types returned by the real proc
       SELECT CAST('' AS NVARCHAR(20)) AS Col1, 
              CAST(0 AS DECIMAL(5,3)) AS Col2, ...
     END;
-- Rest of the actual PROC goes here

FMTONLY ON / sp_describe_first_result_set are fooled by the dummy conditional and assumes the schema from the never-executed branch.

As an aside, for your own sanity, I would suggest that you don't SELECT * in your PROC - rather explicitly list all the real column names returned from Orders

Finally, just make sure you don't include the SET FMTONLY ON; statement in your proc (from your code above!)

END - Proc
GO **
SET FMTONLY ON; ** This isn't part of the Proc!

Answer 2

This is a very late addition, but for those of us new to SSRS/Report design: Remember to check the order of the parameters within the Report Data pane in Visual Studio. They will be created top to bottom, so a parameter on top cannot depend on a parameter on bottom. This was an issue I had with one report but the error message I received did not state that, so I chased my tail for hours trying to resolve it.

Answer 3

Follow below given steps

•   Delete DataSource. Create a new Data Source .
•   Delete DataSet. Create a new DataSet .
•   Use Query Designer. 
•   Add valid parameter when asked  .
•   One should get result for provided prarameters . 
    Donot click on refresh Field .
•   Then Report parameters and Report field will appear .
•   Now Filter criteria should work.

OR

**Add the Report field manually . It works.**

If Stored procedure can not retrieve schema data or metadata , we should manually specify the report fields.

Answer 4

Don't mean to revive a dead thread but was driving me crazy when upgrading our reports from SSRS 2005 to SSRS 2016 where Stored Procedures used openquery.

We narrowed it down to reports that had fields with empty values in. So we added this at the start of the Stored Procedure:

SET CONCAT_NULL_YIELDS_NULL OFF;

which meant we didn't need to CAST every field.

Answer 5

Following is what I did to overcome the issue - Fields are not listed in SSRS

1. Originally I have stored procedure; and it returns data when I execute the dataset. But the fields are not listed in SSRS

2. I copied the text of the stored procedure and made the dataset as Text instead of Stored Procedure. Refer How to: Refresh Fields for a Dataset

3. It produced errors and I "ignored" those errors. Refer The Declare cursor SQL construct or statement is not supported.

4. Now I verified that the fields are populated for SSRS

5. Now I updated the dataset to use my Stored Procedure

After the above steps, do a refresh of the dataset as shown below:

Answer 6

If anyone is still facing this issue, I solved what appears to be a similar problem with ssrs and dynamic sql.

1. For ssrs to properly map the fields from the SP,
2. select the "Text" option under query type,
3. enter the SP name and parameters like you are calling it from and SSMS window. sp_YourStoredProc @Parameter1....@ParameterN
4. Click on the refresh fields button.
5. When the statement runs, the fields will refresh, and your matrix will be populated.

BTW, I'm using SQL 2012

Hope this helps.