Article ID: 889551 - Last Review: November 16, 2007 - Revision: 3.8 Service Pack 3a for Microsoft SQL Server 2000 April 9, 2003Service Pack 3a for Microsoft SQL Server 2000April 9, 2003Copyright Microsoft Corporation, 2003. All rights reserved. The SQL Server documentation team cannot answer technical support questions, but welcomes your suggestions and comments about this readme documentation. You can quickly and directly send e-mail feedback using the link below. All feedback must be in English. To submit written feedback about this document, click here: On This Page1.0 IntroductionMicrosoft SQL Server 2000 Service Pack 3a (SP3a) incorporates changes to SQL Server 2000 SP3 that address issues raised by the Slammer worm:
This release of Service Pack 3a (SP3a) for Microsoft SQL Server 2000 is provided in three parts:
Note Desktop Engine SP3a is the only part of the service pack that is available in Portuguese (Brazil), Swedish, and Dutch, because SQL Server 2000 Desktop Engine (MSDE 2000) is the only version of SQL Server 2000 that is produced for those languages. The SQL Server 2000 components upgraded by Database Components SP3a or Analysis Services SP3a are not available in those languages. Portuguese (Brazil), Swedish, and Dutch users who want to apply SP3a to a version of SQL Server other than Desktop Engine must download the SP3a files that match the language of the edition they want to upgrade. For example, English-language SP3a files must be downloaded to upgrade the English-language version of the SQL Server 2000 database engine. For information about how to download the service pack, see Section 2.0 Downloading and Extracting SP3a. 1.1 Overview of Database Components SP3a InstallationThe Database Components SP3a Setup program automatically detects which edition of SQL Server 2000 is present on the instance of SQL Server 2000 being upgraded. Setup upgrades only the components that are installed for that instance. For example, if you apply the service pack to a computer running SQL Server 2000 Standard Edition, the service pack does not attempt to upgrade components that are included only with SQL Server 2000 Enterprise Edition.You can apply Database Components SP3a to a single default instance or a named instance of SQL Server. If you are upgrading multiple instances of SQL Server 2000 to SP3a, you must apply SP3a to each instance. When one instance on a computer with one or more instances of SQL Server 2000 is upgraded to SP3a, all of the tools are upgraded to SP3a. There are not separate copies of the tools for each instance. 1.2 Overview of Desktop Engine (MSDE 2000) SP3a InstallationDesktop Engine SP3a can be used to:
The procedures for applying SQL Server 2000 Service Pack 3a to an existing instance of MSDE 2000 depend on how the instance was installed. The Desktop Engine SP3a files from Microsoft can only be used to apply SP3a to instances of MSDE 2000 that were installed using the MSDE 2000 setup utility. Most applications install MSDE 2000 using one of two mechanisms:
If there are multiple instances of MSDE 2000 on a computer, you must evaluate each instance individually to determine whether you can apply Desktop Engine SP3a. You must also apply SP3a separately to each instance. Review the material in sections 1 and 2 of this readme, and then follow the instructions in section 3 if:
1.3 Removing SP3aThe way in which you remove SQL Server 2000 SP3a depends on the SQL Server 2000 SP3a components that you are removing.Removing SQL Server Database Components and Desktop Engine SP3a When either SQL Server Database Components SP3a or Desktop Engine SP3a are installed, they make changes to the system tables for maintenance reasons, and they also upgrade user and distribution databases that are members of a replication topology. Because of these changes, SP3a cannot be removed easily. To revert to the build that you were running before installing SP3a, first, you must uninstall the instance of the SQL Server 2000 database engine or MSDE 2000; then, you must reinstall that instance. If you were running a previous SQL Server 2000 service pack or applied any Quick Fix Engineering (QFE) fixes, you must reapply that service pack and any QFE fixes to the instance. Note To remove SP3a, you must have backups of the master, model, and msdb databases, which were taken immediately prior to applying SP3a. For more information, see Section 3.1 Back Up Your SQL Server Databases and Section 3.2 Back Up Your Analysis Services Databases and Repository. For more information, see Uninstalling SQL Server 2000 Components and Desktop Engine SP3a. Removing SQL Server Analysis Services Components SP3a To be able to return SQL Server Analysis Services to its pre-SP3a state, you must back up the registry key HK_LOCAL_MACHINE\Software\Microsoft\OLAP Server and all of its sub-keys before installing SP3a. When uninstalling SP3a, you must delete this registry key and restore the pre-SP3a version from the backup. Note Updates to MDAC 2.7 SP1a that are made during SP3a setup cannot be uninstalled. For more information, see 3.1.4 Uninstalling SQL Server 2000 Analysis Services SP3a. 1.4 Identifying the Current Version of SQL Server or Analysis ServicesUse the techniques in the following sections to determine which version of SQL Server or Analysis Services you have installed.SQL Server To identify which version of the SQL Server 2000 database engine or MSDE 2000 you have installed, type SELECT @@VERSION or SERVERPROPERTY('ProductVersion') at the command prompt using the osql or isql utility or in the Query window in SQL Query Analyzer. Similarly, the product level for a given version of the SQL Server 2000 database engine or MSDE 2000 can be determined by executing SELECT SERVERPROPERTY('ProductLevel'). The following table shows the relationship between the SQL Server 2000 version and level and the version number reported by @@VERSION and the product level reported by SERVERPROPERTY('ProductLevel'). Collapse this table
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) You can also determine the edition by typing SELECT SERVERPROPERTY('Edition') at the command prompt using the osql or isql utility or in the Query window in SQL Query Analyzer. Analysis Services To identify which version of Analysis Services you have installed, follow these steps:
Collapse this table
To determine whether you have SP3 or SP3a installed, look at the version number of the Net-Library file, Ssnetlib.dll. If the version number of this file is 2000.80.760.0, you have SP3; if the version number of this file is 2000.80.766.0, you have SP3a. Assuming that an instance was installed using the default settings, you can find the Ssnetlib.dll file in one of these locations:
1.5 Additional Information about SP3aA list of the fixes contained in this service pack will be provided in Microsoft Knowledge Base article 306908. Each fix listed in 306908 has a link to a Knowledge Base article describing the problem addressed by the fix. Follow the links to the individual Knowledge Base articles to see information about each fixTo find an article in the Knowledge Base
The Knowledge Base articles mentioned in this readme are available in the Microsoft Product Support Services Knowledge Base. Microsoft Data Access Components This service pack includes updates to the Microsoft Data Access Components (MDAC), including updates for MSXML. For more information, see Section 5.5.1 Updates to Microsoft Data Access Components. QFE Fixes All publicly issued SQL Server 2000 SP2 security bulletins have been addressed in SP3a. If you received a SQL Server 2000 hotfix after October 14, 2002, that hotfix is not likely to be included in SP3a. Contact your primary support provider about obtaining the same hotfix for SQL Server 2000 SP3a. SQL Server CE Server Tools Updates Users of Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) who have upgraded or plan to upgrade SQL Server 2000 database and publisher servers to SP3 or later should also update the server replication components on Microsoft Internet Information Services (IIS) servers. An updated SQL Server CE Server Tools installer is available at this Microsoft Web site (http://www.microsoft.com/sql/prodinfo/previousversions/sqlce/default.mspx) . 1.6 Updated Books Online Documentation Is AvailableUpdated documentation for SP3 and SP3a is available. SQL Server 2000 Books Online (Updated - SP3) contains minor revisions plus new information that is related to SP3 and SP3a.You can download SQL Server 2000 Books Online (Updated - SP3) from this Microsoft Web site (http://www.microsoft.com/sql/prodinfo/previousversions/sqlce/20bol.mspx) . 1.7 Updated SQL Server and Analysis Services Samples Are AvailableSamples for the SQL Server 2000 database engine and Analysis Services that have been updated for SP3 and SP3a are available. You can download these updated samples from this Microsoft Web site.2.0 Downloading and Extracting SP3aSP3a is distributed in the following ways:
Note The download files for SP3a have the same names as the files for SP3. If you have an SP3a CD, you can install SP3a directly from the CD. If you do not have an SP3a CD, you can download the appropriate file and then run it. When the download file is run on your computer, it will create a set of folders and files on your disk that have the same organization as the folders and files on the SP3a CD. After this file extraction phase is complete, you can install SP3a from the folders on your disk. When downloading and extracting SP3a installation files from the Internet, use the following guidelines:
How to Determine the Language of an Instance of the SQL Server 2000 Database Engine or MSDE 2000 SQL Server 2000 service packs are language-specific. To upgrade SQL Server 2000, you must download and apply the service pack that has the same language as your SQL Server 2000 software. For example, if you are upgrading an instance of MSDE 2000 that uses Japanese, you must download the Japanese version of Desktop Engine SP3a.
Database Components and Analysis Services installation files both contain updated setup documentation that you can access by clicking Help during SP3a setup. This documentation does not update the version of SQL Server 2000 Books Online that is already installed on your computer. For information about how to get an updated version of SQL Server Books Online, see Section 1.6 Updated Books OnlineDocumentation Is Available. If you want to access just the updated SQL Server 2000 SP3a setup documentation without updating SQL Server Books Online, run the Setupsql.chm file. Setupsql.chm is located in the \Books subfolder of the directory on the SP3a CD-ROM, the local directory, or the network share that contains the extracted service pack files. 2.1 Downloading and Extracting Desktop Engine (MSDE 2000) SP3aUsers who have an existing instance of MSDE 2000 must know how their instance of MSDE 2000 was installed to know how to patch it. You can determine this from the information recorded as a ProductCode entry in a registry key by following the instructions in Microsoft Knowledge Base Article 311762.
If your instance of MSDE 2000 was installed using the MSDE setup utility (original package name was SqlRun01.msi through SqlRun16.msi), you can upgrade your instance of MSDE 2000 using a SQL Server 2000 SP3a download file:
After downloading either SQL2KDeskSP3.exe or LLL_SQL2KDeskSP3.exe, you must extract the MSDE 2000 SP3a files:
3.0 Service Pack InstallationTo install SP3a, use the installation instructions in the following sections. Not all steps are required, depending on which of the following SQL Server 2000 components or configurations you are applying the service pack to:
Before Starting an Installation If your instance of the SQL Server 2000 database engine or MSDE 2000 is being used by an application, first check with the provider of the application to see if there are any database engine or MSDE 2000 upgrade considerations specific to that application. An SP3a installation will fail if either of the following security policies has been set to Do not allow installation:
When installing SQL Server 2000 SP3a on a computer running Windows XP, MDAC 2.7 will not be updated to MDAC 2.7 Service Pack 1a (SP1a). If your system requires any of the fixes included in MDAC 2.7 SP1a, you must first apply Windows XP Service Pack 1 before you install SQL Server 2000 SP3a. Windows XP SP1 will apply all the fixes included in MDAC 2.7 SP1a. When installing this service pack on a pre-release version of Microsoft Windows Server 2003 build 3683 or older, you will receive the following error message: The software you are installing has not passed Windows Logo testing to verify its compatibility with this version of Windows. This software will not be installed. Contact your system administrator. You can choose to ignore this message. Click OK to continue Setup. Note This message blocks an unattended installation. When installing this service pack on a computer running Windows NT 4.0 Service Pack 6a, you must apply the hotfix described in Microsoft Knowledge Base article 258437 (http://support.microsoft.com/kb/258437/ ) . Before you install SP3a on the French version of Windows NT 4.0, follow the instructions in Knowledge Base article 259484. You can access the article from the Microsoft Product Support Services Knowledge Base. For instructions on searching the Knowledge Base, see Section 1.5 Additional Information About SP3a. Before Starting a Database Components Installation If you apply Database Components SP3a to an instance of SQL Server on a computer on which Analysis Services is also installed, Setup may fail when executing the script, Sp3_serv_uni.sql. If this happens, restart the computer and run Setup again.
If SP3a Setup detects user databases or filegroups that are not writable, it:
Note This message does not affect unattended installations. For more information about unattended installations, see Section 4.1 Unattended Installations. Because non-writeable databases no longer cause Setup to fail, you do not have to remove log shipping before upgrading to SP3a. However, if the database is shipping logs to a database that is a replication publisher, you must:
Error Running Script sp_vpupgrade_replication
(1) Note During installation, Setup makes no distinction between read-only databases and databases that are offline or in a suspect state. If a replication database or filegroup is in any of these conditions during setup and is involved in a replication topology, you must reapply the service pack after making the database writable. Note Because non-writable databases no longer cause Setup to fail, you do not have to remove log shipping before upgrading to SP3a. Preparing for a Systems Management Server Distributed Installation You cannot install SQL Server 2000 Service Pack 3a remotely. However, you can use Microsoft Systems Management Server to install SP3a automatically on multiple computers running Windows NT Server 4.0. To do so, you must use a package definition file (Smssql2ksp3.pdf) that automates the creation of a SQL Server package in Systems Management Server. The SQL Server package can then be distributed and installed on computers that are running Systems Management Server. The file Sms2kdef.bat is a batch file that starts an unattended setup using Systems Management Server. In this type of installation, the Setup program automatically detects relevant system information that it needs and no user input is required. Note You cannot use Systems Management Server to install Desktop Engine SP3a. 3.1 Back Up Your SQL Server DatabasesThe following information applies to all component installations except database client components.Before installing either Database Components SP3a or Desktop Engine SP3a, back up the master, msdb, and model databases. Installing SP3a modifies the master, msdb, and model databases, making them incompatible with pre-SP3 versions of SQL Server. These backups are required if you decide to reinstall SQL Server 2000 without SP3 or SP3a. It is also prudent to back up your user databases, although SP3a performs updates only on user databases that are members of replication topologies. 3.2 Back Up Your Analysis Services Databases and RepositoryThe following information applies only to Analysis Services.Before installing Analysis Services SP3a, back up your Analysis Services databases by making a copy of the Microsoft Analysis Services\Data folder, which is installed by default under the C:\Program Files folder. If you have not migrated your Analysis Services repository to SQL Server, make a backup copy of the file Msmdrep.mdb, which is located in the Microsoft Analysis Services\Bin folder. You should also save your Analysis server registry entries by running Regedit.exe and by using the Export Registry File item on the Registry menu to export the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAPServer to a file for backup. If you have migrated your Analysis Services repository to SQL Server, back up the database that contains the repository before installing SP3a. For more information, see Uninstalling SQL Server 2000 Analysis Services SP3a. 3.3 Make Sure the System Databases Have Enough Free SpaceThe following information applies to all component installations except database client components and Analysis Services.If the autogrow option is not selected for the master and msdb databases, the databases must have at least 500 kilobytes (KB) of free space. To verify that they have this space, run the sp_spaceused system stored procedure for the master or msdb database. If the unallocated space in either database is less than 500 KB, increase the size of the database. For more information, see "Expanding a Database" in SQL Server Books Online. If the autogrow option is selected for the master and msdb databases, and there is sufficient room on the drives, you can skip this step. To verify that the autogrow option is selected in SQL Server 2000, open SQL Server Enterprise Manager, right-click the icon for the database, and then click Properties.Verify that the Automatically grow file check box is selected. To verify that this option is selected in MSDE 2000, use the osql command prompt utility to issue the following SQL statements:
3.4 Stop Services and Applications Before Running SP3a SetupThe following information applies to all components. You can apply SP3a without shutting down services.If you do not shut down services, you are prompted to reboot the computer when Setup is complete. Without rebooting, the following services fail to start:
You cannot stop the services in a clustered environment. For more information, see Section 3.10 Installing on a Failover Cluster. 3.5 Install Database Components SP3aThe following information applies to all component installations except Desktop Engine and Analysis Services.Run the Setup.bat script from one of the following locations:
The Setup program then performs the following tasks:
Authentication Mode Dialog Box The Authentication Mode dialog box does not default to the current settings for the installation. The dialog box defaults are:
The Setup program places a record of the actions it performs in the Sqlsp.log file. This log file is stored in the Windows directory of the computer on which Setup is run. If you upgrade multiple instances, only the most recent upgrade is recorded in this log. Backward Compatibility Checklist Dialog Box The Backward Compatibility Checklist dialog box lists backward compatibility issues that you may encounter when applying the service pack. The backward compatibility issues that appear in the checklist vary depending on the configuration of the instance of SQL Server 2000 that is being upgraded. The following backward compatibility issues may be addressed in this dialog:
3.6 Install Analysis Services SP3aThe following information applies only to Analysis Services.To install Analysis Services SP3a, run Setup.exe from either of the following locations:
Additional Analysis Services Installation Issues After installing Analysis Services SP3a you must also upgrade any computers used for remote administration to SP3 or SP3a. Otherwise, you receive the following error message when you attempt to connect remotely through Analysis Manager: Unable to connect to the registry on the server (server_name), or you are not a member of the OLAP Administrators group of this server. Meta Data Services has added a new dedicated role named RepositoryUser, which can be used to access and update repository information in the msdb database. The RepositoryUser role has create, read, update, delete, and execute permissions on the msdb repository. The public role has been replaced by this new role and no longer has permissions on this repository. If the following conditions are met, the OLAP Administrators group must be added to the RepositoryUser role so that members of this group can access the repository after the service pack is applied:
Note The OLAP Administrators group must be added to the RepositoryUser role before you restore a Meta Data Services repositorythat was backed up prior to upgrading to SP3 or SP3a; otherwise, the restore operation will fail. For more information about the RepositoryUser role, see Section 5.6.3 New RepositoryUser Role for Accessing Repository Information. If the Analysis Services Data folder is located on a computer other than the one on which the Analysis server is running, you must modify the permissions on the folder after running SP3a Setup. For more information, see Section 5.2.10 Permissions Must be Modified on a Remote Data Folder. 3.7.2 Security Considerations for MSDE 2000 SP3aThe following information applies only to Desktop Engine (MSDE 2000).Desktop Engine SP3a contains a complete set of the files required to install or upgrade instances of SQL Server 2000 Desktop Engine (MSDE 2000). You can perform all of the MSDE 2000 setup actions with the files from Desktop Engine SP3a, provided you have a license to install or upgrade an instance of MSDE 2000. For more information about MSDE 2000 licensing, see Uses of MSDE 2000. You may not be able to apply Desktop Engine SP3a to instances of MSDE 2000 that were installed by the setup utility of an application.You must contact the application provider for a patch file that can be used to upgrade these instances of MSDE 2000. For instructions on how to determine if an instance of MSDE 2000 falls into this category, see section 2.1 Downloading and Extracting Desktop Engine (MSDE 2000) SP3a. The following sections provide important background information about the MSDE 2000 SP3a Setup:
3.7.1 Prerequisites for MSDE 2000 SP3aThe following information applies only to Desktop Engine (MSDE 2000).The following requirements apply to SQL Server 2000 Desktop Engine (MSDE 2000) SP3a installations. When upgrading MSDE 2000, you must separately apply Desktop Engine SP3a to every instance of MSDE 2000. Note Microsoft does not support configurations where there are more than 16 instances of the database engine on one computer. This includes instances of SQL Server 6.5, SQL Server 7.0, SQL Server 2000, MSDE 1.0, and MSDE 2000. Always start the MSDE 2000 Setup by running Setup.exe. Do not start Setup by directly invoking one of the MSDE 2000 .msi files, such as by double-clicking one of the MSDE 2000 .msi files. You must run Setup from the command prompt and specify parameters to install or upgrade any instance of MSDE. For more information on the required parameters, see sections 3.7.2 through 3.7.6. Problems can occur if you use a Terminal Services connection to attempt to upgrade an existing instance of MSDE to SP3a, or to install a new instance of MSDE 2000 SP3a. If problems are encountered, restart Setup from the local computer. You must know the instance name of the instance of MSDE you are installing or upgrading using the MSDE 2000 SP3a setup. If you are installing or upgrading a named instance of MSDE, you must use the INSTANCENAME parameter to specify the instance name. If you do not specify INSTANCENAME, Setup operates on the default instance of MSDE on that computer. In versions of MSDE 2000 Setup before SP3, users needed to specify the .msi installation package file used to install or upgrade an instance of MSDE 2000. In SP3 and later versions of Setup, Setup manages the .msi files and you do not need to specify the .msi file for either an upgrade or a new installation. You can run the MSDE 2000 SP3a Setup from your hard drive, a network share, or a CD-ROM. When upgrading an instance of MSDE 2000 to SP3a, you can only run Setup from a network share or a CD-ROM if the original files used to install the instance of MSDE 2000 are still present in the location they occupied during the original installation. If the original installation was from a CD-ROM, MSDE 2000 SP3a setup will request the original CD-ROM during the upgrade. If the original files are no longer in their original location, or the original CD-ROM is not available, you must copy the MSDE 2000 SP3a files to your hard drive and run Setup from there. If you create your own CD-ROM for installing SP3a, the volume label on the CD-ROM must be SQL2KSP3. If you are an independent software vendor (ISV) creating a CD-ROM for the purpose of distributing Desktop Engine as a component of your application, the CD-ROM volume label must be identical to the VolumeLabel property in the Media table of the Windows Installer package (*.msi). You can install the upgrade for Desktop Engine from a CD-ROM only if you are using Windows Installer version 2.0.2600.0 or later.If you need to upgrade Windows Installer, SP3a includes the files needed to upgrade Windows Installer. To upgrade Windows Installer:
3.7.2 Security Considerations for MSDE 2000 SP3aThe following information applies only to Desktop Engine (MSDE 2000).The behavior of MSDE 2000 setup has been changed in SP3a so that the default settings result in a more secure configuration. Desktop Engine SP3a changes the default behavior of the DISABLENETWORKPROTOCOLS setup parameter that specifies the configuration of the network connection support for an instance of MSDE 2000. If no application running on another computer will connect to your instance of MSDE 2000, the instance has no need for network support and it is prudent to turn off a resource that is not being used. SP3a will turn off the network support by default when installing new instances of MSDE 2000. If you disable the network support when installing an instance of MSDE 2000 SP3a, you can later reconfigure the instance to enable the support. For more information about disabling and restoring network access, see Microsoft Knowledge Base article 814130. By default, the MSDE 2000 SP3a setup will not install a new instance of MSDE 2000 unless you use the SAPWD parameter to specify a strong sa password. By default, the MSDE 2000 SP3a setup will not upgrade an existing instance of MSDE 2000 unless you have assigned a strong password to the sa login. You should assign a strong password to the sa login, even when upgrading an existing instance, unless the application using your instance of MSDE depends in some way on a null sa password. Even if the instance of MSDE 2000 is running in Windows Authentication mode, the sa login becomes immediately active if the instance is ever switched to Mixed Mode. A null, blank, simple, or well-known sa password could be used for unauthorized access. If you need to assign a strong sa password before upgrading your instance of MSDE 2000 to SP3a, see Microsoft Knowledge Base article 322336. Whenever possible, for greater security, use Windows Authentication with your MSDE 2000 installation. Consider switching from Mixed Mode to Windows Authentication Mode if:
3.7.3 MSDE 2000 Setup ParametersThe following information applies only to Desktop Engine (MSDE 2000).MSDE 2000 is designed to be distributed with applications and installed by the setup program of the application. MSDE 2000 does not have an interactive setup program. The setup mechanisms for MSDE 2000 are designed to be called by an application setup utility, where any required interaction with the end-user is handled by the application setup. MSDE 2000 has two installation mechanisms:
You must enclose the values for MSDE Setup parameters in double quotation marks if the value specified has special characters, such as blanks. Otherwise the quotation marks are optional. These MSDE 2000 SP3a Setup parameters are either not documented in the SQL Server 2000 Books Online (Updated – SP3), or behave differently than described in the Books Online. Collapse this table
These are the behaviors of DISABLENETWORKPROTOCOLS in SP3a. Collapse this table
If you are a developer incorporating the MSDE 2000 merge modules (.msi files) into a Windows Installer setup, you can specify the setup parameters above as properties in the .msi file. Desktop Engine (MSDE 2000) SP3a provides merge modules to support existing applications that use merge modules. The Setup utilities for new applications should be written to call the MSDE 2000 Setup utility instead of directly consuming the MSDE 2000 merge modules. Collapse this table
Requesting a Setup Log If you encounter difficulties running setup and need a verbose log to assist in debugging, specify /L*v <LogFileName>. <LogFileName> is the name of a log file where setup will record all of its actions. If you do not specify a path as part of the name, the log file is created in the current folder. If you are executing setup from the SQL Server 2000 SP3a CD, you must specify the full path to a folder on your disk. This example creates a log file MSDELog.log in root folder of the C: drive: setup /upgradesp sqlrun DISABLENETWORKPROTOCOLS=1 /L*v C:/MSDELog.log 3.7.4 Upgrading Existing Instances of MSDE 2000 To SP3aThe following information applies only to Desktop Engine (MSDE 2000).The examples in this section will upgrade an existing instance of MSDE 2000 to SP3a, and also disable the network connectivity for that instance of MSDE 2000. If the instance must accept connections from applications running on other computers, do not specify the DISABLENETWORKPROTOCOLS parameter. The examples in this section assume that the sa login has a strong password. For more information about the sa login password, see section 3.7.2 Security Considerations for MSDE 2000 SP3a. To upgrade SQL Server 2000 Desktop Engine
Note When upgrading Desktop Engine on a computer running Windows 98 or Windows Millennium Edition, the instance of Desktop Engine that you are upgrading must be stopped before starting Setup. 3.7.5 Installing a New Instance of MSDE 2000 SP3aThe following information applies only to Desktop Engine (MSDE 2000).The examples in this section will install a new instance of MSDE 2000 SP3a that has been configured with its network connectivity disabled. If the instance must accept connections from applications running on other computers, also specify DISABLENETWORKPROTOCOLS=0. These examples install instances using the defaults for all configuration items such as collation and file locations. The configurations can be controlled by setup parameters, such as COLLATION, DATADIR, and TARGETDIR. For more information about the configuration parameters that you can specify with setup, see "Customizing Desktop Engine Setup.exe" in the SQL Server 2000 Books Online (Updated – SP3). For more information about installing SQL Server 2000 Books Online (Updated - SP3), see section 1.6 Updated Books Online Documentation Is Available. To install a new instance of Desktop Engine
Important Always specify a strong password for the sa login, even when the instance is configured to use Windows Authentication Mode. 3.7.6 Upgrading MSDE 1.0 to MSDE 2000 SP3aThe following information applies only to Desktop Engine (MSDE 2000).The examples in this section will upgrade an existing instance of MSDE 1.0 to MSDE 2000 SP3a, and also disable the network connectivity for the instance. If the instance must accept connections from applications running on other computers, do not specify the DISABLENETWORKPROTOCOLS parameter. MSDE 1.0 operates in the same fashion as a default instance of MSDE 2000, and is always upgraded to a default instance of MSDE 2000. To upgrade from Desktop Engine version 1.0
Caution The use of blank passwords is strongly discouraged. Important If you use an .ini file during setup, avoid storing credentials in the .ini file. 3.7.7 Redistributing MSDE 2000 SP3aThe following information applies only to Desktop Engine (MSDE 2000).Desktop Engine SP3a contains all of the files necessary to redistribute MSDE 2000 with applications, provided the application vendor has a license to distribute MSDE 2000. You can distribute the Desktop Engine SP3a files as documented in the SQL Server 2000 Books Online (Updated SP3), with changes specific to SP3a covered in these sections:
Distributing Desktop Engine SP3a Patches If an application has a Windows Installer-based setup utility, it can install an instance of MSDE 2000 by consuming the MSDE 2000 merge modules. Desktop Engine (MSDE 2000) SP3a provides merge modules to support existing applications that use merge modules. The Setup utilities for new applications should be written to call the MSDE 2000 Setup utility instead of directly consuming the MSDE 2000 merge modules. Vendors who choose to install instances of MSDE 2000 using the MSDE 2000 merge modules must also supply all subsequent MSDE 2000 patches to their customers. Instances of MSDE 2000 installed using merge modules are marked with a product code GUID associated with the application by Windows Installer. Only patch files that also contain the application product code GUID can patch those instances of MSDE 2000. Only patch files produced by the application vendor will contain the proper product codes. The Desktop Engine (MSDE 2000) service packs supplied by Microsoft cannot be applied to those instances. The application vendor must build patch files using the Desktop Engine SP3a files and distribute those patch files to any of their MSDE customers who need the fixes in SP3a. For more information about 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 (http://www.microsoft.com/msdownload/platformsdk/sdkupdate/default.htm) . If an application setup utility installs an instance of MSDE 2000 by calling the MSDE 2000 setup utility, the instance of MSDE 2000 is marked with the MSDE 2000 product code GUID. Customers can patch these instances using the standard Microsoft MSDE 2000 service pack files. The application vendor can choose one of the following alternatives for distributing MSDE 2000 SP3a:
For examples of the types of MSDE 2000 service pack instructions Microsoft application teams prepared for their customers, see this Microsoft MSDE Applications Web page (http://www.microsoft.com/sql/prodinfo/previousversions/msde/prodinfo.mspx) . 3.7.8 MSDE 2000 SP3a FilesThe following information applies only to Desktop Engine (MSDE 2000).All Desktop Engine SP3a installation files and folders are located in the \MSDE folder:
If your application setup calls MSDE 2000 Setup, build a folder with this structure and sets of files (where MSDEInstallFolder represents an example folder name): MSDEInstallFolder Copy in these files from the Desktop Engine SP3a \MSDE folder: Setup.exe, Setup.ini, Setup.rll, and sqlresld.dll. MSDEInstallFolder\Msi Copy in all of the files from the Desktop Engine SP3a \MSDE\Msi folder. MSDEInstallFolder\Setup Copy in all of the files from the Desktop Engine SP3a \MSDE\Setup folder. You can then execute Setup.exe to install or upgrade instances of MSDE 2000 SP3a. If Windows Installer has not been installed on the computer, or is a version earlier than the version tested with MSDE 2000 SP3a Setup, Setup will use the files in the MSDEInstallFolder\Msi folder to upgrade Windows Installer. 3.8 Restart ServicesThe following information applies to all components.When Setup completes, it may prompt you to restart the system. After the system restarts (or after Setup completes without requesting a restart), use the Services application in Control Panel to make sure that any services you stopped before applying the service pack are now running. This includes services such as MS DTC and the Microsoft Search, MSSQLServer, MSSQLServerOLAPService, and SQLServerAgent services or their instance-specific equivalents. It is prudent to also back up the upgraded master and msdb databases at this time. 3.9 Restart ApplicationsThe following information applies to all components.Restart the applications you closed before running the service pack Setup program. 3.10 Installing on a Failover ClusterThe following information applies only to SQL Server 2000 components that are part of a failover cluster.To install the service pack on a failover cluster
If you are upgrading a default (non-clustered) instance of SQL Server to a virtual server, first, you must upgrade the default (non-clustered) instance to a virtual instance, and then apply SP3a.For more information about upgrading, see "How to upgrade from a default instance to a default clustered instance of SQL Server 2000 (Setup)" in SQL Server Books Online. For additional information on installing SP3a on a failover cluster, see Knowledge Base article 811168. If you need to rebuild a node in the failover cluster, perform the following steps
When installing Analysis Services SP3a on a cluster, each instance must be upgraded separately. To install SP3a on an Analysis Services cluster
3.11 Installing on Replicated ServersThe following information applies only to SQL Server 2000 components that are part of a replication topology:
Installing SP3a on a Server that Acts as a Publisher and a Subscriber For replication topologies based on merge replication or transactional replication with updating Subscribers that include one or more servers that act as both a Subscriber and a Publisher (or Distributor), you might need to quiesce the system (in other words, stop all updates) and upgrade all servers simultaneously. Example 1: Topology That Requires Simultaneous Upgrades The following table includes servers that both publish and subscribe to publications that allow updates at the Subscriber. As noted earlier, you must follow the upgrade order Distributor, Publisher, Subscriber for topologies that allow updates at the Subscriber.This order requires you to upgrade Server A first for the merge publication and Server B first for the transactional publication with updating Subscribers. In this case, you must quiesce the system and upgrade the servers simultaneously. Collapse this table
In this example, you can upgrade Server A first because the read-only transactional publication allows a Subscriber to be upgraded before the Publisher/Distributor. Collapse this table
Important Before you upgrade to SP3a, ensure that the Windows account under which the SQL Server service runs is a member of the sysadmin fixed server role. You must do this because replication distribution databases are upgraded under the context of the SQL Server service account. After upgrading to SP3a, you should remove the Windows account from the sysadmin role. If you are using merge replication and the Distributor is located on another computer or database instance (a remote Distributor), after applying SP3a you must generate a new snapshot. SP3a introduces a change in the requirements for attaching or restoring replication databases. For more information, see Section 5.3.17 Change to Requirements for Attaching or Restoring a Replication Database. SP3a Setup upgrades user databases that are members of a replication topology. If any of the databases that are members of a replication topology are non-writable, to apply SP3a to those databases you must make them writable, and then reapplySP3a Setup. For more information about making a database writable, see Section 3.12 Applying SP3a to Read-Only Databases or Filegroups. For information about reapplying SP3a, see Section 3.14 Reapplying SP3a. An existing backup scheme that accounts for replication allows you to restore a database to a known point after the SP3a upgrade in case of a failure. After applying SP3a, a log or full database backup is recommended for any user database that is included in a replication topology. This way, if a replication database fails, you do not have to reapply SP3a after the database is restored. 3.12 Applying SP3a to Read-Only Databases or FilegroupsThe following information applies only to SQL Server 2000 components that are part of a replication topology.When non-writable databases or filegroups exist, Setup displays the following message: Setup has detected one or more databases and filegroups which are not writable. In general, you can ignore this warning and setup will continue. However, if any of the non-writable databases listed in the Setup log are members of a replication topology, you must make those databases writable and reapply SP3a Setup to that instance of SQL Server 2000. Note This message does not affect unattended installations. For more information about unattended installations, see Section 4.1 Unattended Installations. Note During installation, Setup makes no distinction between non-writable databases and databases that are offline or in a suspect state. If a database or filegroup is in any of these conditions during setup, you must reapply the service pack. For more information about bringing a database online, see the topic "Attaching and Detaching a Database" in SQL Server Books Online. For more information about diagnosing suspect databases, see the topic "Server and Database Troubleshooting" in SQL Server Books Online. To apply SP3a to a read-only database
3.13 Uninstalling SP3aThe way in which you remove SQL Server SP3a depends on the SQL Server 2000 SP3 components that you are removing.Note MDAC updates are not uninstalled. For more information, see Section 5.5.1 Updates to Microsoft Data Access Components. Uninstalling SQL Server 2000 Database Components and Desktop Engine SP3a To revert to the pre-SP3a versions of SQL Server 2000 components, you must have back ups of the master, msdb, and model databases taken prior to installing SP3a. For more information, see Section 3.1 Back Up Your SQL Server Databases. Note If any of the databases are involved in replication, you must disable publishing. To disable publishing:
Uninstalling SQL Server 2000 Analysis Services SP3a To be able to return Analysis Services to its pre-SP3a state, you must back up the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server registry key prior to installing SP3a. For more information, see Section 3.2 Back Up Your Analysis Services Databases and Repository. Note If you did not backup this registry key, you must follow the process documented in Microsoft Knowledge Base article 330244. To revert to the pre-SP3a version of SQL Server
3.14 Reapplying SP3aThe following information applies to all components.In the following cases, you must reapply SP3a:
4.0 Additional Installation ConsiderationsThis section documents additional service pack installation considerations that apply only in special cases.4.1 Unattended InstallationsDatabase Components SP3a can be applied in unattended mode to an instance of SQL Server. The Database Components SP3a CD-ROM contains .iss files that can be used to perform unattended SP3a setups and other types of installations. The following files are located in the root directory on the CD-ROM:
Unattended Installation Considerations The following considerations relate to unattended installations:
Collapse this table
4.2 Redistributing SP3a Data Access ComponentsDatabase Components SP3a includes the self-extracting file Sqlredis.exe. When Sqlredis.exe runs, it:
5.0 Documentation NotesThis section covers issues that can occur in addition to new features that are available when you run SP3a. These issues apply when running the service pack to upgrade from SQL Server 2000, SQL Server 2000 SP1, or SQL Server 2000 SP2. This section is not meant to be a description of all of the fixes provided in SP3a. For a complete list of these fixes, see Microsoft Knowledge Base article 306908.The Analysis Services and Meta Data Services segments in this section do not apply to Desktop-Engine-only installations. Any information relevant to SQL Server 2000 Service Pack 3a that was not available in time to be included in this readme file will be published in Microsoft Knowledge Base article 816502. These Microsoft Knowledge Base articles are available at the Microsoft Product Support Services Knowledge Base. 5.1 Database and Desktop Engine EnhancementsThe following enhancements apply to SQL Server 2000 instances on which Database Components SP3a is installed. They also apply to MSDE 2000 instances on which Desktop Engine SP3a is installed.5.1.1 Using Chinese, Japanese, or Korean Characters with Database Components SP3aIntroduced in SP1If you install Database Components SP3a on a server running Windows NT 4.0 or Windows 98 and later upgrade to Windows 2000, the Windows 2000 upgrade replaces certain system files. These system files are necessary for sorting Chinese, Japanese, or Korean characters. If you use Chinese, Japanese, or Korean characters in your SQL Server databases, rerun the version of Sqlredis.exe included with SP3a after you upgrade to Windows 2000. For more information about running Sqlredis.exe, see Section 4.2 Redistributing SP3a Data Access Components. Note You do not have to reapply Sqlredis.exe on client computers or on servers that do not have databases containing Chinese, Japanese, or Korean characters. 5.1.2 Hash Teams RemovedIntroduced in SP1Hash teams have been removed. Because of certain enhancements to SQL Server 2000, hash teams no longer produce the performance benefits they offered in SQL Server 7.0. In addition, removing hash teams makes SQL Server 2000 more stable. Therefore, the query optimizer no longer generates query plans using hash teams. In rare cases, the removal of hash teams can cause a query to be processed more slowly. Analyze such queries to see whether creating more suitable indexes will return query performance to its previous level. 5.1.3 Affinity Mask Switches AddedIntroduced in SP1Two affinity mask switches have been added to this service pack. Affinity Mask I/O Switch With this service pack, you can specify which CPUs are used to run threads for disk I/O operations. This switch must be used in conjunction with the affinity mask option. For more information, see article 298402 in the Microsoft Product Support Services Knowledge Base. For instructions on searching the Knowledge Base, see Section 1.5 Additional Information About SP3a. Affinity Mask Connection Switch With this service pack, you can configure systems that are enabled for Virtual Interface Architecture (VIA) to bind the SQL Server connections from certain network cards to a processor or a set of processors. This switch must be used in conjunction with the affinity mask option. For more information, see article 299641 in the Microsoft Product Support Services Knowledge Base. 5.1.4 Filtered Indexed ViewIntroduced in SP2If you have encountered SQL Server 2000 bug 355069 as documented in Microsoft Knowledge Base Article 306467, this service pack will only prevent future occurrences of unexpected results because of data modifications. In addition to applying this fix, all indexes that are based on views with filter conditions must be re-created. For more information, see the Microsoft Product Support Services Knowledge Base. 5.1.5 Full-Text Catalogs Are Rebuilt After Setup CompletesIntroduced in SP3All full-text catalogs are rebuilt as part of the installation of SP3a, except when upgrading from SP3. The rebuild is automatic and resource-intensive. Queries against full-text catalogs may return partial results or no results until the rebuild process is complete. After SP3a is installed, the system event logs contain messages stating that the catalogs were corrupt, of an older version, and had to be rebuilt. For information , see the Knowledge Base article 327217, which also discusses possible workarounds for keeping full-text search available during the rebuild process and to avoid an automatic rebuild. 5.1.6 Syntax Changes for sp_change_users_loginIntroduced in SP3When you run sp_change_users_login with the @Action=Auto_Fix argument, you must now specify a password. sp_change_users_login assigns the password to any new login it creates for the user. The following example shows the new @Password argument. 5.1.7 Ad Hoc Access to OLE DB Providers Disabled by DefaultIntroduced in SP3If the DisallowAdhocAccess registry option is not explicitly set, by default, ad hoc access to OLE DB providers is not allowed. This means ad hoc query syntax, such as OPENDATASOURCE and OPENROWSET, will not work against remote servers. To allow ad hoc access, you must explicitly set the DisallowAdhocAccess option to 0. 5.1.8 New SqlServerLike Provider OptionIntroduced in SP3To enable more efficient processing of remote queries that include LIKE predicates, the SqlServerLike option was added in SP3. SQL Server 2000 SP3 or later now has two options for sending LIKE operations to linked servers. If the OLE DB provider for a linked server supports the SQL Server syntax for the LIKE operator and wildcards, you can specify the SqlServerLIKE option to have SQL Server send LIKE operations using SQL Server syntax. If the OLE DB provider for a linked server reports that it supports the Entry Level ANSI/ISO SQL-92 syntax or returns the SQLPROP_ANSILIKE property, SQL Server will send LIKE operations to the linked server using SQL-92 syntax. For more information on SQLPROP_ANSILIKE, see the topic "Programming the SQLPROPSET_OPTHINTS Property Set" in SQL Server 2000 Books Online. You must add a registry key value to enable the SqlServerLIKE option for an OLE DB provider. Caution Incorrectly editing the registry can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from editing the registry incorrectly can be resolved. Before editing the registry, back up any valuable data.
5.1.9 Expanded Error Messages for Distributed QueriesIntroduced in SP3For distributed queries, SQL Server returns provider error information in addition to server error information. When a query between linked servers results in an error, SQL Server checks to see if the provider supports the IErrorRecords OLE DB interface. If this interface is supported, SQL Server calls the GetErrorInfo function to get additional error information from the provider and returns this information to the user as part of the error message. If the IErrorRecords interface is not supported, there is no change in SQL Server behavior: SQL Server returns a generic error. For example, run the following query against a server that uses MSDASQL, which does not support sql_variant: 5.1.10 New Function fn_get_sql Returns SQL StatementIntroduced in SP3SP3 and later includes the new function fn_get_sql that returns the text of the SQL statement for the specified SQL handle. In addition, to support this function, three new columns have been added to the sysprocesses system table, which are listed in the following table. Collapse this table
Collapse this table
You can get a valid SQL handle from the sql_handle column of the sysprocesses system table. If you pass a handle that no longer exists in cache, fn_get_sql returns an empty result set. If you pass a handle that is not valid, the batch stops, and the following error message is returned: Server: Msg 569, Level 16, State 1, Procedure fn_get_sql, Line 12 The handle passed to fn_get_sql was invalid. SQL Server 2000 cannot cache some Transact-SQL statements, such as bulk copy statements and statements with string literals larger than 8 KB. You cannot retrieve handles to those statements by using the fn_get_sql function. The text column of the result set is filtered for text that may contain passwords. The information returned by the fn_get_sql function is similar to the DBCC INPUTBUFFER command. Use the fn_get_sql function when the DBCC INPUTBUFFER cannot be used, for example:
Only members of the sysadmin fixed server role can run the fn_get_sql function. Examples Database administrators can use the fn_get_sql function to help diagnose problem processes. After an administrator identifies a problem server process ID (SPID), the administrator can retrieve the SQL handle for that SPID, call the fn_get_sql function with the handle, and use the start and end offsets to determine the SQL text of the problem SPID. For example: 5.1.11 Cross-Database Ownership ChainingIntroduced in SP3This service pack provides new options for turning cross-database ownership chaining on and off. When installing Database Components SP3a, the Setup Backward Compatibility Checklist dialog displays an option for configuring cross-database ownership chaining. By default, Setup turns off cross-database ownership chaining for all user databases. You can enable cross-database ownership chaining for all databases. For more information, see Backward Compatibility Checklist dialog box. When installing Desktop Engine SP3a, you can use the ALLOWXDBCHAINING setup parameter to enable cross-database ownership chaining for all databases. For more information, see section 3.7.3 MSDE 2000 Setup Parameters. Note Enabling cross-database ownership chaining for all databases is not recommended. After installation, you can use the following methods to turn cross-database ownership chaining on and off for all databases in the instance:
For more information, click the Help button on the Backwards Compatibility Checklist page when you run Setup, download the updated edition of SQL Server 2000 Books Online, or see Knowledge Base article 810474. 5.1.12 Enhancement for Trace Flag 1204Introduced in SP3Trace flag 1204 returns the type of locks participating in the deadlock and the current command affected. In SP3 and later, when this trace flag is on, the deadlock information is automatically written to the error log 5.1.13 Permissions Change for sp_changedbownerIntroduced in SP3Only members of the sysadmin fixed server role can run the sp_changedbowner system stored procedure. 5.1.14 Debugging Functionality ChangesIntroduced in SP3The functionality for debugging stored procedures with Microsoft Visual Studio 6.0 and older or with SQL Server Query Analyzer prior to SP3 is turned off by default. Application debugging (stopping at a SQL Server Transact-SQL breakpoint while debugging a client application) is also turned off by default. To enable debugging functionality, run sp_sdidebug, passing the parameter legacy_on. To disable debugging, pass legacy_off to this procedure. Note Running the sp_sdidebug stored procedure on production servers is not recommended. For more information, see Microsoft Knowledge Base article 328151. This article is available at the Microsoft Product Support Services Knowledge Base. 5.1.15 Operations On UDP Port 1434Introduced in SP3aStarting with SQL Server 2000 SP3a, instances of the SQL Server 2000 database engine and MSDE 2000 that are not configured to support network communications will stop using User Datagram Protocol (UDP) port 1434. Instances that are configured to support network communications will use UDP 1434 An instance upgraded to SP3a will stop using UDP 1434 whenever all of the server Net-Libraries for the instance, except the shared memory Net-Library, are disabled. The instance will start using port 1434 whenever you enable any of the server Net-Libraries. For information on disabling or enabling server Net-Libraries, see the topic "SQL Server Network Utility" in SQL Server 2000 Books Online. The use of UDP port 1434 by a computer will not stop until all instances of SQL Server 2000 and MSDE 2000 on the computer have been upgraded to SP3a and configured to not support network communications. Whether UDP port 1434 is open or closed does not depend on the state of the shared memory Net-Library. The shared memory Net-Library is used only for local connections, and does not use a network. The shared memory Net-Library is always active; it cannot be enabled or disabled. You can also specify whether the server Net-Libraries are disabled when installing or upgrading an instance of MSDE 2000. Use either the MSDE 2000 Setup utility DISABLENETWORKPROTOCOLS parameter, or the MSDE 2000 merge module SqlDisableNetworkProtocols property. For more information on these options, see 3.7.3 MSDE 2000 Setup Parameters. You cannot disable all of the server Net-Libraries when installing or upgrading instances of the SQL Server 2000 database engine. 5.2 Analysis Services EnhancementsThis section discusses enhancements to SQL Server 2000 Analysis Services that are included with SP3a.5.2.1 Remote PartitionsIntroduced in SP1When a remote partition is created on a local server that has SP1 or a later version installed on it, the remote server must use a domain user account that has full access permissions to the parent cube on the local server. Any user account that is a member of the OLAP Administrators group on the local server has full access permissions. In addition, if the local server has SP1 or a later version installed on it, the remote server also must have SP1 or a later version installed on it so that the local server can create or administer remote partitions. 5.2.2 Updated Analysis Services Redistributable Client SetupIntroduced in SP1Analysis Services SP1 and later versions include updated versions of the following client redistributable setup programs:
Note PTSFull.exe includes MDAC; PTSLite.exe does not. Use these updated client setup programs in your applications to prevent or solve client setup issues that you may encounter when using Analysis Services and Microsoft Office XP. Note When using Analysis Services with Office XP, upgrading the client is highly recommended. 5.2.3 Support Enabled for Third-Party Data Mining Algorithm ProvidersIntroduced in SP1Analysis Services SP1 and later versions include support for the addition of third-party data mining algorithm providers. For more information about developing a data mining algorithm provider, see the "Third Party Data Mining Providers" white paper and the OLE DB for Data Mining Resource Kit, which includes the code for a sample data mining algorithm provider. 5.2.4 Installing Analysis Services on a Computer with Updated Client FilesIntroduced in SP1If you install SQL Server 2000 Analysis Services on a computer that contains updated client files, such as SQL Server 2000 SP1 or Office XP, you must apply Analysis Services SP1 or a later version to ensure that the client works properly and that you can browse through cubes. 5.2.4 Installing Analysis Services on a Computer with Updated Client FilesIntroduced in SP1If you install SQL Server 2000 Analysis Services on a computer that contains updated client files, such as SQL Server 2000 SP1 or Office XP, you must apply Analysis Services SP1 or a later version to ensure that the client works properly and that you can browse through cubes. 5.2.5 Increased Limit for OLAP Cubes Referenced by a Virtual CubeIntroduced in SP3Virtual cubes can now reference up to 255 cubes. However, any virtual cube that references more than 64 cubes is not visible to any version of Microsoft PivotTable® Service released before SP3. 5.2.6 New DESCRIPTION KeywordIntroduced in SP3Local cubes now support the intrinsic member property DESCRIPTION for measures and dimensions. The DESCRIPTION keyword, added to the CREATE CUBE Multidimensional Expressions (MDX) statement, is used to support the use of the DESCRIPTION intrinsic member property. The following BNF clauses describe the changes made to the CREATE CUBE statement: 5.2.7 New PivotTable Service Restricted Client PropertyIntroduced in SP3SP3 introduced a new PivotTable Service connection string property, Restricted Client. This property can be used to restrict PivotTable Service from using local cube functionality. Any attempt to use a statement that involves the creation or use of a local cube, such as the CREATE CUBE, CREATE GLOBAL CUBE, and CREATE SESSION CUBE statements, raises an error. Additionally, any statement that involves deep recursion, such as a series of nested DRILLDOWN statements, raises an error if the statement can potentially overflow the statement stack maintained by PivotTable Service. This property stores a string value. If the value is set to any string value starting with the characters "Y", "y", "T", or "t", or any string value that can be converted to a numeric value other than 0, PivotTable Service is restricted as described earlier. If the value is set to any other string value, including an empty string (""), or any string value that can be converted to a numeric value equal to 0, PivotTable Service is unrestricted. The default value for this property is "0". Note This property does not restrict the use of local data mining models. 5.2.8 Change in the Safety Options PropertyIntroduced in SP3Setting the Safety Options property to DBPROP_MSMD_SAFETY_OPTIONS_ALLOW_SAFE also prevents usage of the PASSTHROUGH keyword in local cubes. 5.2.9 Migrate Repository to Meta Data Services Disabled by DefaultIntroduced in SP3The option to use the Meta Data Services repository format in the Migrate Repository Wizard has been disabled in SP3 or later, and using this format is not recommended. However, if this format is required for business reasons, you can enable the option through the EnableMigrationToMetaDataServicesFormat registry key. By default, this registry key does not exist; it must be manually created at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\Server Connection Info and set to enable repository migration to the Meta Data Services format. This registry key stores a case-sensitive string value. If the value is set to 1 or True, repository migration to the Meta Data Services format is enabled. If the value is set to any other string value, or if the registry key does not exist, repository migration to the Meta Data Services format is disabled. Note If the value of this registry key is changed, the change takes effect immediately. 5.2.10 Permissions Must be Modified on a Remote Data FolderIntroduced in SP3If the Analysis Services Data folder is located on a computer other than the one on which the Analysis server is running, you must modify the permissions on the folder after running SP3a Setup. On the remote computer, set the permissions on the folder to allow full control access by all members of the OLAP Administrators group on the computer running the Analysis server. Over time, as members are added to or removed from the OLAP Administrators group, be sure to modify the permissions on the remote Data folder to so that it reflects the current membership of the OLAP Administrators group. This will allow backup and restore to function properly. Additionally, after running SP3a Setup, you must allow the account under which the Analysis server is running full control access to the remote Data folder. If you apply SP3a to an instance of Analysis Services running in a clustered configuration, the domain-level OLAP Administrators group must be allowed full control access to the remote Data folder. For more information about creating and using a domain-level OLAP Administrators group, see support article 308023 available at the Microsoft Product Support Services Knowledge Base. 5.3 Replication EnhancementsThis section discusses enhancements to SQL Server 2000 replication that are included with SP3a.5.3.1 Transactional Replication UPDATE Custom Stored ProcedureIntroduced in SP1During transactional replication setup, custom stored procedures for insert, delete, and update actions are created in the subscription database. Regardless of how many columns are affected by an UPDATE statement, the update custom stored procedure updates all of the columns in the subscription table. Any column that has not changed is reset to the same values that existed before the update. Typically, this action causes no problems. However, if any of these columns are indexed, this resetting can become expensive. If you use transactional replication and have several indexes on the subscription table, and only a few column values change because of updates, the overhead of maintaining the index can limit performance when changes are applied at the Subscriber. For example, a subscription database that is used for reporting purposes may have many more indexes than the publication database. Dynamically building the UPDATE statement at run time can improve performance. The update includes only the columns that have changed, creating an optimal UPDATE string. This service pack includes a new stored procedure, sp_scriptdynamicupdproc, which generates a custom stored procedure that you can use at the Subscriber to dynamically build the UPDATE statement at run time. However, building the dynamic UPDATE statement at run time requires extra processing. sp_scriptdynamicupdproc The stored procedure sp_scriptdynamicupdproc generates the CREATE PROCEDURE statement that creates a dynamic update stored procedure. The UPDATE statement within the custom stored procedure is built dynamically, based on the MCALL syntax that indicates which columns to change. Use this stored procedure if the number of indexes on the subscribing table is growing and the number of columns being changed is small. This stored procedure is run at the Publisher on the publication database. Result Sets Returns a result set that consists of a single nvarchar(4000) column. The result set forms the complete CREATE PROCEDURE statement that is used to create the custom stored procedure. Remarks sp_scriptdynamicupdproc is used in transactional replication. The default MCALL scripting logic includes all columns within the UPDATE statement and uses a bitmap to determine the columns that have changed. If a column did not change, the column is set back to itself, which usually causes no problems. If the column is indexed, extra processing occurs. By contrast, this stored procedure uses a dynamic approach: it includes only the columns that have changed, which provides an optimal UPDATE string. However, extra processing is incurred at run time when the dynamic UPDATE statement is built. It is recommended that you test both the dynamic stored procedure approach and the static default approach, and then choose the optimal solution for your particular needs. Permissions Members of the public role can execute sp_scriptdynamicupdproc Examples This example creates an article (with artid set to 1) on the authors table in the pubs database, and specifies that the UPDATE statement is the custom stored procedure to execute: 'MCALL sp_mupd_authors' Generate the custom stored procedures to be executed by the Distribution Agent at the Subscriber by running the following stored procedure at the Publisher: 5.3.2 Transactional Replication UPDATE Statements on Unique ColumnsIntroduced in SP1In transactional replication, UPDATE statements usually are replicated as updates. But if the update changes any column that is part of a unique index, a clustered index, or an expression that is used as a unique constraint, the update is performed as a DELETE statement followed by an INSERT statement at the Subscriber. This is done because this type of update can affect multiple rows and a uniqueness violation can occur if updates are delivered row by row. If the update affects only one row, there is no chance for a uniqueness violation. Therefore, trace flag 8207 has been added to this service pack to allow updates to any unique column that affect only one row to be replicated as UPDATE statements. This optimization has been added specifically for applications that install user-defined UPDATE triggers at the Subscriber and require these triggers to fire for updates that affect only one row on a unique column. To use trace flag 8207, turn it on from the command prompt (sqlservr.exe -T8207) or at run time using DBCC TRACEON(8207, -1) before the Log Reader Agent is started. Important Typically, trace flag 8207 is used with read-only transactional replication. Do not use this trace flag with updatable subscriptions if the primary key UPDATE can occur at the Subscriber. 5.3.3 Restrictions Removed from Concurrent Snapshot ProcessingIntroduced in SP1In SQL Server 2000, concurrent snapshot processing was not recommended if the publishing table had a unique index that was not the primary key or the clustering key. If data modifications were made to the clustering key while a concurrent snapshot was being generated, replication could fail with a duplicate key error when applying the concurrent snapshot to a Subscriber. With this service pack, restrictions on using concurrent snapshot processing are removed. 5.3.4 Transactional Replication Scripting Custom Stored ProceduresIntroduced in SP1When setting up nosync subscriptions (that is, subscriptions that do not receive the initial snapshot), the custom stored procedures for INSERT, UPDATE, and DELETE statements must be created manually. Typically, these statements are created at the Subscriber when the initial snapshot is delivered. A new stored procedure, sp_scriptpublicationcustomprocs, has been added to generate scripts for the custom stored procedures at the publication level. This new functionality may make it easier to set up nosync subscriptions. sp_scriptpublicationcustomprocs In a publication in which the option to auto-generate a custom procedure schema is enabled, the stored procedure sp_scriptpublicationcustomprocs scripts the custom INSERT, UPDATE, and DELETE procedures for all table articles. sp_scriptpublicationcustomprocs is particularly useful for setting up subscriptions for which the snapshot is applied manually. Returns a result set that consists of a single nvarchar(4000) column. The result set forms the complete CREATE PROCEDURE statement that is necessary to create the custom stored procedure. Remarks Custom procedures are not scripted for articles without the auto-generate custom procedure (0x2) schema option. Permissions Execute permission is granted to public; a procedural security check is performed inside this stored procedure to restrict access to members of the sysadmin fixed server role and db_owner fixed database role in current database. Example This example generates a script of the custom stored procedures in a publication named Northwind.. 5.3.5 Merge Replication Retention-Based Meta Data Clean UpIntroduced in SP1When merge replication system tables contain large amounts of meta data, cleaning up the meta data improves performance. Prior to SQL Server 2000 SP1, meta data could be cleaned up only by running sp_mergecleanupmetadata. However, SQL Server 2000 SP1 and later versions include retention-based meta data clean up, which means that meta data can be automatically deleted from the following system tables:
Retention-based meta data clean up occurs as follows:
The system stored procedure sp_add_agent_parameter now has a MetadataRetentionCleanup parameter, which allows you to add or remove meta data retention clean up from Merge Agent profiles. A value of 1 indicates that the profile should include clean up; a value of 0 indicates that it should not include clean up. For example, to add meta data retention clean up to a profile, execute the following code: For automatic retention-based clean up to occur in a database involved in merge replication, the database and the Merge Agent must both be on servers running SQL Server 2000 SP1 or later. For example:
Preventing False Conflicts Retention-based meta data clean up prevents non-convergence and silent overwrites of changes at other nodes. However, false conflicts can occur if the following conditions are met:
To prevent this conflict, make sure that meta data is cleaned up at related nodes at about the same time. If -MetadataRetentionCleanup is set to 1, both the Publisher and Subscriber are cleaned up automatically before the merge starts, thereby ensuring that the nodes are cleaned up at the same time. If a conflict occurs, use the merge replication conflict viewer to review the conflict and change the outcome if necessary. If an article belongs to several publications or is in a republishing scenario, it is possible that the retention periods for a given row at the Publisher and Subscriber are different. To reduce the chance of cleaning up meta data on one side but not the other, it is recommended that those different publications have similar retention periods. Note If the system tables contain large amounts of meta data that must be cleaned up, the merge process may take longer to run. Clean up the meta data on a regular basis to prevent this issue. 5.3.6 Backup and Restore Issues for Merge ReplicationIntroduced in SP1A publication database that is restored from a backup should first synchronize with a subscription database that has a global subscription (that is, a subscription having an assigned priority value) to guarantee correct convergence behavior. Synchronization ensures that the changes that were lost at the publication database because of the restore operation are reapplied accurately. Do not synchronize the publication database with a subscription database that has an anonymous subscription. Because anonymous subscriptions do not have enough meta data to apply changes to the publication database, such synchronization can lead to the non-convergence of data. When you are planning back up and restore operations for merge replication, consider the following additional issues:
5.3.7 Restoring Replicated Databases from Different Versions of SQL ServerIntroduced in SP1Restoring a backup to the same server and database—running the same version as the server from which the backup was created—preserves your replication settings. If you are restoring a replicated database to a version of SQL Server that is different from the version used to back up the database, consider the following issues:
5.3.8 New -MaxCmdsInTran Parameter for Log Reader AgentIntroduced in SP1Starting with SP1, a new command prompt parameter, -MaxCmdsInTran, has been added for the Log Reader Agent. For transactions affecting a large number of commands (typically mass updates or deletes), the Distribution Agent must wait for the Log Reader Agent to write the entire transaction to the distribution database before it can start propagating the transaction to the Subscriber. This delay blocks the Distribution Agent and reduces the parallelism between the two agents. By using –MaxCmdsInTran, the Log Reader Agent breaks large transactions into smaller chunks, and each chunk contains the same or fewer commands as the -MaxCmdsInTran input. Therefore, the Distribution Agent can start processing earlier chunks of a transaction while the Log Reader Agent is still working through later chunks of the same transaction. This improvement in parallelism between the Log Reader Agent and the Distribution Agent contributes to better overall replication throughput. Note, however, that the transaction chunks are committed at the Subscriber as individual transactions, which breaks the property of atomicity, one of the ACID (Atomicity, Consistency, Isolation, and Durability) properties. This outcome is not a problem in most circumstances, though it is recommended that you test this to make sure. Defining the –MaxCmdsInTran Parameter Specify a positive integer (1 or above) for the -MaxCmdsInTran parameter value. Specifying a value of 0 is equivalent to not using the parameter. Because this parameter improves performance only when the transaction is very large, a value of 5000 or greater for this parameter is typical. For example: logread.exe -MaxCmdsInTran 10000. To use this parameter, the Publisher must be running SQL Server 2000 SP1 or a later version, and the Log Reader Agent and distribution database must be upgraded to SP3 or later. Otherwise, -MaxCmdsInTran is ignored. 5.3.9 Restriction on Non-unique Clustered IndexesIntroduced in SP2 (applies to Transactional Replication only)You cannot create a non-unique clustered index on a table after it is published for transactional replication. Before creating the index you must first drop any publication that includes the table. 5.3.10 New -MaxNetworkOptimization Command Line Argument for Snapshot AgentIntroduced in SP2During normal processing, merge replication can send DELETE commands to Subscribers for rows that do not belong to the Subscriber's partition. DELETE commands of this type are referred to as irrelevant deletes. Irrelevant deletes do not affect data integrity or convergence, but they can result in unnecessary network traffic. To reduce network traffic caused by irrelevant deletes, you can use the new Snapshot Agent parameter -MaxNetworkOptimization with merge replication publications. Setting the parameter to 1 minimizes the chances of irrelevant deletes, which maximizes network optimization. Note Setting this parameter to 1 is useful only when the synchronization optimization option of the merge publication is set to true (the @keep_partition_changes parameter of sp_addmergepublication). The default is 0 because setting the parameter to 1 can increase the storage of meta data and cause performance to degrade at the Publisher if multiple levels of join filters and complex subset filters are present. You should carefully assess your replication topology and set -MaxNetworkOptimization to 1 only if network traffic from irrelevant deletes is unacceptably high. You can add this parameter to the Snapshot Agent profile by executing the system procedure sp_add_agent_parameter as follows: EXEC sp_add_agent_parameter 1, 'MaxNetworkOptimization', 1 5.3.11 Merge Replication Uses New RoleIntroduced in SP3SP3 and later automatically creates a new role for use by merge replication. The name of the new role is in the form MSmerge-<publication ID>. The role is created on the Publisher for each merge replication publication and acts as the publication access list (PAL) to control access to merge publications on the Publisher. If this role is dropped, you can run a new stored procedure included with SP3 or later, sp_createmergepalrole, to re-create the role. This stored procedure is executed at the Publisher on the publication database to re-create the role. Running sp_createmergepalrole adds a new row to the sysusers table for the new role. The name of this new role is based on the value of the pubid column in the sysmergepublications table for the given publication. The prefix of the role name is 'MSMerge_' and the pubid value is appended (without the hyphens) to the role name. Permissions Only members of the sysadmin fixed server role or db_owner fixed database role can run sp_createmergepalrole. 5.3.12 New Requirements for Subscriptions Created by Non-sysadmin UsersIntroduced in SP3If a subscription is created by a user who is not a member of the sysadmin fixed server role, you must do one of the following:
5.3.13 Changes to Permissions for Stored ProceduresIntroduced in SP3Permissions have been changed on a number of the stored procedures used to implement, administer, and monitor a replication topology. Most of these changes involve a tightening of the permissions that are required to run the stored procedures. For more information about the new permissions, review the Transact-SQL reference documentation for the replication stored procedures in the updated version of SQL Server Books Online. For more information about the updated SQL Server Books Online, see Section 1.6 Updated Books Online Documentation Is Available. 5.3.14 New Parameter for sp_addmergearticle and sp_changemergearticleIntroduced in SP3A new parameter, @published_in_tran_pub, has been added to both sp_addmergearticle and sp_changemergearticle. This parameter is used to indicate that an article in a merge publication is also published in a transactional publication. @published_in_tran_pub is nvarchar(5), with a default of FALSE. TRUE specifies that the article is also published in a transactional publication. Note When you change this parameter in sp_changemergearticle, the snapshot must be invalidated and subscribers must be reinitialized. 5.3.15 New Page for Configure Publishing and Distribution WizardIntroduced in SP3The Configure Publishing and Distribution Wizard now includes a new page: Distributor Password. You must type a password on this page if you select one or more Publishers to use the server as a remote Distributor and one or more of those Publishers require a password. The connection between a Publisher and a remote Distributor is a hybrid of a linked server and a remote server. The connection uses the login distributor_admin. By default, the Publisher is configured as non-trusted at the remote Distributor, so a password is required. Note If you have downloaded and installed SQL Server 2000 Books Online (Updated - SP3), this information is available when you click the Help button for the new page. 5.3.16 Changes to Windows Synchronization Manager SupportIntroduced in SP3SQL Server allows you to enable existing subscriptions (created using SQL Server Enterprise Manager, SQL-DMO, and replication stored procedures) for use with Windows Synchronization Manager. You can also create new subscriptions using Windows Synchronization Manager. After you apply the service pack, when synchronizing a subscription, Windows Synchronization Manager will prompt you to enter the password or passwords required to connect to the servers involved in the synchronization. 5.3.17 Change to Requirements for Attaching or Restoring a Replication DatabaseIntroduced in SP3Under a certain set of conditions, replication could malfunction in the process of attaching or restoring a published database. These conditions are:
Note You must be a member of the sysadmin fixed server role to execute sp_changedbowner. For more information about cross-database ownership chaining, see Section 5.1.11 Cross-Database Ownership Chaining. 5.4 SQL Server Agent EnhancementsThis section discusses enhancements to SQL Server Agent that are included in SP3a.5.4.1 SQL Server Agent Logs Account InformationIntroduced in SP2The SQL Server Agent Job History now records the Windows account under which each job step runs. This information helps administrators diagnose security issues with scheduled jobs, including scheduled jobs that are defined for replication and Data Transformation Services (DTS) tasks. 5.4.2 Changes to Master/Target Server ConfigurationsIntroduced in SP3Multiserver administration is the process of automating administration tasks across multiple instances of SQL Server. Use multiserver administration if you manage two or more servers and you want to centralize maintenance tasks. In SP3a, the SQL Server Agent service account does not have to be a Windows administrator unless you need to use the SQL Server Agent Proxy Account. For more information about the SQL Server Agent Proxy Account, see Section 5.7.3 SQL Server Agent Proxy Account Improvements. The SQL Server Agent service account must be a member of the sysadmin fixed server role. With multiserver administration, you must have at least one master server and at least one target server. A master server distributes jobs to and receives events from target servers. A master server stores the central copy of job definitions for jobs that run on target servers. Target servers connect periodically to their master server to update their list of jobs to perform. If a new job exists, the target server downloads the job and disconnects from the master server. After the target server completes the job, it reconnects to the master server and reports the status of the job. Before you apply SP3a, you must complete several steps to upgrade your SQL Server 2000 master/target server configuration. The changes that are introduced with SP3a are not compatible with SQL Server 7.0 target servers, or with any servers not running SP3a. This is a change from the original SQL Server 2000 functionality. To upgrade your master/target server configuration
5.4.3 New SQL Server Agent Extended Stored ProcedureIntroduced in SP3SP3a includes a new extended stored procedure that allows you to configure the account that the SQL Server Agent TSX server uses to download instructions from an MSX server. This account is also known as the MSX account, or master server account. xp_sqlagent_msx_account The xp_sqlagent_msx_account extended stored procedure sets or retrieves the SQL Server Agent MSX account user name and password to or from the LSA secrets on the TSX server. Only members of the securityadmin fixed server role can run this extended stored procedure. Before you can execute this extended stored procedure, SQL Server Agent must be running. In addition, if the account specified is a SQL Server login, SQL Server Agent must have local Windows administrator rights. SQL Server Agent stores the user name and password as an LSA secret, and access is restricted to local Windows administrators. N'SET' Sets the account to be used as the SQL Server Agent MSX account. Use the MSX_username, and MSX_password parameters to specify the account to use as the SQL Server Agent MSX account. N'SET' is an nvarchar with no default. N'DEL' Deletes the SQL Server Agent MSX account. 'MSX_domain_name' Reserved for future use. 'MSX_username' The name of the Windows account to be used as the SQL Server Agent MSX account. Specify an empty string for this parameter and MSX_password to select Windows security. In this case, the SQL Server Agent service account credentials are used to log on to the MSX server. MSX_username is an nvarchar with no default. 'MSX_password' The password for the SQL Server account specified in MSX_username. Specify an empty string for this parameter and the MSX_username to select Windows security. In this case, the SQL Server Agent service account credentials are used to log in to the MSX server. MSX_password is an nvarchar with no default. Note Parameters for xp_sqlagent_msx_account must be specified in order. Named parameters cannot be used. Return Code Values Returns a value of 0 on success or 1 on failure When xp_sqlagent_msx_account fails and returns a value of 1, SQL Server generates an error message with information about the error. Result Sets If a SQL Server Agent MSX account has been set, xp_sqlagent_msx_account returns a result set with the following information when you specify N'GET'. Collapse this table
Permissions Execute permissions for xp_sqlagent_msx_account default to members of the securityadmin fixed server role. Examples
5.4.4 SQL Server Agent Permission ChecksIntroduced in SP3SQL Server now checks to ensure that the Agent job owner has permission to append or overwrite the output log file from each job. This happens in three ways:
5.4.5 SQL Agent Mail MAPI ProfilesIntroduced in SP3In the 32-bit version of SQL Server 2000, SQL Agent Mail can be configured to use an Extended MAPI e-mail profile for sending e-mail alerts. You can use an Extended MAPI e-mail application, such as Microsoft Outlook, to create an Extended MAPI profile. In the 64-bit version of SQL Server 2000, SQL Agent Mail can only use a Simple MAPI profile for sending e-mail alerts. Do not use Simple MAPI profiles in the 32-bit version of SQL Server 2000. 5.5 SQL Server Connectivity Components EnhancementsThis section discusses enhancements to connectivity components of SQL Sever 2000 that are included with SP3a.5.5.1 Updates to Microsoft Data Access ComponentsIntroduced in SP3aSP3a includes updates to the Microsoft Data Access Components (MDAC). When you install SP3a, MDAC 2.7 Service Pack 1a is also installed. MDAC 2.7 SP1a has no feature changes from the version of MDAC installed by SQL Server 2000 (MDAC 2.6), but it does include fixes and security enhancements. MDAC 2.7 SP1a includes an upgrade to MSXML 3 SP3. Note This version of MDAC is not installed if Setup detects the same version or a newer version. For more information about this version of MDAC, see the Microsoft Universal Data Access Web site at this page on microsoft.com. Fixes included in this version of MDAC will be documented in Knowledge Base article 326848. You can access this article from the Microsoft Product Support Services Knowledge Base. 5.5.2 Support for QLogic Virtual Interface ArchitectureIntroduced in SP3SQL Server now supports QLogic Virtual Interface Architecture (VIA) System Area Network (SAN) implementations. To enable SQL Server support for connections over QLogic VIA, both the client and server computers must provide IP address resolution in a file named Vihosts in the appropriate Windows system32\drivers\etc folder. Note You must identify the correct VIA vendor on client computers by using Client Network Utility. Select the appropriate value in the Vendor drop-down box. The corresponding action should also be completed on server computers through Server Network Utility. 5.6 Meta Data Services EnhancementsThis section discusses enhancements to SQL Server 2000 Meta Data Services that are included with SP3a.5.6.1 Meta Data Browser Exports in UnicodeIntroduced in SP1Meta Data Browser now exports XML-based meta data in Unicode. Before SQL Server 2000 SP1, the browser exported ANSI code, which does not support non-English characters. This functional change is invisible to the user. As of this SP3a 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:
5.6.2 Scripting Support DisabledIntroduced in SP3Script support in information models has been disabled. After installing SP3a, you receive the following error if your application accesses a property or method for which a script is defined: EREP_SCRIPTS_NOTENABLED To enable script support If you need to continue executing scripts, you can use the following procedure to create a registry setting that enables script support.
5.6.3 New RepositoryUser Role for Accessing Repository InformationIntroduced in SP3SQL Server includes in the msdb database a set of tables, stored procedures, and views that store information used by the Meta Data Services repository engine. In SP3, a new dedicated role named RepositoryUser was added and must be used to access and update repository information. This role has been granted create, read, update, delete, and execute permissions on these objects. The public role no longer has permissions on these objects. . This change affects existing repository objects in addition to any additional objects created by the repository engine in the future. Users and applications that access the repository through the public role must be added to the RepositoryUser role. 5.7 Data Transformation Services EnhancementsThis section discusses enhancements to SQL Server 2000 Data Transformation Services that are included with SP3a.5.7.1 DTS Wizard No Longer Limits String Columns to 255 CharactersIntroduced in SP2When you export data to a text file, the DTS Import/Export Wizard now configures the package to write up to 8000 characters of any column containing string-type data. 5.7.2 Security Context Logged for DTS Packages Run by SQL Server AgentIntroduced in SP2 SQL Server Agent records the security context under which each step in a job runs. In SP3 or later, the security context appears in the Job History dialog box. When you run a DTS package from a step in a job, the SQL Server Agent logs the user account under which the package runs. This information helps administrators diagnose permission and authentication issues that occur when DTS packages are scheduled to run on a server. 5.7.3 SQL Server Agent Proxy Account ImprovementsIntroduced in SP2Prior 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, such as C:\Temp. For SP2 and later, DTS has been enhanced to use the system Temp folder if the user Temp folder is unavailable, greatly reducing the need for these adjustments. 5.7.4 Save to Meta Data Services Disabled by DefaultIntroduced in SP3By default, SP3 and later disables the option to store DTS packages in Meta Data Services. This means that the Meta Data Services option does not appear in the Location drop-down list in the Save DTS Packages dialog box. In addition, this option is disabled on the Save, schedule, and replicate package page in the DTS Import/Export Wizard. To allow packages to be saved in Meta Data Services
When the option to store packages in Meta Data Service is disabled, you can load existing packages from Meta Data Services, edit them, and save them to Meta Data Services using the Save option. However, Meta Data Services is not available from the Save As option. For example, you cannot re-save a package to Meta Data Services using a different name. 5.8 XML EnhancementsThe following topic discusses an enhancement for XML and SQLXML in SP3a.5.8.1 Improved Validation of XPath ExpressionsIntroduced in SP3Prior to SP3, the version of MSXML that was installed with SQL Server 2000 (MSXML 2.6) permitted a predicate in an XPath expression to follow the special character abbreviation that identifies the current context node, which is denoted by a period (.) in XPath syntax. This violates the XPath syntax specification, which requires that this character be followed by a location path expression. When you apply SP3 or later, an updated version of MSXML (3.0 SP3) is installed as part of the MDAC upgrade. For more information, see Section 5.5.1 Updates to Microsoft Data Access Components. With the new version of MSXML, a predicate cannot immediately follow the current context node abbreviation special character. XPath expressions in SQLXML queries (XPath queries against annotated mapping schemas and in XSLT style sheets written to transform the results of SQLXML queries) that use the faulty syntax will fail after you upgrade to SP3 or later To prevent these failures, identify and fix any expressions that use the incorrect syntax. For example, the syntax of the XPath expression that is specified as the value of the test attribute in the following xsl:if element is not valid because the predicate, [@ResourceTypeID='2'], immediately follows the special character abbreviation that identifies the current context node. The following statement, which previously did not generate an error, will fail after SP3 or later is installed. 5.9 Virtual Backup Device API EnhancementsThe following item applies to the SQL Server 2000 Virtual Backup Device API.5.9.1 Capturing Multiple Databases in a Single SnapshotIntroduced in SP2 The Virtual Backup Device API allows ISVs to integrate SQL Server 2000 into their products. This API is engineered to provide maximum reliability and performance. It fully supports the backup and restore functionality of SQL Server 2000, including the complete range of hot and snapshot backup capabilities. In SP1 and earlier, there was no way to freeze and back up more than a single database at one time. SP3 or later now provides server-side support for freezing and capturing multiple databases in a single snapshot through the VDC_PrepareToFreeze command. The Virtual Backup Device Specification in SP3a contains updated information about the VDC_PrepareTo Freeze command. An updated version of the Virtual Device Interface header file (Vdi.h) can be found at \Devtools\Include in the SP3a setup directory You can download the updated specification from the Microsoft Download center at the Microsoft SQL Server Downloads Web site. 5.10 Error ReportingIntroduced in SP3Microsoft SQL Server error reporting is disabled by default. You can enable it during installation through SQL Server Setup or Analysis Services Setup, or after installation through the Server Properties dialog box in Enterprise Manager or the Server Properties dialog box in Analysis Manager. Enabling error reporting while you are running SQL Server Setup allows error reporting for the SQL Server database engine and SQL Server Agent. Enabling error reporting while you are running Analysis Services Setup allows error reporting for Analysis Services. If you want to enable error reporting for both SQL Server and Analysis Services, you must enable error reporting for SQL Server while running SQL Server Setup and Analysis Services while running Analysis Services Setup. If you enable this feature, SQL Server is configured to send a report to Microsoft automatically if a fatal error occurs in the SQL Server database engine, in SQL Server Agent, or in SQL Server Analysis Services. Microsoft uses error reports to improve SQL Server functionality and treats all information as confidential. Information about errors is sent over a secure (HTTPS) connection to Microsoft, where it is stored with limited access. Alternatively, this information can be sent to your own Corporate Error Reporting server. The error report contains the following information
For the Microsoft error reporting data collection policy, see this Microsoft Web site (http://wer.microsoft.com/responses/en-us/dcp20.aspx) . If you enable error reporting and a fatal error occurs, you may see a response from Microsoft in the Windows Event log that points to a Microsoft Knowledge Base article about a particular error. A response may look similar to the following example: 5.11 English Query EnhancementsIntroduced in SP21Microsoft has released a security enhancement for English Query applications. This enhancement is not installed as a part of the service pack. However, it is recommended that you apply it if you use English Query. The security enhancement is located on the SP3a CD-ROM in the folder \EQHotfix. Details about the English Query enhancement are available at the Microsoft Product Support Services Knowledge Base. Search the Knowledge Base for article 297105. 5.12 DB-Library and Embedded SQL for CIntroduced in SP1While the DB-Library and Embedded SQL for C APIs are still supported in SQL Server 2000, no future versions of SQL Server will include the files that are necessary to program applications that use these APIs. Connections from existing applications written using DB-Library and Embedded SQL for C will still be supported in the next version of SQL Server, but this support will be dropped in a future release. When writing new applications, avoid using these components. When modifying existing applications, you are strongly encouraged to remove dependencies on these technologies. Instead of DB-Library or Embedded SQL for C, you can use ADO, OLE DB, or ODBC to access data in SQL Server. For more information about these technologies, see SQL Server Books Online. | Article Translations
|
| United States | Change | | | All Microsoft Sites |
Help and Support
Back to the top
|
