Applications may fail after upgrading your database from SQL Server 2000 to a later version

Applies to: Microsoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise EditionMicrosoft SQL Server 2005 Enterprise X64 Edition More

Symptoms

Consider the following scenario:

  • In a SQL Server 2000 database you have a column in a table that is configured to have a specific default value.
  • You upgrade this database to a later version of SQL Server (either SQL Server 2005, or SQL Server 2008 or SQL Server 2008 R2).

In this scenario, you will notice that, in the upgraded version, the default value for the column will have an additional parenthesis around it and any application that is coded to accept the original default values from SQL Server 2000 will fail.

The following table shows how different versions of SQL Server store a default value of 0 in the catalog metadata tables:
VersionSQL Server 2000SQL Server 2005 or later
Value(0)((0))

Note: This problem only affects numeric values. Text and functions will be stored in the same format in both SQL Server 2000 and the newer versions.
For example, text will be stored as (‘text’), function() will be stored as (function()).

Cause

This behavior is by design. The newer versions of SQL Server differ from SQL Server 2000 in the ways they decode and store SQL expressions in the catalog metadata. For additional information refer to various links that are listed in the More Information section of this article.

Resolution

Modify your applications so that they accept the default values in the new format. For example if your application is designed to expect the default value as "(0)", which has a single set of parentheses around a numeric value, change your application to expect two set of parentheses "((0))" so that it continues to work with SQL Server 2005 and later versions of the database.

More Information

How to view the default values for a column: You can use either of the following two methods to view the currently configured default values for a column:

Method 1: Use SQL Server Management Studio:
  1. In Object Explorer, right-click the table with columns for which you want to check the default values for and click Design to open the table in the Table Designer.
  2. Select the column for which you want to check thedefault value.
  3. In the Column Properties tab, you will find the default value in the Default Value or Binding property as shown in the images below:
Default Value or Binding in SQL Server Management Studio


Method 2: Use one of the following System Views:

  • sys.default_constraints - Use the Definition column to get the required information.
  • sys.syscomments - Use the Text column in this view to obtain the necessary information.
    Note:This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see the links under Additional References below.
Additional references: