Timeline for Issue with FMTONLY in SQL Server 2012
Current License: CC BY-SA 3.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 17, 2015 at 20:18 | comment | added | David Clarke | Right, that issue came up a lot in my search results. To be honest I haven't looked at what it does under normal operation - I will need to check. Thanks for the heads up. | |
| Sep 17, 2015 at 20:14 | comment | added | Solomon Rutzky |
@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 :(.
|
|
| Sep 17, 2015 at 19:47 | comment | added | David Clarke |
I was using the profiler to see what was going on and it shows the SET FMTONLY ON followed by EXEC() with all parameters NULL, and finally a SET FMTONLY OFF
|
|
| Sep 17, 2015 at 19:45 | comment | added | Solomon Rutzky |
@DavidClarke Are you saying that CodeSmith adds a literal EXEC() call around the proc? So that it is running EXEC(dbo.ProcName);? Or is it just doing a regular RPC call of the proc from app code, but using FMTONLY ON to get the result set schema without actually executing it? Usually it is the later. There is no reason for it to add in an extra EXEC() layer. In fact, it is quite easy to set FMTONLY ON and EXEC a stored procedure from .NET code by using the CommandBehaviors of SqlCommand, which is what I suspect CodeSmith is doing.
|
|
| Sep 17, 2015 at 19:40 | comment | added | David Clarke |
@srutzky no CodeSmith does actually EXEC() the stored proc with SET FMTONLY ON. It uses the output, i.e. the list of columns, to determine which template to use to generate the source. The issue with the move to SQL Server 2012 was that the output from that stored procedure no longer returned the list of columns so the method generated returned void instead of DataSet. It may well be that a newer version of CodeSmith uses the new options.
|
|
| Sep 16, 2015 at 16:55 | comment | added | Aaron Bertrand | @srutzky In fairness, I'm commenting on the specific code in the question, which is what other readers will come across as well, not some different thing that the OP hasn't explained. | |
| Sep 16, 2015 at 16:52 | comment | added | Solomon Rutzky | @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? | |
| Sep 16, 2015 at 16:50 | comment | added | Solomon Rutzky |
@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.
|
|
| Sep 15, 2015 at 23:22 | comment | added | David Clarke |
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.
|
|
| Sep 15, 2015 at 22:51 | comment | added | Aaron Bertrand | I also think placing the FTMONLY statement inside the EXEC() would work. | |
| Sep 15, 2015 at 22:25 | vote | accept | David Clarke | ||
| Sep 15, 2015 at 20:51 | history | edited | Solomon Rutzky | CC BY-SA 3.0 |
added 4117 characters in body
|
| Sep 15, 2015 at 4:43 | history | edited | Solomon Rutzky | CC BY-SA 3.0 |
added 87 characters in body
|
| Sep 15, 2015 at 4:25 | history | edited | Solomon Rutzky | CC BY-SA 3.0 |
added 293 characters in body
|
| Sep 15, 2015 at 4:14 | history | answered | Solomon Rutzky | CC BY-SA 3.0 |