# Service Pack 3 for Microsoft SQL Server 2000

##### December 9, 2002

© Copyright Microsoft Corporation, 2002. 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. All feedback must be in English.
To submit written feedback about this document, click here: Submit feedback.

# Contents

1.0 Introduction

1.1 Overview of Database Components SP3 Installation

1.2 Removing SP3

1.3 Identifying the Current Version of SQL Server or Analysis Services

1.4 Additional Information About SP3

1.5 Updated Books Online Documentation Is Available

1.6 Updated SQL Server and Analysis Services Samples Are Available

2.0 Downloading and Extracting SP3

3.0 Service Pack Installation

3.1 Back Up Your SQL Server Databases

3.2 Back Up Your Analysis Services Databases and Repository

3.3 Make Sure the System Databases Have Enough Free Space

3.4 Stop Services and Applications Before Running SP3 Setup

3.5 Install Database Components SP3

3.6 Install Analysis Services SP3

3.7 Install Desktop Engine SP3

3.8 Restart Services

3.9 Restart Applications

3.10 Installing on a Failover Cluster

3.11 Installing on Replicated Servers

3.12 Applying SP3 to Read-Only Databases or Filegroups

3.13 Uninstalling SP3

3.14 Reapplying SP3

4.0 Additional Installation Considerations

4.1 Unattended Installations

4.2 Redistributing SP3 Data Access Components

5.0 Documentation Notes

5.1 Database and Desktop Engine Enhancements

5.1.1 Using Chinese, Japanese, or Korean Characters with Database Components SP3

5.1.2 Hash Teams Removed

5.1.3 Affinity Mask Switches Added

5.1.4 Filtered Indexed View

5.1.5 Full-Text Catalogs Are Rebuilt After Setup Completes

5.1.6 Syntax Changes for sp_change_users_login

5.1.7 Ad Hoc Access to OLE DB Providers Disabled by Default

5.1.8 New SqlServerLike Provider Option

5.1.9 Expanded Error Messages for Distributed Queries

5.1.10 New Function fn_get_sql Returns SQL Statement

5.1.11 Cross-Database Ownership Chaining

5.1.12 Enhancement for Trace Flag 1204

5.1.13 Permissions Change for sp_changedbowner

5.1.14 Debugging Functionality Changes

5.2 Analysis Services Enhancements

5.2.1 Remote Partitions

5.2.2 Updated Analysis Services Redistributable Client Setup

5.2.3 Support Enabled for Third-Party Data Mining Algorithm Providers

5.2.4 Installing Analysis Services on a Computer with Updated Client Files

5.2.5 Increased Limit for OLAP Cubes Referenced by a Virtual Cube

5.2.6 New DESCRIPTION Keyword

5.2.7 New PivotTable Service Restricted Client Property

5.2.8 Change in the Safety Options Property

5.2.9 Migrate Repository to Meta Data Services Disabled by Default

5.2.10 Permissions Must be Modified on a Remote Data Folder

5.3 Replication Enhancements

5.3.1 Transactional Replication UPDATE Custom Stored Procedure

5.3.2 Transactional Replication UPDATE Statements on Unique Columns

5.3.3 Restrictions Removed from Concurrent Snapshot Processing

5.3.4 Transactional Replication Scripting Custom Stored Procedures

5.3.5 Merge Replication Retention-Based Meta Data Clean Up

5.3.6 Backup and Restore Issues for Merge Replication

5.3.7 Restoring Replicated Databases from Different Versions of SQL Server

5.3.8 New -MaxCmdsInTran Parameter for Log Reader Agent

5.3.9 Restriction on Non-unique Clustered Indexes

5.3.10 New –MaxNetworkOptimization Command Line Argument for Snapshot Agent

5.3.11 Merge Replication Uses New Role

5.3.12 New Requirements for Subscriptions Created by Non-sysadmin Users

5.3.13 Changes to Permissions for Stored Procedures

5.3.14 New Parameter for sp_addmergearticle and sp_changemergearticle

5.3.15 New Page for Configure Publishing and Distribution Wizard

5.3.16 Changes to Windows Synchronization Manager Support

5.3.17 Change to Requirements for Attaching or Restoring a Replication Database

5.4 SQL Server Agent Enhancements

5.4.1 SQL Server Agent Logs Account Information

5.4.2 Changes to Master/Target Server Configurations

5.4.3 New SQL Server Agent Extended Stored Procedure

5.4.4 SQL Server Agent Permission Checks

5.5 SQL Server Connectivity Component Enhancements

5.5.1 Updates to Microsoft Data Access Components

5.5.2 Support for QLogic Virtual Interface Architecture

5.6 Meta Data Services Enhancements

5.6.1 Meta Data Browser Exports in Unicode

5.6.2 Scripting Support Disabled

5.6.3 New RepositoryUser Role for Accessing Repository Information

5.7 Data Transformation Services Enhancements

5.7.1 DTS Wizard No Longer Limits String Columns to 255 Characters

5.7.2 Security Context Logged for DTS Packages Run by SQL Server Agent

5.7.3 SQL Server Agent Proxy Account Improvements

5.7.4 Save to Meta Data Services Disabled by Default

5.8 XML Enhancements

5.8.1 Improved Validation of XPath Expressions

5.9 Virtual Backup Device API Enhancements

5.9.1 Capturing Multiple Databases in a Single Snapshot

5.10 Error Reporting

5.11 English Query Enhancements

5.12 DB-Library and Embedded SQL for C

# 1.0 Introduction

This release of Service Pack 3 (SP3) for Microsoft® SQL Server™ 2000 is provided in three parts:

- Database Components SP3 provides updates for the database components of an instance of SQL Server 2000, excluding instances of the SQL Server 2000 Desktop Engine. Database Components SP3 includes updates to:
  - The database engine.
  - 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.
- Analysis Services SP3 provides updates for the SQL Server 2000 Analysis Services components of a SQL Server 2000 installation, including:
  - Analysis Services.
  - Analysis Services client components, which include Analysis Manager and the Microsoft OLE DB Provider for Analysis Services.
  - 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.
- Desktop Engine SP3 provides updates for the database components of an instance of SQL Server 2000 Desktop Engine (also referred to as MSDE 2000), including:
  - The database engine.
  - 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.

These three parts of SP3 can be applied individually, as follows:

- SQL Server 2000 sites can use Database Components SP3 to upgrade their database components without upgrading their Analysis Services components or instances of the SQL Server 2000 Desktop Engine.
- Analysis Services 2000 sites can use Analysis Services SP3 to upgrade their Analysis Services components without upgrading instances of Desktop Engine or their database components.
- Desktop Engine sites can use Desktop Engine SP3 to upgrade instances of Desktop Engine without upgrading Analysis Services or instances of other editions of SQL Server 2000.

  Note If separate instances of both Desktop Engine and other editions of SQL Server 2000 are installed on the same computer, you must apply Desktop Engine SP3 to instances of Desktop Engine and Database Components SP3 to the other instances of SQL Server 2000.

For more information about installing Desktop Engine, see Section 3.7 Install Desktop Engine SP3.

Note Desktop Engine SP3 is the only part of the service pack that is available in Portuguese (Brazil), Swedish, and Dutch, because SQL Server 2000 Desktop Engine is the only version of SQL Server 2000 that is produced for those languages. The SQL Server 2000 components upgraded by Database Components SP3 or Analysis Services SP3 are not available in those languages. Portuguese (Brazil), Swedish, and Dutch users who want to apply SP3 to a version of SQL Server other than Desktop Engine must download the SP3 files that match the language of the edition they want to upgrade. For example, English-language SP3 files must be downloaded to upgrade the English-language version of SQL Server 2000. For information about how to download the service pack, see Section 2.0 Downloading and Extracting SP3.

## 1.1 Overview of Database Components SP3 Installation

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

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

## 1.2 Removing SP3

The way in which you remove SQL Server SP3 depends on the SQL Server 2000 SP3 components that you are removing.

##### Removing SQL Server Database Components and Desktop Engine SP3

When SQL Server Database Components SP3 is installed, it makes changes to the system tables for maintenance reasons, and it also upgrades user and distribution databases that are members of a replication topology. Because of these changes, SP3 cannot be removed easily. To revert to the build that you were running before installing SP3, first, you must uninstall the instance of SQL Server 2000; then, you must reinstall that instance. If you were running a previous SQL Server 2000 service pack or applied any Quick Fix Engineering (QFE) fixes, you must reapply that service pack and any QFE fixes to the instance.

Note To remove SP3, you must have backups of the master, model, and msdb databases, which were taken immediately prior to applying SP3. For more information, see Section 3.1 Back Up Your SQL Server Databases and Section 3.2 Back Up Your Analysis Services Databases and Repository.

For more information, see Uninstalling SQL Server 2000 Components and Desktop Engine SP3.

##### Removing SQL Server Analysis Services Components SP3

To be able to return SQL Server Analysis Services to its pre-SP3 state, you must back up the registry key HK_LOCAL_MACHINE\Software\Microsoft\OLAP Server and all of its sub-keys before installing SP3. When uninstalling SP3, you must delete this registry key and restore the pre-SP3 version from the backup.

Note Updates to MDAC 2.7 SP1 that are made during SP3 setup cannot be uninstalled.

For more information, see Uninstalling SQL Server 2000 Analysis Services SP3.

## 1.3 Identifying the Current Version of SQL Server or Analysis Services

Use the techniques in the following sections to determine which version of SQL Server or Analysis Services you have installed.

##### SQL Server

To identify which version of SQL Server 2000 you have installed, type `SELECT @@VERSION` or `SERVERPROPERTY('ProductVersion')` at the command prompt using the osql or isql utility or in the Query window in SQL Query Analyzer.

Similarly, the product level for a given version of SQL Server 2000 can be determined by executing `SELECT SERVERPROPERTY('ProductLevel')`.

The following table shows the relationship between the SQL Server 2000 version and level and the version number reported by `@@VERSION` and the product level reported by `SERVERPROPERTY('ProductLevel')`.

SQL Server 2000 version and level | @@VERSION | ProductLevel
SQL Server 2000 RTM | 8.00.194 | RTM
Database Components SP1 | 8.00.384 | SP1
Database Components SP2 | 8.00.534 | SP2
Database Components SP3 | 8.00.760 | SP3

If you are not sure which edition of SQL Server 2000 you are running, view the last line of output returned by `SELECT @@VERSION`. The last line should match one of the following:

    Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 2)
    Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
    Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
    Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
    Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 2)

Note The beginning of the line indicates the edition of SQL Server. This is followed by current operating system information.

You can also determine the edition by typing `SELECT SERVERPROPERTY('Edition')` at the command prompt using the osql or isql utility or in the Query window in SQL Query Analyzer.

##### Analysis Services

To identify which version of Analysis Services you have installed, follow these steps:

1. From the Start menu, point to Program Files, point to SQL Server 2000, point to Analysis Services, and then click Analysis Manager.
2. In the Analysis Manager tree, right-click the Analysis Servers node, and then click About Analysis Services.
3. Use the following table to determine which version of Analysis Services you have.

Analysis Services version | Build Number in Help About
SQL Server 2000 Analysis Services RTM | 8.0.194
Analysis Services SP1 | 8.0.382
Analysis Services SP2 | 8.0.534
Analysis Services SP3 | 8.0.760

## 1.4 Additional Information about SP3

A list of the fixes contained in this service pack will be provided in Microsoft Knowledge Base article 306908. Each fix listed in 306908 has a link to a Knowledge Base article describing the problem addressed by the fix. Follow the links to the individual Knowledge Base articles to see information about each fix. These articles are published at the Microsoft Product Support Services Knowledge Base.

To find an article in the Knowledge Base

1. From the Select a Microsoft Product list, select SQL Server 2000.
2. In the Search for... field, type the number of the article you want.
3. Under Search Type, select Article ID.
4. Click the Go button.

Any information relevant to SQL Server 2000 Service Pack 3 that was not available in time to be included in this readme file will be published in Microsoft Knowledge Base article 330022. This article is available at the Microsoft Product Support Services Knowledge Base.

#### Microsoft Data Access Components

This service pack includes updates to the Microsoft Data Access Components (MDAC), including updates for MSXML.

For more information, see Section 5.5.1 Updates to Microsoft Data Access Components.

#### QFE Fixes

All publicly issued SQL Server 2000 SP2 security bulletins have been addressed in SP3.

If you received a SQL Server 2000 hotfix after October 14, 2002, that hotfix is not likely to be included in SP3. Contact your primary support provider about obtaining the same hotfix for SQL Server 2000 SP3.

#### SQL Server CE Server Tools Updates

Users of Microsoft SQL Server 2000 Windows® CE Edition (SQL Server CE) who have upgraded or plan to upgrade SQL Server 2000 database and publisher servers to SP3 should also update the server replication components on Microsoft Internet Information Services (IIS) servers. An updated SQL Server CE Server Tools installer is available at this Microsoft Web site.

## 1.5 Updated Books Online Documentation Is Available

Updated documentation for SP3 is available. SQL Server 2000 Books Online (Updated - SP3) contains minor revisions plus new information that is related to SP3.

You can download SQL Server 2000 Books Online (Updated - SP3) from this Microsoft Web site.

## 1.6 Updated SQL Server and Analysis Services Samples Are Available

SQL Server 2000 and Analysis Services samples that have been updated for SP3 are available. You can download these updated samples from this Microsoft Web site.

# 2.0 Downloading and Extracting SP3

SP3 is distributed in the following ways:

- On a SQL Server 2000 Service Pack 3 CD-ROM that contains service packs for the following components:
  - Database Components SP3
  - Analysis Services SP3
  - Desktop Engine SP3
- In three self-extracting files that can be downloaded from the Internet at the Microsoft SQL Server Downloads Web site:
  - Sql2ksp3.exe (Database Components SP3)
  - Sql2kasp3.exe (Analysis Services SP3)
  - Sql2kdesksp3.exe (Desktop Engine SP3)

Note Both the SQL Server 2000 Service Pack 3 CD-ROM and Sql2kdesksp3.exe contain the merge modules and .msi files that are required to install a new instance of Desktop Engine.

##### Database Components and Analysis Services SP3 Files

When downloading and extracting Database Components or Analysis Services SP3 installation files from the Internet, use the following guidelines:

- Download one of the files listed in Section 2.0 and place the self-extracting file either in a folder on the computer that is running the instance of SQL Server 2000 on which you are installing the service pack or in a folder on a network share. From that folder, run the file. The self-extraction program 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 share, the path to the folder that you specify is relative to the folder in which you ran the self-extraction program.

- On the local drive or network share to which you download the service pack installation files, you must have free space approximately that is three times the size of the self-extracting file. This includes space to store the self-extracting file, space for storing the extracted service pack files, and temporary working space required by the self-extraction program itself.
- You can rename or move the folder after extracting the components; however, make sure that the directory path name does not contain blank spaces. You can use the same target folder for each of the self-extracting files; they will not overwrite each other or interfere with each other.
- The self-extracting files are used to build a set of directories and files that are the same as those on the SP3 CD-ROM. If you download SP3 you must extract the files to build the service pack directories before you can run SQL Server Setup.
- For Database Components SP3 and Analysis Services SP3, the setup process is the same for both the CD-ROM and the extracted files.

Note Some of the files in the service packs are system files. You cannot view them unless you follow this procedure: In Windows Explorer, on the View menu, click Options, click the View tab, and then select the Show all files check box.

Database Components and Analysis Services installation files both contain updated setup documentation that you can access by clicking Help during SP3 setup. This 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 Books Online, see Section 1.5 Updated Books Online Documentation Is Available. If you want to access just the updated SQL Server 2000 SP3 setup documentation without updating SQL Server Books Online, run the Setupsql.chm file. Setupsql.chm is located in the \Books subfolder of the directory on the SP3 CD-ROM, the local directory, or the network share that contains the extracted service pack files.

##### Desktop Engine SP3 File

For information on downloading and extracting Desktop Engine SP3, see Section 3.7 Install Desktop Engine SP3.

# 3.0 Service Pack Installation

To install SP3, use the installation instructions in the following sections. Not all steps are required, depending on which of the following SQL Server 2000 components or configurations you are applying the service pack to:

- Database Components
- Analysis Services
- Desktop Engine
- Failover cluster
- Replication server

For each installation step, the applicable components are listed.

Note The service pack is language-specific. Apply the service pack with the same language as the SQL Server component that you are upgrading.

##### Before Starting an Installation

An SP3 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 installing SP3. 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.

When installing this service pack on a pre-release version of Microsoft Windows .NET Server build 3683 or older, you will receive the following error message:

    The software you are installing has not passed Windows Logo testing to
    verify its compatibility with this version of Windows.
    This software will not be installed. Contact your system administrator.

You can choose to ignore this message. Click OK to continue Setup.

Note This message blocks an unattended installation.

Before you install SP3 on the French version of Windows NT 4.0, follow the instructions in Knowledge Base article 259484. You can access the article from the Microsoft Product Support Services Knowledge Base. For instructions on searching the Knowledge Base, see Section 1.4 Additional Information About SP3.

##### Before Starting a Database Components Installation

If you apply Database Components SP3 to an instance of SQL Server on a computer on which Analysis Services is also installed, Setup may fail when executing the script, Sp3_serv_uni.sql. If this happens, restart the computer and run Setup again.

SP3 Setup does not upgrade user databases except for those user databases that are actively involved in replication topologies. Databases that are not involved in replication topologies do not have any dependencies on SP3. For example:

- You can restore a user database backup from an instance of SQL Server 2000, SQL Server 2000 SP1, or SQL Server 2000 SP2 to an instance of SQL Server 2000 SP3.
- You can restore a user database backup from an instance of SQL Server 2000 SP3 to an instance of SQL Server 2000, SQL Server 2000 SP1, or SQL Server 2000 SP2.
- You can detach a user database from an instance of SQL Server 2000, SQL Server 2000 SP1, or SQL Server 2000 SP2 and attach it to an instance of SQL Server 2000 SP3.
- You can detach a user database from an instance of SQL Server 2000 SP3 and attach it to an instance of SQL Server 2000, SQL Server 2000 SP1, or SQL Server 2000 SP2.
- You can implement log shipping between instances of SQL Server 2000, SQL Server 2000 SP1, SQL Server 2000 SP2, and SQL Server 2000 SP3.

SP3 Setup upgrades user databases that are members of a replication topology. Before installing SP3, make sure that replication databases and filegroups are writable and that the user account that is running Setup has permission to access the databases. For more information about applying SP3 to databases that are included in replication topologies, see Section 3.11 Installing on Replicated Servers.

If SP3 Setup detects user databases or filegroups that are not writable, it:

- Applies the SP3 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.

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 SP3 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.

For more information about making a database writable, see Section 3.12 Applying SP3 to Read-Only Databases or Filegroups. For more information about reapplying SP3, see Section 3.14 Reapplying SP3.

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 in any of these conditions during setup and is involved in a replication topology, you must reapply the service pack after making the database writable.

Note Because non-writable databases no longer cause Setup to fail, you do not have to remove log shipping before upgrading to SP3.

##### Preparing for a Systems Management Server Distributed Installation

You cannot install SQL Server 2000 Service Pack 3 remotely. However, you can use Microsoft Systems Management Server to install SP3 automatically on multiple computers running Windows NT Server 4.0. To do so, you must use a package definition file (Smssql2ksp3.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. The file Sms2kdef.bat is a batch file that starts an unattended setup using Systems Management Server. In this type of installation, the Setup program automatically detects relevant system information that it needs and no user input is required.

Note You cannot use Systems Management Server to install Desktop Engine SP3.

## 3.1 Back Up Your SQL Server Databases

The following information applies to all component installations except database client components.

Before installing either Database Components SP3 or Desktop Engine SP3, back up the master, msdb, and model databases. Installing SP3 modifies the master, msdb, and model databases, making them incompatible with pre-SP3 versions of SQL Server. These backups are required if you decide to reinstall SQL Server 2000 without SP3.

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

## 3.2 Back Up Your Analysis Services Databases and Repository

The following information applies only to Analysis Services.

Before installing Analysis Services SP3, back up your Analysis Services databases by making a copy of the Microsoft Analysis Services\Data folder, which is installed by default under the C:\Program Files folder. If you have not migrated your Analysis Services repository to SQL Server, make a backup copy of the file Msmdrep.mdb, which is located in the Microsoft Analysis Services\Bin folder. You should also save your Analysis server registry entries by running Regedit.exe and by using the Export Registry File item on the Registry menu to export the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server to a file for backup. If you have migrated your Analysis Services repository to SQL Server, back up the database that contains the repository before installing SP3. For more information, see Uninstalling SQL Server 2000 Analysis Services SP3.

## 3.3 Make Sure the System Databases Have Enough Free Space

The following information applies to all component installations except database client components and Analysis Services.

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 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 this 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.

To verify that this option is selected in Desktop Engine, issue the following SQL statements:

- sp_helpdb master
- sp_helpdb msdb

In the output of these statements, verify that the value of the growth column is not 0.

## 3.4 Stop Services and Applications Before Running SP3 Setup

The following information applies to all components.

You can apply SP3 without shutting down services. If you do not shut down services, you are prompted to reboot the computer when Setup is complete. Without rebooting, the following services fail to start:

- Microsoft Distributed Transaction Coordinator (MS DTC) and the Microsoft Search and MSSQLServerOLAPService services.
- 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.
- All applications, including Control Panel. (optional)

You can apply SP3 without restarting your computer by stopping the these services and applications before applying the SP3:

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

## 3.5 Install Database Components SP3

The following information applies to all component installations except Desktop Engine and Analysis Services.

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

- The directory on the local computer containing the extracted service pack files from SQL2KSP3.exe.
- The service pack directory on the SQL Server 2000 SP3 CD-ROM.
- The directory on the network share containing the extracted service pack files from SQL2KSP3.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 directory 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 directory on the network share containing the extracted service pack files.

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 be running the Setup program while logged on to Windows using a Windows login account. This login account must be a part 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 Desktop Engine and is not recommended; protect your systems by enforcing an sa password or by using Windows Authentication. To learn more about this dialog, 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. Although you can continue your installation with a blank password for the sa login by explicitly choosing to ignore the recommendation and continue Setup, a blank password poses a security risk and is not recommended. This dialog is displayed regardless of the authentication mode you use.

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

- Displays a Backward Compatibility Checklist dialog box that warns of any backward compatibility issues that you may encounter when applying the service pack. To learn more about this dialog, see Backward Compatibility Checklist Dialog Box later in this section.
- Displays a 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 SP3 setup process and reapply SP3 at a later time after making these objects writable.

  Note The previous steps are necessary only when applying SP3 to non-writable databases or filegroups that are part of a replication topology. For more information, see Section 3.12 Applying SP3 to Read-Only Databases or Filegroups.

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

#### Authentication Mode Dialog Box

The Authentication Mode dialog box does not default to the current settings for the installation. The dialog box defaults are:

- On computers running Windows 98 or Windows Millennium Edition, the default setting is Mixed Mode Authentication (the only authentication mode supported on these operating systems). The dialog box prompts you to specify a password for the sa login. If you specify a password, Setup changes the sa login password. If you leave the password blank, Setup connects without changing the password.
- On computers running Windows NT 4.0 or Windows 2000, the default setting is Windows Authentication. Use the dialog box to switch to Windows Authentication Mode or Mixed Mode with an sa login password that is not blank.

  Note Before changing 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 using Mixed Mode Authentication to using only Windows Authentication, existing applications attempting to connect 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 using 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 directory of the computer on which Setup is run. If you upgrade multiple instances, only the most recent upgrade is recorded in this log.

#### Backward Compatibility Checklist Dialog Box

The Backward Compatibility Checklist dialog box lists backward compatibility issues that you may encounter when applying the service pack. The backward 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:

- SP3 upgrades Microsoft Search service and automatically rebuilds the full-text catalogs for all applications using the service. During the rebuild, full-text functionality may not be fully available. You must select this check box before continuing Setup. For more information, see Section 5.1.5 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 SP3 are compatible only with servers that are running SQL Server 2000 SP3. You must select this check box before continuing Setup. For more information, see Section 5.4.2 Changes to Master/Target Server Configurations.
- Cross-database ownership chaining is turned off by default in this service pack. 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.11 Cross-Database Ownership Chaining.

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

  Note If you had previous enabled cross-database ownership chaining on a pre-release version of SP3 (before build 8.00.760) you will need to enable it again when installing the release version of SP3.

## 3.6 Install Analysis Services SP3

The following information applies only to Analysis Services.

To install Analysis Services SP3, run Setup.exe from either of the following locations:

- The Msolap\Install subdirectory in the directory containing the extracted Analysis Services SP3 file from SQL2KASP3.exe.
- The Msolap\Install directory on the SP3 CD-ROM.

Setup then performs the following tasks:

- Opens a Setup dialog box that prompts you for information.
- Displays an Error Reporting dialog box with information about how to enable the new error reporting feature included in SP3. For more information about error reporting, see 5.10 Error Reporting.
- Copies the necessary files, and completes the installation.

##### Additional Analysis Services Installation Issues

After installing Analysis Services SP3 you must also upgrade any computers used for remote administration to SP3. Otherwise, you receive the following error message when you attempt to connect remotely through Analysis Manager:

    Unable to connect to the registry on the server (server_name), or you
    are not a member of the OLAP Administrators group of this server.

Meta Data Services has added a new dedicated role named RepositoryUser, which can be used to access and update repository information in the msdb database. The RepositoryUser role has create, read, update, delete, and execute permissions on the msdb repository. The public role has been replaced by this new role and no longer has permissions on this repository. If the following conditions are met, the OLAP Administrators group must be added to the RepositoryUser role so that members of this group can access the repository after the service pack is applied:

- The repository is in the Meta Data Services format.
- The repository uses the msdb database.
- The OLAP Administrators group previously used the public role to access the repository.

  Note This change also affects remote servers that access the Meta Data Services repository on a server that has been upgraded to SP3. Remote server logins must be added to the RepositoryUser role as well.

  Note The OLAP Administrators group must be added to the RepositoryUser role before you restore a Meta Data Services repository that was backed up prior to upgrading to SP3; otherwise, the restore operation will fail.

For more information about the RepositoryUser role, see Section 5.6.3 New RepositoryUser Role for Accessing Repository Information.

If the Analysis Services Data folder is located on a computer other than the one on which the Analysis server is running, you must modify the permissions on the folder after running SP 3 Setup. For more information, see Section 5.2.10 Permissions Must be Modified on a Remote Data Folder.

## 3.7 Install Desktop Engine SP3

The following information applies only to Desktop Engine.

The service pack for SQL Server 2000 Desktop Engine (also known as MSDE 2000) is intended for developers who create redistributable applications that use the Desktop Engine. Any instances of Desktop Engine that have been installed by a third-party application must be upgraded with a service pack supplied by the application vendor. If you are running an application that uses the Desktop Engine, contact your software provider for information about upgrading instances of the Desktop Engine that are installed by these applications. For more information, see the topic "Distributing the SQL Server 2000 Desktop Engine" in SQL Server Books Online. For information about the appropriate uses of Desktop Engine, see the Microsoft SQL Server Web site for Desktop Engine. For additional Desktop Engine SP3 installation information, see Knowledge Base article 810826.

The service pack for Desktop Engine is distributed in the following ways:

- On the SQL Server 2000 Service Pack 3 CD-ROM.
- Through SQL2KdeskSP3.exe. You can download this self-extracting file from the Microsoft SQL Server Downloads Web site.

Both delivery vehicles include all of the files that are required to install a new instance of Desktop Engine (.msi files), to upgrade all existing instances of Desktop Engine (.msp files), as well as to consume merge modules (.msm files) into applications. For more information, see "Distributing SQL Server Applications" in SQL Server Books Online or the Knowledge Base article 810826. You can access the article from the Microsoft Product Support Services Knowledge Base.

#### File Locations

This section describes the locations for files associated with Desktop Engine SP3.

Note If you install Desktop Engine as a component of another application, contact the software vendor for more information about upgrading Desktop Engine.

All Desktop Engine installation files are located in the \MSDE directory or one of its subdirectories, which can be found:

- On the CD-ROM
- In the directory containing the extracted service pack files from SQL2KDeskSP3.exe
- In the location on your hard drive or the network share to which you copied the \MSDE subdirectory from the CD-ROM.

Setup.exe is located in the \MSDE directory.

Installation packages (.msi files) are located in the \Setup subdirectory, which is a subfolder of the \MSDE folder that contains Setup.exe.

Windows Installer 2.0 (InstMsi20.exe) is located in the \MSI subdirectory.

The merge modules and related installation files are located in the \MSM subdirectory, which is a subfolder of the \MSDE folder containing Setup.exe. For a complete list of the available Desktop Engine merge modules, see the topic "Using SQL Server Desktop Engine Merge Modules" in SQL Server Books Online.

#### Requirements

The following requirements apply to Desktop Engine installations:

- When upgrading Desktop Engine, you must apply Desktop Engine SP3 to every instance of Desktop Engine.

  Note The maximum number of instances supported for Desktop Engine is 16.

- Always start Setup by running Setup.exe. Do not start Setup by directly invoking one of the .msi files.
- Regardless of whether an instance of Desktop Engine is using Windows or Mixed Mode authentication, Setup cannot proceed if a blank password is detected for the sa login. Setup displays a warning message if a blank password is detected. If you have a blank password on the sa login, before proceeding, you should change the password using sp_password.
- Problems can occur when you upgrade Desktop Engine to SP3 or when you install a new instance of Desktop Engine SP3 through a Terminal Services connection. If problems are encountered, restart Setup from the local computer.
- You can run Setup from your hard drive, a network share, or a CD-ROM. The Desktop Engine Setup program requires the original medium (for example, the original CD-ROM or network share) that you used to install Desktop Engine.
- If Desktop Engine was originally installed from a CD-ROM or from a network share that has been moved, copy the Desktop Engine subdirectory from the SQL Server 2000 Desktop Engine Service Pack 3 CD-ROM to your hard drive or the network share, and then run Setup.exe.
- You can install the upgrade for Desktop Engine from a CD-ROM only if the following conditions are met:
  - You are using Windows Installer version 2.0.2600.0 or later.
  - If you create your own CD-ROM for installing SP3, the volume label on the CD-ROM must be SQL2KSP3. If you are an independent software vendor (ISV) creating a CD-ROM for the purpose of distributing Desktop Engine as a component of your application, the CD-ROM volume label must be identical to the VolumeLabel property in the Media table of the Windows Installer package (*.msi).

If you need to upgrade Windows Installer, SP3 includes the files needed to upgrade Windows Installer.

To upgrade Windows Installer:

1. Run InstMsi20.exe located in \MSI which is a subfolder of the \MSDE folder that contains Setup.exe.
2. When prompted, reboot the computer.

#### Desktop Engine Setup Arguments

Desktop Engine Setup supports the following setup parameters in SP3.

Parameter name | Description
--- | ---
ALLOWXDBCHAINING=1 | Enables cross-database ownership chaining. For more information, see Section 5.1.11 Cross-Database Ownership Chaining.
BLANKSAPWD=1 | Overrides the default behavior of the installer, which is to prohibit installation with a blank sa password. If you set BLANKSAPWD=1, the installer allows installation with a blank sa password. Warning The use of blank passwords is strongly discouraged.
INSTANCENAME= instance_name | Specifies the name for the instance. If no instance name is specified, the default instance name MSSQLSERVER is used.
SAPWD=sa_password | Specifies the sa password. This option can be used for new installations. Unlike other properties, the SAPWD property is hidden and the value is not written to the log file.
SECURITYMODE=SQL | Specifies that the instance use SQL Server Authentication. In Windows NT 4.0, Windows 2000, or Windows XP, if SECURITYMODE=SQL is not specified, Windows Authentication is used and the Windows local administrator's group is added to the SQL Server sysadmin fixed server role. Important When possible, use Windows Authentication.
UPGRADEUSER=sa | Specifies the login to be used when you upgrade Desktop Engine using SQL Server Authentication. The login must be a member of the sysadmin fixed server role.
UPGRADEPWD= sa_password | Specifies the password for the login that is used when you upgrade Desktop Engine using SQL Server Authentication.
UPGRADE=1 | Used when upgrading an instance of Microsoft Desktop Engine version 1.0 to SQL Server 2000 Desktop Engine SP3. The only value supported is 1.

Setup parameters can be specified either in the command line or in an .ini file. For information, see the topic "Customizing Desktop Engine Setup.exe" in SQL Server Books Online.

Note When using the SQL Server 2000 Desktop Engine merge modules as an integrated part of a custom setup, you may not be able to customize the Desktop Engine installation using these command-line parameters. For more information, see Knowledge Base article 810826.

Important Avoid storing credentials in an .ini file.

The following setup switches are used when upgrading, installing, or uninstalling Desktop Engine.

Switch name | Description
--- | ---
/upgradesp <setup_path>\SqlRunXX.msi | SQLRUN | For SP3 and later, this switch replaces the /p switch that you use when specifying the location of packages. When upgrading to SP3, if you know the number of the original MSI that was used to install Desktop Engine, specify it along with the full path to the location of the SP3 .msi file with the same name. Or, you can specify SQLRUN if you specify your instance name with the INSTANCENAME parameter. For examples of how to use this new switch, see the following section.
/l*v [filename] | This standard setup switch is useful when troubleshooting an installation. It specifies that a verbose log be created. If filename is specified, the log is stored in the file specified.
/x package_name | This standard setup switch is used when uninstalling Desktop Engine. You must specify the name of the Windows Installer installation package (.msi) file that was used to install the instance of Desktop Engine.

For information about other Setup arguments, see the topic "Customizing Desktop Engine Setup.exe" in SQL Server Books Online.

#### Upgrading an Existing Instance of Desktop Engine

SP3 introduces a new switch for upgrading Desktop Engine. The /upgradesp switch replaces the /p switch. When using this new switch, you have two options, depending on the information that you have about the instance of Desktop Engine that you are upgrading. You must either know the instance name (the default is MSSQLSERVER) or you must know which .msi file was originally used to install Desktop Engine.

If you know the instance name, use the /upgradesp switch as shown in the following example:

    Setup.exe /upgradesp SQLRUN INSTANCENAME=instance_name SECURITYMODE=SQL
    UPGRADEUSER=<username> UPGRADEPWD=<password>

Important If you use an .ini file during setup, avoid storing credentials in the .ini file.

In this case, the installer uses the instance name to determine which .msi file was originally used to install Desktop Engine.

Note When upgrading the default instance, you do not have to specify INSTANCENAME.

Desktop Engine can be installed with one of 16 .msi files (SqlRun01.msi through SqlRun16.msi). If you know the name of the original .msi file that was used to install Desktop Engine, you can specify it along with the complete path to the SP3 .msi file of the same name, as shown in the following example:

    Setup.exe /upgradesp c:\SQL2KSP3\MSDE\Setup\SqlRun03.msi SECURITYMODE=SQL
    UPGRADEUSER=<username> UPGRADEPWD=<password>

Important If you use an .ini file during setup, avoid storing credentials in the .ini file.

In this example, C:\SQL2KSP3\MSDE\Setup represents the location of the SP3 .msi files and SqlRun03.msi is the name of the .msi file used in the original installation.

If you do not know either the instance name or the name of the original .msi file, see Microsoft Knowledge Base article 311762. This article describes how to determine the name of the original .msi file. You can access the article from the Microsoft Product Support Services Knowledge Base.

##### Upgrading SQL Server 2000 Desktop Engine

To upgrade SQL Server 2000 Desktop Engine

1. Run Setup.exe to initiate setup. (Do not run Setup directly from the .msi file.)
2. If the instance name is known, use /upgradesp SQLRUN INSTANCENAME=instance_name

   -or-

   If the name of the original .msi file is known, use /upgradesp and specify the name of the .msi file along with the complete path to the SP3 .msi file of the same name.

3. Specify SECURITYMODE=SQL.
4. Use the UPGRADEUSER parameter to specify a login account for Setup to use that is a member of the sysadmin fixed server role.
5. Use the UPGRADEPWD setup parameter to specify the password for the chosen login account.

Important If you use an .ini file during setup, avoid storing credentials in the .ini file.

Note If you use BLANKSAPWD=1, you are not required to specify SECURITYMODE=SQL or UPGRADEUSER and UPGRADEPWD.

Warning The use of blank passwords is strongly discouraged.

Note When upgrading Desktop Engine on a computer running Windows 98 or Windows Millennium Edition, the instance of Desktop Engine that you are upgrading must be stopped before starting Setup.

##### Upgrading from Desktop Engine version 1.0

To upgrade from Desktop Engine version 1.0

This service pack supports the capability to upgrade an instance of Desktop Engine version 1.0 directly to SQL Server 2000 Desktop Engine SP3. To upgrade Desktop Engine:

1. Stop the instance of Desktop Engine 1.0 that you are upgrading.
2. Run Setup.exe to initiate setup. (Do not run Setup directly from the .msi file.)
3. Specify UPGRADE=1

If you are using SQL Server Authentication, you also need to do the following:

4. Specify SECURITYMODE=SQL.
5. Use the UPGRADEUSER parameter to specify a login account for Setup to use that is a member of the sysadmin fixed server role.
6. Use the UPGRADEPWD setup parameter to specify the password for the chosen login account.

Note If you use BLANKSAPWD=1, you are not required to specify SECURITYMODE=SQL or UPGRADEUSER and UPGRADEPWD.

Warning The use of blank passwords is strongly discouraged.

Important If you use an .ini file during setup, avoid storing credentials in the .ini file.

##### Installing a New Instance of Desktop Engine

SP3 allows you to install a new instance of Desktop Engine.

To install a new instance of Desktop Engine

- Run Setup.exe to initiate Setup. (Do not run Setup directly from the .msi file.)
- If you are using SQL Server Authentication, specify SECURITYMODE=SQL.
- Regardless of the authentication mode, you must do one of the following:
  - Specify a password for the sa login using the SAPWD setup parameter.
  - Specify the BLANKSAPWD=1 parameter.

  Note If both SAPWD and BLANKSAPWD are specified, SAPWD takes precedence and BLANKSAPWD is ignored.

  Important If you are using an .ini file during setup, avoid storing credentials in the .ini file.

  Warning The use of blank passwords is strongly discouraged.

For additional information, see the topic "Installing Desktop Engine" in SQL Server Books Online. For information about the appropriate uses of Desktop Engine, see the Microsoft SQL Server Web site for Desktop Engine.

##### Redistributing the Service Pack

It is recommended that ISVs who want to redistribute this service pack do so in one of the following ways:

- For installations that use Setup.exe, install the service pack on an instance of Desktop Engine that is configured to match the one distributed with your software.
- For installations that use merge modules, generate your own patch package based on the differences between the old and new installations, and then distribute your custom patch to your customers.

For more information about creating patch packages, see Knowledge Base article 810826 or the documentation included with the Windows Installer Software Development Kit (SDK), which can be downloaded from the Microsoft Platform SDK Web site.

Note You can redistribute a complete copy of SP3.

## 3.8 Restart Services

The following information applies to all components.

When Setup completes, it may prompt you to restart the system. After the system restarts (or after Setup completes without requesting a restart), use the Services application in Control Panel to make sure that MS DTC and the Microsoft Search, MSSQLServer, MSSQLServerOLAPService, and SQLServerAgent services or their instance-specific equivalents are running. Back up the upgraded master and msdb databases.

## 3.9 Restart Applications

The following information applies to all components.

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

## 3.10 Installing 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 SP3. If you do not do this, the installation of SP3 may cause those dependent resources to fail over.
2. Run the service pack from the node that owns the group containing the virtual server that you are going 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 are upgrading.
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 above, add back the dependencies or bring the resources back online.

Note Setup might require rebooting of the failover cluster nodes. This 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, first, you must upgrade the default (non-clustered) instance to a virtual instance, and then apply SP3. 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 Books Online.

For additional information on installing SP3 on a failover cluster, see Knowledge Base article 811168.

If you need to rebuild a node in the failover cluster, perform the following steps

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 Books Online.
2. Run the original SQL Server 2000 Setup program to add the node back to the failover cluster.
3. Run SP3 Setup.

When installing Analysis Services SP3 on a cluster, each instance must be upgraded separately.

To install SP3 on an Analysis Services cluster

1. Install SP3 on a failover node.
2. Fail over to the newly upgraded node.
3. Repeat steps 1 and 2 until all instances in the cluster are upgraded.

## 3.11 Installing on Replicated Servers

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

- You must upgrade the Distributor before you upgrade the Publisher
- For replication topologies based on transactional replication with read-only Subscribers, you can upgrade the Subscriber before or after the Publisher and Distributor.
- For replication topologies 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.

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

For replication topologies based on merge replication or transactional replication with updating Subscribers that include one or more servers that act as both a Subscriber and a Publisher (or Distributor), you might need to quiesce the system (in other words, stop all updates) and upgrade all servers simultaneously.

###### 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 you to 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

##### Additional Replication Installation Issues

Important Before you upgrade to SP3, 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 under the context of the SQL Server service account. After upgrading to SP3, you should remove the Windows account from the sysadmin role.

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

SP3 introduces a change in the requirements for attaching or restoring replication databases. For more information, see Section 5.3.17 Change to Requirements for Attaching or Restoring a Replication Database.

SP3 Setup upgrades user databases that are members of a replication topology. If any of the databases that are members of a replication topology are non-writable, to apply SP3 to those databases you must make them writable, and then reapply SP3 Setup. For more information about making a database writable, see Section 3.12 Applying SP3 to Read-Only Databases or Filegroups. For information about reapplying SP3, see Section 3.14 Reapplying SP3.

An existing backup scheme that accounts for replication allows you to restore a database to a known point after the SP3 upgrade in case of a failure. After applying SP3, 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 SP3 after the database is restored.

## 3.12 Applying SP3 to Read-Only Databases or Filegroups

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 SP3 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.

Note 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 is in any of these conditions during setup, you must reapply the service pack. For more information about bringing a database online, see the topic "Attaching and Detaching a Database" in SQL Server Books Online. For more information about diagnosing suspect databases, see the topic "Server and Database Troubleshooting" in SQL Server Books Online.

To apply SP3 to a read-only database

1. Make the read-only database writable 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) the service pack.
4. If required, make the database read-only again using ALTER DATABASE, as follows:

       ALTER DATABASE database SET READ_ONLY

To apply SP3 to a read-only filegroup

1. Make the read-only filegroup writable 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 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 Books Online. For more information about reapplying SP3, see Section 3.14 Reapplying SP3.

## 3.13 Uninstalling SP3

They way in which you remove SQL Server SP3 depends on the SQL Server 2000 SP3 components that you are removing.

Note MDAC updates are not uninstalled. For more information, see Section 5.5.1 Updates to Microsoft Data Access Components.

#### Uninstalling SQL Server 2000 Components and Desktop Engine SP3

To revert to the pre-SP3 versions of SQL Server 2000 components, you must have back ups of the master, msdb, and model databases taken prior to installing SP3. For more information, see Section 3.1 Back Up Your SQL Server Databases.

Note 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 the pre-SP3 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, and then select the instance of SQL Server that you want to uninstall.
3. Install SQL Server 2000 from the CD-ROM or the location from which you originally installed SQL Server.
4. Apply any service packs and QFE fixes that were installed prior to SP3.
5. Restore the databases master, msdb, and model from the last backup that was created before applying SP3. This automatically attaches any user databases that were attached at the time the backup was created, assuming that the location of the data files has not changed.
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-SP3 version of SQL Server, all changes made to the databases master, msdb, and model since applying SP3 are lost.

#### Uninstalling SQL Server 2000 Analysis Services SP3

To be able to return Analysis Services to its pre-SP3 state, you must back up the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server registry key prior to installing SP3. For more information, see Section 3.2 Back Up Your Analysis Services Databases and Repository.

Note If you did not backup this registry key, you must follow the process documented in Microsoft Knowledge Base article 330244.

To revert to the pre-SP3 version of SQL Server

1. Uninstall SQL Server 2000 Analysis Services. In Control Panel, double-click Add/Remove Programs, click SQL Server 2000 Analysis Services, and click Remove.
2. Reinstall SQL Server 2000 Analysis Services from the CD-ROM or the location from which you originally installed Analysis Services.
3. Apply any service packs and QFE fixes that were installed prior to installing SP3.
4. Remove the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server registry key.
5. Reinstall the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server registry key from the pre-SP3 backup.

## 3.14 Reapplying SP3

The following information applies to all components.

In the following cases, you must reapply SP3:

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

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

# 4.0 Additional Installation Considerations

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

## 4.1 Unattended Installations

Database Components SP3 can be applied in unattended mode to an instance of SQL Server. The Database Components SP3 CD-ROM contains .iss files that can be used to perform unattended SP3 setups and other types of installations. The following files are located in the root directory on the CD-ROM:

- Sql2kdef.iss is the unattended setup file used to apply Database Components SP3 to a default instance of SQL Server 2000 running on Windows 2000 or Windows NT 4.0. The corresponding batch file to use is Sql2kdef.bat.
- Sql2knm.iss is the setup file used to apply Database Components SP3 to a named instance of SQL Server 2000 running on Windows 2000 or Windows NT 4.0. You must change the instance name in this .iss file to identify the instance that is being upgraded.
- Sql2ktls.iss is the setup file used to apply Database Components SP3 to a tools-only installation. This file can be used for installation without any modifications.
- Sql2k9x.iss is the setup file used to apply Database Components SP3 on a computer running Windows Millennium Edition or Windows 98. You must change the instance name in this .iss file to identify the instance being upgraded.
- Sql2kcli.iss is the setup file used to apply Database Components SP3 to a client component installation on a computer running Windows 95.
- Msolap.iss is the setup file that stores the list of parameters for running an unattended setup of Analysis Services SP3.

For more information about performing unattended installations of SQL Server 2000, see the topic "Performing an Unattended Installation" in SQL Server Books Online.

##### Unattended Installation Considerations

The following considerations relate to unattended installations:

- Run unattended installations using Windows Authentication whenever possible.
- If you must run an unattended setup using SQL Server Authentication; for example if you are running an unattended setup of SP3 on a computer running Windows 98 or Windows Millennium Edition, you should not include a password for the sa login in the .iss file or batch file because these files are not encrypted. Instead, you should supply the password for the sa login in the command line using the sapwd parameter as follows:

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

- If the SQL Server instance being upgraded to SP3 has a blank password for the sa login account, a password must be provided prior to running an unattended installation. You should change this password by running sp_password.
- When running an unattended installation of SP3, you must manually set the following setup switches that correspond to the Backward Compatibility Checklist dialog box or the unattended installation will fail.

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.5 Full-Text Catalogs are Rebuilt After Setup Completes.
MSXTSXUpgraded | This switch is required to address the issue regarding upgrading master/target server configurations. If you are applying SP3 to a master or target server, you must set this switch to 1. For more information, see Section 5.4.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.11 Cross-Database Ownership Chaining.

- 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 listed in the Setup log are members of a replication topology, you must make those databases writable and reapply SP3 Setup to that instance of SQL Server 2000. For more information about making a database writable, see Section 3.12 Applying SP3 to Read-Only Databases or Filegroups. For more information about reapplying SP3, see Section 3.14 Reapplying SP3.

## 4.2 Redistributing SP3 Data Access Components

Database Components SP3 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.7 Service Pack 1. This installs the MDAC 2.7 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 SP3. For more information, see 5.5.1 Updates to 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 SP3.

# 5.0 Documentation Notes

This section covers issues that can occur in addition to new features that are available when you run SP3. These issues apply when running the service pack to upgrade from SQL Server 2000, SQL Server 2000 SP1, or SQL Server 2000 SP2. This section is not meant to be a description of all of the fixes provided in SP3. For a complete list of these fixes, see Microsoft Knowledge Base article 306908.

The Analysis Services and Meta Data Services segments in this section do not apply to Desktop-Engine-only installations.

Any information relevant to SQL Server 2000 Service Pack 3 that was not available in time to be included in this readme file will be published in Microsoft Knowledge Base article 330022. This article is available at the Microsoft Product Support Services Knowledge Base.

## 5.1 Database and Desktop Engine Enhancements

The following enhancements apply to SQL Server 2000 instances on which Database Components SP3 is installed. They also apply to Desktop Engine instances on which Desktop Engine SP3 is installed.

### 5.1.1 Using Chinese, Japanese, or Korean Characters with Database Components SP3

Introduced in SP1

If you install Database Components SP3 on a server running Windows NT 4.0 or Windows 98 and later upgrade to Windows 2000, the Windows 2000 upgrade replaces certain system files. These system files are necessary for sorting Chinese, Japanese, or Korean characters. If you use Chinese, Japanese, or Korean characters in your SQL Server databases, rerun the version of Sqlredis.exe included with SP3 after you upgrade to Windows 2000. For more information about running Sqlredis.exe, see Section 4.2 Redistributing SP3 Data Access Components.

Note You do not have to reapply Sqlredis.exe on client computers or on servers that do not have databases containing Chinese, Japanese, or Korean characters.

### 5.1.2 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.

### 5.1.3 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 in the Microsoft Product Support Services Knowledge Base. For instructions on searching the Knowledge Base, see Section 1.3 Additional Information About SP3.

##### 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 in the Microsoft Product Support Services Knowledge Base.

### 5.1.4 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. For more information, see the Microsoft Product Support Services Knowledge Base.

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

Introduced in SP3

All full-text catalogs are rebuilt as part of the installation of SP3. 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 SP3 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.

### 5.1.6 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

### 5.1.7 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.

### 5.1.8 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. If this option is set to 1, SQL Server can push queries with LIKE predicates to the provider. Previously, if the provider was not the SQL Server OLE DB Provider, distributed queries that contained LIKE predicates were always evaluated at the local instance of SQL Server.

### 5.1.9 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, 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].

### 5.1.10 New Function fn_get_sql Returns SQL Statement

Introduced in SP3

SP3 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, which are listed in the following table.

Column name | Data type | Description
--- | --- | ---
sql_handle | binary(20) | Represents the currently executing batch or object.
stmt_start | int | Starting offset of the current SQL statement for the specified sql_handle.
stmt_end | int | Ending offset of the current SQL statement for the specified sql_handle. A value of -1 indicates that the current statement runs to the end of the results returned by the fn_get_sql function for the specified sql_handle.

##### Syntax

fn_get_sql ([ @SqlHandle = ] SqlHandle )

##### Arguments

[ @SqlHandle = ] SqlHandle

The handle value. SqlHandle is binary(20).

##### Tables Returned

Column name | Data type | Description
--- | --- | ---
dbid | smallint | Database ID. This value is NULL for ad hoc SQL statements.
objectid | Int | ID of the database object. This value is NULL for ad hoc SQL statements.
number | smallint | The number of the group, if the procedures are grouped. This value is 0 for entries that are not procedures and NULL for ad hoc SQL statements.
encrypted | Bit | Indicates whether the object is encrypted. The value is 0 if the object is not encrypted and 1 if the object is encrypted.
text | Text | Text of the SQL statement. This value is NULL for encrypted objects.

##### Remarks

You can get a valid SQL handle from the sql_handle column of the sysprocesses system table.

If you pass a handle that no longer exists in cache, fn_get_sql returns an empty result set. If you pass a handle that is not valid, the batch stops, and the following error message is returned:

`Server: Msg 569, Level 16, State 1, Procedure fn_get_sql, Line 12 The handle passed to fn_get_sql was invalid.`

SQL Server 2000 cannot cache some Transact-SQL statements, such as bulk copy statements and statements with string literals larger than 8 KB. You cannot retrieve handles to those statements by using the fn_get_sql function.

The text column of the result set is filtered for text that may contain passwords.

The information returned by the fn_get_sql function is similar to the DBCC INPUTBUFFER command. Use the fn_get_sql function when the DBCC INPUTBUFFER cannot be used, for example:

- When events have more than 255 characters.
- When you have to return the highest current nesting level of a stored procedure. For example, there are two stored procedures that are named sp_1 and sp_2. If sp_1 calls sp_2 and you get the handle from the sysprocesses system table while sp_2 is running, the fn_get_sql function returns information about sp_2. Additionally, the fn_get_sql function returns the complete text of the stored procedure at the highest current nesting level.

##### Permissions

Only members of the sysadmin fixed server role can run the fn_get_sql function.

##### Examples

Database administrators can use the fn_get_sql function to help diagnose problem processes. After an administrator identifies a problem server process ID (SPID), the administrator can retrieve the SQL handle for that SPID, call the fn_get_sql function with the handle, and use the start and end offsets to determine the SQL text of the problem SPID. For example:

    DECLARE @Handle binary(20)
    SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52
    SELECT * FROM ::fn_get_sql(@Handle)

### 5.1.11 Cross-Database Ownership Chaining

Introduced in SP3

This service pack provides new options for turning cross-database ownership chaining on and off. During Setup, the 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.

Note If you previously enabled cross-database ownership chaining on a pre-release version of SP3 (earlier than build 8.00.760), you must enable it again after installing the release version of SP3.

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.

### 5.1.12 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.

### 5.1.13 Permissions Change for sp_changedbowner

Introduced in SP3

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

### 5.1.14 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. This article is available at the Microsoft Product Support Services Knowledge Base.

## 5.2 Analysis Services Enhancements

This section discusses enhancements to SQL Sever 2000 Analysis Services that are included with SP3.

### 5.2.1 Remote Partitions

Introduced in SP1

When a remote partition is created on a local server that has SP1 or a later version installed on it, the remote server must use a domain user account that has full access permissions to the parent cube on the local server. Any user account that is a member of the OLAP Administrators group on the local server has full access permissions.

In addition, if the local server has SP1 or a later version installed on it, the remote server also must have SP1 or a later version installed on it so that the local server can create or administer remote partitions.

### 5.2.2 Updated Analysis Services Redistributable Client Setup

Introduced in SP1

Analysis Services SP1 and later versions include updated versions of the following client redistributable setup programs:

- PTSLite.exe
- PTSFull.exe

These files are located in the \Msolap\Install\PTS path of the SP3 installation directory.

Note PTSFull.exe includes MDAC; PTSLite.exe does not.

Use these updated client setup programs in your applications to prevent or solve client setup issues that you may encounter when using Analysis Services and Microsoft Office XP.

Note When using Analysis Services with Office XP, upgrading the client is highly recommended.

### 5.2.3 Support Enabled for Third-Party Data Mining Algorithm Providers

Introduced in SP1

Analysis Services SP1 and later versions include support for the addition of third-party data mining algorithm providers. For more information about developing a data mining algorithm provider, see the "Third Party Data Mining Providers" white paper and the OLE DB for Data Mining Resource Kit, which includes the code for a sample data mining algorithm provider.

### 5.2.4 Installing Analysis Services on a Computer with Updated Client Files

Introduced in SP1

If you install SQL Server 2000 Analysis Services on a computer that contains updated client files, such as SQL Server 2000 SP1 or Office XP, you must apply Analysis Services SP1 or a later version to ensure that the client works properly and that you can browse through cubes.

### 5.2.5 Increased Limit for OLAP Cubes Referenced by a Virtual Cube

Introduced in SP3

Virtual cubes can now reference up to 255 cubes. However, any virtual cube that references more than 64 cubes is not visible to any version of Microsoft PivotTable® Service released before SP3.

### 5.2.6 New DESCRIPTION Keyword

Introduced in SP3

Local cubes now support the intrinsic member property DESCRIPTION for measures and dimensions. The DESCRIPTION keyword, added to the CREATE CUBE Multidimensional Expressions (MDX) statement, is used to support the use of the DESCRIPTION intrinsic member property. The following BNF clauses describe the changes made to the CREATE CUBE statement:

    <dimensions def> :: = DIMENSION <dimension name> [<time def>]
    [DIMENSION_STRUCTURE <sub_type>] [<hidden def>] [DESCRIPTION <description expression>]
    <options def> <comma> <hierarchy def list>
    <measures def> :: = MEASURE <measure name> <measure function def>
    [<measure format def>] [<measure type def>] [<hidden def>] [DESCRIPTION <description expression>]
    [<comma> <measures def>]

### 5.2.7 New PivotTable Service Restricted Client Property

Introduced in SP3

SP3 implements a new PivotTable Service connection string property, Restricted Client. This property can be used to restrict PivotTable Service from using local cube functionality. Any attempt to use a statement that involves the creation or use of a local cube, such as the CREATE CUBE, CREATE GLOBAL CUBE, and CREATE SESSION CUBE statements, raises an error. Additionally, any statement that involves deep recursion, such as a series of nested DRILLDOWN statements, raises an error if the statement can potentially overflow the statement stack maintained by PivotTable Service.

This property stores a string value. If the value is set to any string value starting with the characters "Y", "y", "T", or "t", or any string value that can be converted to a numeric value other than 0, PivotTable Service is restricted as described earlier. If the value is set to any other string value, including an empty string (""), or any string value that can be converted to a numeric value equal to 0, PivotTable Service is unrestricted. The default value for this property is "0".

Note This property does not restrict the use of local data mining models.

### 5.2.8 Change in the Safety Options Property

Introduced in SP3

Setting the Safety Options property to DBPROP_MSMD_SAFETY_OPTIONS_ALLOW_SAFE also prevents usage of the PASSTHROUGH keyword in local cubes.

### 5.2.9 Migrate Repository to Meta Data Services Disabled by Default

Introduced in SP3

The option to use the Meta Data Services repository format in the Migrate Repository Wizard has been disabled in SP3, and using this format is not recommended. However, if this format is required for business reasons, you can enable the option through the EnableMigrationToMetaDataServicesFormat registry key.

By default, this registry key does not exist; it must be manually created at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\Server Connection Info and set to enable repository migration to the Meta Data Services format. This registry key stores a case-sensitive string value. If the value is set to 1 or True, repository migration to the Meta Data Services format is enabled. If the value is set to any other string value, or if the registry key does not exist, repository migration to the Meta Data Services format is disabled.

Note If the value of this registry key is changed, the change takes effect immediately.

### 5.2.10 Permissions Must be Modified on a Remote Data Folder

Introduced in SP3

If the Analysis Services Data folder is located on a computer other than the one on which the Analysis server is running, you must modify the permissions on the folder after running SP 3 Setup. Set the permissions on the folder to allow full control access by all members of the OLAP Administrators group on the computer running the Analysis server. Over time, as members are added to or removed from the OLAP Administrators group, be sure to modify the permissions on the remote Data folder to so that it reflects the current membership of the OLAP Administrators group. This will allow backup and restore to function properly.

Additionally, after running SP3 Setup, you must allow the account under which the Analysis server is running full control access to the remote Data folder.

If you apply SP3 to an instance of Analysis Services running in a clustered configuration, the domain-level OLAP Administrators group must be allowed full control access to the remote Data folder. For more information about creating and using a domain-level OLAP Administrators group, see support article 308023 available at the Microsoft Product Support Services Knowledge Base.

## 5.3 Replication Enhancements

This section discusses enhancements to SQL Sever 2000 replication that are included with SP3.

### 5.3.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

The stored procedure sp_scriptdynamicupdproc generates the CREATE PROCEDURE statement that creates a dynamic update stored procedure. The UPDATE statement within the custom stored procedure is built dynamically, based on the MCALL syntax that indicates which columns to change. Use this stored procedure if the number of indexes on the subscribing table is growing and the number of columns being changed is small. This stored procedure is run at the Publisher on the publication database.

##### Syntax

sp_scriptdynamicupdproc [ @artid =] artid

##### Arguments

[@artid =] artid

The article ID. artid is an int, with no default.

##### Result Sets

Returns a result set that consists of a single nvarchar(4000) column. The result set forms the complete CREATE PROCEDURE statement that is used to create the custom stored procedure.

##### Remarks

sp_scriptdynamicupdproc is used in transactional replication. The default MCALL scripting logic includes all columns within the UPDATE statement and uses a bitmap to determine the columns that have changed. If a column did not change, the column is set back to itself, which usually causes no problems. If the column is indexed, extra processing occurs. By contrast, this stored procedure uses a dynamic approach: it includes only the columns that have changed, which provides an optimal UPDATE string. However, extra processing is incurred at run time when the dynamic UPDATE statement is built. It is recommended that you test both the dynamic stored procedure approach and the static default approach, and then choose the optimal solution for your particular needs.

##### Permissions

Members of the public role can execute sp_scriptdynamicupdproc.

##### Examples

This example creates an article (with artid set to 1) on the authors table in the pubs database, and specifies that the UPDATE statement is the custom stored procedure to execute:

    'MCALL sp_mupd_authors'

Generate the custom stored procedures to be executed by the Distribution Agent at the Subscriber by running the following stored procedure at the Publisher:

    EXEC sp_scriptdynamicupdproc @artid = '1'
    The statement returns:

    create procedure [sp_mupd_authors]
      @c1 varchar(11),@c2 varchar(40),@c3 varchar(20),@c4 char(12),@c5 varchar(40),@c6 varchar(20),
      @c7 char(2),@c8 char(5),@c9 bit,@pkc1 varchar(11),@bitmap binary(2)
    as

    declare @stmt nvarchar(4000), @spacer nvarchar(1)
    select @spacer =N''
    select @stmt = N'update [authors] set '

    if substring(@bitmap,1,1) & 2 = 2
    begin
      select @stmt = @stmt + @spacer + N'[au_lname]' + N'=@2'
      select @spacer = N','
    end
    if substring(@bitmap,1,1) & 4 = 4
    begin
      select @stmt = @stmt + @spacer + N'[au_fname]' + N'=@3'
      select @spacer = N','
    end
    if substring(@bitmap,1,1) & 8 = 8
    begin
      select @stmt = @stmt + @spacer + N'[phone]' + N'=@4'
      select @spacer = N','
    end
    if substring(@bitmap,1,1) & 16 = 16
    begin
      select @stmt = @stmt + @spacer + N'[address]' + N'=@5'
      select @spacer = N','
    end
    if substring(@bitmap,1,1) & 32 = 32
    begin
      select @stmt = @stmt + @spacer + N'[city]' + N'=@6'
      select @spacer = N','
    end
    if substring(@bitmap,1,1) & 64 = 64
    begin
      select @stmt = @stmt + @spacer + N'[state]' + N'=@7'
      select @spacer = N','
    end
    if substring(@bitmap,1,1) & 128 = 128
    begin
      select @stmt = @stmt + @spacer + N'[zip]' + N'=@8'
      select @spacer = N','
    end
    if substring(@bitmap,2,1) & 1 = 1
    begin
      select @stmt = @stmt + @spacer + N'[contract]' + N'=@9'
      select @spacer = N','
    end
    select @stmt = @stmt + N' where [au_id] = @1'
    exec sp_executesql @stmt, N' @1 varchar(11),@2 varchar(40),@3 varchar(20),@4 char(12),@5 varchar(40),
                                 @6 varchar(20),@7 char(2),@8 char(5),@9 bit',@pkc1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9

    if @@rowcount = 0
       if @@microsoftversion>0x07320000
          exec sp_MSreplraiserror 20598

After running this stored procedure, you can use the resulting script to manually create the stored procedure at the Subscribers.

### 5.3.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.

### 5.3.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.

### 5.3.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

In a publication in which the option to auto-generate a custom procedure schema is enabled, the stored procedure sp_scriptpublicationcustomprocs scripts the custom INSERT, UPDATE, and DELETE procedures for all table articles. sp_scriptpublicationcustomprocs is particularly useful for setting up subscriptions for which the snapshot is applied manually.

##### Syntax

sp_scriptpublicationcustomprocs [@publication]= publication_name

##### Arguments

[@publication]`=` publication_name

The name of the publication. publication_name is a sysname with no default.

##### Return Code Values

0 (success) or 1 (failure)

##### Result Sets

Returns a result set that consists of a single nvarchar(4000) column. The result set forms the complete CREATE PROCEDURE statement that is necessary to create the custom stored procedure.

##### Remarks

Custom procedures are not scripted for articles without the auto-generate custom procedure (0x2) schema option.

##### Permissions

Execute permission is granted to public; a procedural security check is performed inside this stored procedure to restrict access to members of the sysadmin fixed server role and db_owner fixed database role in current database.

##### Example

This example generates a script of the custom stored procedures in a publication named Northwind.

    exec Northwind.dbo.sp_scriptpublicationcustomprocs
    @publication = N'Northwind'

### 5.3.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 The -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 SP3, 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.

### 5.3.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.

### 5.3.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 SQL Server 2000 SP3 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 SQL Server 7.0 (the release version, SP1, SP2, or SP3) 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 SQL Server 2000 SP3 directly from a backup of a replicated database created in SQL Server 7.0, but replication settings are not maintained.

### 5.3.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. Otherwise, -MaxCmdsInTran is ignored.

### 5.3.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.

### 5.3.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

### 5.3.11 Merge Replication Uses New Role

Introduced in SP3

SP3 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, sp_createmergepalrole, to re-create the role. This stored procedure is executed at the Publisher on the publication database to re-create the role.

##### Syntax

sp_createmergepalrole [ @publication = ] 'publication'

##### Arguments

[@publication = ] 'publication'

The publication name. publication is sysname, with no default. This parameter is used to select the publication to use when re-creating a role used by merge replication.

##### Return Code Values

0 (success) or 1 (failure)

##### Remarks

Running sp_createmergepalrole adds a new row to the sysusers table for the new role. The name of this new role is based on the value of the pubid column in the sysmergepublications table for the given publication. The prefix of the role name is 'MSMerge_' and the pubid value is appended (without the hyphens) to the role name.

##### Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can run sp_createmergepalrole.

### 5.3.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.

### 5.3.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.5 Updated Books Online Documentation Is Available.

### 5.3.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.

### 5.3.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 SQL Server 2000 Books Online (Updated - SP3), this information is available when you click the Help button for the new page.

### 5.3.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.

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

Introduced in SP3

After SP3 has been applied, when a user attaches or restores a database that has been published for replication, the user must be a member of the sysadmin fixed server role and cross-database chaining must be enabled. If these conditions are not met, you can execute the sp_changedbowner stored procedure on the database to assign database ownership to the sa built-in administrator login. This ensures 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.11 Cross-Database Ownership Chaining.

## 5.4 SQL Server Agent Enhancements

This section discusses enhancements to SQL Server Agent that are included in SP3.

### 5.4.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.

### 5.4.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, 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.7.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 SP3, you must complete several steps to upgrade your SQL Server 2000 master/target server configuration. The changes that are introduced with SP3 are not compatible with SQL Server 7.0 target servers, or with any servers not running SP3. 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, SQL Server removes the old probe accounts because the TSX servers no longer use them.

2. Upgrade your TSX servers to SP3 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 SP3 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.4.3 New SQL Server Agent Extended Stored Procedure.

4. Apply SP3 to your master server. The old _msx_probe accounts are removed by SP3 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.

### 5.4.3 New SQL Server Agent Extended Stored Procedure

Introduced in SP3

SP3 includes a new extended stored procedure 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

The xp_sqlagent_msx_account extended stored procedure sets or retrieves the SQL Server Agent MSX account user name and password to or from the LSA secrets on the TSX server. Only members of the securityadmin fixed server role can run this extended stored procedure.

Before you can execute this extended stored procedure, SQL Server Agent must be running. In addition, if the account specified is a SQL Server login, SQL Server Agent must have local Windows administrator rights. SQL Server Agent stores the user name and password as an LSA secret, and access is restricted to local Windows administrators.

##### Syntax

xp_sqlagent_msx_account

{N'GET' |

N'SET' | N'DEL', N'MSX_domain_name', N'MSX_username', N'MSX_password'

}

##### Arguments

N'GET'

Retrieves the current SQL Server Agent MSX account. N'GET' is an nvarchar with no default. The password is not reported for security reasons.

N'SET'

Sets the account to be used as the SQL Server Agent MSX account. Use the MSX_username, and MSX_password parameters to specify the account to use as the SQL Server Agent MSX account. N'SET' is an nvarchar with no default.

N'DEL'

Deletes the SQL Server Agent MSX account.

'MSX_domain_name'

Reserved for future use.

'MSX_username'

The name of the Windows account to be used as the SQL Server Agent MSX account. Specify an empty string for this parameter and MSX_password to select Windows security. In this case, the SQL Server Agent service account credentials are used to log on to the MSX server. MSX_username is an nvarchar with no default.

'MSX_password'

The password for the SQL Server account specified in MSX_username. Specify an empty string for this parameter and the MSX_username to select Windows security. In this case, the SQL Server Agent service account credentials are used to log in to the MSX server. MSX_password is an nvarchar with no default.

Note Parameters for xp_sqlagent_msx_account must be specified in order. Named parameters cannot be used.

##### Return Code Values

Returns a value of 0 on success or 1 on failure.

When xp_sqlagent_msx_account fails and returns a value of 1, SQL Server generates an error message with information about the error.

##### Result Sets

If a SQL Server Agent MSX account has been set, xp_sqlagent_msx_account returns a result set with the following information when you specify N'GET'.

Column | Data type | Description
--- | --- | ---
domain | sysname | N/A. Reserved for future use.
username | sysname | Account used as the SQL Server Agent MSX account.

If a SQL Server Agent MSX account has not been set, or if N'SET' is specified, no result set is returned.

##### Permissions

Execute permissions for xp_sqlagent_msx_account default to members of the securityadmin fixed server role.

##### Examples

1. Retrieve the currently assigned SQL Server Agent MSX account

The following example retrieves the account currently assigned for use as the SQL Server Agent MSX account:

    EXEC master.dbo.xp_sqlagent_msx_account N'GET'

2. Set the SQL Server Agent MSX Account to use Windows Authentication

The following example sets the SQL Server Agent MSX account to use Windows Authentication:

    EXEC master.dbo.xp_sqlagent_msx_account N'SET',
       N'',  -- Reserved for future use
       N'',  -- MSX_username
       N''  -- MSX_password

###### C. Set the SQL Server Agent MSX account to SQL Server Authentication

The following example sets the SQL Server Agent MSX account to Ralph and specifies a password:

    EXEC master.dbo.xp_sqlagent_msx_account N'SET',
       N'',  -- Reserved for future use
       N'Ralph',  -- MSX_username
       N'RalphPwd'  -- MSX_password

###### D. Delete the SQL Server Agent MSX account

The following example deletes the SQL Server Agent MSX account. This means that SQL Server Agent defaults to Windows integrated security authentication.

    EXEC master.dbo.xp_sqlagent_msx_account N'DEL'

### 5.4.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.

## 5.5 SQL Server Connectivity Components Enhancements

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

### 5.5.1 Updates to Microsoft Data Access Components

Introduced in SP3

SP3 includes updates to the Microsoft Data Access Components (MDAC). When you install SP3, MDAC 2.7 Service Pack 1 is also installed. MDAC 2.7 SP1 has no feature changes from the version of MDAC installed by SQL Server 2000 (MDAC 2.6), but it does include fixes and security enhancements. MDAC 2.71 includes an upgrade to MSXML 3 SP3.

Note This version of MDAC is not installed if Setup detects the same version or a newer version.

For more information about this version of MDAC, see the Microsoft Universal Data Access Web site at this page on microsoft.com. Fixes included in this version of MDAC will be documented in Knowledge Base article 326848. You can access this article from the Microsoft Product Support Services Knowledge Base.

### 5.5.2 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.

## 5.6 Meta Data Services Enhancements

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

### 5.6.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 SP3 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.

### 5.6.2 Scripting Support Disabled

Introduced in SP3

Script support in information models has been disabled. After installing SP3, 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 You should always secure information models and the repository database to prevent unauthorized access.

### 5.6.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 has been 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.

## 5.7 Data Transformation Services Enhancements

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

### 5.7.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.

### 5.7.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. Beginning with SP3, 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.

### 5.7.3 SQL Server Agent Proxy Account Improvements

Introduced in SP2

Prior to SP3, 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 directory that was accessible to both the startup and the proxy accounts, such as C:\Temp. For SP3, DTS has been enhanced to use the system Temp folder if the user Temp folder is unavailable, greatly reducing the need for these adjustments.

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

Introduced in SP3

By default, SP3 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 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.

## 5.8 XML Enhancements

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

### 5.8.1 Improved Validation of XPath Expressions

Introduced in SP3

Prior to SP3, the version of MSXML that was installed with SQL Server 2000 (MSXML 2.6) 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.

When you apply SP3, an updated version of MSXML (3.0 SP3) is installed as part of the MDAC upgrade. For more information, see Section 5.5.1 Updates to Microsoft Data Access Components.

With the new version of MSXML, 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.

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 is installed.

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

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

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

## 5.9 Virtual Backup Device API Enhancements

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

### 5.9.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. SP3 now provides server-side support for freezing and capturing multiple databases in a single snapshot through the VDC_PrepareToFreeze command.

The Virtual Backup Device Specification in SP3 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 SP3 setup directory.

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

## 5.10 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.

## 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 SP3 CD-ROM in the folder \EQHotfix. Details about the English Query enhancement are available at the Microsoft Product Support Services Knowledge Base. Search the Knowledge Base for article 297105.

## 5.12 DB-Library and Embedded SQL for C

Introduced in SP1

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 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. When writing new applications, avoid using these components. When modifying existing applications, you are strongly encouraged to remove dependencies on these technologies. Instead of DB-Library or Embedded SQL for C, you can use ADO, OLE DB, or ODBC to access data in SQL Server. For more information about these technologies, see SQL Server Books Online.