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)

error: 26 - Error Locating Server/Instance Specified

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

I received this error because the SQL Server Browser service wasn't running. The advantages of having this running is that users connecting remotely don't have to specify a port and security is improved.

Launch the Windows Services manager (Control Panel > Administrative Tools > Services)

Locate the "SQL Server Browser" service

Right click the service and select "Enable"

error: 28 - Server doesn't support requested protocol

After trying to setup SQL Server 2005 Express, I kept getting the following error when I tried to connect from a web application:

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)"

Network protocols are disabled in SQL Server 2005 Express by default so we need to enable TCP/IP:

Launch the SQL Server Configuration Manager

Expand the "SQL Server 2005 Network Configuration" node

Left click the "Protocols for SQLEXPRESS" node to view the supported protocols

Right click the "TCP/IP" protocol and select "Enable"