Monday, December 3, 2012

SQL Server: SET SET, A Strange Compatibility Upgradation Issue



During upgradation of a client database from SQL Server 2000 (Compatibility Level 80) to SQL Server 2005 (Compatibility Level 90), I have found that there are changes which need to apply for compatibility level 90, that are still not documented. Like everyone knows that *= or =* type joins are not acceptable in compatibility level 90. But what about following simple code.
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 80
GO
USE [AdventureWorks]
GO
DECLARE @MyVar INT
SET SET @MyVar = 5
Executing above code with compatibility  level 80 doesn’t generate any error but when we execute same code with COMPATIBILITY_LEVEL =90, it will generate following error.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.

No comments:

Post a Comment

All suggestions are welcome