June 25, 2015 at 12:04 am
Comments posted to this topic are about the item Integration Services, MySql and SET FMTONLY ON
Gerald Britton, Pluralsight courses
June 25, 2015 at 7:58 am
Very interesting resolution to a problem. I looked at your code sample with the "IF 1=0" and couldn't believe it worked. Tested it, and of course it does.
I hadn't ever used the SET FMTONLY command, so I looked it up. This was the first information written on the MSDN page, starting with the 2012 version of the documentation:
Do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL).
It does kinda put a damper on further experimentation. I would be leery of using it in production.
June 25, 2015 at 8:20 am
jim.riedemann (6/25/2015)
Very interesting resolution to a problem. I looked at your code sample with the "IF 1=0" and couldn't believe it worked. Tested it, and of course it does.I hadn't ever used the SET FMTONLY command, so I looked it up. This was the first information written on the MSDN page, starting with the 2012 version of the documentation:
Do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL).
It does kinda put a damper on further experimentation. I would be leery of using it in production.
Yes, we'll need to find a different solution when we move to 2012. The current implementation is using 2008R2
Gerald Britton, Pluralsight courses
June 25, 2015 at 8:25 am
It still works in 2012 (my default environment and the one in which I experimented with your code). It also works in SQL 2014. I just wanted to point out that Microsoft might make it go away at some point.
June 25, 2015 at 8:42 am
jim.riedemann (6/25/2015)
It still works in 2012 (my default environment and the one in which I experimented with your code). It also works in SQL 2014. I just wanted to point out that Microsoft might make it go away at some point.
Quite true. Also interesting to note that Microsoft still uses it with OLE DB connections unless you explicitly choose the newest driver (version 11, that is).
As a side note, OLEDB is/was supposed to be deprecated by now, but the alternatives (ADO, ODBC) are not yet feature-for-feature replacements nor available for all components and tasks.
Gerald Britton, Pluralsight courses
June 25, 2015 at 9:44 am
Do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL).
But don't you see...it is the Microsoft tools such as SSIS and SSRS that are the ones using the old FMTONLY...and not the author of the article. Until Microsoft (and other vendors) stop using FMTONLY to inspect metadata, we all need to understand and use FMTONLY...as Gerald so nicely did in this article.
This is trick: IF 1 = 0 SET @FMTONLY... is neat, and makes perfect sense once you understand that with FMTONLY EVERY conditional branch is taken. (Of course understanding this behavior is important if you have other conditional branches in your code, as I describe in my article Dealing with the Evil of FMTONLY from SSRS[/url] )
Another way of detecting that FMTONLY is set is to test for: IF @@OPTIONS IS NULL
Great article, Gerald. Thanks for sharing!
June 25, 2015 at 10:26 am
Very good article, great solution! Thank you.
June 25, 2015 at 11:07 am
I did see that, David. And then I started my research into how the trick itself worked, to see if I could make use of it in other places for other purposes, and Microsoft's warning was the first thing I found. I just felt that a large message from Microsoft saying "Do not use" was a piece of information that should accompany the article. Even your post on the topic started out with the same warning.
I agree, the article was very informative and opened my eyes to a potential security risk of which I was not familiar.
June 25, 2015 at 11:27 am
Great article! Thank you for sharing.
June 25, 2015 at 11:43 am
David Rueter (6/25/2015)
Do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL).
But don't you see...it is the Microsoft tools such as SSIS and SSRS that are the ones using the old FMTONLY...and not the author of the article. Until Microsoft (and other vendors) stop using FMTONLY to inspect metadata, we all need to understand and use FMTONLY...as Gerald so nicely did in this article.
This is trick:
IF 1 = 0 SET @FMTONLY... is neat, and makes perfect sense once you understand that with FMTONLY EVERY conditional branch is taken. (Of course understanding this behavior is important if you have other conditional branches in your code, as I describe in my article Dealing with the Evil of FMTONLY from SSRS[/url] )Another way of detecting that FMTONLY is set is to test for:
IF @@OPTIONS IS NULLGreat article, Gerald. Thanks for sharing!
Thanks David! And...thanks for pointing out your previous article. BTW is that "IF @@OPTIONS IS NULL" trick documented anywhere along with FMTONLY ON? If not, how did you discover it?
Gerald Britton, Pluralsight courses
June 25, 2015 at 11:52 am
Do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL).
I did some digging on this. Using 2012, this query:
declare @batch nvarchar(4000) = N'
if 1=0 select ''one'' as one
else select 1 as one
';
exec sp_describe_first_result_set @batch;
returns:
Msg 11509, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because the statement 'select 'one' as one' is not compatible with the statement 'select 1 as one'.
indicating that the new hotness (sys.dm_exec_describe_first_result_set) has the same effect as SET FMTONLY ON in the code being examined. Haven't tried it on 2014 or 2016 though i'd be surprised if the behavior changed.
Gerald Britton, Pluralsight courses
June 25, 2015 at 12:54 pm
I had the same concern, our team was also using this feature and when I researched and found the same warning on MSDN, Do not use this feature. This feature has been replaced by . . . I replaced it with NOEXEC ON. See if that works for you as well.
The pain of Discipline is far better than the pain of Regret!
June 25, 2015 at 1:09 pm
SQLArnold (6/25/2015)
I had the same concern, our team was also using this feature and when I researched and found the same warning on MSDN, Do not use this feature. This feature has been replaced by . . . I replaced it with NOEXEC ON. See if that works for you as well.
I don't think that NOEXEC ON is a replacement for FMTONLY ON, since it does not cause SQL to return the schema of the first result set, which is what SSIS/SSRS need. Note that I do not use SET FMTONLY ON in my code, but since SSIS/SSRS do, I can detect the setting and use it to my advantage.
Gerald Britton, Pluralsight courses
June 25, 2015 at 1:14 pm
You are correct. Sorry I did not give more details. Our department was using it to parse query and print dynamic T-SQL statements. We were not using it with SSIS or SSRS.
The pain of Discipline is far better than the pain of Regret!
June 25, 2015 at 1:40 pm
BTW is that "IF @@OPTIONS IS NULL" trick documented anywhere along with FMTONLY ON? If not, how did you discover it?
To my knowledge, @@OPTIONS = NULL is not explicitly documented, but I suspected that since @@OPTIONS returns the options that have been SET that it should somehow reflect SET FMTONLY=ON (since this seems an important options to know about).
I learned about @@OPTIONS in general from:
https://msdn.microsoft.com/en-us/library/ms176031(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/ms177525(v=sql.105).aspx
I then inspected @@OPTIONS to determine that FMTONLY sets it to NULL:
SELECT @@OPTIONS
SET FMTONLY ON
SELECT @@OPTIONS
SET FMTONLY OFF
SELECT @@OPTIONS
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply