Issue with FMTONLY in SQL Server 2012

Question

I support an application that uses CodeSmith and NetTiers templates to generate C# code. CodeSmith inspects the database and uses the SET FMTONLY ON setting to determine the columns for which code should be generated.

Unfortunately in moving from SQL Server 2005 to 2012 there is a particular circumstance where this is now no longer working. There is a Stored Procedure that executes a Stored Procedure on a linked server and the code being generated for that Stored Procedure is incorrect.

I've managed to isolate the issue to the EXEC statement with SET FMTONLY ON. The following (example only) SELECT works on both the 2005 and 2012 instances:

SET FMTONLY ON
SELECT TOP(10) [MCMCU]
              ,[MCSTYL]
              ,[MCDC]
FROM [JDE].[JDE_CRP].[CRPDTA].[F0006]
SET FMTONLY OFF

This returns the column headings only, as expected. The following works only on the 2005 instance:

SET FMTONLY ON
EXEC('SELECT TOP(10) [MCMCU]
                    ,[MCSTYL]
                    ,[MCDC]
      FROM [JDE_CRP].[CRPDTA].[F0006]') AT [JDE]
SET FMTONLY OFF

When run on the 2012 instance, SSMS shows the message "Command(s) completed successfully" but doesn't display the column headings.

Is there anything I'm missing here? Perhaps a setting that I need to change? The linked server definitions are identical, including the identity used to connect. And yes I'm aware that FMTONLY is deprecated but I don't have any ability to change the way CodeSmith interrogates the database.

Answer 1

I can reproduce that same behavior on SQL Server 2012. However, I am able to get it to work by putting the Linked Server name back into the query. Is there a reason you moved it out into the AT clause?

Try the following:

SET FMTONLY ON
EXEC('SELECT TOP(10) [MCMCU]
                    ,[MCSTYL]
                    ,[MCDC]
      FROM [JDE].[JDE_CRP].[CRPDTA].[F0006]');
SET FMTONLY OFF

---

Well, while the above does work on SQL Server 2012, it was not a good test because it does not work when executing a stored procedure. It might have something to do with it being a different type of call (i.e. the stored procedure call is RPC, and that had to be configured separately).

I even just tried adding WITH RESULT SETS ((...)) and that still didn't work.

---

Ok, so I found something. It is generally known that FMTONLY ON doesn't actually run the code, it just scans it for SELECT statements. But, that also means that it doesn't know how to handle conditional code. So it returns whatever result sets could be returned, even if no code path would ever return some of them.

Example 1 has two SELECT statements, but only one can ever be returned at a time. Still, when running with FMTONLY ON, both are returned:

CREATE PROCEDURE dbo.FmtOnlyTest1
AS
SET NOCOUNT ON;
  DECLARE @A INT = 5;

  IF (@A <> 6)
  BEGIN
    SELECT TOP 5 * FROM [master].[sys].[objects];
  END;
  ELSE
  BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
    EXEC(@SQL);
END;
GO

Results:

-- Running normally returns one result set of 5 rows from sys.objects:
EXEC Test.dbo.FmtOnlyTest1;

-- With FMTONLY ON, it returns two result sets: one from sys.objects AND one with [IntVal]
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest1;
SET FMTONLY OFF

Example 2 shows another consequence of not being able to run the code. If the code produces the result set dynamically, it cannot be determined by FMTONLY ON. This probably explains why it can't follow the EXEC call when the code is remote. I suspect that calling local Stored Procedures works by just doing a lookup in the local system catalog table for that definition.

CREATE PROCEDURE dbo.FmtOnlyTest2
AS
SET NOCOUNT ON;
  DECLARE @A INT;

  SELECT TOP 1 @A = [object_id] FROM sys.objects;

  DECLARE @SQL NVARCHAR(MAX);
  SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
  EXEC(@SQL);
GO

Results:

-- Running normally returns one result set of just [IntVal];
EXEC Test.dbo.FmtOnlyTest2;

-- With FMTONLY ON, there are no result sets returned (just like with the remote procedure)
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest2;
SET FMTONLY OFF

Knowing what we now know, we can use the fact that FMTONLY ON will find all result sets while not actually running the code to, even if not ideal, fake a result set to get CodeSmith (and other tools that still work in this fashion) working again.

Example 3 shows that we can effectively hide a dummy result set in a code block that cannot logically execute. Only FMTONLY ON will be able to see this so it won't present a problem for any other code (well, I did try sys.dm_exec_describe_first_result_set and that didn't really like it, but I don't think that is an issue in this case).

CREATE PROCEDURE dbo.FmtOnlyTest3
AS
SET NOCOUNT ON;

  IF (1 = 0)
  BEGIN
    SELECT CONVERT(INT, NULL) AS [FieldName1],
           CONVERT(DATETIME, NULL) AS [FieldName2];
  END;

  DECLARE @A INT;

  SELECT TOP 1 @A = [object_id] FROM sys.objects;

  DECLARE @SQL NVARCHAR(MAX);
  SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
  EXEC(@SQL);
GO

Results:

-- Running normally returns one result set of just [IntVal];
EXEC Test.dbo.FmtOnlyTest3;

-- Running with FMTONLY ON returns one result set of [FieldName1], [FieldName2]
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest3;
SET FMTONLY OFF

For this test I had the code return a different result set for the IF (1 = 0) block to make the difference in behavior more apparent. But in practice, you would want to return the exact same result set structure in the SELECT as the remote stored procedure returns.

The obvious down-side here is that if you change the result set of that remote procedure you will have to remember to also change the definition in the IF (1 = 0) block, but at least CodeSmith will function.