0

We have SQL Server 2008 R2 running in comaptibilty mode 80 (2000) as we lot of discontinued features used. Intially i thought i will get only features 2000 to use, but as pleasent surprise i show INFORMATION_SCHEMA, i thought this is superset case. we have access to all features of 2000, 2005, 2008 and R2 but reecently when I was palying around with DMV/DMF I tried to pass sql_handle to sys.dm_exec_sql_text, but it did not work. A bit of googe/hit and run showed me that i need to change comaptibility mode as this will not work in comaptibilty mode 80. So what features we have access to when we use 2008 r2 in comaptibility mode 80 (2000) ???

Pritesh
  • 1,938
  • 7
  • 32
  • 46
  • Have you come across [this manual page](http://msdn.microsoft.com/en-us/library/bb510680%28v=sql.105%29.aspx "ALTER DATABASE Compatibility Level (Transact-SQL)")? It contains a chart describing differences between various compatibility levels. – Andriy M May 02 '12 at 08:21
  • @AndriyM it does not make clear on this "feature like CTE (CTE came in 2005) will be available, but sql_handle will not be available. – Pritesh May 03 '12 at 05:05

1 Answers1

0

You need to update the outer joins syntax used in sql server 2000.

Also, very important: SQL Server 2012 does not support compatibility level 80 at all, so you need to fix your procedure before you can upgrade.

Romil Kumar Jain
  • 20,239
  • 9
  • 63
  • 92
  • I am looking for what features of 2005/2008/R2 are available and what are not available in mode 80 – Pritesh May 03 '12 at 05:02