We have a SQL 2008 R2 server with a database on it that is running in compatibility mode 80. We are interested in upgrading 2008 R2 to 2014, but the upgrade adviser doesn't work because we can't force it to look for old syntax that works in compat 80 mode ( *= JOIN syntax for example), it just assumes the DB is a 2008 R2 DB, it ignores the compatibility mode.

What are our options here? We know it uses some deprecated syntax, and I can write some XQuery to check the cache for queries that use it, but is there anything else we can do that might be a little more concrete?

1 Answer 1


How about spinning up a SQL Server 2000 or 2005 server. Then script out the 80 database objects and create them on the earlier version SQL Server.

Then run the SQL Server 2008 R2 upgrade advisor to collect the 2000 and 2005 errors that might exist. Once you fixed those issues on 2008 R2, then you can run the 2014 Upgrade Advisor.

  • Neat Approach...
    – eyoung100
    Commented May 20, 2014 at 21:17
  • I like this approach. I would like to leave this unanswered for a bit to see what else people can think of though. Commented May 21, 2014 at 11:29
  • This combined with an interrogation of the plan cache to pick up dynamic code should cover me. Commented May 23, 2014 at 22:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.