How SSIS and Other Tools Obtain Metadata

  • Comments posted to this topic are about the item How SSIS and Other Tools Obtain Metadata

  • Thanks for the heads-up on the potential metadata problem. I haven't stumbled on this problem yet, but it would have only been a matter of time. Clever work-around!

  • Good to know, and the reverse will also be useful! When you have to use a front end tool such as Excel or Access, which does not use FMTONLY, temporarily adding it to the procedure will allow building a query without waiting for full execution.

  • This is also useful to know when using the wizard in Visual Studio 2005 for creating Typed data Sets.

    The ugly work around is to create a table and select at the top of the stored procedure just to satisfy the tool, then comment it out and un-comment the realy code after that.

    This looks like it would be a better way. And its something I always meant to trace using profiler and never had time. Thanks.

  • Good article...

  • Renato, this is a very useful and interesting article. Can you tell us how this code from your example works? It looks like you are evaluating the status of the FMTONLY option by doing a count from sys.filegroups. I don't understand why this would return the session status of FMTONLY.

    CREATE PROCEDURE dbo.ExportGraphicIndex

    AS

    BEGIN

    DECLARE @FmtOnlyTest int; -- If this is null then FMTONLY was ON

    SELECT @FmtOnlyTest = count(*) FROM sys.filegroups;

    IF @FmtOnlyTest IS NULL -- FMTONLY was ON

    SET FMTONLY OFF;

    Andy Hilliard
    Owl Creek Consulting[/url]

  • If FMTONLY is ON, all queries return zero rows, so a variable assignment in any select has no effect, and the variable keeps its previous value, which is NULL if it was never initialized. Here is another example which works in SQL2000, but at any rate you can query any table you have permission on.

    DECLARE @FmtOnlyTest int

    set @FmtOnlyTest = 0

    SELECT count(*) AS C FROM dbo.sysobjects

    SELECT @FmtOnlyTest = count(*) FROM dbo.sysobjects

    IF ( @FmtOnlyTest = 0 ) BEGIN

    SET FMTONLY OFF

    PRINT 'NOW OFF'

    END

  • Hi Andy,

    Thomas Keller is absolutely right and explained it very well.

    I chose SELECT COUNT(*) because that aggregate function will normally always return a row with a non-null value. That means that I could be sure that if the variable is null, the query did not return any rows, and FMTONLY was set.

    The code is improved if @SetFmtOnly is explicitly initialised (as in Thomas's example)

    Renato Buda

  • Thank you, this is excellent tip. Where were you 6 months ago when I spent days working around this limitation??!!! :w00t:

  • Excellent topic!

    I just want to add one thing. If you use table variable or tmp table use all column names instead of just * in select statement.

    Forexample:

    Does not Work

    select * from BLA_BLA

    Use

    select col1, col1,... from BLA_BLA

  • First off, I like this approach more than most of the alternatives, and the trick of using a query to interrogate the current status of FMTONLY is very clever. Having decided I liked it, I wanted to try to clean it up a little. I've been testing in SQL Server 2008 R2, and it appears that you can simplify this:

    SELECT @FmtOnlyTest = 1;

    No need to query a table. Also, in 2008 you can simplify it to:

    DECLARE @NullIfFmtOnly int = (SELECT 1);

    I prefer the variable name @NullIfFmtOnly because it makes the code a little more self explanatory.

  • @t.ovod-everett good catch, selecting a constant works as well as selecting a count from a table (I checked it in SQL 2005).

  • @t.ovod-everett : good one.

    I tested on SQL Server 2012 and the following works great

    DECLARE @NullIfFmtOnly int = (SELECT 1);

    but using a constant does not work i.e.

    SET FMTONLY ON

    SET @NullIfFmtOnly=1

    gets the value of 1 even when SET FMTONLY ON.

  • Another hack I came up with involves using this approach to work with SELECT INTO #Temp (if you want to avoid the maintenance overhead of maintaining the CREATE TABLE statement separately).

    DECLARE @NullIfFmtOnly int = (SELECT 1);

    IF @NullIfFmtOnly IS NULL SET FMTONLY OFF;

    DECLARE @TopPct int = ISNULL(@NullIfFmtOnly, 0) * 100;

    SELECT TOP (@TopPct) PERCENT

    Col1, Col2

    INTO #Temp

    FROM YourTable

    OPTION (RECOMPILE);

    IF @NullIfFmtOnly IS NULL SET FMTONLY ON;

    I found that if I didn't use RECOMPILE, the query plan for the FMTONLY scenario still pulled all the records even if it didn't insert them into the temp table. When I used RECOMPILE (at least in 2008 R2), it intelligently used Constant Scan instead of accessing the underlying tables. One does pay the price of recompiling the query on every execution, but I'm guessing if you're going to all this trouble with Temp tables in SSIS that query compilation is not a significant cost.

  • @Renato Buda-153382: It didn't work for you because you did SET instead of SELECT. That's why I was able to initialize my variable to 0 with SET, then check if SELECT changed it to 1.

Viewing 15 posts - 1 through 15 (of 19 total)

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