SQL Server has a somewhat obscure setting called FMTONLY.  It is used like other SET commands:

SET FMTONLY ON

The setting affects all subsequent commands executed on the connection for the remainder of the execution scope, just like SET NOCOUNT ON does. But what does SET FMTONLY actually do?

Before I answer that:  don’t ever use SET FMTONLY. It is evil. It has many bugs, and is deprecated. BOL for SQL 2012 says “Do not use this feature. This feature has been replaced by …”  (Ref http://msdn.microsoft.com/en-us/library/ms173839.aspx)

Unfortunately SQL Server Reporting Services (SSRS) and other clients still use SET FMTONLY ON, and better alternatives were not really available until SQL 2012.  So we are stuck with the reality that at present FMTONLY will be used for a while. Since there are situations where SET FMTONLY ON will be used, whether you like it or not, there are times where you need to understand what this does and how to deal with the limitations of the FMTONLY setting.

With that disclaimer out of the way, according to BOL SET FMTONLY does the following:

Returns only metadata to the client. Can be used to test the format of the response without actually running the query.

In essence, by setting SET FMTONLY ON, we are telling SQL Server not to actually return or update any data, but only to return an empty resultset containing all of the columns that WOULD have been returned if data were returned as normal.

We’ll look at some examples in just a minute, but first understand that since SSRS uses SET FMTONLY ON, your stored procedures that will be consumed by SSRS need to be aware of the drawbacks.  If you have a stored procedure that works properly outside of SSRS you may find that it does not work properly inside SSRS because of SET FMTONLY ON.

For example, given:

CREATE TABLE #Test1 (Id int)
INSERT INTO #Test1 (id) VALUES (1)
SELECT *
 FROM #Test1

This returns 1 row as expected.  But after SET FMTONLY ON, the same query returns zero rows

What is the point of SET FMTONLY ON? What good is it?  Some situations, such as obtaining a list of fields available to a report designer like SSRS, require an application to obtain a list of fields and other meta data even though the application does not want to receive actual data, nor to update data in anyway.  This is what SET FMTONLY was created for, and this is why SSRS uses it.

The problem is that there are many limitations and bugs with SET FMTONLY ON.

Problem #1

One of the biggest problems is that with SETFMTONLY ON, every possible conditional code branch is evaluated.  Yes, that is right:  every IF / ELSE branch. For example, consider a trivial example like this:

IF 1=0 BEGIN
  SELECT *
   FROM #Test
END

This will never return a result (because 1 will never equal 0).  In fact, the select statement will never be run, so it doesn’t matter if a table #Test really exists or not. EXECPT that is not the case if SET FMTONLY ON is set.  In that case, the above query returns an error:

Msg 208, Level 16, State 0, Line 2

Invalid object name '#Test'.

Even if we explicitly test for the existence of a table we will encounter an error under SET FMTONLY ON:

IF OBJECT_ID('tempdb..#Test') IS NOT NULL
 BEGIN
  SELECT *
   FROM #Test
 END

This will return:

Msg 208, Level 16, State 0, Line 3

Invalid object name '#Test'.

So well-coded error-free stored procedures can produce errors when run under SET FMTONLY ON.

Problem #2

But then the reverse is true as well:  Stored procedures that are trying to return an error will not be able to raise an error under SET FMTONLY ON.  For example:

  RAISERROR('You should see this error.', 16, 1)

This will normally raise an error, as expected.  However this will NOT raise an error when run under SET FMTONLY ON.

Problem #3

There are stranger examples too. Look at this stored procedure.

CREATE PROCEDURE #Sub
  @MyResult int OUTPUT,
  @ReturnResultset bit = 0
AS
BEGIN
  SET @MyResult = 2*3
  IF @ReturnResultset = 1 BEGIN
    SELECT @MyResult AS MyResult
  END
END
CREATE PROCEDURE #Main
AS
BEGIN
  DECLARE @ThisResult int
  EXEC #Sub @MyResult = @ThisResult OUTPUT
  SELECT 'The answer is:', @ThisResult
END
EXEC #Main

This returns a single resultset just as expected:

Caption        Answer

The answer is: 6

But when run under SET FMTONLY ON, we receive TWO empty resultsets:  one with column MyResults (unexpected), and one with the coumns Caption and Answer (expected).

Why did we get the extraneous resultset?  Because SET FMTONLY ON forced “IF @ReturnResultset = 1” to be evaluated, even though our code never sets @ReturnResultset = 1.  Thus stored procedure #Sub returned its resultset  (that we did not want to see), and then #Main returned its resultset that we did want.

As you can imagine, if you were trying to define a report based on the data returned by stored procedure #Main, this would be very confusing:  the report designer would show columns only from the first resultset (MyResult) which you wouldn’t expect to see at all, and would never show columns from the desired resultset that we were trying to report on!

Note that Microsoft has been aware of this problem since at least 5/1/2005.  See this active bug report:  https://connect.microsoft.com/SQLServer/feedback/details/124701/set-fmtonly-on-should-not-raise-false-errors

So here we are almost 10 years later, and we are still forced to deal with this issue because SSRS might call one of our complex stored procedures and get unexpected results back!

Working Around the Problems

What can we do to work around the problem?  We can’t change the behavior of SSRS, and we can’t change the functionality of SET FMTONLY ON. But what we CAN do to work around the problem is to explicitly SET FMTONLY OFF in our stored procedures where we encounter these kinds of problems.

Now, be careful, and be sure you understand what you are doing before you include SET FMTONLY OFF in one of your procedures.  Essentially by doing this you are saying, “SQL Server, I know the caller told you not to update any data or return any data, but darn it, I am smarter than the caller.  I know what I am doing, and by golly, I want you to execute this procedure as you normally would.”

If your procedure were updating or deleting data, you probably would NOT want that update or delete to happen just because SSRS was asking your procedure for metadata.  If you SET FMTONLY OFF to override what the caller specifies, the burden is on you as the developer to make sure your procedure doesn’t do anything inappropriate when SSRS is asking for metadata at design-time.

How could your procedure detect that SET FMTONLY ON was specified so that it could take appropriate action?  Well, for most SET settings there is a handy but not-widely-known built-in function @@OPTIONS that returns a bitmapped value to indicate what settings are enabled.  See http://msdn.microsoft.com/en-us/library/ms190763.aspx and http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server for more information.

But unfortunately @@OPTIONS does not return a flag for SET FMTONLY ON.  In fact, under SET FMTONLY ON, @@OPTIONS returns NULL!  And that is a good thing for our purposes here:  In our stored procedure we can check if @@OPTIONS IS NULL , and if so we know that the caller really didn’t want to receive data or to have us update data.

Finally, remember that even if you SET FMTONLY OFF in one of your procedures, like all other SET setings set within a stored procedure it will revert to its original value upon completion of the stored procedure.

Knowing all this, we could modify our sample procedures to make them usable by SSRS:

CREATE PROCEDURE #Sub
  @MyResult int OUTPUT,
  @ReturnResultset bit = 0
AS
BEGIN
  SET @MyResult = 2*3
  IF @ReturnResultset = 1 BEGIN
    SELECT @MyResult AS MyResult
  END
END
CREATE PROCEDURE #Main
AS
BEGIN
  DECLARE @MetaDataOnly bit
  IF @@OPTIONS IS NULL BEGIN
    SET @MetaDataOnly = 1
    SET FMTONLY OFF
  END  
 ELSE BEGIN
  SET @MetaDataOnly = 0
 END
  
  DECLARE @ThisResult int
  EXEC #Sub @MyResult = @ThisResult OUTPUT
  
  SELECT 'The answer is:' AS Caption, @ThisResult AS Answer
  WHERE @MetaDataOnly  = 0
END
EXEC #Main

Ta da!  Now our procedure still works when called normally, and it will now work when called by SSRS at design-time with SET FMTONLY ON.  SSRS will now see only the correct resultset, and will not be bothered by the unneded resultset from #Sub.

Sometimes writing good modern code requires us to be mindful of historical 10-year-old weaknesses that we may be unaware of or may have forgotten about.  Since I bumped my head on this one today I thought I would share what I learned with you in the hopes that it might help someone.

Bottom line:  SET FMTONLY is evil, but sometimes part of life is dealing with evil that is beyond our control.  Even so, you can take steps to defend yourself from this particular evil when the need arises.