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

marc_s
  • 8,932
  • 6
  • 45
  • 51
user32539
  • 11
  • 1
  • 2

1 Answers1

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.

Glorfindel
  • 2,201
  • 5
  • 17
  • 26
Kin Shah
  • 62,225
  • 6
  • 120
  • 236
  • 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 08 '14 at 05:10
  • @AaronBertrand Excellent reference. Thanks for referencing it. – Kin Shah Jan 08 '14 at 05:12