How to detect compatibility errors - SQL Server 2012

I am changing a database's compatibility mode from 90 (SQL Server 2005) to 110 (SQL 2012) and I am wondering at what point would I see errors from breaking changes if there are any.

I ran Upgrade Advisor 2012 which spotted several Stored Procedures that needed updating. An example was:

"In SQL Server 2005 or later, column aliases in the ORDER BY clause cannot be prefixed by the table alias."

However, when I run this stored procedure in SQL 2012 (without having made changes) it doesn't show any errors. Also, when I changed the database compatibility level from 90 to 110 there were also no errors. Nor when I restored the database.

If I am not seeing errors anywhere, I am hoping that Upgrade Advisor caught everything. Is there any other ways I should check for compatibility errors?

Also, how is it possible that this stored procedure ran successfully even though Upgrade Advisor told me it would fail? Thanks :)

Can you show the query? Just because it runs doesn't mean it's correct. :-) For example this is not legal, but it works:

SELECT CONVERT(SMALLDATETIME, modify_date) AS modify_date
FROM sys.objects AS o
ORDER BY o.modify_date;

Technically, it should be as follows, since o.modify_date is not in the SELECT list:

SELECT CONVERT(SMALLDATETIME, modify_date) AS modify_date
FROM sys.objects AS o
ORDER BY modify_date;

In this Connect bug, it was stated that this rule would be removed from the Upgrade Advisor. I suspect that one of the following (or both) happened:

• They forgot to remove the rule as they said they would

• They had other complications which prevented the above syntax from being blocked by the parser

That all said, there are some breaking changes the upgrade advisor will never catch. For example, this will work on 2005, 2008 and 2008 R2:

CREATE TABLE #foo(id INT);

IF OBJECT_ID('tempdb..#foo') > 0
BEGIN
  DROP TABLE #foo;
END
GO

CREATE TABLE #foo(id INT);

This will break in SQL Server 2012, however, since #temp tables now get a negative object_id. The proper way to test is:

IF OBJECT_ID('tempdb..#foo') IS NOT NULL

I go over a few other issues in this blog post. A couple of other breaking changes:

-
https://sqlblog.org/2011/06/28/sql-server-v-next-denali-breaking-change-to-fn_virtualfilestats

-
https://sqlblog.org/2011/07/08/sql-server-v-next-denali-breaking-change-to-system-databases-database_id-db_id

If you use any of the memory-related DMVs there has been a major overhaul to some of the columns:

• https://blogs.msdn.microsoft.com/sqlosteam/2012/07/11/memory-manager-surface-area-changes-in-sql-server-2012/

Again, most of these are not caught by the upgrade advisor, so running that tool and not doing any thorough testing could really put you in a bad spot.

And I agree with Eric's answer - you should try to track deprecated events. For completeness, here are three approaches:

• Profiler

• Server-side trace

• Extended Events

StackExchange Database Administrators Q#21778, answer score: 6