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 🙂
Best Answer
Can you show the query? Just because it runs doesn't mean it's correct. :-) For example this is not legal, but it works:
Technically, it should be as follows, since
o.modify_date
is not in theSELECT
list: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:
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:
This will break in SQL Server 2012, however, since #temp tables now get a negative object_id. The proper way to test is:
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:
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: