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.
Best Answer
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:
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 forSELECT
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 withFMTONLY ON
, both are returned:Results:
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.Results:
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 trysys.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).Results:
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 theSELECT
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.