Is a Deprecated feature making my job more difficult?

By definition, a “deprecated” feature refers to a feature or specific function in a piece of software that will be replaced with a newer function/feature or just flat out will be no longer available.

Microsoft is notorious for changing and/or removing features in every version of SQL!  SQL 2014 has an extensive list of deprecated features:

The one I found out about today is SET FMTONLY (format only).  This particular setting allows a stored procedure to return the format of the results without actually returning results. This feature has been available since SQL 2005 and apparently will be removed from a future version, to be determined later.

   1: USE AdventureWorks2012;

   2: GO


   4: GO

   5: SELECT * 

   6: FROM HumanResources.Employee;

   7: GO


   9: GO

In my line of work, I do a lot of SSIS packages that export information to flat files.  And my developers use temp tables religiously.  The problem with this, SSIS packages typically have a problem with resolving the columns of the final result set from a stored procedure when temp tables are used.  The use of SET FMTONLY OFF allows the SSIS package to resolve the stored procedure without attempting get the column definitions first so the statements can complete, which in turn presents the SSIS Data Source with the final column definition.

Beginning in SQL 2012 two new features were debuted to replace FMTONLY setting.

sp_describe_first_result_set Returns the metadata for the first possible result set of the Transact-SQL batch.

   1: sp_describe_first_result_set [ @tsql = ] N'Transact-SQL_batch' 

   2:     [ , [ @params = ] N'parameters' ] 

   3:     [ , [ @browse_information_mode = ] <tinyint> ] ]

sys.dm_exec_describe_first_result_set This dynamic management function takes a Transact-SQL statement as a parameter and describes the metadata of the first result set for the statement

   1: sys.dm_exec_describe_first_result(@tsql, @params, @include_browse_information)

Why 2 you ask?  I have no clue.  These two seem to do the same thing, not sure why two different ways to do the same thing, but I digress…

The point of this article is now when I create a stored procedure with temp tables for an SSIS package, I have to pass the T-SQL statements as a parameter into these function first then execute the T-SQL statements.

Maybe I am too much of a novice, but to take a stored procedure with several hundred lines of code and turn it into a variable @tsql, then pass that variable into a function just to get column definition, then EXEC @tsql sounds more difficult than simply typing SET FMTONLY OFF; Apparently smarter people than me have decided this is the best way to do this.

Of course by accident, I discovered another, almost as simple, solution for SSIS packages and temp tables.  Instead of using a temp table, define a Table Variable at the beginning of the stored procedure.  This @TableVariable will be your final result set, insert records into this table. Using this method, your stored procedure can still use temp tables and have a defined result set for the SSIS data source. 

So, to answer the original question:  Is a deprecated feature making my job more difficult? 

Well, Yes and No.  Yes, because I believe the replacement feature is more difficult to execute and write code for, especially when your stored procedure has multiple statements.  And adding more steps to an already lengthy process is never a good thing. 

And No, because I found a just as easy useable solution with table variables. I just switch # for @ and define my columns.  easy as 1…2…3!


Posted on May 20, 2015, in SQL, SQL 2012, SQL 2014 and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

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

You are commenting using your 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

Humor Me a T-SQL Princess

DBAs really do have a sense of humor

Andrea Allred presents RoyalSQL

Bringing happy endings to all your data stories.

IDERA Community

A community site for users of all IDERA product and SQL community members.

Strate SQL

Data Adventures with a Architect

SQL Sanctum

A Join on SQL And Everything Else

Grant Fritchey

Intimidating Databases and Code


To Catch A Falling Cactus


Swimming through the Sea of SQL

SQL Studies

 Live, Learn, Share

Aunt Kathi's SQL Server Home

Learn SQL Server from your favorite aunt!

Jen's Blog: We Owls

...stuff. Also, things.

Journey to SQL Authority with Pinal Dave

SQL, SQL Server, MySQL, Big Data and NoSQL

Voice of the DBA

Writings from Steve Jones, the Voice of the DBA

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

%d bloggers like this: