SQL Server compatability level

When you upgrade to SQL Server 2005, you may find that some SQL statements are no longer valid and produce errors. A typical example is when you have 2 tables under different databases with the same name (the schema is irrelevant).

Assume that you have 2 databases, MyDatabase1 and MyDatabase2, each containing a table called MyTable. Both tables have a primary key called ID which are also foreign keys between Database1..MyTable and Database2..MyTable. The following SQL statement is valid under SQL Server 2000 but invalid under SQL Server 2005:

use [MyDatabase1]
 
select * from [MyTable]
inner join [Database2]..[MyTable] on [MyTable].[ID] = [Database2]..[MyTable].[ID]

SQL Server 2005 is more strict and won't allow you to reference MyTable of MyDatabase1 in this way, even though you've specified the default database (use [MyDatabase1]). Instead you'd have to change the statement:

select * from [Database1]..[MyTable]
inner join [Database2]..[MyTable] on [Database1]..[MyTable].[ID] = [Database2]..[MyTable].[ID]

Alternatively, you can change the compatability level on the nececssary database(s). This is a quick fix and should only be done if you can't easily modify the SQL code.

1. Open Microsoft SQL Server Management Studio
2. Expand the 'Databases' node
3. Right click the database and choose 'Properties'
4. Go to 'Options' and change the 'Compatability level' accordingly

The 3 compatability levels are:

• SQL Server 7.0 (70)
• SQL Server 2000 (80)
• SQL Server 2005 (90)