December 22, 2014 at 10:52 pm
Comments posted to this topic are about the item Dealing with the Evil of FMTONLY from SSRS
December 23, 2014 at 3:25 am
This was removed by the editor as SPAM
December 24, 2014 at 8:34 am
Nicely done. Looks like good approach for a workaround. Thank you.
April 20, 2015 at 5:36 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply