SET FMTONLY ON (Useful in TSQL & SSIS packages for using temp tables)

As we talked earlier about ‘SET NOEXEC ON’ statement in TSQL which restricts the SQL batch statements to get executed but they only get parsed / complied. When we set NOEXEC as ‘ON’ then in the output we will get a message ‘Command completed successfully’; provided SQL statement(s) are correct else error message(s) will there. But in any case we will not be able to see the results or the column names being returned by the SQL batch or a stored procedure using NOEXEC ON statement.

Here we come up with ‘SET FMTONLY’ statement. If this is being set as ‘ON’ (by default it is OFF) at the start if a batch of SQL statements or in any stored procedure; then while executing that batch or stored procedure we will not get the actual results of that sp/batch, but instead we will get only the metadata (i.e. column names) being returned by the SQL.

CREATE PROC TestProc

AS

SELECT TOP 10 Name, ProductNumber

Color  FROM dbo.Product

Result: 12

 

 

 

 

 Using FMTONLY ON

SET FMTONLY ON

CREATE PROC TestProc

AS

SELECT TOP 10 Name, ProductNumber

Color  FROM dbo.Product

 

Result:

21






Use in SSIS

: In SSIS when we use some source to extract the data from, the source component (like OLEDB Source) is supposed to provide the complete information of all the columns being returned by the source. This information of columns is used by its following component (Transformation or destination) at design time to map it accordingly. The source component may be using some SQL statements or Stored Procedure to get the result.

All these columns information is actually detected by the source using the Metadata of the SQL query result-set.

Problem: If we are having some temp tables (#table) in the SQL query / stored procedure of the source and in the result set we are getting result (some or all columns) from those temp tables. In that case, at design time, source will not be providing information /metadata about the columns being returned & thus we will not be able to map them in its following components.

CREATE PROC TestProc

AS

SELECT TOP 10 Name, ProductNumber

Color INTO #MyTempProduct

FROM dbo.Product

SELECT * FROM #MyTempProduct

4051

Solution: This problem of using temp tables in SSIS source can be resolved by using the FMTONLY statement & setting it as ‘ON’ on the top of the batch /sp. Doing so, sp will be executed to provide the metadata of the result set & then it can be used for mapping purposes.

SET FMTONLY ON;

EXEC TestProc

Demerit: There is one issue in using FMTONLY for getting metadata out of temp tables. When we use this statement, SP will be executed 5 times instead of only one time. This not only will slow down the performance but also it may create issue if the stored procedure is doing some modifications etc in the database; so try to avoid this work around in that case.

There are certain properties which can be set to minimize this number of executions.

Set “DelayValidation” on the Data Flow task to TRUE and the executions of the stored proc will go down from 5 to 3.
Set the “ValidateExternalMetadata” setting of the source and destination connections to be FALSE. This further reduced the executions to 2.

Another Workaround: Instead of temp table we can use table variables (@tableVar) in the queries / sp. This will provide the metadata of the result-set automatically; because we would already have declared its structure in the query/sp which is not the case with temp tables (#Table). But table variables sometimes give poorer performance than Temp tables.

Advertisements
This entry was posted in SQL Server, SSIS. Bookmark the permalink.

11 Responses to SET FMTONLY ON (Useful in TSQL & SSIS packages for using temp tables)

  1. santosh jha says:

    Hi,
    Actually I got the working of FMTONLY

    But I want to Know How It Get data in five times .As When It is On It only returns MetaData to map .

    How it make relation between metadata and data .

    And What is the need to execute the sp 5 time and is their any effect when we reduce the execution by using certain properties.

  2. Manish Kumar says:

    Another workaround is to write a select statement inside a always-false conditional block that returns only the metadata. For ex:

    CREATE PROC TestProc
    AS
    If 1=2
    select ” As Name, ” As ProductNumber
    SELECT TOP 10 Name, ProductNumber
    Color INTO #MyTempProduct
    FROM dbo.Product
    SELECT * FROM #MyTempProduct
    GO
    exec TestProc

    The reason for this is when FMTONLY is set on, it searches all code paths for metadata neglecting any conditions. And SSIS and SSRS autmatically emit SET FMTONLY ON at starting and then set it off at the end, in order to get metadata.

  3. Hi Manish,
    Yeah, it’s a better way of getting the metadata.

    Thanks for your comments !

    Munish Bansal

  4. Prabir Sakar says:

    What do you mean by “it searches all code paths for metadata neglecting any conditions”

  5. jsamra says:

    I am usin Microsoft sql server 2008 , I tried all the 3 solutions , but every time I get the same error

    “Error at Data Flow Task[OLEDB source[449]]:No colum information was returned by the sql command”

    I am using the follwoing batch of sql statments to retreive the server level configuaration of all servers in my company. The table variable @tb1_SvrStng has 83 columns and it is populated using diffrenet resources.
    So I summarize the sql script. I cannot use it as stored procedure because this script is going to run against 14 servers(once for each server)
    So if i store the procedure on one server , other server canot able to execute that procedure in its context.
    Please help me to solve my problem. I will highly appretiate your help.
    I am not using any temporary tabel in my script.

    declare @tb1_SvrStng table
    (
    srvProp_MachineName varchar(50),
    srvProp_BldClrVer varchar(50),
    srvProp_Collation varchar(50),
    srvProp_CNPNB varchar(100),

    xpmsver_ProdVer varchar(50),
    ….. .
    syscnfg_UsrCon_cnfgVal int,
    …..
    );
    insert into @tb1_SvrStng
    (
    srvProp_BldClrVer,
    srvProp_Collation,
    srvProp_CNPNB , ……..
    …….. .
    )
    select convert(varchar, serverproperty(‘BuildClrVer’)),
    convert(varchar, serverproperty(‘Collation’))
    ……..
    …….
    declare @temp_msver1 table
    (
    id int, name varchar(100),
    ………..
    );

    insert into @temp_msver1 exec xp_msver
    Update @tb1_SvrStng
    set xpmsver_ProdVer =
    (
    select value from @temp_msver1 where name = ‘ProductVersion’
    ),
    xpmsver_Platform =
    (
    select value from @temp_msver1 where name = ‘Platform’
    ),
    …..
    ……
    select
    srvProp_SerName as srvProp_SerName,
    getdate() as reportDateTime,
    srvProp_BldClrVer as srvProp_BldClrVer,
    srvProp_Collation as srvProp_Collation,
    …..
    …..
    from @tb1_SvrStng

    Thanks
    Jasdeep

  6. quillis says:

    Hi..this is nice to get the error fix …its very clearly written. But for some reasons i get the warning that no columns were return even after following all the things in above article….. can you try to help…. and by the way I am using temp variables.

    One more thing … as soon as i hit the perview, i get the warning and then after say 2 – 3 sec I can see the Columns returned. But for the columns tab… i am cannot see any columns returned… Plz let me know …if i am clear… Thank you

  7. Pingback: FMTONLY and some thoughts « sqlconcept.com

  8. Rahul says:

    Wats the work Around if its a DB2 PROC? I am using a DB2 PROC as a Oledb DataSource in DATAFLOW task in SSIS. and this PROC returns a cursor not a temp table.
    I am facing the same issue i.e. source will not be providing information /metadata about the columns being returned & thus I am not be able to map them in its following components. QUICK helo wud be very much appreciated…

  9. DINESH VISHE says:

    great use of SET FMTONLY OFF…thanks

  10. Pingback: Entity Framework Function Import not returning Columns data | Dotnetcoderoom Blog

  11. Johnny says:

    There is a real easy way to deal with this .. Use CTE to replace the temp table .. Works like a charm .. No need to deal with FMTONLY ON/OFF anymore ..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s