Service Pack 4 for Microsoft SQL Server 2000 - Database Engine Components

# Service Pack 4 for Microsoft SQL Server 2000

# Database Components

##### March 21, 2005

© Copyright Microsoft Corporation, 2004. All rights reserved.

The SQL Server documentation team cannot answer technical support questions, but welcomes your suggestions and comments about this Readme documentation. You can quickly and directly send e-mail feedback using the link below. Please send your feedback in English.
To submit written feedback about this document, click here: Submit feedback.

# Contents

1.0 Introduction

1.1 System Requirements

1.2 Before You Upgrade to Database Components SP4

1.3 Verify the Version of Microsoft Data Access Components

1.4 Identify the Current Version of SQL Server 2000

1.5 Additional Information About SP4

1.6 Updates to SQL Server 2000 Books Online Are Available

2.0 Where to Find and Download Database Components SP4

2.1 Choosing the Correct Language

2.2 Downloading Database Components SP4

2.3 Extracting the Database Components SP4 Files

2.4 Download and Extraction Phase Guidelines

2.5 Database Components SP4 Setup Documentation

3.0 Service Pack Installation

3.1 Prepare for Database Components SP4 Installation

3.2 Install Database Components SP4

3.3 Restart Services and Applications

3.4 Install on a Failover Cluster

3.5 Install Database Components on Replicated Servers

3.6 Apply SP4 to Read-Only Databases or Filegroups in a Replication Topology

3.7 Upgrade the Catalog of Linked Servers

3.8 Uninstall Database Components SP4

3.9 Reapply Database Components SP4

4.0 Additional Installation Information

4.1 Unattended Installations

4.2 Redistributing SP4 Database Components

4.3 Systems Management Server Distributed Installation

5.0 Documentation Notes

5.1 Database Enhancements

5.2 Replication Enhancements

5.3 SQL Server Agent and Shared Tools Enhancements

5.4 SQL Server Connectivity Component Enhancements

5.5 Meta Data Services Enhancements

5.6 Data Transformation Services Enhancements

5.7 XML Enhancements

5.8 Virtual Backup Device API Enhancements

5.9 Error Reporting

5.10 Serviceability Enhancements

5.11 English Query Enhancements

5.12 DB-Library and Embedded SQL for C

[Top]

# 1.0 Introduction

This Readme file describes how to use the Database Components part of Microsoft® SQL Server™ 2000 Service Pack 4 (SP4) to upgrade existing instances of the SQL Server 2000 database engine to SQL Server 2000 SP4.

The general process for installing SP4 is as follows:

1. Determine whether you can use SP4 and if so, which part or parts of SP4 you need to install. Make sure to review all parts of Section 1.0 before you download and install SP4.
2. Download and extract the service pack installation files. Section 2.0 describes how to obtain the SP4 installation files.
3. Prepare an instance for upgrade to SP4. Section 3.1 details the preparatory steps to take before you install SP4.
4. Install SP4. Section 3.2 details options for running SP4 Setup.

SQL Server 2000 SP4 has four parts:

- Database Components SP4 updates an instance of the database engine for SQL Server 2000, excluding instances of the SQL Server 2000 Desktop Engine (MSDE 2000). Database Components SP4 can be used to upgrade an instance of SQL Server 2000 Workgroup Edition. Database Components SP4 includes updates to the following:
  - The database engine.
  - All of the database client tools and utilities such as SQL Server Enterprise Manager and the osql utility.
  - Database client connectivity components, such as the Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver, and the client Net-Libraries.
  - Replication.
  - Data Transformation Services (DTS).
  - Meta Data Services.
- Analysis Services SP4 updates the various parts of SQL Server 2000 Analysis Services in a SQL Server 2000 installation.
- MSDE 2000 SP4 updates instances of SQL Server 2000 MSDE 2000 and MSDE 2000 Release A.
- SQL Server 2000 SP4 (64-bit) includes all updates for SQL Server 2000 (64-bit). This is the first service pack for SQL Server 2000 (64-bit).

All SQL Server service packs are cumulative. SQL Server SP4 includes the fixes delivered in SP1, SP2, SP3, and SP3a.

Database Components SP4 can only be used on instances of the SQL Server 2000 database engine from Enterprise Edition, Standard Edition, Developer Edition, or Personal Edition. The other parts of SQL Server 2000 SP4 apply to the other SQL Server 2000 components, such as Analysis Services, MSDE 2000, or SQL Server 2000 (64-bit). Separate Readme files describe use of the Analysis Services SP4, MSDE 2000 SP4, and SQL Server 2000 SP4 (64-bit). The other Readme files are available at this Microsoft Web site.

[Top]

## 1.1 System Requirements

This section describes changes to system requirements and system related issues that affect the installation of Database Engine SP4. General information on system requirements for SQL Server 2000 can be found at this Microsoft Web site.

##### Changes to Supported Systems

- SQL Server 2000 SP4 is not supported on Windows NT 4.0, Windows Millennium Edition, and Windows 98 platforms.
- SQL Server 2000 SP3a installations on Windows NT 4.0, Windows Millennium Edition and Windows 98 platforms continue to receive critical hotfix support for 12 months after the final release of SQL Server 2000 SP4.
- SQL Server 2000 SP4 introduces support for SQL Server 2000 (32-bit) on Windows 2003 SP1 running in Windows-on-Windows 64 mode on X64 or compatible processors.

##### Installation Issues on Supported Systems

A Database Components SP4 installation will fail if either of the following security policies has been set to Do not allow installation.

- The Devices: Unsigned driver installation behavior local security policy for Windows XP
- The Unsigned non-driver installation behavior local security policy for Windows 2000

If you use the Do not allow installation setting, you must change it to Silently succeed before you install Database Components SP4. If necessary, you can return the policy to its previous setting after the installation is complete.

Note Do not allow installation is not the default setting for these security policies.

To set security policies

1. In Control Panel, double-click Administrative Tools.
2. Double-click Local Security Policy.
3. Expand Local Policies.
4. Select Security Options.
5. Ensure that the following option in the right pane is set to Silently Succeed before installing Database Components SP4:
6. On Windows XP and Windows 2003: Devices: Unsigned driver installation behavior.
7. On Windows 2000: Unsigned non-driver installation behavior.

[Top]

##### Application Requirements

If your instance of SQL Server 2000 is being used by an application, before you upgrade to Database Components SP4 ask the provider of the application whether are any SQL Server 2000 upgrade considerations apply to that application.

[Top]

## 1.2 Before You Upgrade to Database Components SP4

This section describes issues that you must address and tasks you must perform before using Database Components SP4 to upgrade an existing instance of the database engine for SQL Server 2000.

Databases or database backups created on an instance of Database Components SP4 can be attached or restored on an earlier version of SQL Server 2000. However, there are restrictions for databases in a replication topology. For more information, see Section 1.2.2 Considerations for an Instance in a Replication or Log Shipping Topology.

The Database Components SP4 Setup program automatically detects which edition of SQL Server 2000 is present on the instance of SQL Server 2000 that is to be upgraded. Setup upgrades only the components that are installed for that instance. For example, if you apply the service pack to a computer on which SQL Server 2000 Standard Edition is running, the service pack does not attempt to upgrade components that are included only with SQL Server 2000 Enterprise Edition.

You can apply Database Components SP4 to a single default instance or a named instance of SQL Server. If you upgrade multiple instances of SQL Server 2000 to SP4, you must apply SP4 to each instance. When one instance on a computer with one or more instances of SQL Server 2000 is upgraded to SP4, all of the tools are upgraded to SP4. There are not separate copies of the tools for each instance.

If your instance of SQL Server 2000 is being used by an application, first check with the provider of the application whether there are any SQL Server 2000 upgrade considerations specific to that application.

[Top]

### # 1.2.1 Determine How to Remove Database Components SP4

Before you use Database Components SP4 to upgrade an existing instance of the database engine, it is advisable to plan how to return the instance to its previous state in case that becomes necessary later. When SQL Server 2000 Database Components SP4 is installed, it makes changes to the system tables for maintenance purposes. It also upgrades user and distribution databases that are members of a replication topology. Because of the nature of these changes, Database Components SP4 cannot be removed easily. To revert to the build that you were running before you installed Database Components SP4, you first must uninstall the instance of the SQL Server 2000 database engine and then reinstall that instance. Second, if you ran a previous SQL Server 2000 service pack or applied any hotfixes, you must reapply that service pack and any hotfixes to the instance.

Note To remove SP4, you must have backups of the master, model, and msdb databases that were taken immediately prior to applying SP4. For more information, see Section 3.1.1 Back Up Your SQL Server Databases.

For more information, see Section 3.8 Uninstalling SP4.

[Top]

### 1.2.2 Considerations for an Instance in a Replication or Log Shipping Topology

SQL Server 2000 SP4 Setup upgrades user databases that are members of a replication topology. This upgrade factor can affect backup-and-restore functionality for replicated user databases. Before you install SP4, ensure that replication databases and filegroups are writable. For more information about applying SP4 to databases that are included in replication topologies, see Section 3.5 Install Database Components on Replicated Servers. Additional backup and restore considerations for replication are detailed in section 5.2.6 Backup and Restore Issues for Merge Replication.

Note If an instance of SQL Server is not part of a replication topology, you can back up a user database and restore it on any other release of SQL Server 2000.

If SP4 Setup detects user databases or filegroups that are not writable, it does the following:

- Applies the SP4 replication updates to all writable user databases.
- Writes a list of the non-writable databases to the Setup log, which is located at Winnt\Sqlsp.log.
- Displays the following warning message:

      Setup has detected one or more databases and filegroups which are not writable.

  Note This message does not affect unattended installations of Database Components. For more information about unattended installations, see Section 4.1 Unattended Installations.

You can ignore this warning unless some of the databases listed in the Setup log are members of a replication topology. If any of the non-writable databases listed in the Setup log are members of a replication topology, you must make those databases writable and reapply SP4 Setup to that instance of SQL Server 2000.

For information about making a database writable, see Section 3.6 Apply SP4 to Read-Only Databases or Filegroups in a Replication Topology. For more information about reapplying SP4, see Section 3.9 Reapply Database Components SP4.

##### Log Shipping and Database Components

Because non-writable databases do not cause Setup to fail, you need not remove log shipping before upgrading to Database Components SP4. However, if the database is shipping logs to a database that is a replication publisher, you must do the following:

1. Take the database offline before you apply SP4.
2. Apply SP4 to the instance.
3. Bring the database back online.
4. Log on to Query Analyzer and run the following script:

       USE master
       GO
       EXEC sp_vpupgrade_replication
       GO

If you apply SP4 without having taken offline all non-writable databases that ship logs to publication databases, you receive this error:

`Error Running Script sp_vpupgrade_replication (1)`

If you receive this error, follow the preceding procedure.

Note During installation, Setup makes no distinction between read-only databases and databases that are offline or in a suspect state. If a replication database or filegroup is offline or in a suspect state during setup and is involved in a replication topology, you must reapply the service pack after making the database writable.

[Top]

## 1.3 Verify the Version of Microsoft Data Access Components

Database Components SP4 setup determines whether to upgrade an installed version of Microsoft Data Access Components (MDAC) to MDAC 2.8 SP1.

- Computers that run Windows 2000 are upgraded to MDAC 2.8 SP1.
- Computers that run Windows XP or Windows Server 2003 are not upgraded to MDAC 2.8 SP1. If your system requires any of the fixes included in MDAC 2.8 SP1, upgrade your system to Windows XP SP2 or Windows Server 2003 SP1 to obtain MDAC 2.8 SP2.
- MDAC 2.8 SP1 is not installed on computers with an equal or later version of MDAC already installed.

  Note If a computer on which Database Components SP4 is later upgraded to a newer operating system platform, the version of MDAC installed by SP4 is no longer present.

Note See Knowledge Base article 301202 for instructions on determining the version of MDAC on your computer.

When Database Components SP4 installs MDAC 2.8 SP1, the MDAC language version is the same as the language version of Database Components SP4. If you want to maintain a language version of MDAC that is different from that of Database Components SP4, you must download and install the intended language version of MDAC 2.8 SP1 before you run Database Components SP4 Setup. You can download language-specific versions of MDAC 2.8 SP1 from this Microsoft Web site.

MDAC 2.8 SP1 includes an upgrade to MSXML 3.0 SP7. MDAC 2.81 also updates SQLXML 1.0, which shipped with Microsoft SQL Server 2000. This service pack does not install or update SQLXML 3.0. If your application requires SQLXML 3.0, you must download and install it from this Microsoft Web site. For more information about MDAC 2.8 SP1, see this Microsoft Web site. For more information about MDAC versions, see Knowledge Base article 822758. Fixes included in MDAC 2.8 SP1 are documented in Knowledge Base article 884930.

Note Prerelease versions of SQL Server 2000 SP4 installed a prerelease version of MSXML 3.0 SP7. If you installed a prerelease version of SQL Server 2000 SP4, it is recommended that you download and install the final release version of MSXML 3.0 SP7 from this Microsoft Web site.

[Top]

## 1.4 Identify the Current Version of SQL Server 2000

Before running Setup, you must identify the version of the Database Components instance that is being upgraded.

To identify the installed version of SQL Server 2000 Database Components

1. Execute one of the following queries against an instance of the database engine by using isql, osql, or Query Analyzer.
   - `SELECT SERVERPROPERTY('ProductLevel')`
   - `SELECT @@VERSION`
   - `SELECT SERVERPROPERTY('ProductVersion')`
2. Find out your version of Database Components by means of the following table.

   SQL Server 2000 Version and Level | @@VERSION | Product Level
   SQL Server 2000 Original Release | 8.00.194 | RTM
   Database Components SP1 | 8.00.384 | SP1
   Database Components SP2 | 8.00.534 | SP2
   Database Components SP3, SP3a or MSDE 2000 Release A. | 8.00.760 | SP3
   Database Components SP4 | 8.00.2039 | SP4

   Note Your product version may be different than these values if you applied a hotfix after installing the product or after installing a previous service pack. For example, `@@VERSION` returns a value of 8.00.818 after you apply the security fix MS03-031 to SQL Server 2000 SP3a.

3. (Optional) If you are not sure whether you are running an edition of the SQL Server 2000 database engine or MSDE 2000, use isql, osql or Query Analyzer to execute the following query against the instance in question.
   - `SELECT SERVERPROPERTY('Edition')`

   If this query returns "desktop engine," you are running an instance of MSDE 2000; otherwise, you are running an instance of SQL Server 2000 database engine.

[Top]

## 1.5 Additional Information About SP4

A list of the fixes contained in this service pack will be provided in Microsoft Knowledge Base article 888799. Each fix listed in 888799 has a link to a Knowledge Base article about the problem that the fix addresses. Follow the links to the individual Knowledge Base articles to see information about each fix.

Any information relevant to SQL Server 2000 Service Pack 4 that was not available in time to be included in this Readme file will be published in Microsoft Knowledge Base article 884525.

The Knowledge Base articles mentioned in this Readme are available in the Microsoft Support Knowledge Base.

To find an article in the Knowledge Base

1. Under Advanced Search, in the For text box, type the number of the article you want.
2. Under Search Type, select Article ID.
3. Click the Run the search right-arrow button.

##### Hotfixes

All publicly issued SQL Server 2000 SP3a and SQL Server 2000 (64-bit) security bulletins have been addressed in SP4.

If you received a SQL Server 2000 hotfix after December 2, 2004, that hotfix is unlikely to be included in SP4. Contact your primary support provider about obtaining the same hotfix for SQL Server 2000 SP4.

SQL Server 2000 SP4 includes serviceability enhancements that enable you to uninstall future hotfixes. For more information, see Section 5.10 Serviceability Enhancements.

##### Slammer Worm-Related Fixes

Microsoft SQL Server 2000 (SP4 incorporates changes to the SQL Server 2000 components that address issues raised by the Slammer worm:

- SP4 can be applied to SQL Server 2000 Enterprise Evaluation Edition in order to apply fixes to instances of that edition of SQL Server.
- SP4 addresses setup issues some customers encountered with the version of the Microsoft Data Access Components (MDAC) in SQL Server 2000 SP3.
- When an instance of SQL Server 2000 SP4 or SP3a is configured to not support network connections, the instance also stops using UDP port 1434.

##### SQL Server CE and SQL Mobile Server Tools Updates

Users of Microsoft SQL Server 2000 Windows® CE Edition (SQL Server CE) and SQL Server 2005 Mobile Edition (SQL Mobile) who have upgraded or plan to upgrade SQL Server 2000 database and publisher servers to SP4 should also update the server replication components on Microsoft Internet Information Services (IIS) servers. Updated server tools installers are available for SQL Server CE and for SQL Mobile.

Note Even if you updated your server replication components after you upgraded to SQL Server 2000 SP3 or SP3a, you must install the latest SP4-specific updates to server tools components.

##### OPENXML Update

SQL Server 2000 SP4 removes the OPENXML dependency on the version of MSXML installed by the operating system. Database Components SP4 installs an internal version of the MSXML technology that is backward compatible with MSXML 2.6.

[Top]

## 1.6 Updates to SQL Server 2000 Books Online Are Available

SQL Server 2000 Books Online is the primary user documentation set for Database Components 2000. Books Online is periodically updated with fixes and new information.

- The latest version of the Books Online is available as a set of downloadable files at this Books Online download site.
- An English-language copy of the latest version of Books Online is available at this Web site.

##### Updated SQL Server 2000 Samples Are Available

Samples for the SQL Server 2000 database engine and Analysis Services that were updated for SP3 and SP3a are available. You can download these updated samples from this Microsoft Web site.

[Top]

# 2.0 Where to Find and Download Database Components SP4

SQL Server 2000 SP4 is distributed in the following ways:

- On a SQL Server 2000 Service Pack 4 CD-ROM that contains service packs for the following components:
  - Database Components SP4
  - Analysis Services SP4
  - MSDE 2000 SP4
  - SQL Server 2000 SP4 (64-bit)

  If you have an SP4 CD, you can upgrade an instance of SQL Server 2000 to SP4 by using the Database Components SP4 files directly from the CD.

- In four self-extracting installation package files that can be downloaded from the Internet at this Microsoft Web site:
  - SQL2000-KB884525-SP4-x86-LLL.exe (Database Components SP4)
  - SQL2000.AS-KB884525-SP4-x86-LLL.exe (Analysis Services SP4)
  - SQL2000.MSDE-KB884525-SP4-x86-LLL.exe (Desktop Engine (MSDE 2000) SP4)
  - SQL2000-KB884525-SP4-ia64-LLL.exe (SQL Server 2000 SP4 (64-bit))

  Note LLL represents a designator that varies by language.

  If you do not have an SP4 CD, you can download SQL2000-KB884525-SP4-x86-LLL.exe and then run it to extract the Database Components SP4 files onto your computer. SQL2000-KB884525-SP4-x86-LLL.exe creates a set of folders and files on your disk that have the same organization as the Database Components folders and files on the SP4 CD. After this file extraction phase is complete, you can install Database Components SP4 from the folders on your disk.

[Top]

## 2.1 Choosing the Correct Language

SQL Server 2000 Database Components service packs are language-specific. To upgrade an instance of SQL Server 2000, you must obtain the service pack that has the same language as your instance. You get the service pack either on a SQL Server 2000 SP4 CD or by downloading the Database Components SP4 files. For example, if you upgrade an instance of SQL Server 2000 that uses Japanese, you must get the Japanese version of Database Components SP4.

If you are unsure of the language of an instance of SQL Server 2000:

- Click Start, then click Run.
- In the Open box, type Regedit, then click OK.
- Locate and select one of the following keys:
  - For a default instance of SQL Server 2000, locate and select this subkey:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion

  - For a named instance of SQL Server 2000, locate and select this subkey:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\CurrentVersion

    where InstanceName is the name of the instance.

- In the right list pane, read the value of the Language entry. Compare that value to those in the following table to determine the language for your instance of SQL Server 2000:

  Language Registry Value (in Hexadecimal) | Language Registry Value (in Decimal) | Language of That Instance
  0x00000404 | 1028 | Traditional Chinese
  0x00000407 | 1031 | German
  0x00000409 | 1033 | English
  0x0000040a | 1034 | Spanish
  0x0000040c | 1036 | French
  0x00000410 | 1040 | Italian
  0x00000411 | 1041 | Japanese
  0x00000412 | 1042 | Korean
  0x00000804 | 2052 | Simplified Chinese

[Top]

## 2.2 Downloading Database Components SP4

To download the self-extracting installation package for Database Components SP4:

- Go to this Microsoft Web site.
- From the Full Download drop-down list in the upper-right portion of this page, select the language that matches the language for your instance of SQL Server 2000, and click Go.
- On the Microsoft Download Center page that corresponds to the language chosen, select the file SQL2000-KB884525-SP4-x86-LLL.exe, where LLL varies by language.

[Top]

## 2.3 Extracting the Database Components SP4 Files

After you download the self-extracting file that contains the installation package, you must extract the Database Components SP4 files:

- Open Windows Explorer and navigate to the folder into which you downloaded the self-extracting installation package file.
- Double-click the downloaded file to extract the Database Components SP4 files. When asked, specify the folder into which you want the SP4 files to be extracted.

[Top]

## 2.4 Download and Extraction Phase Guidelines

When you download and extract Database Components SP4 installation files from the Internet, use the following guidelines:

- To install or upgrade to Database Components SP4, you download SQL2000-KB884525-SP4-x86-LLL.exe either into a folder on a computer or a folder on a network shared directory. Make sure you have free space that is approximately three times the size of the executable file in order to extract and store the service pack installation files. You must have space enough to store the self-extracting file and the extracted service pack files, and temporary working space for the self-extraction program.
- When you download SQL2000-KB884525-SP4-x86-LLL.exe to your local or shared directory and run the installation file, SQL2000-KB884525-SP4-x86-LLL.exe prompts you for the name of the folder into which you want the service pack files placed.

  Note When you extract the service pack to a network shared directory, the path to the folder that you specify is relative to the folder from which you ran SQL2000-KB884525-SP4-x86-LLL.exe.

- You can rename or move the folder after you extract the components. However, make sure that the folder path name does not contain blank spaces.
- The self-extracting files are used to build a set of folders and files that are the same as those on the SP4 CD-ROM. If you download SP4 you must extract the files to build the service pack folders before you can run SQL Server Setup.
- The Database Components SP4 setup process is the same for both the CD-ROM and the extracted files.
- You can download all three SP4 files into the same target folder to create a folder that has the same structure as the complete SQL Server 2000 SP4 CD. The three self-extraction files will not overwrite each other or interfere with each other.

[Top]

## 2.5 Database Components SP4 Setup Documentation

Database Components SP4 installation files contain updated setup documentation that you can access by clicking Help during Database Components SP4 setup. This setup documentation does not update the version of SQL Server 2000 Books Online that is already installed on your computer. For information about how to get an updated version of SQL Server 2000 Books Online, see Section 1.6 Updates to SQL Server 2000 Books Online Are Available. If you want to access just the updated SQL Server 2000 SP4 setup documentation without updating SQL Server 2000 Books Online, run the Setupsql.chm file. Setupsql.chm is located in the \Books subfolder of the folder on the SP4 CD-ROM, the local folder, or the network share that contains the extracted service pack files.

[Top]

# 3.0 Service Pack Installation

To install Database Components SP4, use the installation instructions in the following sections. Review the material in Section 1.0 Introduction before you install Database Components SP4. The steps to install Database Components SP4 are as follows:

1. Prepare for Database Components SP4 Installation
2. Install Database Components SP4
3. Restart Services and Applications

[Top]

## 3.1 Prepare for Database Components SP4 Installation

You must do the following before you install Database Components SP4:

1. Back up your SQL Server databases
2. Verify that the system databases have enough free space
3. Stop services and applications

[Top]

### 3.1.1 Back Up Your SQL Server Databases

Before installing Database Components SP4, back up the master, msdb, and model databases. Installation of SP4 modifies the master, msdb, and model databases, making them incompatible with pre-SP4 versions of SQL Server. Backups of these databases are required if you decide to reinstall SQL Server 2000 without SP4.

It is also prudent to back up your user databases, although SP4 updates only those user databases that are members of replication topologies.

An existing backup scheme that accounts for replication allows you to restore a database to a known point after the SP4 upgrade in case of a failure. After applying SP4, a log or full database backup is recommended for any user database that is included in a replication topology. This way, if a replication database fails, you do not have to reapply SP4 after the database is restored.

[Top]

### 3.1.2 Verify That the System Databases Have Enough Free Space

If the autogrow option is not selected for the master and msdb databases, the databases must have at least 500 kilobytes (KB) of free space. To verify that they have this space, run the sp_spaceused system stored procedure for the master or msdb database. If the unallocated space in either database is less than 500 KB, increase the size of the database. For more information, see "Expanding a Database" in SQL Server 2000 Books Online.

If the autogrow option is selected for the master and msdb databases, and there is sufficient room on the drives, you can skip the preceding space-verification step.

To verify that the autogrow option is selected in SQL Server 2000, open SQL Server Enterprise Manager, right-click the icon for the database, and then click Properties. Verify that the Automatically grow file check box is selected.

[Top]

### 3.1.3 Stop Services and Applications Before You Run Database Components SP4 Setup

You should stop all applications and services, including Control Panel, Add and Remove Programs, SQL Server 2000 Reporting Services, SQL Server 2000 Notification Services, and all applications that make connections to the instance of the database engine being upgraded, before installing Database Engine SP4.

You can apply Database Components SP4 without first shutting down services, but then some services will not start again unless you restart the system. If you do not shut down services, you are prompted to restart the computer when Setup is complete. If you do not restart the system, the following services may fail to start:

- Microsoft Distributed Transaction Coordinator (DTC) and the Microsoft Search service.
- The MSSQLServer and SQLServerAgent services for the instance being upgraded (for example, MSSQL$NamedInstance).
- Microsoft Component Services, Microsoft Message Queuing, and Microsoft COM Transaction Integrator.

You can reduce the likelihood that you will need to restart your computer after you install Database Components SP4. To reduce this likelihood, stop the services and applications in the preceding list before you run Setup.

You cannot stop the services in a clustered environment. For more information, see Section 3.4 Install on a Failover Cluster.

[Top]

## 3.2 Install Database Components SP4

The following information applies only to the Database Components part of SQL Server 2000.

Run the Setup.bat script from one of the following locations:

- The folder on the local computer that contains the extracted service pack files from SQL2000-KB884525-SP4-x86-LLL.exe, where LLL varies by language.
- The root folder on the SQL Server 2000 SP4 CD-ROM.
- The folder on the network share containing the extracted service pack files from SQL2000-KB884525-SP4-x86-LLL.exe.

Note In order to install database components from a network share, you must first do one of the following:

- Map the network share containing the extracted service pack files to an available drive letter on the local computer, navigate to the folder on the mapped drive that contains Setup.bat, and then run Setup.bat.
- Start Setup by running the Setupsql.exe file found in \X86\Setup, which is a subfolder of the folder on the network share that contains the extracted service pack files.

##### Run Database Components SP4 Setup

Setup displays a dialog box that prompts you for information, such as whether you want to use SQL Server Authentication or Windows Authentication. If you choose SQL Server Authentication, you must supply the Setup program with the password for the sa login. If you choose Windows Authentication, you must run the Setup program while logged on to Windows with a Windows login account that is a member of the sysadmin fixed server role for the instance of SQL Server 2000 you are upgrading.

The Setup program then performs the following tasks:

- Displays an Authentication Mode dialog box if it detects that the installation is using Mixed Mode Authentication with a blank password for the sa login. Leaving the sa login password blank provides users with easy administrative access to SQL Server or MSDE and is not recommended; protect your systems by enforcing an sa password or by using Windows Authentication. To learn more about this dialog box see Authentication Mode Dialog Box later in this section.
- Displays an SA Password Warning dialog box if it detects a blank password for the sa login. You can continue your installation with a blank password for the sa login by explicitly choosing to ignore the warning box and continue Setup. However, a blank password poses a security risk and is not recommended. This warning dialog box appears regardless of the authentication mode you use.

  Note This password change is made immediately; even if Setup fails, the password is nonetheless changed.

- Displays a Backward Compatibility Checklist dialog box that warns of any problems you might encounter if you apply the service pack to versions of SQL Server earlier than SP3. To learn more about this dialog box, see Backward Compatibility Checklist Dialog Box later in this section.
- Displays a message dialog box if it detects that any databases or filegroups are not writable. At this point, if any of these databases or filegroups are part of a replication topology you can either:
  - Stop the setup process, make these objects writable, and restart Setup.
  - Continue with the current SP4 setup process and reapply SP4 at a later time after making these objects writable.

  Note The previous steps are necessary only when you apply SP4 to non-writable databases or filegroups that are part of a replication topology. For more information, see Section 3.6 Apply SP4 to Read-Only Databases or Filegroups in a Replication Topology.

- Displays an Error Reporting dialog box with information about how to enable the new error-reporting feature that is included in SP4. For more information about error reporting, see Section 5.9 Error Reporting.
- Updates MDAC components if necessary. For more information, see Section 1.3 Verify the Version of Microsoft Data Access Components.
- Replaces existing SQL Server 2000 files with SP4 files.
- Runs Transact-SQL script files to update system stored procedures.
- Displays an option in the final dialog box to restart the computer if Setup determines that a restart is needed.

[Top]

##### Authentication Mode Dialog Box

The Authentication Mode dialog box does not select the current settings by default for the installation. The dialog box default setting is Windows Authentication. Use the dialog box to switch Windows Authentication or Mixed Mode Authentication by means of an sa login password that is not blank.

Note Before you change the authentication mode or the password for the sa login, make sure that this change does not affect existing applications. For example, if you change an instance of SQL Server from selection for Mixed Mode Authentication to only Windows Authentication, existing applications that attempt to connect by using SQL Server Authentication cannot connect until the authentication mode is set to Windows Authentication. Also, if you change the sa login password, applications or administrative processes that use the old password cannot connect until they are configured to use the new password.

Important For security reasons, you should never have a blank password on the sa login.

The Setup program places a record of the actions it performs in the Sqlsp.log file. This log file is stored in the Windows folder of the computer on which Setup is run. If you upgrade multiple instances, only the most recent upgrade is recorded in this log.

[Top]

##### Backward Compatibility Checklist Dialog Box

The Backward Compatibility Checklist dialog box lists issues that you may encounter when you apply the service pack to versions of SQL Server earlier than SP3. The compatibility issues that appear in the checklist vary depending on the configuration of the instance of SQL Server 2000 that is being upgraded.

The following backward compatibility issues may be addressed in this dialog box:

- When you upgrade from SP2 or earlier, SP4 upgrades Microsoft Search service and automatically rebuilds the full-text catalogs for all applications that use the service. During the rebuild, full-text functionality might not be fully available. You must select this check box before continuing Setup. For more information, see Section 5.1.4 Full-Text Catalogs Are Rebuilt After Setup Completes.
- Before you apply the service pack, you must upgrade your SQL Server 2000 master/target server configuration. The enhancements introduced with SP4 are compatible only with servers on which SQL Server 2000 SP3 or later is running. You must select this check box before continuing Setup. For more information, see Section 5.3.2 Changes to Master/Target Server Configurations.
- Cross-database ownership chaining is turned off by default in SP4. After installation, you can enable cross-database ownership chaining for individual databases. Selecting this optional check box enables you to allow cross-database ownership chaining for all databases. For more information, see Section 5.1.10 Cross-Database Ownership Chaining.

  Security Note Enabling cross-database ownership chaining for all databases is not recommended.

[Top]

## 3.3 Restart Services and Applications

When Setup completes, it may prompt you to restart the system. Section 3.1.3 Stop Services and Applications Before You Run Database Components SP4 Setup provides guidelines on when a restart is required. After the system restarts (or after Setup completes without requesting a restart), use the Services application in Control Panel to make sure that any services you stopped before you applied the service pack are now running. Services you might have stopped before you applied the service pack include DTC and the Microsoft Search, MSSQLServer, MSSQLServerOLAPService, and SQLServerAgent services or their instance-specific equivalents.

Restart the applications you closed before running the service pack Setup program.

Also, it is advisable to back up the upgraded master and msdb databases at this time.

[Top]

## 3.4 Install on a Failover Cluster

The following information applies only to SQL Server 2000 components that are part of a failover cluster.

To install the service pack on a failover cluster

1. If any resources have been added with dependencies on SQL Server resources, those dependencies must either be removed or taken offline before you install SP4. If you do not remove the dependencies, the installation of SP4 will take those resources offline.

   Note When a clustered resource is taken offline, all dependent resources are also taken offline by the cluster service.

2. Run the service pack from the node that owns the group containing the virtual server that you plan to upgrade. This installs the service pack files on all nodes in the failover cluster.
3. In the Setup dialog box, type the name of the virtual server that you plan to upgrade.
4. Keep all nodes of the cluster online during setup. This ensures that the upgrade is applied to each node of the cluster.
5. If you removed dependencies or took resources offline in Step 1, restore the dependencies or bring the resources online.

Note Setup might require restarting of the failover cluster nodes. This restart replaces the files that were in use during setup with the updated files.

If you are upgrading a default (non-clustered) instance of SQL Server to a virtual server, you must upgrade the default (non-clustered) instance to a virtual instance, and then apply SP4. For more information about upgrading, see "How to upgrade from a default instance to a default clustered instance of SQL Server 2000 (Setup)" in SQL Server 2000 Books Online.

For additional information about how to install SP4 on a failover cluster, see Knowledge Base article 811168.

If you need to rebuild a node in the failover cluster

1. Rebuild the node in the failover cluster. For more information about rebuilding a node, see "How to recover from failover cluster failure in Scenario 1" in SQL Server 2000 Books Online.
2. Run the original SQL Server 2000 Setup program to add the node back to the failover cluster.
3. Run Database Components SP4 Setup on the node you have added. This setup process updates to SP4 only the binaries on the new node.

Note If you run Setup from the node where the virtual server is running, you must reapply SP4 to all the nodes. You must also run the database upgrade scripts again.

[Top]

## 3.5 Install Database Components on Replicated Servers

The following information applies only to existing instances of SQL Server 2000 that are part of a replication topology:

- Before you upgrade to SP4, ensure that the Windows account under which the SQL Server service runs is a member of the sysadmin fixed server role. You must do this because replication distribution databases are upgraded in the context of the SQL Server service account. After you upgrade to SP4, remove the Windows account from the sysadmin role.
- You must upgrade the Distributor before you upgrade the Publisher.
- When you need to upgrade an instance of MSDE 2000 in replication topologies that are based on transactional replication with read-only Subscribers, you can upgrade the Subscriber before or after the Publisher and Distributor.
- When you need to upgrade an instance of MSDE 2000 in replication topologies that are based on merge replication or transactional replication with updating Subscribers, you must upgrade the Subscriber after the Publisher and Distributor.

  Note In many cases, especially in merge replication, the Distributor and Publisher are on the same server and are upgraded at the same time.

- If you are using merge replication and the Distributor is located on another computer or database instance (a remote Distributor), you must generate a new snapshot after you apply SP4.

[Top]

##### Installing SP4 on a Server that Acts as a Publisher and a Subscriber

You might need to quiesce the system (stop all updates) and upgrade all servers simultaneously in the following cases.

- For replication topologies based on merge replication
- For replication topologies based on transactional replication, which updates Subscribers, including one or more servers that act as both a Subscriber and a Publisher (or Distributor).

###### Example 1: Topology That Requires Simultaneous Upgrades

The following table includes servers that both publish and subscribe to publications that allow updates at the Subscriber. As noted earlier, you must follow the upgrade order Distributor, Publisher, Subscriber for topologies that allow updates at the Subscriber. This order requires that you upgrade Server A first for the merge publication and Server B first for the transactional publication with updating Subscribers. In this case, you must quiesce the system and upgrade the servers simultaneously.

Server A | Server B
--- | ---
Publisher/Distributor for merge replication | Subscriber for merge replication
Subscriber for transactional replication with updating | Publisher/Distributor for transactional replication with updating

###### Example 2: Topology That Allows Sequential Upgrades

In this example, you can upgrade Server A first because the read-only transactional publication allows a Subscriber to be upgraded before the Publisher/Distributor.

Server A | Server B
--- | ---
Publisher/Distributor for merge replication | Subscriber for merge replication
Subscriber for read-only transactional replication | Publisher/Distributor for read-only transactional replication

[Top]

## 3.6 Apply SP4 to Read-Only Databases or Filegroups in a Replication Topology

The following information applies only to SQL Server 2000 components that are part of a replication topology.

When non-writable databases or filegroups exist, Setup displays the following message:

    Setup has detected one or more databases and filegroups which are not writable.

In general, you can ignore this warning and setup will continue. However, if any of the non-writable databases listed in the Setup log are members of a replication topology, you must make those databases writable and reapply SP4 Setup to that instance of SQL Server 2000.

Note This message does not affect unattended installations. For more information about unattended installations, see Section 4.1 Unattended Installations.

During installation, Setup makes no distinction between non-writable databases and databases that are offline or in a suspect state. If a database or filegroup in a replication topology is non-writable during setup, you must reapply the service pack to upgrade this database. For more information about how to bring a database online, see the topic "Attaching and Detaching a Database" in SQL Server 2000 Books Online. For more information about diagnosing suspect databases, see the topic "Server and Database Troubleshooting" in SQL Server Books Online.

To apply Database Components SP4 to a read-only database

1. Make the read-only database writable by using the `ALTER DATABASE` statement, as follows:

       ALTER DATABASE database SET READ_WRITE

2. Repeat Step 1 for all read-only databases.
3. Apply (or reapply) SP4.
4. If required, make the database read-only again by using `ALTER DATABASE`, as follows:

       ALTER DATABASE database SET READ_ONLY

To apply SP4 to a read-only filegroup

1. Make the read-only filegroup writable by using `ALTER DATABASE`, as follows:

       ALTER DATABASE Database
       MODIFY FILEGROUP filegroup_name READWRITE

2. Repeat Step 1 for all read-only filegroups.
3. Apply (or reapply) the service pack.
4. Make the filegroup read-only again by using `ALTER DATABASE`, as follows:

       ALTER DATABASE Database
       MODIFY FILEGROUP filegroup_name READONLY

For more information about ALTER DATABASE, see the "ALTER DATABASE" reference topic in SQL Server 2000 Books Online. For more information about reapplying SP4, see Section 3.9 Reapply Database Components SP4.

[Top]

## 3.7 Upgrade the Catalog of Linked Servers

When you upgrade an instance of the SQL Server 2000 database engine to Database Components SP4, you might need to ensure that some of the system stored procedures are updated in other instances of SQL Server or MSDE.

Database Components SP4 includes an upgrade of the Microsoft Data Access Components (MDAC) to MDAC version 2.8 SP1. MDAC 2.8 SP1 includes updates to the SQLOLEDB provider and the SQL Server ODBC driver. For more information, see Section 1.3 Verify the Version of Microsoft Data Access Components. When either the provider or driver connects to an instance of SQL Server or MSDE, the provider or driver uses a set of system stored procedures known as the catalog stored procedures. The versions of the catalog stored procedures on the instance must be the same version or later than the versions that the provider and driver use. If you attempt to connect to an instance of SQL Server or MSDE that have earlier versions of the catalog stored procedures you will receive the following error:

`The ODBC catalog stored procedures installed on server <ServerName>
are version <OldVersionNumber>; version <NewVersionNumber> or later
is required to ensure proper operation. Please contact your system
administrator.`

[Top]

##### Run the Instcat.sql Script

Each version of the provider and driver is shipped with a script named Instcat.sql. Instcat.upgrades the catalog stored procedures in any instance of SQL Server or MSDE that has an earlier version of the catalog.

After installing Database Components SP4, you must run the Instcat.sql script from Database Components SP4 against any instance of SQL Server or MSDE that is an earlier version than SQL Server 2000 SP4 and has the following characteristics:

- Is accessed by applications that use the SQLClient managed namespace, ADO, OLE DB, or ODBC and run on the computer on which SQL Server 2000 SP4 is installed.
- Is referenced by distributed queries that run on an instance of SQL Server 2000 SP4. Distributed queries include references to linked server entries that you define by using sp_addlinkedserver and the OPENROWSET and OPENQUERY functions.

To upgrade the catalog stored procedures on an instance using Windows Authentication Mode:

1. Log on to Windows using a login that is a member of the SQL Server sysadmin fixed server role.
2. Open a command prompt window.
3. Run the osql utility:
   - For a default instance, execute:

         osql -E -SComputerName -ilocation\instcat.sql

   - For a named instance, execute:

         osql -E -SComputerName\InstanceName -ilocation\instcat.sql

To upgrade the catalog stored procedures on an instance using Mixed Mode:

1. Log on to Windows using any login.
2. Open a command prompt window.
3. Run the osql utility:
   - For a default instance, execute:

         osql -UAnAdminLogin -PAdminPassword -SComputerName
         -ilocation\instcat.sql

   - For a named instance, execute:

         osql -UAnAdminLogin -PAdminPassword
         -SComputerName\InstanceName -ilocation\instcat.sql

where:

- AnAdminLogin is a SQL Server login that is a member of the sysadmin fixed server role.
- AdminPassword is the password for AnAdminLogin.
- ComputerName is the name of the computer on which the instance of SQL Server or MSDE is running.
- InstanceName is the name of a named instance of SQL Server 2000 or MSDE 2000.
- location is the full path to the folder containing Instcat.sql. The default location for an installed instance of SQL Server 2000 is c:\program files\Microsoft SQL Server\MSSQL\Install.

The Instcat.sql script generates many messages. Typically the messages do not indicate any errors; they just inform you how many rows were affected by each Transact-SQL statement in the script. The last message should indicate whether the script ran successfully.

[Top]

## 3.8 Uninstall Database Components SP4

To remove Database Components SP4, follow the steps in this section.

Note MDAC updates are not uninstalled. For more information, see Section 1.3 Verify the Version of Microsoft Data Access Components.

To be able to return to pre-SP4 versions of SQL Server 2000 components, you must back up the master, msdb, and model databases before you install SP4. For more information, see Section 3.1.1 Back Up Your SQL Server Databases.

If any of the databases are involved in replication, you must disable publishing.

To disable publishing:

1. In SQL Server Enterprise Manager, expand a SQL Server group, expand a server, right-click the Replication folder, and then click Configure Publishing, Subscribers, and Distribution.
2. Click the Publication Databases tab.
3. Clear the check box for each database that is involved in replication. This allows the databases to be detached.

To revert to a pre-SP4 version of SQL Server

1. Detach all user databases. For more information, see "How to attach and detach a database (Enterprise Manager)" in SQL Server Books Online.
2. Uninstall SQL Server. In Control Panel, double-click Add/Remove Programs, select the instance of SQL Server that you want to uninstall, and click Remove.
3. Reinstall SQL Server 2000 from the CD-ROM or from the location where you originally installed SQL Server.
4. Apply any service packs and hotfixes that were installed before Database Components SP4.
5. Restore the databases master, msdb, and model from the last backup that was created before you installed. If the location of the data files has not changed, this restoration automatically attaches any user databases that were attached at the time the backup was created.
6. Attach any user databases that were created after the last backup of the master database.
7. Configure replication if necessary.

   Warning When you revert to the pre-SP4 version of SQL Server 2000, all changes made to the databases master, msdb, and model since applying SP4 are lost.

[Top]

## 3.9 Reapply Database Components SP4

The following information applies to all components.

In the following cases, you must reapply SP4:

- If new SQL Server 2000 components are added after SP4 is applied.
- If databases or filegroups that are part of a replication topology and were read-only during the initial application of SP4 are made writable.

To reapply SP4, follow the steps in Section 3.0 Service Pack Installation.

[Top]

# 4.0 Additional Installation Information

This section documents additional service pack installation considerations that apply only in special cases.

[Top]

## 4.1 Unattended Installations

Database Components SP4 no longer includes pre-defined setup initialization (.iss) files. However, each time you run an attended installation of Database Components SP4, the setup options are written to the setup.iss file, which is located in the system folder. This .iss file can be used later to run an unattended installation of Database Components SP4. For more information about performing unattended installations, see the topic "Performing an Unattended Installation" in SQL Server 2000 Books Online.

[Top]

##### Unattended Installation Considerations

The following considerations relate to unattended installations:

- You must generate a separate setup.iss file for each combination of system configuration and installation settings on which you intend to run an unattended installation. To change the setup behavior, you must modify the setup.iss file. For more information, see the topic "Creating a Setup File Manually" in SQL Server 2000 Books Online.
- Each time you run an attended installation of Database Components SP4 the setup.iss file is overwritten. When you run an attended installation of Analysis Services SP4 from the command prompt and elect to generate the Setup.iss file, by default this file is also created in the system folder and may overwrite the existing .iss file. For more information, see Section 4.1 Unattended Installations in the Analysis Services SP4 readme file.
- When installing Database Components SP4 in a Terminal Services environment, the setup.iss file is created in the temp directory for the logged-in user.
- Run unattended installations using Windows Authentication whenever possible.
- If you must run an unattended setup using SQL Server Authentication, do not include a password for the sa login in the .iss file or batch file because these files are not encrypted. Instead, supply the password for the sa login in the command line by using the sapwd parameter as follows:

      start /wait setupsql.exe -s -sms -f1 C:\Windows\setup.iss -sapwd password

  Security Note When possible, supply security credentials at run time. If you store credentials in a script file, you must secure the file to prevent unauthorized access.

- If the SQL Server instance to be upgraded to SP4 has a blank password for the sa login account, a password must be provided before an unattended installation is run. You change the password from blank by running sp_password. For more information, see the topic "sp_password" in SQL Server 2000 Books Online.

  Security Note The use of blank passwords is strongly discouraged because blank passwords add a significant vulnerability to security breaches.

- When you run an unattended installation of SP4 by using a manually created .iss file or an .iss file generated prior to SP3, you must manually set the following setup switches that correspond to the Backward Compatibility Checklist and Error Reporting dialog boxes. Otherwise, the unattended installation fails.

Unattended setup switch | Description
--- | ---
UpgradeMSSearch | This switch is required to address the required rebuild of full-text catalogs. If Full Text Search is enabled, you must set this switch to 1. For more information, see Section 5.1.4 Full-Text Catalogs are Rebuilt After Setup Completes.
MSXTSXUpgraded | This switch is required to address the issue of upgrading master/target server configurations. If you are applying SP4 to a master or target server, you must set this switch to 1. For more information, see Section 5.3.2 Changes to Master/Target Server Configurations.
EnableCrossDBChaining | (Optional) This switch is used to enable cross-database ownership chaining. To enable cross-database ownership chaining, set this switch to 1. For more information, see Section 5.1.10 Cross-Database Ownership Chaining.
EnableErrorReporting | (Optional) This switch is used to enable Error Reporting. To enable error reporting, set this switch to 1. For more information, see Section 5.9 Error Reporting.

- During an unattended installation, if Setup detects any non-writable databases or filegroups, that information is written to the Setup log and the unattended installation continues. If any of the non-writable databases that are listed in the Setup log are members of a replication topology, you must make those databases writable and reapply Database Components SP4 Setup to that instance of SQL Server 2000. For more information about making a database writable, see Section 3.6 Applying SP4 to Read-Only Databases or Filegroups. For more information about reapplying SP4, see Section 3.11 Reapplying SP4.

[Top]

## 4.2 Redistributing SP4 Database Components

Database Components SP4 includes the self-extracting file Sqlredis.exe. When Sqlredis.exe runs, it:

1. Executes the file Mdac_typ.exe from Microsoft Data Access Components (MDAC) 2.8 Service Pack 1. This installs the MDAC 2.8 SP1 core components (if the same or newer version is not detected) and the versions of the SQL Server and Desktop Engine client connectivity components that are included with SP4. For more information, see 1.3 Verify the Version of Microsoft Data Access Components.
2. Installs Microsoft Jet ODBC drivers and connectivity components.

You can redistribute the Sqlredis.exe file under the terms and conditions noted in the Redist.txt file that accompanies SP4.

[Top]

## 4.3 Systems Management Server Distributed Installation

You cannot install Database Components SP4 from a remote location. However, you can use Microsoft Systems Management Server to install SP4 automatically on multiple computers that are running on Windows Server 2003, Windows XP, or Windows 2000. To do so, you must use a package definition file (Smssql2ksp4.pdf) that automates the creation of a SQL Server package in Systems Management Server. The SQL Server package can then be distributed and installed on computers that are running Systems Management Server. Sms2kdef.bat is a batch file that starts an unattended setup by using Systems Management Server. In this type of installation, the Setup program automatically detects relevant system information that it needs. No user input is required.

[Top]

# 5.0 Documentation Notes

This section covers issues that can occur after applying Database Components SP4 and new features that are available when you run SP4. These issues apply when running the service pack to upgrade from any earlier version of SQL Server 2000. This section does not describe all of the fixes provided in SP4. For a complete list of these fixes, see Microsoft Knowledge Base article 888799.

Any information relevant to SQL Server 2000 Service Pack 4 that was not available in time to be included in this Readme file will be published in Microsoft Knowledge Base article 884525.

[Top]

## 5.1 Database Enhancements

The following enhancements apply to instances of SQL Server 2000 on which Database Components SP4 is installed.

[Top]

### 5.1.1 Hash Teams Removed

Introduced in SP1

Hash teams have been removed. Because of certain enhancements to SQL Server 2000, hash teams no longer produce the performance benefits they offered in SQL Server 7.0. In addition, removing hash teams makes SQL Server 2000 more stable.

Therefore, the query optimizer no longer generates query plans using hash teams.

In rare cases, the removal of hash teams can cause a query to be processed more slowly. Analyze such queries to see whether creating more suitable indexes will return query performance to its previous level.

[Top]

### 5.1.2 Affinity Mask Switches Added

Introduced in SP1

Two affinity mask switches have been added to this service pack.

##### Affinity Mask I/O Switch

With this service pack, you can specify which CPUs are used to run threads for disk I/O operations. This switch must be used in conjunction with the affinity mask option. For more information, see article 298402.

##### Affinity Mask Connection Switch

With this service pack, you can configure systems that are enabled for Virtual Interface Architecture (VIA) to bind the SQL Server connections from certain network cards to a processor or a set of processors. This switch must be used in conjunction with the affinity mask option. For more information, see article 299641.

[Top]

### 5.1.3 Filtered Indexed View

Introduced in SP2

If you have encountered SQL Server 2000 bug 355069 as documented in Microsoft Knowledge Base Article 306467, this service pack will only prevent future occurrences of unexpected results because of data modifications. In addition to applying this fix, all indexes that are based on views with filter conditions must be re-created.

[Top]

### 5.1.4 Full-Text Catalogs Are Rebuilt After Setup Completes

Introduced in SP3

All full-text catalogs are rebuilt as part of the installation of SP4 when upgrading from SP2 or earlier. The rebuild is automatic and resource-intensive. Queries against full-text catalogs may return partial results or no results until the rebuild process is complete. After SP4 is installed, the system event logs contain messages stating that the catalogs were corrupt, of an older version, and had to be rebuilt.

For information , see the Knowledge Base article 327217, which also discusses possible workarounds for keeping full-text search available during the rebuild process and to avoid an automatic rebuild.

[Top]

### 5.1.5 Syntax Changes for sp_change_users_login

Introduced in SP3

When you run sp_change_users_login with the @Action=Auto_Fix argument, you must now specify a password. sp_change_users_login assigns the password to any new login it creates for the user. The following example shows the new @Password argument:

    sp_change_users_login [ @Action = ] 'action'
        [ , [ @UserNamePattern = ] 'user' ]
        [ , [ @LoginName = ] 'login' ]
        [ , [ @Password = ] 'password' ]

Use the @Password argument only with @Action=Auto_Fix. The following example shows the new syntax for the sp_change_users_login command when using Auto_Fix. Other examples in SQL Server Books Online remain unchanged.

    USE pubs
    go
    EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'
    Go

[Top]

### 5.1.6 Ad Hoc Access to OLE DB Providers Disabled by Default

Introduced in SP3

If the DisallowAdhocAccess registry option is not explicitly set, by default, ad hoc access to OLE DB providers is not allowed. This means ad hoc query syntax, such as OPENDATASOURCE and OPENROWSET, will not work against remote servers. To allow ad hoc access, you must explicitly set the DisallowAdhocAccess option to 0.

[Top]

### 5.1.7 New SqlServerLike Provider Option

Introduced in SP3

To enable more efficient processing of remote queries that include LIKE predicates, the SqlServerLike option was added in SP3. SQL Server 2000 SP3 or later now has two options for sending LIKE operations to linked servers. If the OLE DB provider for a linked server supports the SQL Server syntax for the LIKE operator and wildcards, you can specify the SqlServerLIKE option to have SQL Server send LIKE operations using SQL Server syntax. If the OLE DB provider for a linked server reports that it supports the Entry Level ANSI/ISO SQL-92 syntax or returns the SQLPROP_ANSILIKE property, SQL Server will send LIKE operations to the linked server using SQL-92 syntax. For more information on SQLPROP_ANSILIKE, see the topic "Programming the SQLPROPSET_OPTHINTS Property Set" in SQL Server 2000 Books Online.

You must add a registry key value to enable the SqlServerLIKE option for an OLE DB provider.

Security Note Incorrectly editing the registry can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from editing the registry incorrectly can be resolved. Before editing the registry, back up any valuable data.

1. Open Regedit32.
2. Locate the proper registry key:
   - For named instances, locate this key:

     HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<Instance Name>\Providers\<Provider Name>

   - For default instances, locate this key:

     HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Providers\<Provider Name>

3. In the <Provider Name> key, add a DWORD value named SqlServerLIKE and set its value to 1.

[Top]

### 5.1.8 Expanded Error Messages for Distributed Queries

Introduced in SP3

For distributed queries, SQL Server returns provider error information in addition to server error information. When a query between linked servers results in an error, SQL Server checks to see if the provider supports the IErrorRecords OLE DB interface. If this interface is supported, SQL Server calls the GetErrorInfo function to get additional error information from the provider and returns this information to the user as part of the error message. If the IErrorRecords interface is not supported, there is no change in SQL Server behavior: SQL Server returns a generic error.

For example, run the following query against a server that uses MSDASQL, which does not support sql_variant:

    SELECT * FROM remote2k.dqtable.dbo.sqlvariantnotnull
    --Remote2k is a loopback server.

Prior to SP3, SQL Server returned the following error message:

`Server: Msg 7356, Level 16, State 1, Line 1`

`OLE DB provider 'msdasql' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.`

After you apply SP3 or later, SQL Server returns the following error message:

`Server: Msg 7356, Level 16, State 1, Line 1`

`OLE DB provider 'msdasql' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.`

`OLE DB error trace [Non-interface error: Column 'sql_variant' (compile-time
ordinal 3) of object '"dqtable"."dbo"."sqlvariantnotnull"' was reported
to have a DBCOLUMNFLAGS_ISFIXEDLENGTH of 16 at compile time and 0 at run time].`

[Top]

### 5.1.9 New Function fn_get_sql Returns SQL Statement

Introduced in SP3

SP3 and later includes the new function fn_get_sql that returns the text of the SQL statement for the specified SQL handle. In addition, to support this function, three new columns have been added to the sysprocesses system table: sql_handle, stmt_start, and stmt_end.

fn_get_sql is documented in the latest copy of the SQL Server 2000 Books Online. For information about installing the latest version of the SQL Server 2000 Books Online, see section 1.6 Updates to SQL Server 2000 Books Online Are Available. This is an English-language copy of the reference topic for fn_get_sql.

[Top]

### 5.1.10 Cross-Database Ownership Chaining

Introduced in SP3

This service pack provides new options for turning cross-database ownership chaining on and off.

When installing Database Components SP4, the Setup Backward Compatibility Checklist dialog displays an option for configuring cross-database ownership chaining. By default, Setup turns off cross-database ownership chaining for all user databases. You can enable cross-database ownership chaining for all databases. For more information, see Backward Compatibility Checklist dialog box

Note Enabling cross-database ownership chaining for all databases is not recommended.

After installation, you can use the following methods to turn cross-database ownership chaining on and off for all databases in the instance:

- Use the new Cross DB Ownership Chaining argument of the sp_configure system stored procedure.
- Use the Allow Cross-Database Ownership Chaining option on the Security tab of the SQL Server Properties dialog box in Enterprise Manager.
- Use the SQLServer.Configuration.ConfigValues Database Management Objects (DMO) collection. When the SQLServer object references an instance of SQL Server 2000 SP3 or later, this collection contains a ConfigValue object named Cross DB Ownership Chaining.

If cross-database ownership chaining is turned off for the instance, you can configure it for individual databases. Use the following methods to turn cross-database ownership chaining on and off for a database:

- Use the new db chaining option of the sp_dboption system stored procedure.
- Use the Allow Cross-Database Ownership Chaining option on the Options tab of the Database Properties dialog box in Enterprise Manager.
- Use the DBChaining property of the DBOption2 DMO object.

For more information, click the Help button on the Backwards Compatibility Checklist page when you run Setup, download the updated edition of SQL Server 2000 Books Online, or see Knowledge Base article 810474.

[Top]

### 5.1.11 Enhancement for Trace Flag 1204

Introduced in SP3

Trace flag 1204 returns the type of locks participating in the deadlock and the current command affected. In SP3 and later, when this trace flag is on, the deadlock information is automatically written to the error log.

[Top]

### 5.1.12 Permissions Change for sp_changedbowner

Introduced in SP3

Only members of the sysadmin fixed server role can run the sp_changedbowner system stored procedure.

[Top]

### 5.1.13 Debugging Functionality Changes

Introduced in SP3

The functionality for debugging stored procedures with Microsoft Visual Studio® 6.0 and older or with SQL Server Query Analyzer prior to SP3 is turned off by default. Application debugging (stopping at a SQL Server Transact-SQL breakpoint while debugging a client application) is also turned off by default. To enable debugging functionality, run sp_sdidebug, passing the parameter legacy_on. To disable debugging, pass legacy_off to this procedure.

Note Running the sp_sdidebug stored procedure on production servers is not recommended.

For more information, see Microsoft Knowledge Base article 328151.

Note Books Online refers to the client-side debugging component sqldbreg.exe. In SP3, this component file was renamed to sqldbreg2.exe.

[Top]

### 5.1.14 Named Pipes Cannot Be Disabled on Clustered Servers

Introduced in SP3

After applying the service pack, you are no longer able to disable the Named Pipes protocol on instances of the database engine participating in a failover cluster.

[Top]

### 5.1.15 Operations On UDP Port 1434

Introduced in SP3a

Starting with SQL Server 2000 SP3a, instances of the SQL Server 2000 database engine and MSDE 2000 that are not configured to support network communications will stop using User Datagram Protocol (UDP) port 1434. Instances that are configured to support network communications will use UDP 1434.

An instance upgraded to SP3a or later will stop using UDP 1434 whenever all of the server Net-Libraries for the instance, except the shared memory Net-Library, are disabled. The instance will start using port 1434 whenever you enable any of the server Net-Libraries. For information on disabling or enabling server Net-Libraries, see the topic "SQL Server Network Utility" in SQL Server 2000 Books Online.

The use of UDP port 1434 by a computer will not stop until all instances of SQL Server 2000 and MSDE 2000 on the computer have been upgraded to SP3a or later and configured to not support network communications.

Whether UDP port 1434 is open or closed does not depend on the state of the shared memory Net-Library. The shared memory Net-Library is used only for local connections, and does not use a network. The shared memory Net-Library is always active; it cannot be enabled or disabled.

You cannot disable all of the server Net-Libraries when installing or upgrading instances of the SQL Server 2000 database engine.

[Top]

### 5.1.16 Change to Maximum Network Packet Size

Introduced in SP4

In SP4, the maximum value for the network packet size option (set using sp_configure) is 32767. This is slightly less than half the previous maximum of 65536. During upgrade, existing values larger than 32767 will automatically be adjusted to 32767. If a script attempts to use sp_configure to set a value larger than 32767 but less than or equal to 65536, the value will also be set to 32767. Setting the network packet size to a value larger than 65536 results in an error.

[Top]

### 5.1.17 Optimization of Queries with Large IN Lists or Many OR Clauses

Introduced in SP4

SP4 includes a change in the behavior of the SQL Server optimizer that affects queries containing predicates with large IN lists or many OR clauses. More specifically, this change (introduced in SQL Server 2000 hotfix 789) affects queries that contain (or that can be rewritten using an equivalent expression that contains) the following:

- More than 10,000 elements in the IN list
- Two IN lists, each containing over 100 elements
- Over 10,000 disjuncts in the OR clause
- A combination of OR clauses and IN lists such that their equivalent expression will contain more than 10,000 disjuncts

Because of this change, SQL Server uses less memory when compiling these types of statements and thereby avoids out-of-memory errors. In rare cases when these types of queries are run on systems that have very large memory and a low degree of parallelism, a query plan with inferior performance may be chosen by the optimizer. To override the change in optimizer behavior, trace flag 9060 is provided in this service pack. By default, trace flag 9060 is OFF. When the trace flag is ON, SP3 behavior prior to hotfix 789 is enabled. If error 701 (insufficient system memory) is encountered when the trace flag is ON, consider rewriting the queries using temporary tables or table variables for the values in the IN lists. For numerical ranges, use BETWEEN clauses or greater than (>) or less than (<) operators. For information on using trace flags, see "Trace Flags" in SQL Server Books Online.

[Top]

### 5.1.18 Future Network Protocol Support

Introduced in SP4

Banyan VINES, Multiprotocol, AppleTalk, and NWLink IPX/SPX network protocols are supported in SP4. However, these protocols will not be supported in SQL Server 2005 and later releases. Please plan accordingly.

[Top]

### 5.1.19 Monitoring an Instance of SQL Server Running on Windows-on-Windows 64 Mode

Introduced in SP4

When you run in Windows-on-Windows 64 (WOW) mode on Windows Server 2003 x64 SP1 or later, the default 64-bit version of Windows Performance Monitor cannot be used to access the SQL Server Performance Counters that are used to monitor an instance of SQL Server 2000 SP4. Instead, you must use the 32-bit version of Windows Performance Monitor. The 32-bit version is located at:

%systemdrive%\WINDOWS\SysWOW64\perfmon.exe

In WOW mode, SQL Server Performance Counters can be viewed only when the 32-bit version of Performance Monitor is running on the same computer as the instance of SQL Server 2000 SP4.

This restriction does not apply to Windows Server 2003 for 64-Bit Itanium-based Systems.

[Top]

## 5.2 Replication Enhancements

This section discusses enhancements to SQL Server 2000 replication that are included with SP4.

[Top]

### 5.2.1 Transactional Replication UPDATE Custom Stored Procedure

Introduced in SP1

During transactional replication setup, custom stored procedures for insert, delete, and update actions are created in the subscription database. Regardless of how many columns are affected by an UPDATE statement, the update custom stored procedure updates all of the columns in the subscription table. Any column that has not changed is reset to the same values that existed before the update. Typically, this action causes no problems. However, if any of these columns are indexed, this resetting can become expensive.

If you use transactional replication and have several indexes on the subscription table, and only a few column values change because of updates, the overhead of maintaining the index can limit performance when changes are applied at the Subscriber. For example, a subscription database that is used for reporting purposes may have many more indexes than the publication database. Dynamically building the UPDATE statement at run time can improve performance. The update includes only the columns that have changed, creating an optimal UPDATE string.

This service pack includes a new stored procedure, sp_scriptdynamicupdproc, which generates a custom stored procedure that you can use at the Subscriber to dynamically build the UPDATE statement at run time. However, building the dynamic UPDATE statement at run time requires extra processing.

sp_scriptdynamicupdproc is documented in the latest copy of the SQL Server 2000 Books Online. For information about installing the latest version of the SQL Server 2000 Books Online, see section 1.6 Updates to SQL Server 2000 Books Online Are Available. This is an English-language copy of the reference topic for sp_scriptdynamicupdproc.

[Top]

### 5.2.2 Transactional Replication UPDATE Statements on Unique Columns

Introduced in SP1

In transactional replication, UPDATE statements usually are replicated as updates. But if the update changes any column that is part of a unique index, a clustered index, or an expression that is used as a unique constraint, the update is performed as a DELETE statement followed by an INSERT statement at the Subscriber. This is done because this type of update can affect multiple rows and a uniqueness violation can occur if updates are delivered row by row.

If the update affects only one row, there is no chance for a uniqueness violation. Therefore, trace flag 8207 has been added to this service pack to allow updates to any unique column that affect only one row to be replicated as UPDATE statements. This optimization has been added specifically for applications that install user-defined UPDATE triggers at the Subscriber and require these triggers to fire for updates that affect only one row on a unique column.

To use trace flag 8207, turn it on from the command prompt (sqlservr.exe -T8207) or at run time using DBCC TRACEON(8207, -1) before the Log Reader Agent is started.

Important Typically, trace flag 8207 is used with read-only transactional replication. Do not use this trace flag with updatable subscriptions if the primary key UPDATE can occur at the Subscriber.

[Top]

### 5.2.3 Restrictions Removed from Concurrent Snapshot Processing

Introduced in SP1

In SQL Server 2000, concurrent snapshot processing was not recommended if the publishing table had a unique index that was not the primary key or the clustering key. If data modifications were made to the clustering key while a concurrent snapshot was being generated, replication could fail with a duplicate key error when applying the concurrent snapshot to a Subscriber. With this service pack, restrictions on using concurrent snapshot processing are removed.

[Top]

### 5.2.4 Transactional Replication Scripting Custom Stored Procedures

Introduced in SP1

When setting up nosync subscriptions (that is, subscriptions that do not receive the initial snapshot), the custom stored procedures for INSERT, UPDATE, and DELETE statements must be created manually. Typically, these statements are created at the Subscriber when the initial snapshot is delivered. A new stored procedure, sp_scriptpublicationcustomprocs, has been added to generate scripts for the custom stored procedures at the publication level. This new functionality may make it easier to set up nosync subscriptions.

sp_scriptpublicationcustomprocs is documented in the latest copy of the SQL Server 2000 Books Online. For information about installing the latest version of the SQL Server 2000 Books Online, see section 1.6 Updates to SQL Server 2000 Books Online Are Available. This is an English-language copy of the reference topic for sp_scriptpublicationcustomprocs.

[Top]

### 5.2.5 Merge Replication Retention-Based Meta Data Clean Up

Introduced in SP1

When merge replication system tables contain large amounts of meta data, cleaning up the meta data improves performance. Prior to SQL Server 2000 SP1, meta data could be cleaned up only by running sp_mergecleanupmetadata. However, SQL Server 2000 SP1 and later versions include retention-based meta data clean up, which means that meta data can be automatically deleted from the following system tables:

- MSmerge_contents
- MSmerge_tombstone
- MSmerge_genhistory
- Any before image tables, if present

Note Before image tables are present if the @keep_partition_changes synchronization optimization option is enabled on the publication.

Retention-based meta data clean up occurs as follows:

- If the –MetadataRetentionCleanup Merge Agent parameter is set to 1, as it is by default, the Merge Agent cleans up the Subscriber and the Publisher that are involved in the merge.
- If the -MetadataRetentionCleanup parameter is set to 0, automatic clean up does not occur. In this case, manually initiate retention-based meta data clean up by executing sp_mergemetadataretentioncleanup. This stored procedure must be executed at every Publisher and Subscriber that should be cleaned up. It is recommended, but not required, that the Publisher and Subscribers be cleaned up at similar points in time (see Preventing False Conflicts later in this section).

  Note -MetadataRetentionCleanup parameter is set to 1 for all Merge Agent profiles that are included with SQL Server 2000 SP1 and later versions. If you upgrade a server to SP1 or later and then add merge replication, the Merge Agent profile is automatically updated to include this parameter. If you upgrade a server that already has merge replication enabled to SP1 or later, the Merge Agent profile is not automatically updated; update the profile by running sp_add_agent_parameter (see Additional Parameter for sp_add_agent_parameter later in this section).

  Important The default retention period for publications is 14 days. If an article belongs to several publications, there might be different retention periods. In that situation, the longest retention period is used to determine the earliest possible time that clean up can occur. If there are multiple publications on a database, and if any one of those publications uses an infinite publication retention period (@retention=0), merge meta data for the database is not automatically cleaned up. For this reason, use infinite publication retention with caution.

##### Additional Parameter for sp_add_agent_parameter

The system stored procedure sp_add_agent_parameter now has a MetadataRetentionCleanup parameter, which allows you to add or remove meta data retention clean up from Merge Agent profiles. A value of 1 indicates that the profile should include clean up; a value of 0 indicates that it should not include clean up. For example, to add meta data retention clean up to a profile, execute the following code:

    EXEC sp_add_agent_parameter @profile_id=<my_profile_id>,
      @parameter_name='MetadataRetentionCleanup', @parameter_value=1

##### Meta Data Cleanup in Topologies with Different Versions of SQL Server

For automatic retention-based clean up to occur in a database involved in merge replication, the database and the Merge Agent must both be on servers running SQL Server 2000 SP1 or later. For example:

- A SQL Server 7.0 pull Subscriber does not run clean up at a SQL Server 2000 SP1 Publisher.
- A SQL Server 2000 SP1 push Merge Agent does not run clean up in a pre-SP1 SQL Server 2000 Subscriber database.
- A SQL Server 2000 SP1 push Merge Agent runs clean up in a SQL Server 2000 SP1 Publisher database, even if it has subscribers that are running SQL Server 2000 or earlier.

Automatic clean up on some servers and not on others will at most cause false conflicts, and those should be rare. For topologies that include versions of SQL Server prior to SQL Server 2000 SP1, you may see performance benefits by running sp_mergemetadatacleanup on all servers that are not cleaned up automatically.

##### Preventing False Conflicts

Retention-based meta data clean up prevents non-convergence and silent overwrites of changes at other nodes. However, false conflicts can occur if the following conditions are met:

- The meta data is cleaned up at one node and not another.
- A subsequent update at the cleaned-up node occurs on a row whose meta data was deleted.

For example, if meta data is cleaned up at the Publisher but not at the Subscriber, and an update is made at the Publisher, a conflict occurs even though the data appears to be synchronized.

To prevent this conflict, make sure that meta data is cleaned up at related nodes at about the same time. If -MetadataRetentionCleanup is set to 1, both the Publisher and Subscriber are cleaned up automatically before the merge starts, thereby ensuring that the nodes are cleaned up at the same time. If a conflict occurs, use the merge replication conflict viewer to review the conflict and change the outcome if necessary.

If an article belongs to several publications or is in a republishing scenario, it is possible that the retention periods for a given row at the Publisher and Subscriber are different. To reduce the chance of cleaning up meta data on one side but not the other, it is recommended that those different publications have similar retention periods.

Note If the system tables contain large amounts of meta data that must be cleaned up, the merge process may take longer to run. Clean up the meta data on a regular basis to prevent this issue.

[Top]

### 5.2.6 Backup and Restore Issues for Merge Replication

Introduced in SP1

A publication database that is restored from a backup should first synchronize with a subscription database that has a global subscription (that is, a subscription having an assigned priority value) to guarantee correct convergence behavior. Synchronization ensures that the changes that were lost at the publication database because of the restore operation are reapplied accurately.

Do not synchronize the publication database with a subscription database that has an anonymous subscription. Because anonymous subscriptions do not have enough meta data to apply changes to the publication database, such synchronization can lead to the non-convergence of data.

When you are planning back up and restore operations for merge replication, consider the following additional issues:

- Restore the subscription databases from backups that are not beyond the retention period.

Restore a subscription database from a backup only if the backup is no older than the shortest retention period of all publications to which the Subscriber subscribes. For example, if a Subscriber subscribes to three publications with retention periods of 10, 20, and 30 days, respectively, the backup used to restore the database should not be more than 10 days old.

- Synchronize before generating a backup.

It is strongly recommended that a Subscriber synchronize with the Publisher before you perform a backup. Otherwise, the system might not converge correctly if the Subscriber is restored from this backup. Although the backup file itself might be new, the last synchronization with a Publisher could be almost as old as the retention period. For example, consider a publication with a retention period of 10 days. The last synchronization was 8 days ago, and now the backup is performed. If the backup is applied 4 days later, the last synchronization will have occurred 12 days ago, which is past the retention period. If the Subscriber had synchronized right before the backup, the subscription database would be within the retention period.

- Reinitialize the Subscriber if you change the publication retention value.

If you need to change the publication retention value, manually reinitialize the Subscriber to avoid the non-convergence of data. The retention-based meta data clean up feature deletes outdated meta data from merge system tables when the publication retention period is reached.

The publication retention value is used to determine when subscriptions that have not synchronized within the retention period should expire. If, after a clean up, the publication retention period is increased and a subscription attempts to merge with the Publisher (which has already deleted the meta data), the subscription will not expire because of the increased retention value. Furthermore, the Publisher does not have enough meta data to download changes to the Subscriber, which leads to non-convergence.

- Use the same publication retention values for all Publishers and their alternate synchronization partners. Using different values can lead to non-convergence.

[Top]

### 5.2.7 Restoring Replicated Databases from Different Versions of SQL Server

Introduced in SP1

Restoring a backup to the same server and database—running the same version as the server from which the backup was created—preserves your replication settings. If you are restoring a replicated database to a version of SQL Server that is different from the version used to back up the database, consider the following issues:

- If you are restoring the database to Database Components SP4 from a backup created with SQL Server 2000 and you want to preserve replication settings, you must run sp_vupgrade_replication. Running sp_vupgrade_replication ensures that the replication meta data is upgraded. If you do not run sp_vupgrade_replication, the replication meta data can be left in an unpredictable state.
- If you are restoring the database to SQL Server 2000 from a backup created with any version of SQL Server 7.0 (the release version or any service pack) and you want to preserve replication settings, you must re-create the backup before installing service packs. It is possible to restore the database to Database Components SP4 directly from a backup of a replicated database created in SQL Server 7.0, but replication settings are not maintained.

[Top]

### 5.2.8 New -MaxCmdsInTran Parameter for Log Reader Agent

Introduced in SP1

Starting with SP1, a new command prompt parameter, -MaxCmdsInTran, has been added for the Log Reader Agent. For transactions affecting a large number of commands (typically mass updates or deletes), the Distribution Agent must wait for the Log Reader Agent to write the entire transaction to the distribution database before it can start propagating the transaction to the Subscriber. This delay blocks the Distribution Agent and reduces the parallelism between the two agents.

By using –MaxCmdsInTran, the Log Reader Agent breaks large transactions into smaller chunks, and each chunk contains the same or fewer commands as the -MaxCmdsInTran input. Therefore, the Distribution Agent can start processing earlier chunks of a transaction while the Log Reader Agent is still working through later chunks of the same transaction.

This improvement in parallelism between the Log Reader Agent and the Distribution Agent contributes to better overall replication throughput. Note, however, that the transaction chunks are committed at the Subscriber as individual transactions, which breaks the property of atomicity, one of the ACID (Atomicity, Consistency, Isolation, and Durability) properties. This outcome is not a problem in most circumstances, though it is recommended that you test this to make sure.

##### Defining the –MaxCmdsInTran Parameter

Specify a positive integer (1 or above) for the -MaxCmdsInTran parameter value. Specifying a value of 0 is equivalent to not using the parameter. Because this parameter improves performance only when the transaction is very large, a value of 5000 or greater for this parameter is typical. For example:

    logread.exe -MaxCmdsInTran 10000.

To use this parameter, the Publisher must be running SQL Server 2000 SP1 or a later version, and the Log Reader Agent and distribution database must be upgraded to SP3 or later. Otherwise, -MaxCmdsInTran is ignored.

[Top]

### 5.2.9 Restriction on Non-unique Clustered Indexes

Introduced in SP2 (applies to Transactional Replication only).

You cannot create a non-unique clustered index on a table after it is published for transactional replication. Before creating the index you must first drop any publication that includes the table.

[Top]

### 5.2.10 New -MaxNetworkOptimization Command Line Argument for Snapshot Agent

Introduced in SP2.

During normal processing, merge replication can send DELETE commands to Subscribers for rows that do not belong to the Subscriber's partition. DELETE commands of this type are referred to as irrelevant deletes. Irrelevant deletes do not affect data integrity or convergence, but they can result in unnecessary network traffic.

To reduce network traffic caused by irrelevant deletes, you can use the new Snapshot Agent parameter
-MaxNetworkOptimization with merge replication publications. Setting the parameter to 1 minimizes the chances of irrelevant deletes, which maximizes network optimization.

Note Setting this parameter to 1 is useful only when the synchronization optimization option of the merge publication is set to true (the @keep_partition_changes parameter of sp_addmergepublication).

The default is 0 because setting the parameter to 1 can increase the storage of meta data and cause performance to degrade at the Publisher if multiple levels of join filters and complex subset filters are present. You should carefully assess your replication topology and set -MaxNetworkOptimization to 1 only if network traffic from irrelevant deletes is unacceptably high.

You can add this parameter to the Snapshot Agent profile by executing the system procedure sp_add_agent_parameter as follows:

    EXEC sp_add_agent_parameter 1, 'MaxNetworkOptimization', 1

[Top]

### 5.2.11 Merge Replication Uses New Role

Introduced in SP3

SP3 and later automatically creates a new role for use by merge replication. The name of the new role is in the form MSmerge-<publication ID>. The role is created on the Publisher for each merge replication publication and acts as the publication access list (PAL) to control access to merge publications on the Publisher. If this role is dropped, you can run a new stored procedure included with SP3 or later, sp_createmergepalrole, to re-create the role. This stored procedure is executed at the Publisher on the publication database to re-create the role.

sp_createmergepalrole is documented in the latest copy of the SQL Server 2000 Books Online. For information about installing the latest version of the SQL Server 2000 Books Online, see section 1.9 Updates to SQL Server 2000 Books Online Are Available. This is an English-language copy of the reference topic for sp_createmergepalrole.

[Top]

### 5.2.12 New Requirements for Subscriptions Created by Non-sysadmin Users

Introduced in SP3

If a subscription is created by a user who is not a member of the sysadmin fixed server role, you must do one of the following:

- Configure the SQL Server Agent proxy account with a login and password so that the SQL Server Agent job step associated with the replication agent runs with sufficient privileges. For more information, see the topic "xp_sqlagent_proxy_account" in SQL Server Books Online.
- Change the owner of the job step to a user that is a member of the sysadmin fixed server role before running the agent.

Note The remote agent activation feature always requires the job step to run in the context of a user account in the sysadmin fixed server role.

[Top]

### 5.2.13 Changes to Permissions for Stored Procedures

Introduced in SP3

Permissions have been changed on a number of the stored procedures used to implement, administer, and monitor a replication topology. Most of these changes involve a tightening of the permissions that are required to run the stored procedures. For more information about the new permissions, review the Transact-SQL reference documentation for the replication stored procedures in the updated version of SQL Server Books Online. For more information about the updated SQL Server Books Online, see Section 1.6 Updates to SQL Server 2000 Books Online Are Available.

[Top]

### 5.2.14 New Parameter for sp_addmergearticle and sp_changemergearticle

Introduced in SP3

A new parameter, @published_in_tran_pub, has been added to both sp_addmergearticle and sp_changemergearticle. This parameter is used to indicate that an article in a merge publication is also published in a transactional publication. @published_in_tran_pub is nvarchar(5), with a default of FALSE. TRUE specifies that the article is also published in a transactional publication.

Note When you change this parameter in sp_changemergearticle, the snapshot must be invalidated and subscribers must be reinitialized.

[Top]

### 5.2.15 New Page for Configure Publishing and Distribution Wizard

Introduced in SP3

The Configure Publishing and Distribution Wizard now includes a new page: Distributor Password. You must type a password on this page if you select one or more Publishers to use the server as a remote Distributor and one or more of those Publishers require a password. The connection between a Publisher and a remote Distributor is a hybrid of a linked server and a remote server. The connection uses the login distributor_admin. By default, the Publisher is configured as non-trusted at the remote Distributor, so a password is required.

Note If you have downloaded and installed the latest version of the SQL Server 2000 Books Online, this information is available when you click the Help button for the new page.

[Top]

### 5.2.16 Changes to Windows Synchronization Manager Support

Introduced in SP3

SQL Server allows you to enable existing subscriptions (created using SQL Server Enterprise Manager, SQL-DMO, and replication stored procedures) for use with Windows Synchronization Manager. You can also create new subscriptions using Windows Synchronization Manager. After you apply the service pack, when synchronizing a subscription, Windows Synchronization Manager will prompt you to enter the password or passwords required to connect to the servers involved in the synchronization.

[Top]

### 5.2.17 Change to Requirements for Attaching or Restoring a Replication Database

Introduced in SP3

Under a certain set of conditions, replication could malfunction in the process of attaching or restoring a published database. These conditions are:

- SP3 or later has been applied.
- The user attaching the database is not a member of the sysadmin fixed server role.
- Cross-database chaining has not been allowed.

If all of these conditions are true, you should execute the sp_changedbowner stored procedure on the attached or restored database. Assign ownership to the sa built-in administrator login. This will ensure that replication functions correctly.

Note You must be a member of the sysadmin fixed server role to execute sp_changedbowner .

For more information about cross-database ownership chaining, see Section 5.1.10 Cross-Database Ownership Chaining.

[Top]

### 5.2.18 Change to Security Designations of Replication ActiveX Controls

Introduced in SP4

Replication ActiveX® controls (sqlinitx.dll, sqldistx.dll, sqlmergx.dll, and replerrx.dll) are no longer designated as "safe for scripting" and "safe for initialization." The security and functional behaviors of the controls have not changed since SP3; however, the security designations have been changed to meet security standards. These changes may affect applications that invoke embedded replication ActiveX controls in a Web page.

[Top]

### 5.2.19 New Parameter for Articles in Merge Publications

Introduced in SP4

A new parameter, @compensate_for_errors, can be specified when calling sp_addmergearticle. The parameter specifies whether compensating actions are taken if errors (such as a constraint violation) are encountered during synchronization. When set to TRUE (the default), a change that cannot be applied at a node during synchronization leads to compensating actions that undo the change at all other nodes. In some cases this behavior is desirable, but in some cases it can be problematic; for example, one incorrectly configured Subscriber that generates an error can cause changes to be undone at the Publisher and all other Subscribers.

Specifying a value of FALSE disables these compensating actions; however, the errors are still logged and subsequent merges will continue to attempt to apply the changes. Although data in the affected rows might appear to be out of convergence, as soon as you address the error the change can be applied, and data will converge.

Note If the source table for an article is already published in another publication, then the value of @compensate_for_errors must be the same for both articles.

[Top]

### 5.2.20 New Schema Option for Replicating Identity Columns in Transactional Publications

Introduced in SP4

In previous releases, identity columns in transactional publications were replicated as the base data type, such as int, without the identity property set. This approach is appropriate for applications that do not allow inserts at the Subscriber. SQL Server 2000 SP4 introduces a new schema option (0x4) for transactional publications, which is used to replicate the identity column as an identity column. This is useful in a number of cases, including bidirectional replication and using the Subscriber as a warm standby server. In these cases, inserts can occur at the Subscriber, and the inserts result in the identity column being incremented.

To specify that an identity column should be replicated as an identity column:

1. When creating the table at the Publisher, specify the NOT FOR REPLICATION option for the identity column. This ensures that only user inserts, not replication agent inserts, increment the identity column. For more information, see "CREATE TABLE" in SQL Server Books Online.
2. When adding an article with an identity column, set the option 0x4 for the @schema_option parameter of sp_addarticle. For more information about this parameter, see "sp_addarticle" in SQL Server Books Online.
3. After initializing the Subscriber, execute DBCC CHECKIDENT for each table with an identity column. This allows you to specify a starting value for inserts into the identity column at the Subscriber, so that the values inserted will not be the same as those inserted at the Publisher. For example, you could specify that inserts at the Subscriber should start at 1,000,000:

       USE Northwind
       GO
       DBCC CHECKIDENT ('Employees', RESEED, 1000000)
       GO

For more information, see DBCC CHECKIDENT in SQL Server Books Online.

### 5.2.21 Non-SQL Server Subscribers Not Supported for Distributors Running in Windows-on-Windows 64 Mode

Introduced in SP4

Distributor instances of SQL Server 2000 (32-bit) running in Windows-on-Windows 64 mode on Windows 2003 SP1 systems running on X64 or compatible processors cannot have non-SQL Server Subscribers. Although running Windows-on-Windows 64 mode is now supported for SQL Server 2000 SP4, this mode is not supported by drivers or providers used to connect from the Distributor to the non-SQL Server Subscriber.

[Top]

## 5.3 SQL Server Agent and Shared Tools Enhancements

This section discusses enhancements to SQL Server Agent and shared tools that are included in SP4.

[Top]

### 5.3.1 SQL Server Agent Logs Account Information

Introduced in SP2

The SQL Server Agent Job History now records the Windows account under which each job step runs. This information helps administrators diagnose security issues with scheduled jobs, including scheduled jobs that are defined for replication and Data Transformation Services (DTS) tasks.

[Top]

### 5.3.2 Changes to Master/Target Server Configurations

Introduced in SP3

Multiserver administration is the process of automating administration tasks across multiple instances of SQL Server. Use multiserver administration if you manage two or more servers and you want to centralize maintenance tasks.

In SP3 or later, the SQL Server Agent service account does not have to be a Windows administrator unless you need to use the SQL Server Agent Proxy Account. For more information about the SQL Server Agent Proxy Account, see Section 5.6.3 SQL Server Agent Proxy Account Improvements. The SQL Server Agent service account must be a member of the sysadmin fixed server role.

With multiserver administration, you must have at least one master server and at least one target server. A master server distributes jobs to and receives events from target servers. A master server stores the central copy of job definitions for jobs that run on target servers. Target servers connect periodically to their master server to update their list of jobs to perform. If a new job exists, the target server downloads the job and disconnects from the master server. After the target server completes the job, it reconnects to the master server and reports the status of the job.

Before you apply SP4, you must complete several steps to upgrade your SQL Server 2000 master/target server configuration. The changes that are introduced with SP4 are not compatible with SQL Server 7.0 target servers, or with any servers not running SP3 or later. This is a change from the original SQL Server 2000 functionality.

To upgrade your master/target server configuration

1. Create a new MSX (master server) account on your master server. This is to prepare TSX (target server) server (or servers) for the upgrade. To do this, run the following commands.

       --Option A: Windows authentication
       EXEC sp_grantlogin 'DOMAIN\user'
       GO
       USE msdb
       GO
       EXEC sp_adduser 'DOMAIN\user', 'DOMAIN\user', 'TargetServersRole'
       GO

       --Option B: SQL Server authentication – see explanation below for
       --details.
       EXEC sp_addlogin <MSXAccount>, <MSXAccountPassword>, 'msdb'
       GO
       USE msdb
       GO
       EXEC sp_adduser <MSXAccount>, <MSXAccount>, 'TargetServersRole'
       GO

   Where <MSXAccount> represents the SQL Login name you choose and <MSXAccountPassword> represents the associated password.

   Note These values must be enclosed in single quotation marks.

   You have the following options when choosing an MSX account:

   - Windows Authentication. This is the most secure option, because passwords do not have to be stored, and SQL Server and SQL Server Agent can be configured without local Windows administrator rights.
   - SQL Server Authentication. This requires the SQL Server Agent service accounts to have local Window administrator rights because SQL Server stores the user name and password as a local security authority (LSA) secret, and access is restricted to local Windows administrators. You can create an account for all TSX servers, or you can create one account for each TSX server.

   Do not specify a SQL Server Agent probe account (<computer_name>_msx_probe_login). As part of the upgrade to SP3 or later, SQL Server removes the old probe accounts because the TSX servers no longer use them.

2. Upgrade your TSX servers to SP4 one at a time. (Before you apply the service pack, see Step 3 for more information about timing your upgrade.)
3. To minimize down time, run the extended stored procedure xp_sqlagent_msx_account on each TSX server shortly after the SP4 update is complete.

   Note After running xp_sqlagent_msx_account, SQL Agent must be stopped and restarted on each server.

   For more information about xp_sqlagent_msx_account, see Section 5.3.3 New SQL Server Agent Extended Stored Procedure.

4. Apply SP4 to your master server. The old _msx_probe accounts are removed by SP4 Setup, because the TSX servers no longer use them. If an account owns SQL Agent jobs, the account is not removed, and you must change the owner of the jobs to another user and manually remove these accounts. If you want to continue to use the old _msx_probe accounts that own SQL Agent jobs, you may have to change the password of the _msx_probe account.

[Top]

### 5.3.3 New SQL Server Agent Extended Stored Procedure

Introduced in SP3

SP3a includes a new extended stored procedure (xp_sqlagent_msx_account) that allows you to configure the account that the SQL Server Agent TSX server uses to download instructions from an MSX server. This account is also known as the MSX account, or master server account.

xp_sqlagent_msx_account is documented in the latest copy of the SQL Server 2000 Books Online. For information about installing the latest version of the SQL Server 2000 Books Online, see section 1.6 Updates to SQL Server 2000 Books Online Are Available. This is an English-language copy of the reference topic for xp_sqlagent_msx_account .

[Top]

### 5.3.4 SQL Server Agent Permission Checks

Introduced in SP3

SQL Server now checks to ensure that the Agent job owner has permission to append or overwrite the output log file from each job. This happens in three ways:

- If the job owner is a member of the sysadmin fixed server role, the job output log file can be written to the server.
- If the job owner is a Windows user, SQL Server tests to see if the user has permission to write to the selected job output log file location on the server.
- If the job owner is a SQL Server user, SQL Server tests the SQL Server Agent proxy account for permission to write to the selected job output log file location on the server. If the proxy account has not been set, no log is written.

In all cases, jobs are written with SQL Server Agent credentials, but SQL Server now tests to ensure the user has permission to write to the selected job output log file location on the server. Errors appear in the job history, but the job steps do not fail if the log file cannot be written.

[Top]

### 5.3.5 SQL Agent Mail MAPI Profiles

Introduced in SP3

In the 32-bit version of SQL Server 2000, SQL Agent Mail can be configured to use an Extended MAPI e-mail profile for sending e-mail alerts. You can use an Extended MAPI e-mail application, such as Microsoft Outlook, to create an Extended MAPI profile. In the 64-bit version of SQL Server 2000, SQL Agent Mail can only use a Simple MAPI profile for sending e-mail alerts. Do not use Simple MAPI profiles in the 32-bit version of SQL Server 2000.

[Top]

### 5.3.6 View Designer Help Topic Is Not Available

Introduced in SP4

In SQL Server Enterprise Manager, the Help topic "View Properties" is not available from Design View and Create View. The updated topic is available at this Microsoft Web site.

[Top]

## 5.4 SQL Server Connectivity Components Enhancements

This section discusses enhancements to connectivity components of SQL Sever 2000 that are included with SP4.

[Top]

### 5.4.1 Support for QLogic Virtual Interface Architecture

Introduced in SP3

SQL Server now supports QLogic Virtual Interface Architecture (VIA) System Area Network (SAN) implementations. To enable SQL Server support for connections over QLogic VIA, both the client and server computers must provide IP address resolution in a file named Vihosts in the appropriate Windows system32\drivers\etc folder.

The Vihosts file should be formatted as follows:

<VI IP Address of server computer> <SERVER_COMPUTERNAME>

<VI IP Address of client computer> <CLIENT_COMPUTERNAME>

For example:

139.4.130.1 SQLCOMPUTER

139.4.130.2 SQLCLIENT

Use IP addresses from the respective QLogic VIA network cards and actual computer names. Otherwise, connections cannot be made to named instances or with other IP protocols such as TCP or Named Pipes. The Vihosts file is not necessary for Giganet VIA connectivity.

Note You must identify the correct VIA vendor on client computers by using Client Network Utility. Select the appropriate value in the Vendor drop-down box. The corresponding action should also be completed on server computers through Server Network Utility.

[Top]

## 5.5 Meta Data Services Enhancements

This section discusses enhancements to SQL Server 2000 Meta Data Services that are included with Database Components SP4.

[Top]

### 5.5.1 Meta Data Browser Exports in Unicode

Introduced in SP1

Meta Data Browser now exports XML-based meta data in Unicode. Before SQL Server 2000 SP1, the browser exported ANSI code, which does not support non-English characters. This functional change is invisible to the user. As of this SP4 release, exported data is always expressed as Unicode. You can still export in ANSI code by setting the value of the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Repository\Engine\XMLExport to 0. The following list represents the values that you can set for this registry key:

- NOOBJID=1
- NOHEADER=2
- INDENTATION=4
- UNICODE=8
- LOGUNMAPPEDTAGS=16
- EXPORTBASE=32

For more information about each flag, see "IExport::Export Method" in SQL Server Books Online.

[Top]

### 5.5.2 Scripting Support Disabled

Introduced in SP3

Script support in information models has been disabled. After installing SP3a or later, you receive the following error if your application accesses a property or method for which a script is defined:

    EREP_SCRIPTS_NOTENABLED

To enable script support

If you need to continue executing scripts, you can use the following procedure to create a registry setting that enables script support.

1. Open Registry Editor and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft.
2. Create a registry key named Repository (if one does not already exist), and then create a sub-key named Engine so that the path equals Repository\Engine.
3. On the Engine registry key, add a new DWORD value named AllowScripting and set the value to 1.

If you want to disable scripting later, set the value of this new registry key to 0.

Important For security reasons, you should never have a blank password on the sa login.

[Top]

### 5.5.3 New RepositoryUser Role for Accessing Repository Information

Introduced in SP3

SQL Server includes in the msdb database a set of tables, stored procedures, and views that store information used by the Meta Data Services repository engine. In SP3, a new dedicated role named RepositoryUser was added and must be used to access and update repository information. This role has been granted create, read, update, delete, and execute permissions on these objects. The public role no longer has permissions on these objects.

This change affects existing repository objects in addition to any additional objects created by the repository engine in the future. Users and applications that access the repository through the public role must be added to the RepositoryUser role.

[Top]

## 5.6 Data Transformation Services Enhancements

This section discusses enhancements to SQL Server 2000 Data Transformation Services that are included with SP4.

[Top]

### 5.6.1 DTS Wizard No Longer Limits String Columns to 255 Characters

Introduced in SP2

When you export data to a text file, the DTS Import/Export Wizard now configures the package to write up to 8000 characters of any column containing string-type data.

[Top]

### 5.6.2 Security Context Logged for DTS Packages Run by SQL Server Agent

Introduced in SP2

SQL Server Agent records the security context under which each step in a job runs. In SP3 or later, the security context appears in the Job History dialog box. When you run a DTS package from a step in a job, the SQL Server Agent logs the user account under which the package runs. This information helps administrators diagnose permission and authentication issues that occur when DTS packages are scheduled to run on a server.

[Top]

### 5.6.3 SQL Server Agent Proxy Account Improvements

Introduced in SP2

Prior to SP2, DTS packages stored on the server could not be run under the credentials of the SQL Server Agent Proxy Account unless the proxy account had access to the user Temp folder for the account under which either the server (in the case of jobs run from xp_cmdshell), or the agent (in the case of agent jobs) was running. Because of this, users often had to adjust the TEMP environment variable for the SQL Server or SQL Agent startup account to point to a folder that was accessible to both the startup and the proxy accounts, such as C:\Temp. For SP2 and later, DTS has been enhanced to use the system Temp folder if the user Temp folder is unavailable, greatly reducing the need for these adjustments.

[Top]

### 5.6.4 Save to Meta Data Services Disabled by Default

Introduced in SP3

By default, SP3 and later disables the option to store DTS packages in Meta Data Services. This means that the Meta Data Services option does not appear in the Location drop-down list in the Save DTS Packages dialog box. In addition, this option is disabled on the Save, schedule, and replicate package page in the DTS Import/Export Wizard.

To allow packages to be saved in Meta Data Services

- In SQL Server Enterprise Manager, right-click the Data Transformation Services node, click Properties, and then click Enable Save to Meta Data Services.

  Note You must be logged on with administrator privileges to modify this property.

When the option to store packages in Meta Data Service is disabled, you can load existing packages from Meta Data Services, edit them, and save them to Meta Data Services using the Save option. However, Meta Data Services is not available from the Save As option. For example, you cannot re-save a package to Meta Data Services using a different name.

[Top]

## 5.7 XML Enhancements

The following topic discusses an enhancement for XML and SQLXML in SP4.

[Top]

### 5.7.1 Improved Validation of XPath Expressions

Introduced in SP3, updated in SP4

When you apply SP4, OPENXML is updated to use a custom-built XML parsing technology designed to be backward compatible with MSXML 2.6.

Prior to SP3, the version of the XML parser used by OPENXML permitted a predicate in an XPath expression to follow the special character abbreviation that identifies the current context node, which is denoted by a period (`.`) in XPath syntax. This violates the XPath syntax specification, which requires that this character be followed by a location path expression.

With the new OPENXML behavior, a predicate cannot immediately follow the current context node abbreviation special character. XPath expressions in SQLXML queries (XPath queries against annotated mapping schemas and in XSLT style sheets written to transform the results of SQLXML queries) that use the faulty syntax will fail after you upgrade to SP3 or later.

To prevent these failures, identify and fix any expressions that use the incorrect syntax. For example, the syntax of the XPath expression that is specified as the value of the test attribute in the following `xsl:if` element is not valid because the predicate, `[@ResourceTypeID='2']`, immediately follows the special character abbreviation that identifies the current context node.

The following statement, which previously did not generate an error, will fail after SP3 or later is installed.

    <xsl:if test=".[@ResourceTypeID='2']">

To prevent a failure, the XPath expression must be amended as follows:

    <xsl:if test="@ResourceTypeID='2'">

[Top]

## 5.8 Virtual Backup Device API Enhancements

The following item applies to the SQL Server 2000 Virtual Backup Device API.

[Top]

### 5.8.1 Capturing Multiple Databases in a Single Snapshot

Introduced in SP2

The Virtual Backup Device API allows ISVs to integrate SQL Server 2000 into their products. This API is engineered to provide maximum reliability and performance. It fully supports the backup and restore functionality of SQL Server 2000, including the complete range of hot and snapshot backup capabilities.

In SP1 and earlier, there was no way to freeze and back up more than a single database at one time. SP2 or later now provides server-side support for freezing and capturing multiple databases in a single snapshot through the VDC_PrepareToFreeze command.

The Virtual Backup Device Interface Specification in SP4 contains updated information about the VDC_PrepareTo Freeze command. An updated version of the Virtual Device Interface header file (Vdi.h) can be found at \Devtools\Include in the SP4 setup folder.

You can download the updated specification from the Microsoft Download center at the Microsoft SQL Server Downloads Web site.

[Top]

## 5.9 Error Reporting

Introduced in SP3

Microsoft SQL Server error reporting is disabled by default. You can enable it during installation through SQL Server Setup or Analysis Services Setup, or after installation through the Server Properties dialog box in Enterprise Manager or the Server Properties dialog box in Analysis Manager. Enabling error reporting while you are running SQL Server Setup allows error reporting for the SQL Server database engine and SQL Server Agent. Enabling error reporting while you are running Analysis Services Setup allows error reporting for Analysis Services. If you want to enable error reporting for both SQL Server and Analysis Services, you must enable error reporting for SQL Server while running SQL Server Setup and Analysis Services while running Analysis Services Setup.

If you enable this feature, SQL Server is configured to send a report to Microsoft automatically if a fatal error occurs in the SQL Server database engine, in SQL Server Agent, or in SQL Server Analysis Services. Microsoft uses error reports to improve SQL Server functionality and treats all information as confidential.

Information about errors is sent over a secure (HTTPS) connection to Microsoft, where it is stored with limited access. Alternatively, this information can be sent to your own Corporate Error Reporting server. See this Microsoft Web site for more information about setting up a Corporate Error Reporting server.

The error report contains the following information:

- The condition of SQL Server when the problem occurred.
- The operating system version and computer hardware information.
- Your digital Product ID, which can be used to identify your license.
- The network IP address of your computer.
- Information from memory or files of the process that caused the error.

Microsoft does not intentionally collect your files, name, address, e-mail address, or any other form of personal information. The error report can, however, contain customer-specific information from the memory or files of the process that caused the error. Although this information can potentially be used to determine your identity, Microsoft does not use this information for that purpose.

For the Microsoft error reporting data collection policy, see this Microsoft Web site.

If you enable error reporting and a fatal error occurs, you may see a response from Microsoft in the Windows Event log that points to a Microsoft Knowledge Base article about a particular error. A response may look similar to the following example:

    Source = MSSQLServerOlapServicesDW
    EventID = 1010
    data = http://support.microsoft.com/support/misc/kblookup.asp?id=Q123456
    &iBucketTable=1&iBucket=39980&Cab=21474432.cab&LCID=1033
    &OS=5.1.2600.2.00010100.0.0

To disable error reporting for the SQL Server database engine and SQL Server Agent, go to SQL Server Properties (General Tab) in Enterprise Manager and clear the Enable the error reporting check box. To disable error reporting for Analysis Services, go to Server Properties in Analysis Manager and clear the Enable Error Reporting check box. If error reporting is enabled for both SQL Server (database engine and SQL Server Agent) and Analysis Services, you must disable error reporting for SQL Server and Analysis Services individually.

[Top]

## 5.10 Serviceability Enhancements

Introduced in SP4

SQL Server 2000 SP4 introduces new serviceability functionality that enables you to uninstall hotfixes applied to SP4 and later versions of SQL Server 2000 running on Windows XP and Windows Server 2003. (This same functionality was available with SQL Server 2000 SP3, but only after the application of an additional hotfix).

[Top]

## 5.11 English Query Enhancements

Introduced in SP1

Microsoft has released a security enhancement for English Query applications. This enhancement is not installed as a part of the service pack. However, it is recommended that you apply it if you use English Query. The security enhancement is located on the SP4 CD-ROM in the folder \EQHotfix. Details about the English Query enhancement are available in Knowledge Base article 297105.

[Top]

## 5.12 DB-Library and Embedded SQL for C

Introduced in SP1, updated for SP4

While the DB-Library and Embedded SQL for C APIs are still supported in SQL Server 2000, no future versions of SQL Server will include the files or documentation that are necessary to program applications that use these APIs. Connections from existing applications written using DB-Library and Embedded SQL for C will still be supported in the next version of SQL Server, but this support will be dropped in a future release. Do not use DB-Library or Embedded SQL when writing new applications. Remove dependencies on these technologies when modifying existing applications. Instead of DB-Library or Embedded SQL for C, use the System.Data.SQLClient namespace from the .NET Framework or an API such as ADO, OLE DB, or ODBC to access data in SQL Server. For more information about these technologies, see SQL Server Books Online or the .NET Framework SDK.

[Top]