No Fields for Dynamic SQL Stored Procedure in Ssrs with Set Fmtonly

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

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!

How to list fields from dynamic SQL query in SSRS dataset

Someone over at the MSDN forums suggested I select into a temp table, so I replaced the final exec statement with

declare @CarrierList table (Listname varchar(200), PatCount int);
insert @CarrierList exec(@CPSQuery)
select * from @CarrierList

and it seems to be working fine now.

SET FMTONLY OFF effects on stored procedures with dynamic query

If you are using SQL Server 2012+ you could use WITH RESULT SETS to define resultset:

CREATE OR ALTER PROCEDURE dbo.mysp_test
AS
BEGIN

   DECLARE @sql NVARCHAR(MAX) = 'SELECT id, b FROM dbo.tab  ' + 'WHERE 1=1';

   EXEC sp_executesql @sql

END
GO

SELECT *
FROM sys.dm_exec_describe_first_result_set (
  'EXEC dbo.mysp_test'
  ,NULL
  ,NULL
);

The metadata could not be determined because statement 'EXEC sp_executesql @sql' in procedure 'mysp_test' contains dynamic SQL.

Using WITH RESULT SETS:

CREATE OR ALTER PROCEDURE dbo.mysp_test
AS
BEGIN

   DECLARE @sql NVARCHAR(MAX) = 'SELECT id, b FROM dbo.tab  ' + 'WHERE 1=1';

   EXEC sp_executesql @sql
   WITH RESULT SETS(  
        (id INT NOT NULL,
         b CHAR(1)
        )  
    ); 
END
GO

SELECT *
FROM sys.dm_exec_describe_first_result_set (
  'EXEC dbo.mysp_test'
  ,NULL
  ,NULL
);

DBFiddle Demo

SQL Server stored procedure not working through codesmith

I work for CodeSmith Tools. The following detailed thread will describe the issue you are running into and how to resolve it.

SSRS Dataset Field Refresh not updating for Tablix

Just found it... I don't understand the need for hidden menus like this..

Click the report itself, then go up to View on the menu and at the very bottom there is "Report Data". From here you can select your dataset and go to its properties and refresh the reports attached dataset's fields. What a pain in the butt.

Here is a link that helps better explain it.
http://blog.dontpaniclabs.com/post/2012/01/26/Developings-Reports-for-SQL-Server-Reporting-Services

SSRS Sub-Report not displaying in production

Seriously, sometimes I hate M$.

The stored procedures for the two sub reports only bring back 4 fields, one I don't even use (district ID). The report only has three text boxes in a table, one is just a literal. The other two are

=Description + " (" + code + ")"

and

=DistCounts

ABSOLUTELY NOTHING TO THESE.

Finally we took the one that worked, copied the XML to an new sub report, changed the dataset, field names and literal, redeployed, and it works. Comparing the one that works with the 2 that didn't ... shows nothing of interest. Arrrrgh!

As I said, both used the same input parameters, the stored procedures were just counting different fields. Wasted more than a day on a wild goose chase, and we still don't know what was wrong.

Retrieve fields schema in Visual Studio of stored procedure which uses temp tables

After googling for a while I found a bizarre solution putting:

IF 1=0 BEGIN
SET FMTONLY OFF
END

At the start of your stored procedure allows the designer to correctly get the schema information. This code obviously never runs but it solves the issue. FMTONLY is related to only returning meta data about a stored procedure. I am using sql server 2005.

---

Related Topics