Upgrading database to SQL Server 2008

There are minor syntax changes in Transact-SQL that prevent applications written for earlier SQL Server versions from running in SQL Server 2008. It is possible to set the Compatibility Level of a database to support earlier versions of Transact-SQL. The following statement will alter the database to run at full SQL Server 2008 mode:

Alter Database Set COMPATIBILITY_LEVEL = 100

One of the typical problem is that older, non-ANSI style outer join using *= or =* does not work in SQL 2008. It is possible to do a quick assessment of offending queries in stored procedures, functions and views using the following SQL statements:

select r.ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES r where r.ROUTINE_DEFINITION like '%*=%' or r.ROUTINE_DEFINITION like '%=*%'

select v.TABLE_NAME from INFORMATION_SCHEMA.VIEWS v where v.VIEW_DEFINITION like '%*=%' or v.VIEW_DEFINITION like '%=*%'