1

I have to move a DB running in compatibility mode 80 (SQL Server 2000) on a SQL Server 2005 to a SQL Server 2012. How can I scan it for stored procedures that might break due to deprecated or discontinued syntax? I realize that external (VB or Java) programs might also need some changes. I want to

2

Run Upgrade advisor or you can run server side trace to Identify deprecated SQL Server code -By Aaron Bertrand.

It will show you - what will break when you upgrade to new version of sql server and change the compatibility mode to the upgraded version.

Also, when upgrading from SQL Server 2000 to 2012, consider using both the older 2008 R2 Upgrade Advisor and the new SQL 2012 Upgrade Advisor, as this will provide a more complete picture of issues you may need to fix.

  • Upgrade advisor and trace/DMVs etc. won't show everything, of course. For example, in earlier versions of SQL Server, I've seen people check for the existence of a #temp table using IF OBJECT_ID('tempdb..#foo') > 0 - but in SQL Server 2012, all #temp tables get a negative object_id. I don't think this behavior change is documented anywhere, but it is potentially a breaking change. I've compiled a few other things to look for in this answer. (Don't let the modest number of up-votes fool you - it's useful info. IMHO.) – Aaron Bertrand Jan 8 '14 at 5:10
  • @AaronBertrand Excellent reference. Thanks for referencing it. – Kin Shah Jan 8 '14 at 5:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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