1

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.

3
  • 1
    What if you also add SET inside the exec()? What if you try sp_executesql instead of exec()? – Aaron Bertrand Sep 15 '15 at 1:53
  • Thanks Aaron, I tried sp_executesql. The behaviour was slightly different but didn't resolve the issue. – David Clarke Sep 15 '15 at 22:39
  • what about my other suggestion? – Aaron Bertrand Sep 15 '15 at 22:52
5

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.

10
  • I also think placing the FTMONLY statement inside the EXEC() would work. – Aaron Bertrand Sep 15 '15 at 22:51
  • 1
    Aaron that might work but I don't have any control over where the SET FMTONLY ON is placed. It is executed by the CodeSmith tool. – David Clarke Sep 15 '15 at 23:22
  • @AaronBertrand I highly doubt that either EXEC() or sp_executesql is involved in the actual operation here. I believe the EXEC was used for demo purposes. CodeSmith should just be executing the proc as it normally would and knows nothing of the Linked Server. – Solomon Rutzky Sep 16 '15 at 16:50
  • @DavidClarke Am I correct about what I just replied to Aaron with regarding CodeSmith? You were just using EXEC() in the question to get the overall point across, right? As opposed to that being exactly what CodeSmith was doing? – Solomon Rutzky Sep 16 '15 at 16:52
  • 1
    @DavidClarke What does it show during normal operation? Does it still have the EXEC? If I get time I will profile from a simple .NET app using the appropriate CommandBehavior to see how it shows up. However, I believe it's all a moot point since I tested by calling SET FMTONLY ON; EXEC Test.dbo.RemoteTest; SET FMTONLY OFF; where that proc has in it an EXEC of a proc across a Linked Server. It returns no result set at all, even using WITH RESULT SETS. The issue here has to do with changes made in 2012. I ran into this 3 years ago going from 2005 to 2012 with OPENQUERY. But we had no fix :(. – Solomon Rutzky Sep 17 '15 at 20:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.