Description of Service Pack 2 for SQL Server 2000: November 9, 2001 (889552)

MORE INFORMATION

1.0 Introduction

This release of Service Pack 2 (SP2) for Microsoft SQL Server 2000 is provided in three parts:

• Database Components SP2 provides updates for the database components of an instance of SQL Server 2000, excluding instances of the SQL Server 2000 Desktop Engine. Database Components SP2 includes upgrades to:
  • The data engine.
  • Database client tools and utilities such as SQL Server Enterprise Manager and osql.
  • 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 SP2 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 SP2 provides updates for the database components of an instance of the SQL Server 2000 Desktop Engine (also referred to as MSDE 2000), including:
  • The data 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 SP2 can be applied individually, as follows:

• SQL Server 2000 sites can use Database Components SP2 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 SP2 to upgrade their Analysis Services components without upgrading instances of the Desktop Engine or their database components.
• Desktop Engine sites can use Desktop Engine SP2 to upgrade instances of the Desktop Engine without upgrading Analysis Services or instances of other editions of SQL Server 2000.
  
  Note If you have separate instances of both the Desktop Engine and other editions of SQL Server on the same computer, you will need to apply the Desktop Engine SP2 to the Desktop Engine 2000 instances and the Database Components SP2 to the other instances of SQL Server 2000.

For more information about installing the Desktop Engine, see Section 2.2 "Desktop Engine SP2 file" and Section 3.7 "Install Desktop Engine SP2."

Note Desktop Engine SP2 is the only service pack part made available in Portuguese (Brazil), Swedish, and Dutch, because SQL Server 2000 Desktop Engine is the only version of SQL Server 2000 produced for those languages. The SQL Server 2000 components upgraded by Database Components SP2 or Analysis Services SP2 are not available in these languages. Portuguese (Brazil), Swedish, and Dutch users who want to apply Service Pack 2 to a version of SQL Server other than the Desktop Engine must download the Service Pack 2 files that match the language of the edition they will upgrade, such as downloading the English-language Service Pack 2 files if they are running an English-language version of SQL Server 2000. You will find download instructions below in Section 2.0 "Downloading and extracting SP2."
Details of Database Components SP2 installation
Database Components SP2 Setup automatically detects which edition of SQL Server 2000 is present on the instance of SQL Server 2000 being upgraded, and only upgrades the components that have been installed for that instance. For example, when the service pack is applied to a computer running SQL Server 2000 Standard Edition, it will not attempt to upgrade components that ship only with SQL Server 2000 Enterprise Edition.

Database Components SP2 can be applied to a single default instance or a named instance of SQL Server. If multiple instances of SQL Server 2000 need to be upgraded to SP2, you must apply SP2 to each instance. When one instance on a computer with one or more instances of SQL Server 2000 is upgraded to SP2, all of the tools will be upgraded to SP2. There are not separate copies of the tools for each instance on a computer.
Removing SP2
When the service pack 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. Due to these changes, SP2 cannot be removed easily. To revert to the build you were running before you installed SP2, you must first uninstall the instance of SQL Server 2000. Then you must reinstall that instance of SQL Server 2000. If you were running SQL Server 2000 SP1, then you will need to reapply SP1 to the instance. For more information about uninstalling SP2, see Section 3.13 "Uninstalling SP2."

Note To remove SP2, you must have a backup of the master, model, and msdb databases, taken immediately prior to applying SP2. For more information, see Section 3.1 "Back up your SQL Server databases' and Section 3.2 "Back up your Analysis Services databases."
Additional information about SP2
A list of the fixes contained in this service pack is provided in Microsoft Knowledge Base article Q306908. Each fix listed in 306908 has a link to a Knowledge Base article describing the problem addressed by the fix. These articles are published at the Microsoft Product Support Services Knowledge Base.
Follow the links to the FIX articles to see information about each fix.

Any information relevant to SQL Server 2000 Service Pack 2 that was not available in time to be included in this Readme file will be published in Microsoft Knowledge Base article Q306909. This article is available at the Microsoft Product Support Services Knowledge Base.
QFE fixes
If you received a QFE fix after October 3, 2001, the fix is not likely to be included in SP2. Please contact your primary support provider about a QFE fix against SP2.

back to the top

1.1 Identifying the current version of SQL Server or Analysis Services

Use the following techniques for finding out 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 at the command prompt when using the osql or isql utilities or in the Query window in SQL Query Analyzer. The following table shows the relationship between the version string reported by @@VERSION and the SQL Server 2000 version number.

@@VERSION	SQL Server 2000 version
8.00.194	SQL Server 2000 RTM
8.00.384	Database Components SP1
8.00.532	Database Components SP2

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 SQL Server edition. This is followed by current operating system information.

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

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

Help About	Analysis Services version
8.0.194	SQL Server 2000 Analysis Services RTM
8.0.382	Analysis Services SP1
8.0.532	Analysis Services SP2

back to the top

2.0 Downloading and extracting SP2

This service pack is distributed in three ways:

• On a SQL Server 2000 Service Pack 2 CD-ROM that contains service packs for the following components:
  • Database Components SP2
  • Analysis Services SP2
  • Desktop Engine SP2
  Note This CD-ROM includes all of the files required to upgrade existing instances of the Desktop Engine. The maximum number of instances supported for the Desktop Engine is 16.
• In three self-extracting files that can be downloaded from the Internet at the Microsoft SQL Server Downloads Web site:
  • SQL2KSP2.exe (Database Components SP2)
  • SQL2KASP2.exe (Analysis Services SP2)
  • SQL2KDeskSP2.exe (Desktop Engine SP2)
  Note The SQL2KDeskSP2.exe file includes an upgrade for a single instance of the Desktop Engine that was created only with sqlrun01.msi. For more information, see Section 2.2 "Desktop Engine SP2 file."
• On a SQL Server 2000 Desktop Engine Service Pack 2 CD-ROM (Desktop Engine only)

Note Only the SQL Server 2000 Desktop Engine Service Pack 2 CD-ROM contains the merge modules or .msi files that are required to install a new instance of the Desktop Engine.

back to the top

2.1 Database and Analysis Services SP2 files

When downloading and extracting Database Components or Analysis Services SP2 installation files from the Internet, please use the following guidelines.

Download one of the files listed in section 2.0, and place the self-extracting file into a folder on the computer that is running the instance of SQL Server 2000 on which you are installing this service pack. From that folder, execute the file. The self-extraction program will prompt you for the name of the folder into which you want the service pack files placed.

On the local drive where you have downloaded the service pack installation files, you will need free space approximately three times the size of the self-extracting file. This includes space to store the file, space for storing the extracted service pack files, and temporary working space required by the self-extraction program itself.

You can rename the chosen folder after extracting the components; however, make sure the directory name does not contain blank spaces. You can use the same target folder for each of the above files; they will not overwrite or interfere with each other.

The self-extracting files are used to build a set of directories and files on your computer that are the same as those present on the SP2 CD-ROM. If you download SP2 you must extract the files to build the service pack directories before you can run SQL Server Setup.

For Database Components and Analysis Services, the setup process is the same for both the SP2 CD-ROM and the extracted SP2 files.

Note Some of the files in service packs are system files, so 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.

back to the top

2.2 Desktop Engine SP2 file

When downloading and extracting the Desktop Engine SP2 installation file from the Internet, please use the following guidelines.

Download and extract the Desktop Engine SP2 file as described above for the Database Components and Analysis Services SP2 files, with the following exceptions.

If you download the Desktop Engine SP2 file from the Internet, you can apply the service pack only to instances of the Desktop Engine that were installed from sqlrun01.msi. If you attempt to apply the service pack to instances that were created using sqlrun02.msi - sqlrun16.msi from the Setup.exe file that was downloaded from the Internet, you will receive one of the following errors:
-or- To upgrade instances of the Desktop Engine that were created using sqlrun02.msi - sqlrun16.msi, you must apply the service pack from the Microsoft CD-ROM. You can order the SQL Server 2000 SP2 CD-ROM from Microsoft by visiting the Microsoft SQL Server Downloads Web site.

back to the top

3.0 Service pack installation

To install SP2, follow the installation instructions below. Not all steps are required, depending on which of the following SQL Server 2000 components or configurations to which the service pack is being applied:

• Database Server components
• Database Client 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.

Note Before you install SP2 on the French version of Windows NT 4.0, read and follow the instructions in Knowledge Base article 259484. You can access the article from the Microsoft Product Support Services Knowledge Base.

Before starting an installation
Changed from SP1

The following applies to all component installations except Analysis Services:

SP2 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 SP2. For example:

• You can restore a user database backup from an instance of SQL Server 2000 or SQL Server 2000 SP1 to an instance of SQL Server 2000.
• You can restore a user database backup from an instance of SQL Server 2000 SP2 to an instance of SQL Server 2000 SP2, to an instance of SQL Server 2000 SP1, or to an instance of SQL Server 2000.
• You can detach a user database from an instance of SQL Server 2000 or SQL Server 2000 SP1 and attach it to an instance of SQL Server 2000 SP2.
• You can detach a user database from an instance of SQL Server 2000 SP2 and attach it to an instance of SQL Server 2000 or SQL Server 2000 SP1.
• You can engage in log shipping between instances of SQL Server 2000, SQL Server 2000 SP1, and SQL Server 2000 SP2. SP2 Setup does upgrade user databases that are members of a replication topology.

Before installing SP2, make sure that replication databases and filegroups are writable and that the user account running Setup has permission to access the databases. For more information about applying SP2 to databases involved in replication topologies, see Section 3.11 "Installing on replicated servers."

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

• Applies the SP2 replication updates to all writable user databases.
• Writes a list of the non-writable databases to the Setup log, located at winnt\sqlsp.log.
• Displays the following warning message:
  Setup has detected one or more databases and filegroups which are not writable.

You may 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 SP2 Setup to that instance of SQL Server 2000.

Note This message will not affect unattended installations. To learn more about unattended installations, see Section 4.0 "Unattended installations."

For more information about making a database writable, see Section 3.12 "Applying SP2 to non-writable databases or filegroups." To learn more about reapplying SP2, see Section 3.14 "Reapplying SP2."

Note During installation, Setup makes no distinction between read-only databases and databases that are offline or suspect. If a replication database or filegroup is in any of these conditions during setup and is involved in a replication topology, the service pack must be reapplied after the database is made writable.

Note Since non-writable databases will no longer cause Setup to fail, you no longer need to remove log shipping before upgrading to SP2.

Preparing for a Systems Management Server distributed installation

SQL Server 2000 Service Pack 2 cannot be installed remotely. However, you can use Microsoft Systems Management Server to install SP2 automatically on multiple computers running Microsoft Windows NT Server 4.0. To do so, you must use a package definition file (Smssql2ksp2.pdf file) 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 running Systems Management Server. The Sms2kdef.bat file is a batch file that detects the platform of the computer and runs the appropriate version of the Setup program.

Systems Management Server cannot be used to install the Desktop Engine SP2.

back to the top

3.1 Back up your SQL Server databases

The following applies to all component installations except Database Client Components:

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

It is also prudent to backup your user databases, although SP2 will only perform updates in databases that are members of replication topologies.

back to the top

3.2 Back up your Analysis Services databases

The following applies only to Analysis Services:

Before installing Analysis Services SP2, back up your Analysis Services databases by making a copy of your 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, located in the Microsoft Analysis Services\bin folder. You also can 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\OLAP Server to a file for backup.

back to the top

3.3 Make sure that the system databases have enough free space

The following applies to all component installations except Database Client Components and Analysis Services:

If the autogrow option has not been 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 has been selected for the master and msdb databases, and there is sufficient room on the drives, you can skip this step.

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

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

• sp_helpdb master
• sp_helpdb msdb

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

back to the top

3.4 Stop services and applications before running SP2 Setup

The following applies to all components:

You can apply SP2 without shutting down services. If you do not shut down services, you will be prompted to reboot when Setup is complete. Without rebooting, the services will fail to start.

You can apply SP2 without restarting your computer by stopping the following services and applications before applying the service pack:

• Microsoft Distributed Transaction Coordinator (MS DTC) and the Microsoft Search and MSSQLServerOLAPService services.
• 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. This step is recommended, but not required.

You cannot stop services in a clustered environment. For more information, see section 3.10.

back to the top

3.5 Install Database Components SP2

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

Run the Setup.bat script from either of these locations:

• The directory containing the extracted service pack files from SQL2KSP2.exe.
• The service pack directory on the SQL Server 2000 SP2 CD-ROM.

Setup.bat opens a Setup 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 or Desktop Engine you are upgrading.

The Setup program then:

• Replaces the existing SQL Server 2000 or Desktop Engine files with the SP2 files.
• Runs several Transact-SQL script files to update system stored procedures.
• 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; help protect your systems by enforcing an sa password or by using Windows Authentication. To learn more about this dialog, see Authentication Mode Dialog 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:
  1. Stop the setup process, make these objects writable and restart the setup.
  2. Continue with the current SP2 setup and reapply SP2 at a later time after making these objects writable.
  Note The previous steps are only necessary when applying SP2 to non-writable databases or filegroups that are part of a replication topology. To learn more, see Section 3.12 Applying SP2 to non-Writable Databases or Filegroups.

Authentication Mode dialog

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

• On computers running Microsoft 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, the Setup program will change the sa login password. If you leave the password blank, the Setup program will connect and not change 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.

Before changing the authentication mode or the password for the sa login, make sure that this change will 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 will not be able to connect until the authentication mode is changed to use Windows Authentication. Also, if you change the sa login password, applications or administrative processes using the old password will not be able to connect until they have been changed to use the new password.

The Setup program places a record of the actions it performed in the Sqlsp.log file in the Windows directory of the computer on which it was run. If multiple instances are upgraded, only the most recent upgrade will be recorded in this log.

back to the top

3.6 Install Analysis Services SP2

The following applies only to Analysis Services:

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

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

Setup.exe opens a Setup dialog box that prompts you for information and then completes the installation.

back to the top

3.7 Install Desktop Engine SP2

The following applies only to Desktop Engine:

The service pack for the SQL Server 2000 Desktop Engine (also referred to as MSDE 2000) is intended for developers who create redistributable applications that use the Desktop Engine. If you are running an application that uses the Desktop Engine, contact your software provider for information about upgrading instances of the Desktop Engine installed by these applications. For more information, see the topic "Distributing the SQL Server 2000 Desktop Engine" in SQL Server Books Online.

The service pack for the Desktop Engine is provided in three forms:

• SQL Server 2000 Service Pack 2 CD-ROM: The CD-ROM includes all of the files required to upgrade existing instances of the Desktop Engine. The maximum number of instances supported for the Desktop Engine is 16. This CD-ROM does not include the merge modules or .msi files that are required to install a new instance of the Desktop Engine.
• SQL2KdeskSP2.exe: You can download this self-extracting file from the Microsoft SQL Server Downloads Web site. This file includes an upgrade for a single instance of the Desktop Engine that was created with sqlrun01.msi. The download cannot be used to upgrade instances of the Desktop Engine that were created using any of the other .msi files (sqlrun02.msi through sqlrun16.msi). The file does not include the merge modules or .msi files that are required to install a new instance of the Desktop Engine. For more information, see Section 2.2 "Desktop Engine SP2 File."
  
  Note To learn how to determine which .msi file was used to create an instance of MSDE, please refer to the Knowledge Base article Q311762. You can access the article from the Microsoft Product Support Services Knowledge Base.
• SQL Server 2000 Desktop Engine Service Pack 2 CD-ROM: This CD-ROM is available by request to MSDN Universal subscribers through Microsoft Product Support. It includes all of the files required to upgrade existing instances of the Desktop Engine. In addition, it contains the merge modules and .msi files that are required to install a new instance of the Desktop Engine. This CD-ROM is provided for developers who create applications that require the Desktop Engine. For more information, see "Distributing SQL Server Applications" in SQL Server Books Online.
  
  Note The SQL Server 2000 Desktop Engine Service Pack 2 CD-ROM does not include the Service Pack for SQL Server or Analysis Services.

The procedure for upgrading an existing installation of the Desktop Engine varies depending on which of the three versions of the SP2 software you use, and the medium (for example, CD-ROM or network share) from which you perform the upgrade. When upgrading the Desktop Engine, the Desktop Engine service pack must be applied to every instance of the Desktop Engine.

Requirements

Use Setup.exe to apply this service pack. The Setup.exe in Desktop Engine SP2 accepts the same parameters as the SQL Server 2000 Desktop Engine Setup.exe released with SQL Server 2000. The reference material for Setup.exe can be found in:

• The topic "SQL Server 2000 Desktop Engine Setup" in SQL Server 2000 Books Online.
• The following sections of the original SQL Server 2000 Readme.txt file:
  • 3.1.4
  • 3.1.23
  • 3.1.28
  • 3.1.29
  • 3.1.30

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) used to install the Desktop Engine. You can only install the Desktop Engine from a CD-ROM if the following conditions are met:

• The original installation of the Desktop Engine was not installed from a CD-ROM.
• If you create your own CD-ROM for installing SP2, the volume label on the CD-ROM must be SQL2KSP2. If you are an Independent Software Vendor (ISV) creating a CD-ROM for distributing the 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 Desktop Engine was originally installed from a CD-ROM or from a network share that has been moved, copy the MSDE subdirectory from the SQL Server 2000 Desktop Engine Service Pack 2 CD-ROM to your hard drive or the network share and run Setup.exe.

If you need to apply Service Pack 2 directly from the CD-ROM, you must first upgrade Windows Installer to version 2.0.2600.0 or later. This process will require that you reboot your computer.

To upgrade Windows Installer:

1. Make a note of the complete path of the original installation.
2. Run InstMsi20.exe.
3. Enter the complete path of the original installation in the text box.

Setup.exe is located in one of the following places:

• The MSDE subdirectory that is in the directory containing the extracted service pack files from SQL2KDeskSP2.exe.
• The location on your hard drive or the network share to which you copied the MSDE subdirectory from the SQL Server 2000 SP2 CD-ROM.

Note The setup procedure is different if you installed the Desktop Engine as a component of another application. Contact the software vendor for more information about upgrading the Desktop Engine.

Applying patch packages

The patch packages distributed in this Service Pack will upgrade the default numbered instances of MSDE (01-16) included on the SQL Server 2000 CD. To upgrade an instance of MSDE, you must apply its corresponding patch. For example, to upgrade an instance originally installed from SqlRun03.msi, you must use SQLRun03.msp.

Note Because these files are very large, the downloadable version of this Service Pack contains only one patch package: SQLRun01.msp. The other fifteen patch packages are on the SQL Server 2000 Service Pack 2 CD.

Use Setup.exe from the command line to apply a patch package to an installed instance of MSDE. If you know what installation package was used to install your instance of Desktop Engine, you can specify that Setup.exe use the corresponding patch with the /p option. For example:

Setup /p SQLRun03.msp

If you do not know what installation package was used, type SQLRUN after the /p option. The SQLRUN switch tells Setup.exe to choose the correct patch package itself. This will only work if the following conditions are met:

• All of the .msp files are located in a folder named Setup directly under the folder containing Setup.exe. This requires the SQL Server 2000 Service Pack 2 CD.
• When the instance to be upgraded is not the default instance, its instance name is specified with the INSTANCENAME variable.

For example, the following command will run Setup.exe to patch an installation of an instance named MyInstance:

Setup /p SQLRUN INSTANCENAME=MyInstance

The instance name can also be specified in an .ini file. For more information, see the topic "Customizing Desktop Engine Setup.exe" in SQL Server Books Online.

Note Do not use opening and closing quotation marks around the instance name.

If Setup cannot find the correct patch package, you will get an error message.

Note When using setup.exe to install MSDE SP2 on a computer running Windows 98 or Windows Millennium Edition, you must use the UPGRADEUSER switch to specify a login that is a member of the sysadmin fixed server role. For more information on UPGRADEUSER, see Section 3.1.23 of the original SQL Server 2000 Readme.txt file.
Redistributing the service pack

We recommend that independent software vendors (ISVs) who wish to redistribute this service pack do so as follows:

1. Install the Service Pack on an instance of Desktop Engine configured to match the one distributed with your software.
2. Generate your own patch package based on the differences between the old and new installations.
3. Distribute your custom patch to your customers.

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

Note You may redistribute a complete copy of SP2.

File locations

On the SQL Server 2000 Desktop Engine Service Pack 2 CD-ROM, the merge modules and installation files are located in \MSDE. For a complete listing, see "Using SQL Server Desktop Engine Merge Modules" in SQL Server Books Online.

back to the top

3.8 Restart services

The following applies to all components:

When the Setup program completes, it may prompt you to restart the system. After the system restarts (or after the Setup program completes without requesting a restart), use the Services application in Control Panel to make sure 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.

back to the top

3.9 Restart applications

The following applies to all components:

Restart the applications you closed before running the service pack setup.

back to the top

3.10 Installing on a failover cluster

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

To install this service pack on a failover cluster:

• Run this service pack from the node that owns the group containing the virtual server you are going to upgrade. This will install the service pack files on all failover cluster nodes.
• In the Setup dialog box, enter the name of the virtual server you are upgrading.
• Keep all nodes of the cluster online during setup. This ensures that the upgrade is applied to each node of the cluster.

Note SQL Server 2000 SP2 Setup may require rebooting the failover cluster nodes. This replaces the files that were in use during the SQL Server 2000 SP2 Setup with the updated files.

If you are upgrading a default (non-clustered) instance of SQL Server to a virtual server, you must first perform the upgrade from a default instance to a virtual instance, and then apply SQL Server 2000 SP2. 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.

If you need to rebuild a node in the failover cluster, you should:

• 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.
• Run the original SQL Server 2000 Setup to add the node back to the failover cluster.
• Run SQL Server 2000 SP2 Setup.

back to the top

3.11 Installing on replicated servers

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

Deploy this service pack across replicated servers in this sequence:

1. Distributor (if separate from the Publisher)
2. Publisher
3. Subscriber

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

In merge replication, the distribution database is used only to store agent history. Typically, the distribution database resides on the same computer as the published database. However, it is also possible to have a remote distribution database for merge replication at sites that want to centralize agent history logging.

You may not be able to upgrade all the servers in a replication topology immediately; however, replication operations are generally unaffected between servers running instances of SQL Server 2000 and this service pack. The exceptions to this are covered in Section 5.3.7 "Restoring replicated databases from different versions of SQL Server."

Note SP2 Setup does upgrade 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 SP2 to those databases you must make them writable and reapply SP2 Setup. For more information about making a database writable, see Section 3.12 "Applying SP2 to non-writable databases or filegroups." To learn more about reapplying SP2, see Section 3.14 "Reapplying SP2."

An existing backup scheme that accounts for replication will allow restoring a database to a known point after the SP2 upgrade in case of a failure. After applying SP2, a log or full database backup is recommended for any user database involved in a replication topology. This way, in case of a failure of a replication database, SP2 will not have to be reapplied after the database has been restored.

back to the top

3.12 Applying SP2 to non-writable databases or filegroups

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

When non-writable databases or filegroups exist, the Setup program will display the following message: Setup has detected one or more databases and filegroups which are not writable. In general, you may 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 SP2 Setup to that instance of SQL Server 2000.

Note This message will not affect unattended installations. To learn more about unattended installations, see Section 4.0 Unattended Installations.

Note During installation, Setup makes no distinction between non-writable databases and databases that are offline or suspect. If a database or filegroup is in any of these conditions during Setup, the service pack must be reapplied. For more information about bringing a database online, see "Attaching and Detaching a Database" in SQL Server Books Online, and for more information about diagnosing suspect databases, see "Server and Database Troubleshooting" in SQL Server Books Online.

To apply SP2 to a read-only database:

1. Make a read-only database writable using ALTER DATABASE as follows:

   ALTER DATABASE database
   SET READ_WRITE 

2. Repeat Step 2 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 SP2 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 

To learn more about ALTER DATABASE, see the "ALTER DATABASE" reference topic in SQL Server Books Online. To learn more about reapplying SP2, see Section 3.14 "Reapplying SP2."

back to the top

3.13 Uninstalling SP2

The following applies to all components except Analysis Services:

To revert to the pre-SP2 version of SQL Server 2000 components, you must have taken a backup of the master, msdb, and model databases prior to installing SP2. 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.

1. To disable publishing: 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. Select the Publication Databases tab.
3. Clear the check box for each database that is involved in replication. This will allow the databases to be detached.

Follow these steps to revert to the pre-SP2 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. Restore the databases master, msdb, and model from the last backup that was created before applying SP2. This automatically attaches any user databases that were attached at the time the backup was created.
5. Attach any user databases that were created after the last backup of the master database.
6. Configure replication if necessary.
   
   Warning When you revert to the pre-SP2 version of SQL Server, all changes made to the databases master, msdb, and model since applying SP2 are lost.

back to the top

3.14 Reapplying SP2

The following applies to all components:

In the following cases, it will be necessary to reapply SQL Server 2000 SP2:

• When new SQL Server 2000 components have been added after SP2 was applied.
• When databases or filegroups that are part of a replication topology and were read-only during the initial application of the service pack are made writable.

To reapply SP2, follow the steps in Section 3.0 "Service pack installation."

back to the top

4.0 Unattended installations

Database Components SP2 can be applied to an instance of SQL Server 2000 running on a computer in unattended mode. The Database Components SP2 CD-ROM contains ISS files that can be used to perform unattended SP2 setups. These files are located in the root directory on the CD-ROM and can be used to perform different types of installations:

• sql2kdef.iss is the unattended setup file used to apply Database Components SP2 to a default instance of SQL Server 2000 running on the Windows 2000 or Windows NT 4.0 operating system. The corresponding batch file to use is Sql2kdef.bat.
• sql2knm.iss is the setup file used to apply Database Components SP2 to a named instance of SQL Server 2000 running on the Windows 2000 or Windows NT 4.0 operating system. You must change the instance name in the ISS file to identify the instance that is being upgraded.
• sql2ktls.iss is the setup file used to apply Database Components SP2 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 SP2 on a computer running the Windows Millennium Edition or Windows 98 operating system. The instance name will need to be changed to identify the instance being upgraded.
• sql2kcli.iss is the setup file used to apply Database Components SP2 to a client components installation on a computer running the Windows 95 operating system.
• msolap.iss is the setup file that stores the list of parameters for running an unattended setup of Analysis Services SP2.

Note 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 SP2 Setup to that instance of SQL Server 2000. For more information about making a database writable, see Section 3.12 "Applying SP2 to non-writable databases or filegroups." To learn more about reapplying SP2, see Section 3.14 "Reapplying SP2."

back to the top

4.1 Redistributing SP2 Data Access Components

This service pack includes a self-extracting file, Sqlredis.exe, and a version of Redist.txt. These files are in the Database Components SP2. By default, when Sqlredis.exe is executed, it:

1. Executes the Mdac_typ.exe from Microsoft Data Access Components (MDAC) 2.62. This installs the MDAC 2.62 core components and the versions of the SQL Server and Desktop Engine client connectivity components that ship with this service pack.
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 this service pack.

back to the top

5.0 Documentation notes

This section covers issues that may occur as well as new features that will be available when you run this service pack. These issues apply to running the service pack to upgrade to SQL Server 2000 and SQL Server 2000 SP1 and are not the result of fixes contained in this service pack.

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

back to the top

5.1 Database and Desktop Engine enhancements

The following issues apply to SQL Server 2000 instances on which the Database Components SP2 has been installed, and Desktop Engine instances on which Desktop Engine SP2 has been installed.

back to the top5.1.1 Installing new instances of the Desktop Engine
Introduced in SP1

Follow these procedures to install a new instance of the Desktop Engine after applying SP2.

1. In the \Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033 directory, rename the following files to *.rdl:
   • semnt.rll
   • sqlsvc.rll
2. In the \Program Files\Microsoft SQL Server\80\Tools\Binn directory, rename the following files to *.dld:
   • semnt.dll
   • sqlsvc.dll
   • sqlresld.dll
3. Install the Desktop Engine without SP2.
4. Rename the semnt.rdl and sqlsvc.rdl files in the \Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033 directory to *.rll.
5. Rename the semnt.dld, sqlsvc.dld, sqlresld.dld files in the \Program Files\Microsoft SQL Server\80\Tools\Binn directory to *.dll.

The newly installed instance of the Desktop Engine will be SP2.

back to the top5.1.2 Using Chinese, Japanese, or Korean characters with Database Components SP2
Introduced in SP1

If you install Database Components SP2 on a server running the Windows NT 4.0 or Windows 98 operating system 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 that came with SP2 after you perform the upgrade to Windows 2000. For more information about executing Sqlredis.exe, see Section 4.1 "Redistributing SP2 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.

back to the top5.1.3 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 version 7.0. In addition, removing hash teams makes SQL Server 2000 more stable.

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

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

back to the top5.1.4 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 will be 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 the 298402 in Microsoft Product Support Services Knowledge Base.

Affinity mask Connection switch

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

back to the top5.1.5 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 was run. This information helps administrators diagnose security issues with scheduled jobs, including scheduled jobs defined for replication and Data Transformation Services tasks.

back to the top5.1.6 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 due to data modifications. So in addition to applying this fix, all indexes based on views with filter conditions need to be re-created. For more information, see the 306467 in Microsoft Product Support Services Knowledge Base.

back to the top

5.2 Analysis Services enhancements

Analysis Services enhancements in SP2 include:

• Remote partitions.
• Updated Analysis Services redistributable client setup.
• Support for third-party data mining algorithm providers.
• Ability to install Analysis Services on a computer with updated client files.

back to the top5.2.1 Remote partitions
Introduced in SP1

When a remote partition is created on a local server that has SP1 or 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 will have full access permissions.

In addition, if the local server has SP1 installed on it, the remote server also must have SP1 installed on it in order to create or administer remote partitions.

back to the top5.2.2 Updated Analysis Services redistributable client setup
Introduced in SP1

The Analysis Services SP1 and later versions includes 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 SP2 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.

back to the top5.2.3 Support enabled for third-party data mining algorithm providers
Introduced in SP1

The Analysis Services SP1 and later includes 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, at the Microsoft Data Access and Storage Development Center.

back to the top5.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 later to ensure that the client works properly and that you can browse cubes.

back to the top

5.3 Replication enhancements

Replication enhancements in SP2 include:

• Custom stored procedure for transactional replication updates
• Transactional replication updates on unique columns
• Restrictions removed from concurrent snapshot processing
• Ability to script custom stored procedures in transactional replication
• Merge replication retention-based meta data clean up
• Restoring replicated databases from different versions of SQL Server
• New -MaxCmdsInTran parameter for Log Reader Agent

back to the top5.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 will update all the columns in the subscription table. Any column that has not changed is simply 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 are using transactional replication and have several indexes on the subscription table, and only a few column values are changing because of updates, the index maintenance overhead may become a limiting performance factor when changes are applied at the Subscriber. For example, a subscription database that is being used for reporting purposes may have many more indexes than the publication database. Dynamically building the UPDATE statement at runtime may improve performance. The update will include only the columns that have changed, thus creating an optimal UPDATE string.

This service pack includes a new stored procedure, sp_scriptdynamicupdproc, which generates a custom stored procedure you can use at the Subscriber to dynamically build the update statement at runtime. However, extra processing will be incurred at runtime in order for the dynamic UPDATE statement to be built.

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 Is 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 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 runtime 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.

back to the top5.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, clustered index, or expression 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 could affect multiple rows and there is a chance for a uniqueness violation if updates were delivered row by row.

However, 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 runtime 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.

back to the top5.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. In this service pack, there are no longer any restrictions on using concurrent snapshot processing.

back to the top5.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 need to 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, this stored procedure 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

Is 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 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'

back to the top5.3.5 Merge replication retention-based meta data clean up
Introduced in SP1

When there is a large amount of meta data in merge replication system tables, 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 includes retention-based meta data cleanup, 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 cleanup 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 cleanup does not occur. In this case, manually initiate retention-based meta data cleanup 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 1 parameter is now part of all Merge Agent profiles that ship with SQL Server 2000 SP1 and later. If you upgrade a server to SP1 or SP2 and then add merge replication, the Merge Agent profile is automatically updated to include this parameter. If you upgrade to SP1 or SP2 a server that already has merge replication enabled, the Merge Agent profile is not automatically updated; update the profile by executing sp_add_agent_parameter (see "Additional parameter for sp_add_agent_parameter," described 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 cleanup can occur. If there are multiple publications on a database, and any one of those publications uses an infinite publication retention period (@retention=0), merge meta data for the database will not automatically be cleaned up. For this reason, use infinite publication retention with caution.

Additional parameter for sp_add_agent_parameter

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

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 cleanup 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 will not run cleanup at a SQL Server 2000 SP1 Publisher.
• A SQL Server 2000 SP1 push Merge Agent will not run cleanup in a SQL Server 2000 (without SP1) Subscriber database.
• A SQL Server 2000 SP1 push Merge Agent will run cleanup in a SQL Server 2000 SP1 Publisher database, even if it has subscribers that are SQL Server 2000 or earlier.

Automatic cleanup 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 cleanup prevents non-convergence and silent overwrites of changes at other nodes. However, false conflicts can occur if both of 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 will occur even though data appears to be synchronized.

To prevent this conflict, make sure meta data is cleaned up at related nodes at about the same time. If -MetadataRetentionCleanup 1 is used, 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 there is a large amount of meta data in the system tables 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.

back to the top5.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 could lead to the non-convergence of data.

When you are planning backup 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 very 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 tries 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 may lead to non-convergence.

back to the top5.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-will preserve 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 to SQL Server 2000 SP2 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 has been upgraded. If you do not run sp_vupgrade_replication, the replication meta data may be left in an unpredictable state.
• If you are restoring to SQL Server 2000 from a backup created with SQL Server 7.0 (whether it be the release version, Service Pack 1, Service Pack 2, or Service Pack 3), and you want to preserve replication settings, you must re-create the backup before installing service packs. It is possible to directly restore to SQL Server 2000 SP2 from a backup of a replicated database created in SQL Server 7.0, but replication settings will not be maintained.

back to the top5.3.8 A 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 than 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 ACID property of atomicity. 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 at all. 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 later, and the Log Reader Agent and distribution database must be upgraded to this service pack. Otherwise, -MaxCmdsInTran is ignored.

back to the top5.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.

back to the top5.3.10 A new -MaxNetworkOptimization command line argument for Snapshot Agent
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 can result in unnecessary network traffic.

To reduce network traffic from 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 degradation at the Publisher if multiple levels of join filters and complex subset filters are present. You should carefully assess your replication topology and only set -MaxNetworkOptimization to 1 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:

EXEC sp_add_agent_parameter 1, 'MaxNetworkOptimization', 1

back to the top

5.4 Meta Data Services

The following items apply to SQL Server 2000 Meta Data Services. 5.4.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 transparent to the user. As of this SP2 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.

back to the top

5.5 Data Transformation Services

The following items apply to SQL Server 2000 Data Transformation Services (DTS). 5.5.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.

back to the top5.5.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 SP2, 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 is run. This information helps administrators diagnose permissions and authentication issues that occur when DTS packages are scheduled to run on a server.

back to the top5.5.3 SQL Server Agent Proxy Account improvements
Introduced in SP2

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

back to the top

5.6 Enhancements to the Virtual Backup Device API

The following item applies to the SQL Server 2000 Virtual Backup Device API. 5.6.1 Capturing multiple databases in a single snapshot
Introduced in SP2

The Virtual Backup Device API allows independent software vendors 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 for SQL Server 2000, including the complete range of hot and snapshot backup capabilities.

In SP1 and earlier, there was no way to freeze and back up more than a single database at one time. SP2 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 SP2 contains updated information on the VDC_PrepareToFreeze command. An updated version of the Virtual Device Interface header file (vdi.h) can be found at \devtools\include in the SP2 setup directory.

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

back to the top

6.0 English Query enhancement

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 are using English Query. The security enhancement is located on the SP2 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.

back to the top

7.0 DB-Library and Embedded SQL for C

Introduced in SP1

While the DB-Library and Embedded SQL for C application programming interfaces (APIs) are still supported in SQL Server 2000, no future versions of SQL Server will include the files needed to do programming work on 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 also 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. Please refer to SQL Server Books Online for more information on these technologies.

back to the top