No fields for dynamic SQL stored procedure in SSRS with SET FMTONLY

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?

Ellerey

try adding SET FMTONLY OFF; after SET NOCOUNT ON – Maller

Ok I have edited the code above to reflect your changes but nothing happens. Am I putting it in the right place? – Ellerey

No, you should add it inside the sp, after (or before) SET NOCOUNT ON; – Maller

No luck. Adding manually does work but there are a lot of fields :( – Ellerey

what does "adding manually" means? – Maller

Adding each field manually to the Fields section of a DataSet in the SSRS report. – Ellerey

weird, can you try using SET FMTONLY ON;?, just to see if the behavior changes – Maller

If someone is facing this even after refreshing the dataset fields and ssrs still not detecting new columns, simply go to query editor for the dataset and execute it once. SSRS will now detect all the fields returned by the query. More details here: inkeysolutions.com/blogs/… – Ingalls

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.
• Temporarily adding SET FMTONLY OFF; as the first line in the PROC - this will force execution of the PROC. Revert the original PROC once done (although note 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!

Countermove

Thanks for the detailed explanation and I only have SELECT * for the purpose of this example since I have many columns. I will probably just add the columns in this instance by hand but for the future I will try the hack. – Ellerey

Saved my bacon 1/28/2015 – Infringe

Here's an example of the last hack: worked as well! Great! +1 – Forgather

A gotcha re: the trick pointed out at the end...if you happen to have a dynamic statement AND a non-dynamic statement in your proc, if you don't use SET FMTONLY OFF you will get the fields from the non-dynamic statement, even if it occurs second in the proc, and is wrapped in a false if statement (as you stated, but just pointing out another scenario that I overlooked myself). – Gertrudgertruda

That hack is amazing and terrible and I am using it right now. I sort of can't believe it works (but I understand why it does) – Siloxane

Answer 2

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.

Logging

This works great in Report Builder SQL Server 2016 as well.. thanks! – Blucher

Answer 3

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:

Ocasio

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.

Carbamate

YOU MADE MY DAY !! THANKS !! – Tonneau

Answer 5

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.

Godunov

Answer 6

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.

Ephesus

Answer 7

I ran into a similar issue. I have some debug statments that I often turn on/off w/ a flag variable.

this line was still affecting the results, even though it doesnt run (@debug =0)

--check your work
if @debug =1  select @reportStartDate  RSD ,@reportEndDate 
         , @yearStart YS, @folderStartDate  FSD, @MonthCount MC
         , @dataStart  DataStart

i discovered the issue by running the sp w/ fmtONly on

SET FMTONLY ON
exec rpt_mo_DashBoard_YTD_Total  'ajax20','12/1/2019'

I solved the problem by commenting out the code in the SP. Yes, now I have to uncomment /comment each return statement when its time to debug...

Dysphasia