Problem: Microsoft SQL Server Reporting Services won’t populate the data set’s field list. Clicking Refresh Fields does nothing—the field list remains empty.
Is something wrong with the query? Is there a database permissions issue? Let’s check. Click the Query Designer button, then the red “!” (execute) icon. The query runs and…a normal result set is returned. Since the query executes correctly, the query and permissions must be fine. So, the blank field list indicates an SSRS bug—correct? Not necessarily.
SET FMTONLY ON
SSRS’s Refresh Fields process executes the data set’s query in a slightly different manner than we did in our debugging. Refresh Fields is only interested in the result set’s field list—not the data row portion of the result set—so it executes SET FMTONLY ON prior to running the query. SET FMTONLY ON tells Microsoft SQL Server not to process or return data rows. When the query is executed, an empty result set containing metadata (such as column names) but no data rows is returned.
However, once in a great while SQL Server encounters a very complex query scenario (say a call to a stored procedure that uses sp_executesql to assemble an OPENQUERY statement that is executed against a non-SQL Server linked server) where it can’t figure out column names unless it first builds the result set—which it won’t do because of SET FMTONLY ON. If it can’t figure out column names, SQL Server does not return any result set (vs. returning empty result set). Without a result set, SSRS’s Refresh Fields can’t populate the field list and so leaves the list empty.
To see if this is why your data set’s field list is blank, run your query proceeded by SET FMTONLY ON. If no result set is returned, you have a query issue to investigate.
I understand your description however i am still hitting a wall. I have an SSRS report calling a SQL Server2008 SP which dynamically creates SQL and issues an OPENQUERY against DB2. The SP executes perfectly within SSRS Query Designer but no fields carry to the DataSet object in my SSRS report. I have tried SET FMTONLY ON; and SET FMTONLY OFF; in the Stored procedure with no success. I even tried to embed the set command within the EXEC (@OpenQueryString) Statement (shot in the dark) no luck. My project hinges on this working. Do you have any advice?
——————– SP CALLED FROM SSRS. PARMS PASSED FROM SSRS ——–
ALTER PROCEDURE [AUDREP].[SP_LS_MIDS_TBACCT_MH] ( @in_ofc varchar(3), @in_period varchar(7) ) AS
BEGIN
SET FMTONLY OFF;
DECLARE @remotesql nvarchar(4000),
@localsql nvarchar(4000)
SELECT @remotesql = ‘SELECT YRMO_AC_PE as Period,DSSSCA_NO as AcctNo,FC_OFC_NO as FA_OFC_NO,FC_NO as FC_NO,AC_EOM_AST_AM as AssetEOM FROM MIDS.TBACCT_MH WHERE LEFT(MIDS.TBACCT_MH.dsssca_no,3) = ‘ +
AUDREP.UDF_quotestring(@in_ofc) +
‘ AND MIDS.TBACCT_MH.yrmo_ac_pe = ‘ +
AUDREP.UDF_quotestring(@in_period) ;
SELECT @localsql = ‘SELECT Q1.AcctNo FROM OPENQUERY(MIDAS, ‘ +
AUDREP.UDF_quotestring(@remotesql) + ‘) Q1’;
EXEC (@localsql);
END
GO
Why don’t you try this? Use SQL Profiler to trace what happens when you click “Refresh Fields” in SSRS. Take the trace output and run it in SQL Server Management Studio. If no result set is returned (i.e. the output is completely empty—no data rows, no column headers, nothing), then there is a query issue. If a result set is returned, you’re likely dealing with an SSRS issue.
Once we’ve determined where the problem lies, it should be easier to determine if/how we can troubleshoot it further.
I laughed as you described my exact scenario.
Stored proc that uses sp_executesql to run an openquery MDX against a cube.
I am thinking of two ways around this.
1) create a table variable in the stored proc & insert the results into there and then select them again. This works but the openquery MDX returns all ntext so you need to muck around with datatypes.
2) Create the fields manually in SSRS.. but this doesn’t work because SSRS can’t work out the source of the fields.
Any other ideas on how to modify the S Proc?
I suppose using sp_executesql is just habit because it protects against sql injection (to some degree). The openquery is unavoidable because there’s joins between mdx data and sql tables …don’t ask 🙁
I ran into the same problem but without sp_executesql . My proc returns two different datasets based on the value of the input parameter. For example it does the following:
IF @Input = 0 BEGIN
SELECT 1 AS Field1
END ELSE BEGIN
SELECT 1 AS Field1, 2 AS Field2, 3 AS Field3
END
This returns both select statements if SET FMTONLY ON is used. I haven’t found a solution yet.
I had an if statement at the start of the proc,
if param = null/empty
BEGIN
SELECT ‘version info’
return;
END
..followed by my actual query. This was likely the cause of the problem.
I also had encryption active. Deactivating both options fixed the problem. Once the fields were imported, I re-enabled the above changes without any untoward problems.
Hi kannankeril,
Can You plz eloborate how you fixed the problem of fields not being populated in the dataset while using a storedproc in SSRS?
Thanks in advance.
I looked at the profiler on the server I was running. I noticed two calls after I clicked the ‘!’ in the dataset field designer and ran the sproc. Both calls referenced the same Stored Procedure.
The first included the parameters I entered, which correlates to seeing a dataset on the designer screen.
The second one showed up after pressing the next ‘OK’ on dataset properties window. This one had NULL values assigned to the params.
I ran this in a query window and got a ‘command successful’ notice instead of a blank dataset. This is the problem.
I think Kannankerril was alluding to the fix.
SOLUTION:
— Alter your sproc to return blank fields if NULL parameter values are provided.
This only took me 2 ISNULL checks (1 for each param) and a single query for my expected fields listed as”
‘Select ” as Field1, ” as Field2, ….’.
Works like a charm in SSRS 🙂
The way I handled this was to add a bit param @get_column_names_only, set it to false by default, and then just select column names if true. This only works if you know the column names you are going to use. In my case the column names were simply a guid value and label, used to get distinct name/value pairs from entities for SSRS dropdown lists. When creating the dataset in SSRS, I set @get_column_names_only to false, but it still pulled the column names. With out this, it just came up blank.
— =============================================
— Author: Ron Smith
— Create date: 4/17/2012
— Description: Generates Drop Down Select list for report params
— =============================================
create procedure p_param_select
@get_column_names_only bit = 0,
@include_all bit = 1,
@entity_name nvarchar(200),
@value_name nvarchar(200),
@label_name nvarchar(200),
@filter nvarchar(max) = ”
as
begin
if @get_column_names_only = 1
select
cast(null as nvarchar(36)) value,
cast(null as nvarchar(200)) label
else begin
declare @sql nvarchar(max)
set @sql = ”
if @include_all = 1
set @sql = ‘
select
cast(”” as nvarchar(36)) value,
cast(” – All – ” as nvarchar(200)) label
union’
set @sql = @sql + ‘
select distinct
cast(‘ + @value_name + ‘ as nvarchar(36)),
‘ + @label_name + ‘
from ‘ + @entity_name + ‘
where ‘ + @value_name + ‘ is not null’
if @filter ”
set @sql = @sql + ‘
‘ + @filter
set @sql = @sql + ‘
order by
value’
exec(@sql)
end
end
go
I’m also getting an empty dataset list. The query runs successfully in the Query Designer and gets results. The query is using a subquery and a UNION and table alias’s. How do I get it to display the dataset list? Is there a way to create the dataset list manually, because the query runs fine?
Manually populating the list has a downside: whenever you perform a report designer action which triggers an automatic list refresh, your manual entries will be cleared. To start troubleshooting this issue, you might consider simplifying the query bit by bit until the field list populates correctly automatically. Once you’ve identified the piece of the query that’s breaking list population, it should be easier to find a workaround for the issue.
I have the same issue, and tried all the solution mentioned but no success. I’ am using SQL Server 2008 R2
When i click refresh field button, in SQL profiler i can see the below Queries run one by one.
exec [myDB].[sys].[sp_procedure_params_100_managed] @procedure_name=N’RPT_SampleTest’
SET FMTONLY OFF; SET FMTONLY ON;
exec RPT_SampleTest @Sample_code=NULL,@Costing_Ref=NULL
SET FMTONLY OFF;
The SSRS Dataset does not have any records. but sometime dataset contains row, sometime its not. (!)
Try entering SET FMTONLY OFF; as the first line of your stored procedure.
This worked for me:
http://www.bidn.com/blogs/Daniel/ssas/1908/ssrs-dataset-fields-disappear-when-using-a-stored-procedure-with-dynamic-sql-%E2%80%93-how-to-get-them-back-and-save-your-report
Thank you, I was having the same issue and using SQL profiler I was able to catch the exact call that was being sent to the server. There was an error in the underlying query and it allowed us to fix the issue.
Hi,
I have a stored proc with one SMALLINT parameter. lt works fine until i try to run it via SSRS. I can see the resultset initially when i set up the shared dataset. When i run the report….nada.
I put your line in ‘ SET FMTONLY ON; ‘ into my stored procedure after that i couldn’t even view as shared dataset. It was unable to read from a #table i used in the sp between the source and resultset.
Any thoughts please?
Many thanks,
Quentin
Hi Quentin! In SSMS, what happens if you run
SET FMTONLY ON;then call your stored proc (e.g.SET FMTONLY ON; EXEC sp_mysp;)?Hi,
I struggled with this just now as well. In my procedures I am using dynamic SQL with OPENQUERY using a linked server against SSAS DMV’s .
The only solution that worked for me was to change the Query Type from “Stored Procedure” to “Text” in the SSRS DataSet, and formulate the query as such:
SET FMTONLY OFF;EXEC nameofSP @parameter1, @parameter2;
Using this method, and after adding values for the parameters in a pop-up window, the field list was filled with the fields returned from the query and the report could be saved and used successfully.
What did NOT work for me was what I first tried, per recommendations from others: adding the SET FMTONLY OFF statement as the first line in the stored procedure itself. That did not seem to have any effect in my particular scenario and I do not know why.
I finally got my same version of this problem to work. Once I made sure that the parameters could not end up with NULL values before the query portion of my stored procedure ran, I was good to go. I did also add the
SET FMTONLY OFFat the beginning of the procedure, but that was before I had solved the NULL parameter problem, so I don’t know if that was necessary.The easiest solution I have found for this problem is to select into a temp table inside the dynamic portion of your code, then after the dynamic portion select all of the rows from the temp table. Then Visual Studio will detect the fields.
— Define dynamic SQL
set @query = ‘SELECT foo, bar INTO ##finalResult FROM someTable’
— Execute dynamic SQL
execute(@query);
— Allow for tools to see the results with SET FMTONLY ON
SELECT * FROM ##finalResult
also try
in your stored proc