In the previous installment on this topic, I looked at discontinued features in SQL Server 2012 which may mostly impact your administration scripts. Today, we are widening the net and looking at discontinued features which may impact either the application layer or administration programs and scripts. Most of these items have been deprecated as far back as SQL Server 2000, so it isn’t like they have gone away overnight. However, if you are still running on SQL Server 2000, then you really haven’t had a need to go replace them…until now.
SQL Mail is finally gone! It would have been totally fine with me if SQL Mail had been discontinued as soon as Database Mail was added in SQL Server 2005. However, you do lose functionality (receive mail processing) with Database Mail, but the trade-off is much improved reliability, security, supportability, and scalability. For example, you no longer need an Extended MAPI client (e.g. Outlook) installed; you don’t run the risk of attacks via reading mail; and Database Mail is supported on clusters. So, if you are still using the read\process mail functionality of SQL Mail, you’ll need to find another method for your application to use before upgrading to SQL Server 2012. If all you are doing are send mail tasks, including notifications from SQLAgent, then the switch to Database Mail is a no-brainer and you can do it now in SQL Server 2005/2008/2008R2. However, this is more than just a configuration change; it does require usage coding changes for example from xp_sendmail to sp_send_dbmail.
Unless you’ve been stuck on SQL Server 2000, you have probably already started converting applications using SQL-DMO (Database Management Objects) to use SQL-SMO (SQL Server Management Objects) when you found you wanted to access newer features in SQL Server 2005+ that were not available via SQL-DMO. If you want to use your SQL-DMO scripts against SQL Server 2012 – you must convert to SQL-SMO; SQL-DMO is discontinued.
If you have databases in compatibility mode 80, it might be because the code using them is still using the old non-ANSI outer join syntax “*=” or “=*”. This syntax is not supported in later compatibility modes and because compatibility mode 80 is not supported in SQL Server 2012, this syntax is no longer recognized in SQL Server 2012 in any scenario. You’ll need to get all of this code up-to-date with current ANSI join syntax used in the FROM clause.
Another T-SQL syntax which is disappearing is the COMPUTE / COMPUTE BY clause in SELECT statements; you will need to convert these statements to use the ROLLUP option of the GROUP BY clause.
If you tried to speed up the results returned to your client or force a particular query plan by using the FASTFIRSTROW query hint, you’ll need to switch to the OPTION (FAST n) syntax as FASTFIRSTROW is no longer available. For more info on the overall impact of using this option, see this blog from the SQL Server Query Optimization team and this one from SQL MVP Grant Fritchey. As with all query hints, you should re-evaluate these periodically as they may no longer be as beneficial as when originally added.
In SQL Server 2012, you can no longer invoke an ad-hoc error message number using RAISERROR integer ‘message’ syntax as in: RAISERROR 50111 ‘Sample error message’. You’ll need to convert to the RAISERROR() syntax: RAISERROR (‘Sample error message’, 10, 1). But, if you want to really get your code current, convert to TRY…CATCH (available since SQL Server 2005) and use THROW (new in SQL Server 2012). There are some differences in behavior between RAISERROR and THROW and these are documented in BOL’s THROW entry.
I’ve only highlighted a sample of discontinued features in this set of articles; for the complete list of discontinued database engine features in SQL Server 2012, please reference BOL. And for help finding out if you are using these features (which were deprecated in earlier releases), I covered that here.
Filed under: SQL Server, SQLServerPedia Syndication | Tagged: Backward Compatibility, Deprecated Features, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, Upgrade | Leave a comment »