System Configuration

Installing SQL Server

Installing SQL Server

System Configuration

On the System Configuration screen, you can set options for system objects to transfer, ANSI Nulls, and quoted identifiers.

System Objects to Transfer

When the SQL Server Upgrade Wizard upgrades the master database, it can upgrade several configuration options:

  • Server configuration

    Logon information and remote logon registrations and server configuration options relevant to Microsoft® SQL Server™ 2000 are transferred as part of the version upgrade process. The SQL Server 6.5 configuration options not used in SQL Server 2000 are not transferred.

  • Replication settings

    All articles, subscriptions, and publications of each selected database, including the distribution database, if any, are transferred and upgraded.

  • SQL Executive settings

    All tasks scheduled by SQL Executive are transferred and upgraded so that the SQL Server 2000 can schedule and run the tasks in SQL Server Agent.

Note  Upgrading replication or SQL Executive settings causes existing modifications made to the SQL Server 2000 replication or SQL Server Agent settings to be overwritten.

ANSI Nulls

The ANSI_NULLS option controls both database default nullability and comparisons against null values. When upgrading Microsoft SQL Server version 6.5 to the SQL Server 2000, set the ANSI_NULLS option to ON or OFF.

When the SQL Server Upgrade Wizard creates the SQL Server 2000 database tables, the database default nullability determined by the ANSI_NULLS option is not an issue. All columns are explicitly qualified as NULL or NOT NULL based on their status in SQL Server 6.5.

The ANSI_NULLS option is important with regard to comparisons against null values, when the SQL Server Upgrade Wizard creates the SQL Server 2000 database objects. With ANSI_NULLS set to ON, the comparison operators EQUAL (=) and NOT EQUAL (<>) always return NULL when one of its arguments is NULL. With ANSI_NULLS set to OFF, these operators return TRUE or FALSE, depending on whether both arguments are NULL.

In SQL Server 6.5, the ANSI_NULLS option in objects, such as stored procedures and triggers, is resolved during query execution time. In SQL Server 2000, the ANSI_NULLS option is resolved when the object is created. You must choose the ANSI_NULLS option setting you want for all objects in the databases you are upgrading. The SQL Server Upgrade Wizard then creates all database objects using this ANSI_NULLS setting.

Quoted Identifiers

Note  Quoted identifiers are used by default in SQL Server 2000, that is, they are set to ON. This is different from SQL Server 7.0 where they were set to OFF by default.

The QUOTED_IDENTIFIER setting determines what meaning Microsoft SQL Server gives to double quotation marks ("). When QUOTED_IDENTIFIER is set to OFF, double quotation marks delimit a character string, just as single quotation marks do. When QUOTED_IDENTIFIER is set to ON, double quotation marks delimit an identifier, such as a column name. An identifier must be enclosed in double quotation marks; for example, if its name contains characters that are otherwise not allowed in an identifier, including spaces and punctuation, or if the name conflicts with a reserved word in Transact-SQL. Regardless of the QUOTED_IDENTIFIER setting, an identifier can also be delimited by square brackets.

The meaning of the following statement, for example, depends on whether QUOTED_IDENTIFIER is set to ON or OFF:

SELECT "x" FROM T

If QUOTED_IDENTIFIER is set to ON, "x" is interpreted to mean the column named x. If it is set to OFF, "x" is the constant string x and is equivalent to the letter x.

If the previous SELECT statement example were part of a stored procedure created when QUOTED_IDENTIFIER was set to ON, then "x" would always mean the column named x. Even if the QUOTED_IDENTIFIER setting was later switched, and set to OFF, the stored procedure would respond as if it were set to ON and treat "x" as the column named x.

When the SQL Server Upgrade Wizard re-creates database objects in SQL Server 2000, the QUOTED_IDENTIFIER setting determines how all of these objects behave. If all database objects were created in SQL Server 6.5 with the same QUOTED_IDENTIFIER setting, click that setting, either On or Off. If objects were created in SQL Server version 6.5 with a mix of the two settings, or if you are unsure of the settings used, click Mixed.

With the Mixed option, the SQL Server Upgrade Wizard first converts all objects containing double quotation marks with QUOTED_IDENTIFIER set ON. The SQL Server Upgrade Wizard then converts any objects that failed to be created with QUOTED_IDENTIFIER set OFF.

See Also

How to perform a SQL Server version 6.5 to SQL Server 2000 upgrade using a direct pipeline (SQL Server Upgrade Wizard)

How to perform a SQL Server version 6.5 to SQL Server 2000 upgrade using a tape drive (SQL Server Upgrade Wizard)