Category Archives: SQL 2014
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.
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.
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
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!
So, I was working on a project for my employer trying to determine the best course of action for licensing a new instance of SQL Server (i.e. what is the cheapest way to get it legally). And I came across something I did not know.
Found deep inside the bowels of a 40 something page document, I learned something new from the Microsoft SQL Server 2014 Licensing Guide.
SQL Server Developer Edition
SQL Server 2014 Developer Edition is a full-function version of SQL Server software—including all of the features and capabilities of Enterprise Edition—licensed under the Developer Tools model, which is a “per user” model. One license is required for each person that accesses or uses the software.
“Per User” is what I didn’t realize. If I translate this correctly, I purchase one Developer License and install it on a machine, only I can use it as a development box. To open it up to a team of developers, each member of the team must purchase a Developer License. If I have multiple development servers for a multiple person team, this could get expensive. But hey, it is still cheaper than licensing a server by Cores and most likely cheaper than Server+CAL method.
Write this up as my “learning for the day”!