Dealing with the Evil of FMTONLY from SSRS

  • David Rueter

    SSCrazy

    Points: 2632

    Comments posted to this topic are about the item Dealing with the Evil of FMTONLY from SSRS

  • This was removed by the editor as SPAM

  • Mike Good

    SSCertifiable

    Points: 7383

    Nicely done. Looks like good approach for a workaround. Thank you.

  • Thomas Keller

    Ten Centuries

    Points: 1222

    Good summary and followup to the prior article http://www.sqlservercentral.com/articles/FMTONLY/64130/ "How SSIS and Other Tools Obtain Metadata". Based on the comments there, two additional points to make:

    1 - Note that PRINT does nothing with FMTONLY on, just like RAISERROR

    2 - Note that any variable assignment while FMTONLY is ON takes effect as soon as it is set OFF. This matters when you are trying to tell whether your proc was called with FMTONLY ON, before setting it OFF, and then doing something different based on that.

    For example, you might raise an error if an ID parameter is not a valid entry in a table, which will always be true when SSIS is testing your metadata, so you need to check how your proc was called before actually raising the error.

    Moreover, a simple SELECT into a variable without a table is optimized as equivalent to a SET, so in the following example, only the third variation returns the expected NULL:

    DECLARE @SetOpt INT, @SelOpt INT, @SetSelOpt INT

    Set FMTONLY ON;

    SET @SetOpt = @@OPTIONS

    SELECT @SelOpt = @@OPTIONS

    SET @SetSelOpt = (SELECT @@OPTIONS)

    Set FMTONLY OFF;

    SELECT @SetOpt AS SetOpt, @SelOpt AS SelOpt, @SetSelOpt AS SetSelOpt

    Therefore, for maximum readability and predictability, I now use @t.ovod-everett simple suggestion:

    DECLARE @NullIfFmtOnly INT = (SELECT 1);

    or if you need it to work in 2005 and earlier:

    DECLARE @NullIfFmtOnly INT SET @NullIfFmtOnly = (SELECT 1)

Viewing 4 posts - 1 through 4 (of 4 total)

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