T-SQL Tuesday #002: Checking for SET FMTONLY ON

This post is my submission for the second T-SQL Tuesday, the theme of which is A Puzzling Situation.

A number of SQL Server tools execute queries with SET FMTONLY ON in order to obtain information about the result sets returned by those queries.  SQL Server Reporting Services (SSRS) does this to get the list of fields returned by the query so that you can lay them out on your report as desired.  SQL Server Integration Services (SSIS) does this in several places, including within the Data Flow task when you create your source in order to gather the meta data of the fields that will make up your data flow.

You can run into problems when complex queries are run with SET FMTONLY ON.  The most common problem might be with temp tables.  If your query is a stored procedure that creates a temp table and then subsequently references that temp table, you will get an error.  When running with SET FMTONLY ON, the statement that creates the temp table is not actually executed.  Therefore, when the statement that references the temp table is compiled, it generates an error due to referencing an object that does not exist.  See the following example.

CREATE PROCEDURE dbo.p_FmtOnly

AS

SELECT GETDATE() AS theDate

INTO #tDate

SELECT theDate AS [fulldate]

, DATEPART(MONTH, theDate) AS [month]

, DATEPART(YEAR, theDate) AS [year]

FROM #tDate

GO

If you execute this stored procedure with SET FMTONLY ON, you will receive the following error:

Msg 208, Level 16, State 0, Procedure FmtOnly, Line 7
Invalid object name ‘#tDate’.

The most common workaround for this problem is to essentially inject “SET FMTONLY OFF” into the query.  For example, if you had an SSRS report that called the procedure above, the actual query would be “SET FMTONLY OFF; EXEC dbo.p_FmtOnly”.   However, sometimes you might be dealing with a stored procedure that takes a long time to run or for some other reason you do not want it to run for real at design time.  In this case, the SET FMTONLY OFF workaround does not work for you.

My puzzle for this post is a challenge to come up with a way inside the stored procedure to capture whether SET FMTONLY is ON.  If it is on, the procedure should just return a single row of hardcoded values of the same data type as the real final result set.  If SET FMTONLY is off, then the real stored procedure should be run.  It would look something like the following (note, this code does not work and only serves to explain the challenge):

CREATE PROCEDURE dbo.p_FmtOnly
AS
DECLARE
@bFmtOnlyOn AS BIT

SET @bFmtOnlyOn = ???

IF (@bFmtOnlyOn = 0)

SELECT GETDATE() AS [fulldate]
, DATEPART(MONTH, GETDATE()) AS [month]
, DATEPART(YEAR, GETDATE()) AS [year]

ELSE

BEGIN

SELECT GETDATE() AS theDate
INTO #tDate

SELECT theDate AS [fulldate]
, DATEPART(MONTH, theDate) AS [month]
, DATEPART(YEAR, theDate) AS [year]
FROM #tDate

END
GO

A colleague of mine was able to come up with a clever way to solve this problem.  I thought it would make an interesting puzzle for the T-SQL Tuesday event to see how many other techniques folks might come up with.

CREATE PROCEDURE dbo.FmtOnly
AS
SELECT
GETDATE() AS theDate
INTO #tDate SELECT theDate AS [fulldate]
, DATEPART(MONTH, theDate) AS [month]
, DATEPART(YEAR, theDate) AS [year]
FROM #tDate
GO
About these ads

4 Responses to T-SQL Tuesday #002: Checking for SET FMTONLY ON

  1. Hmmm… I’ve noticed that FMTONLY ON ignores IF statements and runs both blocks either way.

    My solution includes Setting @bfmtonly on with a simple count from sys.objects. And using dynamic sql for the else block:

    DECLARE @SQL nvarchar(200)

    SELECT @SQL = N’
    SELECT GETDATE() AS theDate
    INTO #tDate

    SELECT theDate AS [fulldate]
    , DATEPART(MONTH, theDate) AS [month]
    , DATEPART(YEAR, theDate) AS [year]
    FROM #tDate’
    FROM sys.objects

    exec sp_executesql @SQL

    • nullgarity says:

      Michael – That works! Not quite what my colleague came up with, but quite similar. I took the liberty of plugging your idea into a proc. Going to see if the HTML code works in the comment here…

      CREATE PROCEDURE dbo.p_Comment

      AS

      DECLARE 
      @bFmtOnlyOn AS BIT

      SET @bFmtOnlyOn 0

      SET @bFmtOnlyOn (SELECT MAX(1FROM sysobjects)

      IF (@bFmtOnlyOn 0)

      SELECT GETDATE() AS [fulldate]

      DATEPART(MONTHGETDATE()) AS [month]

      DATEPART(YEARGETDATE()) AS [year]

      ELSE

      BEGIN

      DECLARE @SQL NVARCHAR(200)

      SELECT @SQL N'

      SELECT GETDATE() AS theDate

      INTO #tDate

      SELECT theDate AS [fulldate]

      , DATEPART(MONTH, theDate) AS [month]

      , DATEPART(YEAR, theDate) AS [year]

      FROM #tDate'

      FROM sys.objects

      EXEC sp_executesql @SQL

      END

      GO

  2. [...] Another SSRS-related issue comes up in the puzzle posted by Brian Garrity. How do you tell whether FMTONLY is turned on? [...]

  3. mhthomas says:

    Cool blog post. I liked it. Thanks.

    What about: sysobjects ???

    REF: sysobjects on BOL

    http://bit.ly/bImyOI

    “SQL Server 2008 Books Online (November 2009)
    sys.sysobjects (Transact-SQL)

    Updated: 31 August 2009

    Contains one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure.
    ms177596.note(en-us,SQL.100).gifImportant:
    This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature”.

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

Follow

Get every new post delivered to your Inbox.

Join 434 other followers

%d bloggers like this: