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:
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.
- Open Microsoft SQL Server Management Studio
- Expand the 'Databases' node
- Right click the database and choose 'Properties'
- 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)