Description of Service Pack 1 for SQL Server 2000: May 30, 2001 (889553)

SUMMARY

Microsoft SQL Server 2000 Service Pack 1 (SP1) provides updates for the database components of a SQL Server 2000 installation. This article includes the following information:

• How to identify which version of SQL Server and Analysis Services is installed
• How to download and extract SQL Server 2000 SP1
• How to install SQL Server 2000 SP1
• How to redistribute the client components of the Database Components SP1
• Information about issues that may occur when you run this service pack
• Information about a security enhancement for English queries that is available separately

INTRODUCTION

This release of Microsoft SQL Server 2000 Service Pack 1 (SP1) is provided in three parts:

• Database Components SP1 provides updates for the database components of an instance of SQL Server 2000, excluding instances of the SQL Server 2000 Desktop Engine. Database Components SP1 includes upgrades to the following:
  • The data engine
  • Database client tools and utilities, such as SQL Server Enterprise Manager and osql
  • Database client connectivity components, such as Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver, and the client Net-Libraries
• Analysis Services SP1 provides updates for the SQL Server 2000 Analysis Services components of a SQL Server 2000 installation, including the following:
  • Analysis Services
  • Analysis Services client components, such as Analysis Manager and Microsoft OLE DB Provider for Analysis Services
  • Database client connectivity components, such as Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver, and the client Net-Libraries
• Desktop Engine SP1 provides updates for the database components of an instance of the SQL Server 2000 Desktop Engine. These components include the following:
  • The data engine
  • Database client connectivity components, such as Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver, and the client Net-Libraries

These three parts of SQL Server 2000 SP1 can be applied individually in the following ways:

• Use Database Components SP1 to upgrade your database components without upgrading your Analysis Services components or instances of the SQL Server 2000 Desktop Engine.
• Use Analysis Services SP1 to upgrade your Analysis Services components without upgrading instances of the Desktop Engine or the database components.
• Use Desktop Engine SP1 to upgrade instances of the Desktop Engine without upgrading Analysis Services or instances of other editions of SQL Server 2000. For more information about installing the Desktop Engine, see the "Install Desktop Engine SP1" section later in this article.

However, to upgrade both your database components and Analysis Services components, you must apply both Database Components SP1 and Analysis Services SP1.

Database Components SP1 installation

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

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

Removing SQL Server 2000 SP1

When the service pack is installed, it changes the system tables for maintenance. Because of these changes, SQL Server 2000 SP1 cannot be easily removed. To revert to the build that you were running before you installed SP1, you must remove and reinstall SQL Server 2000. Before you remove SQL Server 2000, detach your databases. Reattach them after you reinstall SQL Server 2000. For information about using sp_attach_db and sp_detach_db, see SQL Server Books Online.

After you have reinstalled SQL Server 2000, you must do the following:

• Re-create the required logons.
• Re-create scheduled tasks that depend on information in the msdb database.
• Repeat any changes that you made in the model database.
• Reconfigure replication if you use replication.

Additional information about SQL Server 2000 SP1 fixes For additional information about the fixes that are contained in this service pack, click the following article number to view the article in the Microsoft Knowledge Base: Each fix listed in this article has a link to a Knowledge Base article that describes the problem that the fix addresses.

QFE Fixes

If you received a QFE fix after May 1, 2001, the fix is not included in SQL Server 2000 SP1. Contact your primary support provider about QFE fixes and SQL Server 2000 SP1.

back to the top

Identifying the current version of SQL Server

To identify the version of SQL Server 2000 that you have installed, type SELECT @@VERSION at a command prompt if you use the osql or isql utilities. If you use SQL Query Analyzer, type SELECT @@VERSION in the Query window. The following table shows the relationship between the version string that the SELECT @@VERSION command reports and the SQL Server 2000 version number.

@@VERSION	SQL Server 2000 version
8.00.194	SQL Server 2000 release version
8.00.384	Database Components SP1

If you are not sure which edition of SQL Server 2000 you are running, view the last line of output that is returned by the SELECT @@VERSION command:

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

back to the top

Identifying the current version of Analysis Services

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

1. Click Start, 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 Analysis Servers, and then click About Analysis Services.

The following table shows the version of Analysis Services that you have.

Help About	Analysis Services version
8.0.194	SQL Server 2000 Analysis Services release version
8.0.382	Analysis Services SP1

back to the top

Downloading and extracting SQL Server 2000 SP1

SQL Server 2000 SP1 is distributed in two formats:

• On a CD
• In the three following self-extracting files that can be downloaded from the Internet:
  • SQL2KSP1.exe: Database Components SP1
  • SQL2KASP1.exe: Analysis Services SP1
  • SQL2KDeskSP1.exe: Desktop Engine SP1

The self-extracting files are used to build a set of directories and files on your computer. These directories and files are the same as the set of directories and files on the SQL Server 2000 SP1 CD. If you download SQL Server 2000 SP1, you must extract the files to build the service pack directories before you can run the Setup program.

The setup process is the same for both the SQL Server 2000 SP1 CD and the extracted SQL Server 2000 SP1 files.

Note Some of the files in service packs are system files. Therefore, you cannot view them unless you have the Show all files check box selected. To select the Show all files check box, do the following:

1. In Microsoft Windows Explorer, on the View menu, click Options.
2. Click the View tab, and then click to select the Show all files check box.

To download one of the files listed earlier in this section, save the self-extracting file to a folder on the computer that is running the instance of SQL Server 2000 where you are installing this service pack. From that folder, run the file. The self-extraction program prompts you for the name of the folder where you want to save the service pack files.

On your local hard disk drive, you need free space that is approximately three times the size of the self-extracting file. This space includes space to store the file, space to store the extracted service pack files, and temporary working space that the self-extraction program itself requires.

You can rename the folder where you extract the files after you extract the components. However, make sure that the folder name does not contain space characters. You can use the same target folder for each file that is listed earlier in this section. They will not overwrite or interfere with each other.

back to the top

Installing SQL Server 2000 SP1

To install SQL Server 2000 SP1, do one the following, depending on the components that you are installing:

• To apply both the Database Components SP1 and Analysis Services SP1, follow the steps that are described in the following sections later in this article:
  • "Back up your SQL Server databases"
  • "Back up your Analysis Services databases"
  • "Make sure that the system databases have sufficient free space"
  • "Stop services and applications before you run the SQL Server 2000 SP1 Setup program"
  • "Install Database Components SP1"
  • "Install Analysis Services SP1"
  • "Restart services"
  • "Restart applications"
• To apply only Database Components SP1 to a computer that is running both the server and client components, follow the steps that are described in the following sections later in this article:
  • "Back up your SQL Server databases"
  • "Make sure that the system databases have sufficient free space"
  • "Stop services and applications before you run the SQL Server 2000 SP1 Setup program"
  • "Install Database Components SP1"
  • "Install Analysis Services SP1"
  • "Restart services"
  • "Restart applications"
• To apply only Database Components SP1 to a computer that is running only the database client components, follow the steps that are described in the following sections later in this article:
  • "Install Database Components SP1"
  • "Restart services"
  • "Restart applications"
• To apply only Analysis Services SP1, follow the steps that are described in the following sections later in this article:
  • "Back up your Analysis Services databases"
  • "Stop services and applications before you run the SQL Server 2000 SP1 Setup program"
  • "Install Analysis Services SP1"
  • "Restart services"
  • "Restart applications"
  Use the same steps for computers that are running the Analysis Services client components only and for computers that are running both the Analysis Services client and server components.
• To apply only Desktop Engine SP1 to a computer that is running only the Desktop Engine, follow the steps that are described in the following sections later in this article:
  • "Stop services and applications before you run the SQL Server 2000 SP1 Setup program"
  • "Install Desktop Engine SP1"
• To apply the service pack to a server cluster, follow the steps that are described in the "Installing SQL Server 2000 SP1 on a server cluster" section later in this article

Note You cannot use SQL Server Setup to remotely install SQL Server 2000 SP1.

Before you install SQL Server 2000 SP1, make sure that your databases and file groups are writable and that the user account that is running SQL Server Setup has permission to access the databases. Setup fails if you do not do this. The cause of the failure is recorded in the Sqlsp.log file for SQL Server or in the Olapsp.log file for Analysis Services. Also, if the databases are not writable, you must disable log shipping to apply the service pack.

You also can use Systems Management Server to install SQL Server 2000 SP1 automatically on multiple computers that are running Windows NT Server 4.0 by using a package definition file (Smssql2ksp1.pdf file). The package definition file automatically creates 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 Sms2kdef.bat file is a batch file that detects the operating system of the computer and runs the appropriate version of the Setup program.

Note Before you install SQL Server 2000 SP1 on the French language version of Windows NT 4.0, read and follow the instructions in the following article in the Microsoft Knowledge Base:back to the top

Back up your SQL Server databases

Before you install the service pack, back up the master database and the msdb database. Installing the service pack changes the master database and msdb database. The changes make them incompatible with pre-SP1 versions of SQL Server 2000. These backups are required if you decide to reinstall SQL Server 2000 without SP1.

Applying SQL Server 2000 SP1 does not affect any other databases.

back to the top

Back up your Analysis Services databases

Before you install the service pack, back up your Analysis Services databases by making a backup copy of your Microsoft Analysis Services\Data folder. By default, this folder is installed in the C:\Program Files folder. If you have not migrated your Analysis Services repository to SQL Server, make a backup copy of the Msmdrep.mdb file. This file is located in the Microsoft Analysis Services\bin folder. To save your Analysis Services registry entries, run Registry Editor, and use the Export Registry File item on the Registry menu to export the HKEY_LOCAL_MACHINE\SOFTWARE\OLAP Server subkey to a file for backup.

back to the top

Make sure that the system databases have sufficient free space

If the autogrow option has not been selected for the master database and msdb database, 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 database 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 database and msdb database in the instance of SQL Server 2000 or Desktop Engine where you apply SQL Server 2000 SP1, 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 then 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

Stop services and applications before you run the SQL Server 2000 SP1 Setup program

You can apply SQL Server 2000 SP1 without shutting down services. If you do not shut down services, the Setup program restarts your computer.

To run the Setup program without restarting your computer, stop the following services and applications before you apply the service pack:

• Microsoft Distributed Transaction Coordinator (MS DTC) and the Microsoft Search, MSSQLServer, MSSQLServerOLAPService, and SQLServerAgent services.
  
  Note You must also stop these services for each named instance. 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 the "Installing on a server cluster" section later in this article.

back to the top

Install Database Components SP1

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

• The directory that contains the extracted service pack files from Sql2ksp1.exe

• The service pack directory on the SQL Server 2000 SP1 CD

Setup.bat opens the Setup dialog box. The dialog box 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 logon account. If you choose Windows Authentication, you must be logged on to Windows by using a Windows logon account when you run the Setup program. This logon account must be a part of the sysadmin fixed server role for the instance of SQL Server 2000 or Desktop Engine that you are upgrading.

The Setup program then does the following:

• Replaces the existing SQL Server 2000 or Desktop Engine files with the SP1 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 logon account. Leaving the sa logon password blank provides users with easy administrative access to SQL Server or Desktop Engine. You can help protect your systems by enforcing an sa password or by using Windows Authentication.

The Authentication Mode dialog box does display the current settings for the installation. The following are the dialog box default settings:

• On computers that run Windows 98 and Windows Millennium Edition, the Authentication Mode dialog box is set to Mixed Mode Authentication and requests that you specify a password for the sa logon account. Mixed Mode Authentication is the only authentication mode that is supported on these operating systems. If you specify a password, the Setup program changes the sa logon password. If you leave the password blank, the Setup program connects and does not change the password.
• On computers that run Windows NT 4.0 or Windows 2000, the Setup program is set to Windows Authentication. Use the dialog box to switch to Windows Authentication Mode or Mixed Mode with an sa logon password that is not blank.

Before you change the authentication mode or the password for the sa logon account, make sure that this change does not affect existing applications. For example, if you change from Mixed Mode Authentication to Windows Authentication, existing applications that try to connect by using SQL Server Authentication cannot connect until the authentication mode is changed to use Windows Authentication. Also, if you change the sa logon password, applications or administrative processes that use the old password cannot connect until they have been changed to use the new password.

The Setup program puts a record of the actions it performed in the Sqlsp.log file in the Windows directory of the computer where it was run.

back to the top

Install Analysis Services SP1

To install Analysis Services SP1, run Setup.exe from one of the following locations:

• The Msolap\Install subdirectory in the directory that contains the extracted Analysis Services SP1 file from SQL2KASP1.exe

• The Msolap\Install directory on the SP1 CD.

Setup.exe opens the Setup dialog box. You are prompted for information, and then Setup completes the installation.

back to the top

Install Desktop Engine SP1

To upgrade an existing installation of the Desktop Engine, run Setup.exe from one of the following locations:

• The MSDE subdirectory in the directory that contains the extracted service pack files from Sql2kdesksp1.exe

• The MSDE subdirectory on the SQL Server 2000 SP1 CD

You must apply the correct patch file to each instance of the Desktop Engine that you upgrade. A patch file is a file that has an .msp file name extension that is used to apply this service pack. If you know the installation package that was used to install the Desktop Engine, specify a patch file from the command prompt by using the /p option. If you do not know which installation package was used, type SQLRUN after the /p option. With the SQLRUN keyword, the Setup program chooses the correct patch file if the following conditions are true:

• All the .msp files are located in a folder that is named Setup. This folder is directly under the folder that contains Setup.exe.
• The instance name of the target Desktop Engine installation is specified at the command prompt if it is not the default instance.

The following example runs the Setup program as a patch installation on a named instance that is called MyInstance:

Setup.exe /p SQLRUN INSTANCENAME=MyInstance

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

The instance name may also be specified in a Setup.ini file or another settings file. For more information, see "SQL Server 2000 Desktop Engine Setup" in SQL Server Books Online.

If Setup cannot find the correct patch file, you will receive an error message.

Installing new instances of the Desktop Engine after you apply SQL Server 2000 SP1

Follow these steps to install a new instance of the Desktop Engine after you apply SQL Server 2000 SP1.

1. Change the file name extension of the following files to the .rdl file name extension in the \Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033 directory:
   • semnt.rll
   • sqlsvc.rll
2. Change the file name extension of the following files to the .dld file name extension in the \Program Files\Microsoft SQL Server\80\Tools\Binn directory:
   • Semnt.dll
   • Sqlsvc.dll
   • Sqlresld.dll
3. Install the Desktop Engine without SP1.
4. Change the file name extensions of the Semnt.rdl and Sqlsvc.rdl files in the \Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033 directory to .rll.
5. Change the file name extension of 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 SP1.

back to the top

Restart services

When the Setup program finishes, it may prompt you to restart the system. After the system restarts or after the Setup program finishes 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 are running. Back up the upgraded master database and msdb database, and re-enable log shipping.

back to the top

Restart applications

Restart the applications that you closed before you ran SQL Server 2000 SP1 Setup.

back to the top

Install SQL Server 2000 SP1 on a server cluster

To install SQL Server 2000 SP1 on a server cluster:

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

Setup causes the cluster to restart.

back to the top

Install SQL Server 2000 SP1 on replicated servers

Apply this service pack to all SQL Server 2000 participants in your replication topology These participants include Publisher, Distributor, and Subscribers. Deploy SQL Server 2000 SP1 across replicated servers in the following sequence:

• Distributor, if separate from the Publisher
• Publisher
• Subscribers

Note Most of the time, the Distributor and the Publisher are on the same server. This is especially true in merge replication. The Distributor and the Publisher are upgraded at the same time.

In merge replication, the distribution database is used only to store the agent history. Typically, the distribution database resides on the same computer that the published database resides on. However, you may also 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 that are running instances of SQL Server 2000 and this service pack. The exceptions to this are covered in the "A new -MaxCmdsInTran parameter for the Log Reader Agent" section later in this article.

back to the top

Uninstalling SQL Server 2000 SP1

To revert to the pre-SP1 version of SQL Server or Analysis Services, you must do the following:

• Remove SQL Server 2000 SP1.
• Install SQL Server 2000.
• Restore the master database, the msdb database, and any user databases.

Follow these steps to revert to the pre-SP1 version of SQL Server or Analysis Services:

1. Back up all user databases.
2. In Control Panel, double-click Add/Remove Programs, click the instance of SQL Server that you want to remove, and then click Remove.
3. Install SQL Server 2000 from the CD or the location that you originally installed SQL Server from.
4. Restore the master database and the msdb database from the last backup that was created before you applied SQL Server 2000 SP1.
5. Restore the user databases.

back to the top

Unattended installations

Database Components SP1 can be applied to an instance of SQL Server 2000 that is running on a computer that is in unattended mode. The Database Components SP1 CD contains .iss files that can be used to perform unattended SP1 setups. These files are located in the root directory on the CD. The files can be used to perform different types of installations, as follows:

• Use Sql2kdef.iss to apply Database Components SP1 to a default instance of SQL Server 2000 that is running on the Windows 2000 or the Windows NT 4.0 operating system. The corresponding batch file to use is Sql2kdef.bat.
• Use Sql2knm.iss to apply Database Components SP1 to a named instance of SQL Server 2000 that is running on the Windows 2000 or the Windows NT 4.0 operating system. You must change the instance name in the .iss file to identify the instance that is being upgraded.
• Use Sql2ktls.iss to apply Database Components SP1 to a tools-only installation. This file can be used for installation without any modifications.
• Use Sql2k9x.iss to apply Database Components SP1 on a computer that is running the Windows Millennium Edition or the Windows 98 operating system. You must change the instance name to identify the instance that is being upgraded.
• Use Sql2kcli.iss to apply Database Components SP1 to a client components installation on a computer that is running the Windows 95 operating system.

back to the top

Redistributing Database Components SP1 client components

This service pack includes a self-extracting file that is named Sqlredis.exe, and a version of Redist.txt. These files are in the Database Components SP1. By default, when Sqlredis.exe is run, it takes the following actions:

• Runs the Mdac_typ.exe from Microsoft Data Access Components (MDAC) 2.61. This installs the MDAC 2.61 core components. It also installs the versions of SQL Server and Desktop Engine client connectivity components that are included in this service pack.
• Installs Microsoft Jet ODBC drivers and connectivity components.

You can redistribute the Sqlredis.exe file under the same terms and conditions that are noted in the Redist.txt file that is included in this service pack.

back to the top

Documentation notes

This section describes issues that may occur when you run this service pack. These issues apply to running the service pack to upgrade to SQL Server 2000. They do not result from fixes that this service pack contains.

back to the top

Using Chinese, Japanese, or Korean characters with Database Components SP1

If you install Database Components SP1 on a server that is running the Windows NT 4.0 or Windows 98 operating systems and you upgrade to Windows 2000, the Windows 2000 upgrade replaces some system files. These system files are required 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 SQL Server 2000 SP1 after you complete the upgrade to Windows 2000. For more information about running Sqlredis.exe, see the "Redistributing Database Components SP1 client components" section.

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

back to the top

Meta Data Browser exports metadata in Unicode

Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

Meta Data Browser now exports XML-based metadata in Unicode. Before SQL Server 2000 SP1, the browser exported ANSI code. However, ANSI code does not support non-English characters. This functional change is transparent to the user. As of this SQL Server 2000 SP1 release, exported data is always expressed as Unicode.

You can still export in ANSI code by setting the value of the following registry subkey to zero: The following lists the values that you can set for this registry subkey:

• 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

Remote partitions

When a remote partition is created on a local server that has SQL Server 2000 SP1 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.

Additionally, if the local server has SQL Server 2000 SP1 installed on it, the remote server also must have SQL Server 2000 SP1 installed on it to create or administer remote partitions.

back to the top

Hash teams removed

In this service pack, 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. Also, removing hash teams makes SQL Server 2000 more stable. Therefore, the query optimizer no longer generates query plans by using the hash teams.

Sometimes, removing hash teams may cause the query to be processed more slowly. Analyze such queries to see whether creating more appropriate indexes return query performance to its previous level.

back to the top

Affinity mask switches added

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

Affinity mask I/O switch

With this service pack, you can specify the CPUs that are used to run threads for disk I/O operations. You must use this switch with the affinity mask option. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: 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 to a set of processors. This switch must be used with the affinity mask option. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: back to the top

Analysis Services enhancements

The following Analysis Services enhancements are included in SQL Server 2000 SP1:

• An updated Analysis Services redistributable client setup
• Support is enabled for third-party data mining algorithm providers
• Analysis Services can be installed on computers that have updated client files

Analysis Services redistributable client setup updates

Analysis Services SP1 includes updated versions of the following client redistributable Setup programs:

• PTSLite.exe
• PTSFull.exe

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

Support is enabled for third-party data-mining algorithm providers

The Analysis Services SP1 now includes support for adding third-party data mining algorithm providers. For more information about developing a data-mining algorithm provider, see the OLE DB for Data Mining Resource Kit. The kit includes the code for a sample data-mining algorithm provider. For additional information visit the following Microsoft Web site:Analysis Services installation on a computer with updated client files

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 to make sure that the client works correctly and that you can browse cubes.

back to the top

Replication enhancements

The following replication enhancements are included in SQL Server 2000 SP1:

• Transactional replication in an UPDATE custom stored procedures
• Transactional replication updates on unique columns
• Restrictions removed from concurrent snapshot processing
• Transactional replication scripting custom procedures
• Merge replication retention-based metadata cleanup
• Restoration of replicated databases from different versions of SQL Server
• New -MaxCmdsInTran parameter for the Log Reader Agent

Transactional replication UPDATE in custom stored procedure

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 stored procedure updates all the columns in the subscription table. This update sets any column that has not changed back to itself. Typically, this action causes no problems. However, if any one of these columns is indexed, Resetting the columns can become expensive.

If the following conditions are true, the index maintenance overhead may become a limiting factor when changes are applied at the Subscriber:

• You are using transactional replication.
• You have several indexes on the subscription table.
• Only some column values are changing because of updates.

For example, a subscription database that is being used for reporting may have many more indexes than the publication database. Dynamically building the UPDATE statement at run time may improve performance. The update includes only the columns that have changed. Therefore, it creates an optimal UPDATE string.

This service pack includes a new stored procedure, sp_scriptdynamicupdproc. This stored procedure generates a custom stored procedure you can use at the Subscriber to dynamically build the update statement at runtime. However, additional processing will be incurred at run time 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 int, without a default.

Result Sets

Returns a result set that is made up 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

The sp_scriptdynamicupdproc stored procedure is used in transactional replication. The default MCALL scripting logic includes all columns within the UPDATE statement. The logic uses a bitmap to determine the columns that have changed. If a column did not change, the column is set back to itself. This process generally causes no problems. If the column is indexed, additional processing occurs. The dynamic approach includes only the columns that have changed. Including only the columns that have changed provides an optimal UPDATE string. However, additional processing occurs at run time when the dynamic UPDATE statement is built. We recommend that you test the dynamic and static approaches and that you choose the optimal solution.

Permissions

Members of the public role can run the sp_scriptdynamicupdproc stored procedure.

Examples

This example creates an article (with artid set to 1) on the authors table in the pubs database. The example specifies that the UPDATE statement is the custom procedure to run:

'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 you run this stored procedure, you can use the resulting script to manually create the stored procedure at the Subscribers.
Transactional replication UPDATE statements on unique columns

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

However, if the update affects only one row, a uniqueness violation will not occur. Therefore, trace flag 8207 has been added to this service pack to enable 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 that require these triggers to fire for updates that affect only one row on a unique column.

To use trace flag 8207, turn it on at the command prompt by typing sqlservr.exe -T8207, or turn it on at run time by 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.

Restrictions removed from concurrent snapshot processing

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 the concurrent snapshot was applied to a Subscriber. In this service pack, there are no longer restrictions on using concurrent snapshot processing.

Transactional replication scripting custom procedures

When you set up nosync subscriptions, the custom procedures for INSERT, UPDATE, and DELETE statements must be manually created. The nosync subscriptions are subscriptions that do not receive the initial snapshot. 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

Scripts the custom INSERT, UPDATE, and DELETE procedures for all table articles in a publication that has the auto-generate custom procedure schema option enabled. The sp_scriptpublicationcustomprocs stored procedure is particularly useful for setting up subscriptions that have the snapshot manually applied.

Syntax

sp_scriptpublicationcustomprocs [@publication]= publication_name

Arguments

[@publication] = publication_name
Is the name of the publication. publication_name is sysname without a default.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Returns a result set that is made up of a single nvarchar(4000) column. The result set forms the complete CREATE PROCEDURE statement that is required 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 the current database.

Example

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

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

Merge replication retention-based metadata cleanup
Sometimes, cleaning up metadata that was created in system tables during merge-replication processing improves the performance of merge replication. To clean up metadata in the system tables in SQL Server 2000, the replication topology had to be inactive, and data had to be quiesced.

However, SQL Server 2000 SP1 includes retention-based metadata cleanup. Therefore, metadata can be more easily deleted from the following system tables:

• MSmerge_contents
• MSmerge_tombstone
• MSmerge_genhistory

When the @keep_partition_changes synchronization optimization option is enabled on the publication, retention-based metadata cleanup deletes metadata from the before images tables.

Retention-based metadata cleanup occurs as follows:

• If the -MetadataRetentionCleanup Merge Agent parameter is set to 1, the Merge Agent cleans up the Subscriber and the Publisher that are involved in the merge. By default, the -MetadataRetentionCleanup Merge Agent parameter is set to 1.

Note The MetadataRetentionCleanup 1 parameter is part of all Merge Agent profiles that are included with this service pack.

• If the MetadataRetentionCleanup parameter is set to 0, the automatic cleanup does not occur. In this case, manually initiate retention-based metadata cleanup by running the sp_mergemetadataretentioncleanup stored procedure. This procedure is a new system stored procedure that is included in this service pack. You must run this stored procedure at both the Subscriber and the Publisher.

The default retention period for publications is 14 days. If an article is part of several publications, it might have different retention periods. In that situation, the longest retention period is used to determine the earliest possible time that cleanup can occur.

Preventing false conflicts

Retention-based metadata cleanup prevents nonconvergence and silent overwrites of changes at other nodes. However, false conflicts can occur if the following are true:

• The metadata is cleaned up at one node and not at another node in the topology.
• A subsequent update at the cleaned-up node occurs on a row whose metadata was deleted.

For example, if metadata is cleaned up at the Publisher but not at the Subscriber and an update is made at the Publisher, a conflict occurs even though data appears to be synchronized. To prevent this conflict, make sure that the metadata is cleaned up at related nodes at about the same time. If the MetadataRetentionCleanup 1 parameter is used, both the Publisher and the Subscriber are cleaned up automatically before the merge starts. This process guarantees that the nodes are cleaned up at the same time. To resolve this conflict, use a resolver for merge-replication conflicts. Handle this conflict the same way that you handle any other merge-replication conflict.

If an article belongs to several publications or if an article is in republishing scenarios, the retention periods for a specific row at the Publisher and Subscriber may be different. To reduce the chance of cleaning up metadata on one side but not on the other side, we recommend that those different publications have similar retention periods.

Note If lots of metadata in the system tables must be cleaned up, the merge process may take longer to run. Clean up the metadata regularly to prevent this issue.

Backup and restore issues for merge replication

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

Do not synchronize the publication database with a subscription database that has an anonymous subscription. Because anonymous subscriptions do not have sufficient metadata to apply changes to the publication database. Synchronizing with a database that has an anonymous subscription could lead to the non-convergence of data.

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

• Restore the subscription databases from backups that are not past 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 that the Subscriber subscribes to. For example, if a Subscriber subscribes to three publications with retention periods of 10, 20, and 30 days, respectively, the backup that is used to restore the database should not be more than 10 days old.

• Synchronize before generating a backup.

Microsoft strongly recommends that you synchronize a Subscriber 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, assume that a publication has a retention period of 10 days. The last synchronization was 8 days ago when the backup is performed. If the backup is applied 4 days later, the last synchronization occurred 12 days ago. The synchronization was performed past the retention period. If the Subscriber had been 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 must change the publication retention value, manually reinitialize the Subscriber to avoid the non-convergence of data. The retention-based metadata-cleanup feature deletes outdated metadata from merge system tables when the publication retention period is reached.

The publication retention value is used to determine when subscriptions that have not been synchronized within the retention period should expire. If the following conditions are true, the subscription will not expire because of the increased retention value:

• The publication retention period is increased after a cleanup
• A subscription tries to merge with the Publisher and the Publisher has already deleted the metadata.

Additionally, the Publisher does not have sufficient metadata to download changes to the Subscriber. This situation leads to nonconvergence.

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

Restoring replicated databases from different versions of SQL Server

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

• If you are restoring the database to SQL Server 2000 SP1 from a backup that was created with SQL Server 2000 and you want to preserve replication settings, you must run the sp_vupgrade_replication stored procedure. Running the sp_vupgrade_replication stored procedure guarantees that the replication metadata is upgraded. If you do not run sp_vupgrade_replication, the replication metadata may be left in an unpredictable state.
• If you are restoring a database to SQL Server 2000 from a backup that created with SQL Server 7.0 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 you install service packs. You can directly restore a database to SQL Server 2000 SP1 from a backup of a replicated database that was created in SQL Server 7.0. However, replication settings are not maintained.

A new -MaxCmdsInTran parameter for the Log Reader Agent
In SQL Server 2000 SP1, a new command-prompt parameter that is named -MaxCmdsInTran has been added for the Log Reader Agent. This parameter improves parallelism between the Log Reader Agent and the Distribution Agent. Therefore, the parameter improves overall replication throughput. However, these chunks are committed at the Subscriber as individual transactions. Therefore, the ACID property of atomicity is broken. This outcome is not a problem most of the time. However, we recommend that you test the outcome to make sure that it is not a problem.

For transactions that affect many commands such as mass update or delete transactions, the Distribution Agent must wait for the Log Reader Agent to write the whole transaction to the distribution database before it can propagate the transaction to the Subscriber. This delay blocks the Distribution Agent. It also reduces the parallelism between the two agents.

By using the -MaxCmdsInTran parameter, the Log Reader Agent breaks large transactions into smaller chunks. Each chunk contains the same or fewer commands than the -MaxCmdsInTran input. Therefore, the Distribution Agent can process earlier chunks of a transaction while the Log Reader Agent works through later chunks of the same transaction.

Defining the -MaxCmdsInTran Parameter

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

logread.exe -MaxCmdsInTran 10000.

To use this parameter, the Publisher must be running SQL Server 2000 Service Pack 1. The Log Reader Agent and distribution database must be upgraded to this service pack. Otherwise, the -MaxCmdsInTran parameter is ignored.

back to the top

English Query enhancements

Microsoft has released a security enhancement for English Query applications. This enhancement is not installed as a part of SQL Server 2000 SP1. However, we recommend that you apply it if you use English Query. For additional information about the English Query enhancement, click the following article number to view the article in the Microsoft Knowledge Base: