Integration Services, MySql and SET FMTONLY ON

  • g.britton

    SSChampion

    Points: 13685

    Comments posted to this topic are about the item Integration Services, MySql and SET FMTONLY ON

    Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

  • jim.riedemann

    SSChasing Mays

    Points: 634

    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.

  • g.britton

    SSChampion

    Points: 13685

    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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • jim.riedemann

    SSChasing Mays

    Points: 634

    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.

  • g.britton

    SSChampion

    Points: 13685

    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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • David Rueter

    SSCrazy

    Points: 2627

    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!

  • Mike Good

    SSCertifiable

    Points: 7356

    Very good article, great solution! Thank you.

  • jim.riedemann

    SSChasing Mays

    Points: 634

    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.

  • Misha_SQL

    SSCertifiable

    Points: 5384

    Great article! Thank you for sharing.

  • g.britton

    SSChampion

    Points: 13685

    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 NULL

    Great 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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • g.britton

    SSChampion

    Points: 13685

    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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • SQLArnold

    SSCommitted

    Points: 1846

    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!

  • g.britton

    SSChampion

    Points: 13685

    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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • SQLArnold

    SSCommitted

    Points: 1846

    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!

  • David Rueter

    SSCrazy

    Points: 2627

    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:

    http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server

    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 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply