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.