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: https://msdn.microsoft.com/en-us/ms143729.aspx.

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

   3: SET FMTONLY ON;

   4: GO

   5: SELECT * 

   6: FROM HumanResources.Employee;

   7: GO

   8: SET FMTONLY OFF;

   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!