Database Compatibility Level

Articolo
02/17/2008

VSTS-DB, both the 2005 and 2008 version, makes an assumption with regards to database compatibility levels, which can cause problems in certain use cases.

The assumption is that the DesignDB instance used for validation of the schema inside the project is set to a 80 compatibility level when using a SQL Server 2000 project and to 90 when you are using a SQL Server 2005 project. If you have a database schema that was migrated from SQL Server 2000 to 2005 and is relying on the fact that the database compatibility level must be set to 80 to keep your code running, this will cause TSD4001 errors when you import/open that schema in to a SQL Server 2005 project and ultimately prevent you from building and deploying your project, since TSD4xxxx are always errors, which come from the DesignDB instance.

Lets look at two common cases that some of you have reported that cause problems:

Problem description	SQL Server 2000 sp_dbcmptlevel = 80	SQL Server 2005 sp_dbcmptlevel = 80	SQL Server 2005 sp_dbcmptlevel = 90
Using hints without a WITH clause, for example: CREATE VIEW [dbo].[v_test] AS SELECT T1.column_1 FROM dbo.T1 (NOLOCK, FASTFIRSTROW) WHERE T1.column_1 = 1	Works	Works	Errors TSD4001: Invalid column name 'NOLOCK'. (SQL error = 207) TSD4001: Invalid column name 'FASTFIRSTROW'. (SQL error = 207) TSD4001: Parameters supplied for object 'dbo.T1' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required. (SQL error = 215)
Using old join ANSI syntax, for example: CREATE PROCEDURE [dbo].[p_test] AS BEGIN SELECT T1.column_1 FROM dbo.T1, dbo.T2 WHERE T1.column_1 *= T2.column_1 RETURN 0; END	Works	Works	Error TSD4027: The query uses non-ANSI outer join operators ("=" or "="). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes. (SQL error = 4147)

The above list examples are not exhaustive and do not represent the complete list of difference in database compatibility levels. Please see the SQL Server Books Online topic on sp_dbcmptlevel, it contains a list of differences. (Look for the label "Differences Between Lower Compatibility Levels and Level 90".)

Resolution:

So what do you need to do when you are running in to this problem? First you need to determine which version of Visual Studio Team System Database Edition you have and if you need to install the latest QFE or not.

Visual Studio 2005

If you are using the Visual Studio 2005 based version, you need to make sure that you install the latest QFE roll-up, which is documented in the following KB article:

KB 941278: Visual Studio 2005 Team System Database Professional Post Service Release 1 (SR1) Rollup Package #1

Visual Studio 2008

If you are using a Visual Studio 2008 RTM based version, you are OK and do not need to install an update; however if you are using a Visual Studio 2008 pre-release version (CTP, beta or RC) you do need to update to the RTM version, since the fix went in at the last moment.

Workaround

Now that you have identified which version you need, how do you work around the problem? We have added a way to set the database compatibility level inside the project, however there is no UI support to set this option. You need to follow the following steps.

Step 1: Unload the project

We will need to add a property to the project file, in order to do this you have to unload the project so that you can edit project file using the XML editor. Right click on the project node inside the Solution Explorer and choose "Unload Project".

Step 2: Add the CompatLevel property to the project file

The next step is to modify the project file to add the CompatLevel property.

Right click on the unloaded project in Solution Explorer and choose "Edit <project name.dbproj>".

This will open the project file inside the XML editor. You need to make sure you are adding the property to the right configuration block. By default there is only one configuration named 'Default'. Which is declared at the top of the project file, below you see the first 18 lines.

    1: <Project DefaultTargets="Build" xmlns="https://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="3.5">

    2:   <PropertyGroup>

    3:     <Configuration Condition=" '$(Configuration)' == '' ">Default</Configuration>

    4:     <Name>"DatabaseProject"</Name>

    5:     <SchemaVersion>2.0</SchemaVersion>

    6:     <ProjectGuid>{86c9eefc-ee75-4a7f-a518-b398f5922055}</ProjectGuid>

    7:     <ShowWizard>False</ShowWizard>

    8:     <OutputType>Database</OutputType>

    9:     <DBProduct>SQL Server 2005</DBProduct>

   10:     <RootPath>

   11:     </RootPath>

   12:     <ArithAbort>True</ArithAbort>

   13:     <NumericRoundAbort>False</NumericRoundAbort>

   14:     <AnsiNulls>True</AnsiNulls>

   15:     <ConcatNullYieldsNull>True</ConcatNullYieldsNull>

   16:     <AnsiPadding>True</AnsiPadding>

   17:     <AnsiWarnings>True</AnsiWarnings>

   18:     <QuotedIdentifier>True</QuotedIdentifier>

   19:     <...>

   20:   </PropertyGroup>

What you need to do is at the CompatLevel property by adding the following XML element: <CompatLevel>80</CompatLevel>. I suggest to place it under the DBProduct property, since this information is related.

    1: <Project DefaultTargets="Build" xmlns="https://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="3.5">

    2:   <PropertyGroup>

    3:     <Configuration Condition=" '$(Configuration)' == '' ">Default</Configuration>

    4:     <Name>"DatabaseProject"</Name>

    5:     <SchemaVersion>2.0</SchemaVersion>

    6:     <ProjectGuid>{86c9eefc-ee75-4a7f-a518-b398f5922055}</ProjectGuid>

    7:     <ShowWizard>False</ShowWizard>

    8:     <OutputType>Database</OutputType>

    9:     <DBProduct>SQL Server 2005</DBProduct>

   10:     <CompatLevel>80</CompatLevel>

   11:     <RootPath>

   12:     </RootPath>

   13:     <ArithAbort>True</ArithAbort>

   14:     <NumericRoundAbort>False</NumericRoundAbort>

   15:     <AnsiNulls>True</AnsiNulls>

   16:     <ConcatNullYieldsNull>True</ConcatNullYieldsNull>

   17:     <AnsiPadding>True</AnsiPadding>

   18:     <AnsiWarnings>True</AnsiWarnings>

   19:     <QuotedIdentifier>True</QuotedIdentifier>

   20:     <...>

   21:   </PropertyGroup>

Line 10 shows the required change.

Step 3: Reload the project

After you made the project change, adding the property to the project file, it is time to reload the project. Right click on the unloaded project node inside Solution Explorer and choose "Reload Project". This will reload the project.

NOTE: Make sure you have no errors when loading the project, because this will be an indication that your edits were not correct.

After you reload the project, the errors related to the database compatibility mode differences should have disappeared.

Restrictions

You can add the CompatLevel property to both SQL Server 2000 and SQL Server 2005 projects, however the values allowed for the database compatibility mode are restricted to: 80 and 90, other values are not supported.

Side effects

What are the side effects of adding and setting the value of the CompatLevel property?

The DesignDB instance will be set to the database compatibility level set in the project. When the property is not set explicitly the values are implicitly derived from the DBProject property value.
- <DBProduct>SQL Server 2000</DBProduct> implies database compatibility level 80
- <DBProduct>SQL Server 2005</DBProduct> implies database compatibility level 90

The database compatibility level will get propagated to the build script. For example when using a SQL Server 2005 project with the CompatLevel set to 80, a new build script you will contain:

    1:  CREATE DATABASE [$(DatabaseName)] ON ( NAME = N'PrimaryFileName', FILENAME = N'$(PrimaryFilePhysicalName)') LOG ON ( NAME = N'PrimaryLogFileName', FILENAME = N'$(PrimaryLogFilePhysicalName)') COLLATE SQL_Latin1_General_CP1_CS_AS

    2:  GO

3:

    4:  :on error resume

5:

    6:  EXEC sp_dbcmptlevel N'$(DatabaseName)', 80

    7:  GO

Where an incremental build will contain:

    1:  DECLARE @dbcompatlvl as int;

    2:  SELECT  @dbcompatlvl = cmptlevel

    3:  FROM    [master].[dbo].[sysdatabases]

    4:  WHERE   [name] = N'$(DatabaseName)';

    5:  IF (ISNULL(@dbcompatlvl, 0) != 80)

    6:  BEGIN

    7:      RAISERROR(N'The database compatibility level of the build script %i does not match the compatibility level of the target database %i. Verify whether your database project settings are correct and whether your build script is up to date.', 16, 127, 80, @dbcompatlvl) WITH NOWAIT;

    8:      RETURN;

    9:  END

   10:  GO

Summary

If you are relying on the SQL Server database compatibility level, to keep your code running, you need to set the CompatLevel property inside your project. If you want you can update your project templates to include the CompatLevel property so you do not forget to set it.

-GertD

Comments

Anonymous
February 16, 2008
PingBack from http://www.biosensorab.org/2008/02/16/database-compatibility-level/
Anonymous
April 03, 2008
When I work with clients and show them how Visual Studio Team System Database Edition can impact their
Anonymous
May 02, 2008
A common question in my mind is how to correctly refer to the various editions of Team System in a short...

Condividi tramite