Service Pack 3a for Microsoft SQL Server 2000
April 9, 2003
© Copyright 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: Submit feedback.
Contents
1.0 Introduction
1.1 Overview of Database Components SP3a Installation
1.2 Overview of Desktop Engine (MSDE 2000) SP3a Installation
1.3 Removing SP3a
1.4 Identifying the Current Version of SQL Server or Analysis Services
1.5 Additional Information About SP3a
1.6 Updated Books Online Documentation Is Available
1.7 Updated SQL Server and Analysis Services Samples Are Available
2.0 Downloading and Extracting SP3a
2.1 Downloading and Extracting Desktop Engine (MSDE 2000) SP3a
3.0 Service Pack Installation
3.1 Back Up Your SQL Server Databases
3.2 Back Up Your Analysis Services Databases and Repository
3.3 Make Sure the System Databases Have Enough Free Space
3.4 Stop Services and Applications Before Running SP3a Setup
3.5 Install Database Components SP3a
3.6 Install Analysis Services SP3a
3.7 Install Desktop Engine SP3a
3.7.1 Prerequisites for MSDE 2000 SP3a
3.7.2 Security Considerations for MSDE 2000 SP3a
3.7.3 MSDE 2000 Setup Parameters
3.7.4 Upgrading Existing Instances of MSDE 2000 To SP3a
3.7.5 Installing a New Instance of MSDE 2000 SP3a
3.7.6 Upgrading MSDE 1.0 to MSDE 2000 SP3a
3.7.7 Redistributing MSDE 2000 SP3a
3.7.8 MSDE 2000 SP3a File Locations
3.8 Restart Services
3.9 Restart Applications
3.10 Installing on a Failover Cluster
3.11 Installing on Replicated Servers
3.12 Applying SP3a to Read-Only Databases or Filegroups
3.13 Uninstalling SP3a
3.14 Reapplying SP3a
4.0 Additional Installation Considerations
4.1 Unattended Installations
4.2 Redistributing SP3a Data Access Components
5.0 Documentation Notes
5.1 Database and Desktop Engine Enhancements
5.1.1 Using Chinese, Japanese, or Korean Characters with Database Components SP3a
5.1.2 Hash Teams Removed
5.1.3 Affinity Mask Switches Added
5.1.4 Filtered Indexed View
5.1.5 Full-Text Catalogs Are Rebuilt After Setup Completes
5.1.6 Syntax Changes for sp_change_users_login
5.1.7 Ad Hoc Access to OLE DB Providers Disabled by Default
5.1.8 New SqlServerLike Provider Option
5.1.9 Expanded Error Messages for Distributed Queries
5.1.10 New Function fn_get_sql Returns SQL Statement
5.1.11 Cross-Database Ownership Chaining
5.1.12 Enhancement for Trace Flag 1204
5.1.13 Permissions Change for sp_changedbowner
5.1.14 Debugging Functionality Changes
5.1.15 Operations On UDP Port 1434
5.2 Analysis Services Enhancements
5.2.1 Remote Partitions
5.2.2 Updated Analysis Services Redistributable Client Setup
5.2.3 Support Enabled for Third-Party Data Mining Algorithm Providers
5.2.4 Installing Analysis Services on a Computer with Updated Client Files
5.2.5 Increased Limit for OLAP Cubes Referenced by a Virtual Cube
5.2.6 New DESCRIPTION Keyword
5.2.7 New PivotTable Service Restricted Client Property
5.2.8 Change in the Safety Options Property
5.2.9 Migrate Repository to Meta Data Services Disabled by Default
5.2.10 Permissions Must be Modified on a Remote Data Folder
5.3 Replication Enhancements
5.3.1 Transactional Replication UPDATE Custom Stored Procedure
5.3.2 Transactional Replication UPDATE Statements on Unique Columns
5.3.3 Restrictions Removed from Concurrent Snapshot Processing
5.3.4 Transactional Replication Scripting Custom Stored Procedures
5.3.5 Merge Replication Retention-Based Meta Data Clean Up
5.3.6 Backup and Restore Issues for Merge Replication
5.3.7 Restoring Replicated Databases from Different Versions of SQL Server
5.3.8 New -MaxCmdsInTran Parameter for Log Reader Agent
5.3.9 Restriction on Non-unique Clustered Indexes
5.3.10 New –MaxNetworkOptimization Command Line Argument for Snapshot Agent
5.3.11 Merge Replication Uses New Role
5.3.12 New Requirements for Subscriptions Created by Non-sysadmin Users
5.3.13 Changes to Permissions for Stored Procedures
5.3.14 New Parameter for sp_addmergearticle and sp_changemergearticle
5.3.15 New Page for Configure Publishing and Distribution Wizard
5.3.16 Changes to Windows Synchronization Manager Support
5.3.17 Change to Requirements for Attaching or Restoring a Replication Database
5.4 SQL Server Agent Enhancements
5.4.1 SQL Server Agent Logs Account Information
5.4.2 Changes to Master/Target Server Configurations
5.4.3 New SQL Server Agent Extended Stored Procedure
5.4.4 SQL Server Agent Permission Checks
5.4.5 SQL Agent Mail MAPI Profiles
5.5 SQL Server Connectivity Component Enhancements
5.5.1 Updates to Microsoft Data Access Components
5.5.2 Support for QLogic Virtual Interface Architecture
5.6 Meta Data Services Enhancements
5.6.1 Meta Data Browser Exports in Unicode
5.6.2 Scripting Support Disabled
5.6.3 New RepositoryUser Role for Accessing Repository Information
5.7 Data Transformation Services Enhancements
5.7.1 DTS Wizard No Longer Limits String Columns to 255 Characters
5.7.2 Security Context Logged for DTS Packages Run by SQL Server Agent
5.7.3 SQL Server Agent Proxy Account Improvements
5.7.4 Save to Meta Data Services Disabled by Default
5.8 XML Enhancements
5.8.1 Improved Validation of XPath Expressions
5.9 Virtual Backup Device API Enhancements
5.9.1 Capturing Multiple Databases in a Single Snapshot
5.10 Error Reporting
5.11 English Query Enhancements
5.12 DB-Library and Embedded SQL for C
1.0 Introduction
Microsoft SQL Server 2000 Service Pack 3a (SP3a) incorporates changes to SQL Server 2000 SP3 that address issues raised by the Slammer worm:
- SP3a can be applied to SQL Server 2000 Enterprise Evaluation Edition in order to apply fixes to instances of that edition of SQL Server.
- SP3a addresses setup issues some customers encountered with the version of the Microsoft Data Access (MDAC) components in SQL Server 2000 SP3.
- The MSDE 2000 SP3a Setup utility now defaults to turning off support for network connections when installing new instances of MSDE 2000. The current network support settings are retained when instances are upgraded to SP3a.
- When an instance of SQL Server 2000 SP3a or MSDE 2000 SP3a is configured to not support network connections, the instance also stops using UDP port 1434.
Because most changes introduced in SP3a are related to Setup, you do not need to apply SP3a to instances of SQL Server 2000 or MSDE 2000 that have already been upgraded to SP3. If you have instances that have not yet been upgraded to SP3, however, you should always upgrade those instances directly to SP3a instead of SP3. If you have any copies of the SP3 download files that you were planning to use for future upgrades, you should delete those copies and instead use either the SP3a download files or the SP3a CD-ROM. Applications that distribute and install MSDE 2000 should install SP3a instead of SP3. While you can apply SP3a to instances of SQL Server 2000 SP3 or MSDE 2000 SP3, doing so has little effect on those instances.
This release of Service Pack 3a (SP3a) for Microsoft® SQL Server™ 2000 is provided in three parts:
- Database Components SP3a provides updates for the database components of an instance of SQL Server 2000, excluding instances of the SQL Server 2000 Desktop Engine. Database Components SP3a includes updates to:
- The database engine.
- All of the database client tools and utilities such as SQL Server Enterprise Manager and the osql utility.
- Database client connectivity components, such as the Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver, and the client Net-Libraries.
- Analysis Services SP3a provides updates for the SQL Server 2000 Analysis Services components of a SQL Server 2000 installation, including:
- Analysis Services.
- Analysis Services client components, which include Analysis Manager and the Microsoft OLE DB Provider for Analysis Services.
- Database client connectivity components, such as the Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver, and the client Net-Libraries.
- Desktop Engine SP3a provides updates for the database components of an instance of SQL Server 2000 Desktop Engine (MSDE 2000), including:
- The database engine.
- The database command prompt utilities that come with MSDE 2000, such as the osql and bcp utilities.
- Database client connectivity components, such as the Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver, and the client Net-Libraries.
These three parts of SP3a can be applied individually, as follows:
- SQL Server 2000 sites can use Database Components SP3a to upgrade their database components without upgrading their Analysis Services components or instances of the SQL Server 2000 Desktop Engine.
- Analysis Services 2000 sites can use Analysis Services SP3a to upgrade their Analysis Services components without upgrading instances of Desktop Engine or their database components.
- MSDE 2000 sites can use Desktop Engine SP3a to upgrade instances of MSDE 2000 without upgrading Analysis Services or instances of the SQL Server 2000 database engine.
Note If separate instances of both MSDE 2000 and other editions of the SQL Server 2000 database engine are installed on the same computer, you must apply Desktop Engine SP3a to instances of MSDE 2000, and Database Components SP3a to instances of the SQL Server 2000 database engine, such as Personal Edition, Standard Edition, or Enterprise Edition.
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 Installation
The 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 Installation
Desktop Engine SP3a can be used to:
- Upgrade an existing instance of SQL Server 2000 Desktop Engine (MSDE 2000) to SP3a.
- Install a new instance of MSDE 2000 SP3a, provided you have an MSDE 2000 license that allows you to install instances of MSDE 2000.
- Upgrade an instance of MSDE 1.0 to MSDE 2000, provided you have an MSDE 2000 license that allows you to run instances of MSDE 2000.
- Provide the files needed for a developer to write an application setup utility that can install an instance of MSDE 2000 SP3a as part of the installation of the application. The developer must have a license to distribute MSDE with applications.
For more information about MSDE 2000 licensing, see Uses of MSDE 2000.
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:
- The setup utility of the application calls the MSDE 2000 setup utility during installation. Instances of MSDE 2000 installed this way can be upgraded to SP3a using the Desktop Engine SP3a files.
- If the setup utility of the application uses Microsoft Windows Installer technology, the setup utility can directly consume the MSDE 2000 merge modules. Instances of MSDE 2000 installed this way cannot be upgraded using the Desktop Engine SP3a files. You must contact the application vendor for a patch 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.
Instructions for determining whether an instance of MSDE 2000 can be upgraded to SP3a using Desktop Engine SP3a are in section 2.1 Downloading and Extracting Desktop Engine (MSDE 2000) SP3a. Even if you are applying Desktop Engine SP3a from the SQL Server 2000 SP3a CD, read the instructions in section 2.1 to determine whether you can apply Desktop Engine SP3a or you must contact an application vendor for a patch file.
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:
- You have determined that you can apply Desktop Engine SP3a to your existing instance of MSDE.
- You are installing a new instance of MSDE 2000.
- You are incorporating the Desktop Engine SP3a files into the setup utility of an application.
These sections give examples for the most common scenarios of either upgrading an existing instance of MSDE to SP3a, or installing a new instance of MSDE 2000 SP3a:
1.3 Removing SP3a
The 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 Uninstalling SQL Server 2000 Analysis Services SP3a.
1.4 Identifying the Current Version of SQL Server or Analysis Services
Use the techniques in the following sections to determine which version of SQL Server or Analysis Services you have installed.
SQL Server
To identify which version of 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').
| SQL Server 2000 version and level |
@@VERSION |
ProductLevel |
| SQL Server 2000 Original Release |
8.00.194 |
RTM |
| Database Components SP1 or Desktop Engine SP1 |
8.00.384 |
SP1 |
| Database Components SP2 or Desktop Engine SP2 |
8.00.534 |
SP2 |
| Database Components SP3 or SP3a, or Desktop Engine SP3 or SP3a |
8.00.760 |
SP3 |
If you are not sure which edition of the SQL Server 2000 database engine or MSDE 2000 you are running, view the last line of output returned by SELECT @@VERSION. The last line should match one of the following:
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 2)
Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
Note The beginning of the line indicates the edition of the SQL Server database engine or MSDE 2000. This is followed by current operating system information.
You can also determine the edition by typing SELECT SERVERPROPERTY('Edition') at the command prompt using the osql or isql utility or in the Query window in SQL Query Analyzer.
Analysis Services
To identify which version of Analysis Services you have installed, follow these steps:
- From the Start menu, point to Program Files, point to SQL Server 2000, point to Analysis Services, and then click Analysis Manager.
- In the Analysis Manager tree, right-click the Analysis Servers node, and then click About Analysis Services.
- Use the following table to determine which version of Analysis Services you have.
| Analysis Services version |
Build Number in Help About |
| SQL Server 2000 Analysis Services Original Release |
8.0.194 |
| Analysis Services SP1 |
8.0.382 |
| Analysis Services SP2 |
8.0.534 |
| Analysis Services SP3 or SP3a |
8.0.760 |
Distinguishing Between SP3 and SP3a
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:
- Default instance: C:\Program Files\Microsoft SQL Server\Binn\Ssnetlib.dll
- Named instance: C:\Program Files\Microsoft SQL Server\MSSQL$<InstanceName>\Binn\Ssnetlib.dll
To find out whether you have the SP3 or SP3a setup folders, open the SP3Readme.htm file and read the first page. The service pack version, SP3a or SP3, is listed at the top of the first page.
1.5 Additional Information about SP3a
A list of the fixes contained in this service pack will be provided in Microsoft Knowledge Base article 306908. Each fix listed in 306908 has a link to a Knowledge Base article describing the problem addressed by the fix. Follow the links to the individual Knowledge Base articles to see information about each fix.
To find an article in the Knowledge Base
- From the Select a Microsoft Product list, select SQL Server 2000.
- In the Search for... field, type the number of the article you want.
- Under Search Type, select Article ID.
- Click the Go button.
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.
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.
1.6 Updated Books Online Documentation Is Available
Updated 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.
1.7 Updated SQL Server and Analysis Services Samples Are Available
Samples 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 SP3a
SP3a is distributed in the following ways:
- On a SQL Server 2000 Service Pack 3a CD-ROM that contains service packs for the following components:
- Database Components SP3a
- Analysis Services SP3a
- Desktop Engine SP3a
- In three self-extracting files that can be downloaded from the Internet at the Microsoft SQL Server Downloads Web site:
- Sql2ksp3.exe (Database Components SP3a)
- Sql2kasp3.exe (Analysis Services SP3a)
- Sql2kdesksp3.exe (Desktop Engine SP3a)
Note Both the SQL Server 2000 Service Pack 3a CD-ROM and Sql2kdesksp3.exe contain all of the files that are required to install a new instance of Desktop Engine.
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:
Note Some of the files in the service packs are system files. You cannot view them unless you follow this procedure: In Windows Explorer, on the View menu, click Options, click the View tab, and then select the Show all files check box.
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.
If you are unsure of the language of an instance of the SQL Server 2000 database engine or MSDE 2000:
- Click Start, then click Run.
- In the Open box, type Regedit, then click OK.
- Locate and select one of the following keys:
- For a default instance of the SQL Server 2000 database engine or MSDE 2000, locate and select this key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion
- For a named instance of the SQL Server 2000 database engine or MSDE 2000, locate and select this key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\CurrentVersion
where InstanceName is the name of the instance.
- In the right pane, read the value of the Language entry. Compare that value to the following table to determine the language for your instance of the SQL Server 2000 database engine or MSDE 2000:
| Language registry value (in hexadecimal) |
Language registry value (in decimal) |
Language of that instance |
| 0x00000404 |
1028 |
Traditional Chinese |
| 0x00000407 |
1031 |
German |
| 0x00000409 |
1033 |
English |
| 0x0000040a |
1034 |
Spanish |
| 0x0000040c |
1036 |
French |
| 0x00000410 |
1040 |
Italian |
| 0x00000411 |
1041 |
Japanese |
| 0x00000412 |
1042 |
Korean |
| 0x00000413 |
1043 |
Dutch |
| 0x00000416 |
1046 |
Portuguese (Brazil) |
| 0x0000041d |
1053 |
Swedish |
| 0x00000805 |
2053 |
Simplified Chinese |
Database Components and Analysis Services SP3a Files
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 Online Documentation 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) SP3a
Users 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 the original package name for your ProductCode value was SqlRun01.msi through SqlRun16.msi, you can upgrade your instance of MSDE 2000 using the SQL Server 2000 SP3a download files. See above for information on how to determine the language of an instance of MSDE 2000.
- If the original package name for your ProductCode value refers to a Microsoft application, see the following page for instructions on how to upgrade that instance of MSDE 2000: Microsoft MSDE Applications Web page
- If the ProductCode value is not listed in the article, then the instance was installed by the setup utility of an application. You cannot use the SQL Server 2000 SP3a download files to apply SP3a to such MSDE instances. Instead, you must get a patch file from the company that wrote the application. If the application came from a company other than Microsoft, you must contact that company for a patch file. If the application came from Microsoft, see the following page that lists the MSDE applications from Microsoft (this page will be updated with information about how to upgrade these instances of MSDE 2000): Microsoft MSDE Applications Web page
- If the original package name for your ProductCode value was Sample.msi or SampleUpg.msi, see Knowledge Base article 314131
Downloading MSDE 2000 SP3a
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:
- Go to the Microsoft SQL Server Downloads Web site.
- In the Full Download box in the upper-right of this page, select the language that matches the language for your instance of MSDE 2000, and click Go.
- On the next page, locate the Files in this Download section at the bottom of the page. Depending on the language, select one of these files:
- If you selected English, download the file SQL2KDeskSP3.exe to a folder on your computer.
- If you selected another language, download the file LLL_SQL2KDeskSP3.exe, where LLL varies by language.
Extracting the MSDE 2000 SP3a Files
After downloading either SQL2KDeskSP3.exe or LLL_SQL2KDeskSP3.exe, you must extract the MSDE 2000 SP3a files:
- Open Windows Explorer and navigate to the folder in which you downloaded the MSDE 2000 SP3a download file (either SQL2KDeskSP3.exe or LLL_SQL2KDeskSP3.exe, where LLL varies by language).
- Double click on the download file to extract the SP3a MSDE files. When asked, specify the folder into which the SP3a files can be extracted.
3.0 Service Pack Installation
To 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:
- Database Components
- Analysis Services
- Desktop Engine
- Failover cluster
- Replication server
The first sentence in each of the following sections indicates the component(s) to which that section applies.
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:
- The Devices: Unsigned driver installation behavior local security policy for Windows XP
- The Unsigned non-driver installation behavior local security policy for Windows 2000
If you use the Do not allow installation setting, you must change it to Silently succeed before installing SP3a. If necessary, you can return the policy to its previous setting after the installation is complete.
Note Do not allow installation is not the default setting for these security policies.
When installing 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 HREF="http://support.microsoft.com?kbid=258437" target=_blank>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.
- SQL Server 2000 service packs do not affect the ability to back up user databases from one release (original release or a service pack) of SQL Server 2000 or MSDE 2000 and restore them on another release, except when the user database is part of a replication topology. If the user database is not part of a replication topology, you can back it up from an instance running any release of SQL Server 2000 or MSDE 2000, and restore it on an instance running any other release of SQL Server 2000 or MSDE 2000. There are additional considerations for replication that are detailed in section 5.3.6 Backup and Restore Issues for Merge Replication
SP3a Setup upgrades user databases that are members of a replication topology. Before installing SP3a, make sure that replication databases and filegroups are writable and that the user account that is running Setup has permission to access the databases. For more information about applying SP3a to databases that are included in replication topologies, see Section 3.11 Installing on Replicated Servers.
If SP3a Setup detects user databases or filegroups that are not writable, it:
You can ignore this warning unless some of the databases listed in the Setup log are members of a replication topology. If any of the non-writable databases listed in the Setup log are members of a replication topology, you must make those databases writable and reapply 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.
For more information about making a database writable, see Section 3.12 Applying SP3a to Read-Only Databases or Filegroups. For more information about reapplying SP3a, see Section 3.14 Reapplying SP3a.
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:
- Take the database offline before applying SP3a.
- Apply SP3a to the instance.
- Bring the database back online.
- Log on to Query Analyzer and run the following script:
USE master
GO
EXEC sp_vpupgrade_replication
GO
If you apply SP3a without having taken offline all non-writeable databases shipping logs to publication databases, you will receive this error:
Error Running Script sp_vpupgrade_replication (1)
If you receive this error, follow the procedure above.
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 Databases
The 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 Repository
The 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\OLAP Server to a file for backup. If you have migrated your Analysis Services repository to SQL Server, back up the database that contains the repository before installing SP3a. For more information, see Uninstalling SQL Server 2000 Analysis Services SP3a.
3.3 Make Sure the System Databases Have Enough Free Space
The following information applies to all component installations except database client components and Analysis Services.
If the autogrow option is not selected for the master and msdb databases, the databases must have at least 500 kilobytes (KB) of free space. To verify that they have this space, run the sp_spaceused system stored procedure for the master or msdb database. If the unallocated space in either database is less than 500 KB, increase the size of the database. For more information, see "Expanding a Database" in SQL Server Books Online.
If the autogrow option is selected for the master and msdb databases, and there is sufficient room on the drives, you can skip this step.
To verify that the autogrow option is selected in SQL Server 2000, open SQL Server Enterprise Manager, right-click the icon for the database, and then click Properties. Verify that the Automatically grow file check box is selected.
To verify that this option is selected in MSDE 2000, use the osql command prompt utility to issue the following SQL statements:
- sp_helpdb master
- sp_helpdb msdb
In the output of these statements, verify that the value of the growth column is not 0.
3.4 Stop Services and Applications Before Running SP3a Setup
The 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:
- Microsoft Distributed Transaction Coordinator (MS DTC) and the Microsoft Search and MSSQLServerOLAPService services.
- The MSSQLServer and SQLServerAgent services for the instance being upgraded (for example, MSSQL$NamedInstance).
- Microsoft Component Services, Microsoft Message Queuing, and Microsoft COM Transaction Integrator.
- All applications, including Control Panel. (optional)
You can apply SP3a without restarting your computer by stopping these services and applications before applying the SP3a.
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 SP3a
The following information applies to all component installations except Desktop Engine and Analysis Services.
Run the Setup.bat script from one of the following locations:
- The directory on the local computer containing the extracted service pack files from SQL2KSP3.exe.
- The service pack directory on the SQL Server 2000 SP3a CD-ROM.
- The directory on the network share containing the extracted service pack files from SQL2KSP3.exe.
Note In order to install database components from a network share, you must first do one of the following:
- Map the network share containing the extracted service pack files to an available drive letter on the local computer, navigate to the directory on the mapped drive that contains Setup.bat, and then run Setup.bat.
- Start Setup by running the Setupsql.exe file found in \X86\Setup, which is a subfolder of the directory on the network share containing the extracted service pack files.
Setup displays a dialog box that prompts you for information, such as whether you want to use SQL Server Authentication or Windows Authentication. If you choose SQL Server Authentication, you must supply the Setup program with the password for the sa login. If you choose Windows Authentication, you must be running the Setup program while logged on to Windows using a Windows login account. This login account must be a part of the sysadmin fixed server role for the instance of SQL Server 2000 you are upgrading.
The Setup program then performs the following tasks:
- Displays an Authentication Mode dialog box if it detects that the installation is using Mixed Mode Authentication with a blank password for the sa login. Leaving the sa login password blank provides users with easy administrative access to SQL Server or Desktop Engine and is not recommended; protect your systems by enforcing an sa password or by using Windows Authentication. To learn more about this dialog, see Authentication Mode Dialog Box later in this section.
- Displays an SA Password Warning dialog box if it detects a blank password for the sa login. Although you can continue your installation with a blank password for the sa login by explicitly choosing to ignore the recommendation and continue Setup, a blank password poses a security risk and is not recommended. This dialog is displayed regardless of the authentication mode you use.
Note This password change is made immediately; even if Setup fails, the password is still changed.
- Displays a Backward Compatibility Checklist dialog box that warns of any backward compatibility issues that you may encounter when applying the service pack. To learn more about this dialog, see Backward Compatibility Checklist Dialog Box later in this section.
- Displays a dialog box if it detects that any databases or filegroups are not writable. At this point, if any of these databases or filegroups are part of a replication topology you can either:
- Stop the setup process, make these objects writable, and restart Setup.
- Continue with the current SP3a setup process and reapply SP3a at a later time after making these objects writable.
Note The previous steps are necessary only when applying SP3a to non-writable databases or filegroups that are part of a replication topology. For more information, see Section 3.12 Applying SP3a to Read-Only Databases or Filegroups.
- Displays an Error Reporting dialog box with information about how to enable the new error reporting feature that is included in SP3a. For more information about error reporting, see Section 5.10 Error Reporting.
- Updates MDAC components if necessary. For more information, see Section 5.5.1 Updates to Microsoft Data Access Components.
- Replaces existing SQL Server 2000 files with SP3a files.
- Runs Transact-SQL script files to update system stored procedures.
- Displays an option to reboot the computer in the final dialog box if Setup determines that a reboot is needed.
Authentication Mode Dialog Box
The Authentication Mode dialog box does not default to the current settings for the installation. The dialog box defaults are:
- On computers running Windows 98 or Windows Millennium Edition, the default setting is Mixed Mode Authentication (the only authentication mode supported on these operating systems). The dialog box prompts you to specify a password for the sa login. If you specify a password, Setup changes the sa login password. If you leave the password blank, Setup connects without changing the password.
- On computers running Windows NT 4.0 or Windows 2000, the default setting is Windows Authentication. Use the dialog box to switch to Windows Authentication Mode or Mixed Mode with an sa login password that is not blank.
Note Before changing the authentication mode or the password for the sa login, make sure that this change does not affect existing applications. For example, if you change an instance of SQL Server from using Mixed Mode Authentication to using only Windows Authentication, existing applications attempting to connect using SQL Server Authentication cannot connect until the authentication mode is set to Windows Authentication. Also, if you change the sa login password, applications or administrative processes using the old password cannot connect until they are configured to use the new password.
Important For security reasons, you should never have a blank password on the sa login.
The Setup program places a record of the actions it performs in the Sqlsp.log file. This log file is stored in the Windows directory of the computer on which Setup is run. If you upgrade multiple instances, only the most recent upgrade is recorded in this log.
Backward Compatibility Checklist Dialog Box
The Backward Compatibility Checklist dialog box lists backward compatibility issues that you may encounter when applying the service pack. The backward compatibility issues that appear in the checklist vary depending on the configuration of the instance of SQL Server 2000 that is being upgraded.
The following backward compatibility issues may be addressed in this dialog:
- When upgrading from SP2 or earlier, SP3a upgrades Microsoft Search service and automatically rebuilds the full-text catalogs for all applications using the service. During the rebuild, full-text functionality may not be fully available. You must select this check box before continuing Setup. For more information, see Section 5.1.5 Full-Text Catalogs Are Rebuilt After Setup Completes.
- Before you apply the service pack, you must upgrade your SQL Server 2000 master/target server configuration. The enhancements introduced with SP3a are compatible only with servers that are running SQL Server 2000 SP3 or SP3a. You must select this check box before continuing Setup. For more information, see Section 5.4.2 Changes to Master/Target Server Configurations.
- Cross-database ownership chaining is turned off by default in this service pack. After installation, you can enable cross-database ownership chaining for individual databases. Selecting this optional check box enables you to allow cross-database ownership chaining for all databases. For more information, see Section 5.1.11 Cross-Database Ownership Chaining.
Note Enabling cross-database ownership chaining for all databases is not recommended.
Note If you had previously enabled cross-database ownership chaining on a pre-release version of SP3 (before build 8.00.760) you will need to enable it again when installing SP3a.
3.6 Install Analysis Services SP3a
The following information applies only to Analysis Services.
To install Analysis Services SP3a, run Setup.exe from either of the following locations:
- The Msolap\Install subdirectory in the directory containing the extracted Analysis Services SP3a files from SQL2KASP3.exe.
- The Msolap\Install directory on the SP3a CD-ROM.
Setup then performs the following tasks:
- Opens a Setup dialog box that prompts you for information.
- Displays an Error Reporting dialog box with information about how to enable the new error reporting feature included in SP3a. For more information about error reporting, see 5.10 Error Reporting.
- Copies the necessary files, and completes the installation.
Additional Analysis Services Installation Issues
After installing Analysis Services 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:
- The repository is in the Meta Data Services format.
- The repository uses the msdb database.
- The OLAP Administrators group previously used the public role to access the repository.
Note This change also affects remote servers that access the Meta Data Services repository on a server that has been upgraded to SP3 or SP3a. Remote server logins must be added to the RepositoryUser role as well.
Note The OLAP Administrators group must be added to the RepositoryUser role before you restore a Meta Data Services repository that was backed up prior to upgrading to SP3 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 Install Desktop Engine SP3a
The 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:
The following sections cover the most common scenarios for either upgrading an existing instance of MSDE to SP3a, or installing a new instance of MSDE 2000 SP3a:
If you need additional information, the primary source of documentation for MSDE 2000 SP3a setup is in the SQL Server 2000 Books Online (Updated - SP3). There were some changes made to MSDE 2000 SP3 and SP3a that are not fully reflected in the SQL Server 2000 Books Online (Updated - SP3); those features are covered in this readme file. For more information about installing SQL Server 2000 Books Online (Updated - SP3), see section 1.6 Updated Books Online Documentation Is Available
3.7.1 Prerequisites for MSDE 2000 SP3a
The 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:
- Using Windows Explorer, navigate to the \MSDE\MSI folder on either the SQL Server 2000 SP3a CD, or in the folders that you extracted from SQL2KDeskSP3.exe.
- Run InstMsi20.exe.
- When prompted, reboot the computer.
3.7.2 Security Considerations for MSDE 2000 SP3a
The 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:
- The applications using MSDE 2000 can run using Windows Authentication.
- You have defined Windows Authentication logins for all users who need to connect to the instance. For more information about adding logins, see Adding a Windows User or Group in the SQL Server 2000 Books Online.
- The instance of MSDE 2000 is not running on Windows 98 or Windows ME.
For more information on changing an instance of MSDE 2000 from Mixed Mode to Windows Authentication Mode, see Microsoft Knowledge Base article 322336.
3.7.3 MSDE 2000 Setup Parameters
The 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:
- A command prompt Setup utility. The setup utility is usually called by an application setup utility, but can also be run from a command prompt window. The MSDE 2000 setup utility does not have a graphical user interface through which a user can control the behavior of the utility. Instead, this utility accepts a set of parameters that define what actions the utility should take.
- A set of Windows Installer merge modules. Developers who write Windows Installer-based setup utilities can code their application setup to install an instance MSDE 2000 by consuming the MSDE 2000 merge modules. Developers can control the configuration of the installed instance by specifying installation package properties that correspond to the MSDE 2000 setup utility parameters.
Users can use the command prompt MSDE 2000 Setup utility to either upgrade existing instances of MSDE or to install new instances of MSDE 2000. Users control the behavior of the MSDE 2000 Setup program by specifying parameters. The setup parameters can be specified in one of two ways:
- On the command line.
- In an .ini file read by the Setup utility.
Most of the parameters supported by the MSDE 2000 SP3a version of Setup are documented in the topic "Customizing Desktop Engine Setup.exe" in SQL Server 2000 Books Online (Updated – SP3). For more information about installing SQL Server 2000 Books Online, see section 1.6 Updated Books Online Documentation Is Available
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.
| Parameter name |
Description |
| ALLOWXDBCHAINING=1 |
Enables cross-database ownership chaining. For more information, see Section 5.1.11 Cross-Database Ownership Chaining. |
| SAPWD=sa_password |
Specifies the password to be assigned to the sa login when installing a new instance of MSDE 2000. SAPWD is ignored when you upgrade an existing instance of MSDE 2000, so you should ensure the sa login has a strong password before upgrading. You should always specify a strong sa password, even when using Windows Authentication Mode. While the SAPWD property not written to the installation log file when running Setup.exe, it is if you install using merge modules. |
| /upgradesp
{
SQLRUN
|
[<MSIPath>]SqlRunXX.msi
}
|
Specifies that setup will upgrade an existing instance of MSDE 2000 to SP3a. For SP3 and later, this switch replaces the /p switch supported by earlier versions of Setup. Do not use the /p switch with SP3 or later. When upgrading to SP3a, you are no longer required to specify the .msi file used to install the existing instance of MSDE 2000.
Most users simply specify SQLRUN, in which case the MSDE 2000 SP3a setup utility determines which .msi file to use. When you specify SQLRUN without specifying an INSTANCENAME, setup will upgrade the default instance of MSDE 2000. If you specify both SQLRUN and an INSTANCENAME, setup will upgrade the instance you specified using the INSTANCENAME parameter.
When you specify the name of an MSDE 2000 .msi installation package file, setup will upgrade whichever instance on the computer was originally installed with a merge module of the same name. For example, if you specify SqlRun01.msi, setup will upgrade whichever instance of MSDE 2000 was originally installed using SqlRun01.msi. MSIPath is the path to the folder holding the .msi file. MSIPath defaults to Setup\.
For examples of specifying /upgradesp, see section 3.7.4 Upgrading Existing Instances of MSDE 2000 To SP3a
|
| UPGRADEUSER=AnAdminLogin |
Specifies the login to be used when you upgrade an instance of either MSDE 1.0 or MSDE 2000 using SQL Server Authentication. The login must be a member of the sysadmin fixed server role. This parameter is only used when you specify SECURITYMODE=SQL when upgrading an instance of MSDE. |
| UPGRADEPWD=
AdminPassword
|
Specifies the password for the login specified in UPGRADEUSER when you upgrade Desktop Engine using SQL Server Authentication. |
Desktop Engine SP3 introduced a new DISABLENETWORKPROTOCOLS parameter. The behavior of DISABLENETWORKPROTOCOLS changed in the Desktop Engine SP3a to be more secure by default.
These are the behaviors of DISABLENETWORKPROTOCOLS in SP3a.
| DISABLENETWORKPROTOCOLS Value |
Upgrading Existing Instance |
Installing New Instance |
| 1 |
Instance is configured with all server Net-Libraries disabled. |
Instance is configured with all server Net-Libraries disabled. |
| 0 |
The existing server Net-Library configuration is retained. |
Instance is configured with default server Net-Libraries and addresses enabled. |
| Parameter not specified, or is any value other than 0 or 1 |
The existing server Net-Library configuration is retained. |
Instance is configured with all server Net-Libraries disabled. |
In SP3, DISABLENETWORKPROTOCOLS has two differences in behavior compared to SP3a:
- When installing a new instance using SP3, and DISABLENETWORKPROTOCOLS is either not specified or set to a value other than 0 or 1, then the instance is installed with the default Net-Libraries and addresses enabled. In SP3a, the Net-Libraries are disabled.
- Whenever all Net-Libraries are disabled for an instance of MSDE 2000 SP3, the instance will still use UDP port 1434. In SP3a, the instance will not use UDP port 1434 in that configuration. For more information, see section 5.1.15 Operations On UDP Port 1434.
For more information about default Net-Library configurations, see the topic "Controlling Net-Libraries and Communications Addresses" in SQL Server 2000 Books Online.
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.
| Parameter name |
Merge Module Property |
| ALLOWXDBCHAINING |
SqlAllowXDBChaining |
| DISABLENETWORKPROTOCOLS |
SqlDisableNetworkProtocols |
| SAPWD |
SqlSaPwd |
| UPGRADEUSER |
SqlUpgradeUser |
| UPGRADEPWD |
SqlUpgradePwd |
For information about the merge module properties that correspond to other Setup parameters, see the topic "Using the SQL Server Desktop Engine Merge Modules" in SQL Server 2000 Books Online.
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 SP3a
The 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
- Open a command prompt window.
- From the command prompt, use the cd command to navigate to the folder containing the MSDE 2000 SP3a setup utility:
cd c:\MSDESP3aFolder\MSDE
where c:\MSDESP3aFolder is either the path to the folder into which you extracted the MSDE 2000 SP3a files, or the Desktop Engine SP3a folder on the SQL Server 2000 SP3a CD.
- Execute one of the following commands:
- For a default instance using Windows Authentication Mode, execute:
setup /upgradesp sqlrun DISABLENETWORKPROTOCOLS=1
- For a named instance using Windows Authentication Mode, execute:
setup /upgradesp sqlrun INSTANCENAME=InstanceName DISABLENETWORKPROTOCOLS=1
- For a default instance using Mixed Mode (where AnAdminLogin is a member of the sysadmin fixed server role), execute:
setup /upgradesp sqlrun SECURITYMODE=SQL UPGRADEUSER=AnAdminLogin
UPGRADEPWD=AdminPassword DISABLENETWORKPROTOCOLS=1
- For a named instance using Mixed Mode (where AnAdminLogin is a member of the sysadmin fixed server role), execute:
setup /upgradesp sqlrun INSTANCENAME= InstanceName SECURITYMODE=SQL
UPGRADEUSER=AnAdminLogin UPGRADEPWD=AdminPassword DISABLENETWORKPROTOCOLS=1
Important If you use an .ini file during setup, avoid storing credentials in the .ini file.
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 SP3a
The 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
- Open a command prompt window.
- From the command prompt, use the cd command to navigate to the folder containing the MSDE 2000 SP3a setup utility:
cd c:\MSDESP3aFolder\MSDE
where c:\MSDESP3aFolder is either the path to the folder into which you extracted the MSDE 2000 SP3a files, or the Desktop Engine SP3a folder on the SQL Server 2000 SP3a CD.
- Execute one of the following commands:
- To install a default instance configured to use Windows Authentication Mode, execute:
setup SAPWD="AStrongSAPwd"
Where AStrongSAPwd is a strong password to be assigned to the sa login.
- To install a named instance configured to use Windows Authentication Mode, execute:
setup INSTANCENAME="InstanceName" SAPWD="AStrongSAPwd"
Where AStrongSAPwd is a strong password to be assigned to the sa login and InstanceName is the name to be assigned to the instance.
- To install a default instance configured to use Mixed Mode, execute:
setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL
Where AStrongSAPwd is a strong password to be assigned to the sa login.
- To install a named instance configured to use Mixed Mode, execute:
setup INSTANCENAME="InstanceName" SECURITYMODE=SQL SAPWD="AStrongSAPwd"
Where AStrongSAPwd is a strong password to be assigned to the sa login and InstanceName is the name to be assigned to the instance.
Important If you are using an .ini file during setup, avoid storing credentials in the .ini file.
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 SP3a
The 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
- Open a command prompt window.
- From the command prompt, use the cd command to navigate to the folder containing the MSDE 2000 SP3a setup utility:
cd c:\MSDESP3aFolder\MSDE
where c:\MSDESP3aFolder is either the path to the folder into which you extracted the MSDE 2000 SP3a files, or the Desktop Engine SP3a folder on the SQL Server 2000 SP3a CD.
- Execute one of the following commands:
Note If you use BLANKSAPWD=1, you are not required to specify SECURITYMODE=SQL or UPGRADEUSER and UPGRADEPWD.
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 SP3a
The 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:
For more information about installing SQL Server 2000 Books Online (Updated - SP3), see section 1.6 Updated Books Online Documentation Is Available.
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.
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:
- Instruct their customers to install Desktop Engine SP3a from either the SQL Server 2000 SP3a download page or a SQL Server 2000 SP3a CD
- Download the SQL2KDeskSP3.exe download file and distribute that to their customers with instructions on how to apply the service pack.
- Using the Desktop Engine SP3a files, build a service pack application utility that will install SP3a on the instances of MSDE 2000 installed by the application.
Application vendors who elect to instruct their customers to apply SP3a directly should develop a set of instructions customized to the needs of their customers.
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
3.7.8 MSDE 2000 SP3a Files
The following information applies only to Desktop Engine (MSDE 2000).
All Desktop Engine SP3a installation files and folders are located in the \MSDE folder:
- On the SQL Server 2000 SP3a CD-ROM
- In the directory containing the extracted service pack files from SQL2KDeskSP3.exe
The \MSDE folder holds the SP3readme.htm file, the readme.txt file, the license.txt file, and the executable files for the Setup utility. It also has the following subfolders:
- \Msi: contains the executable files needed to install Windows Installer, or upgrade Windows Installer if it is earlier than 2.0.2600.0.
- \MSM: Contains the merge modules needed for a merge module setup.
- \Setup: Contains the .msi installation package files required by the MSDE 2000 setup to install a new instance of MSDE 2000 SP3a, or the msp patch package files to upgrade existing instances of MSDE 2000 to SP3a, and the sqlrun.cab cabinet file containing the files installed by Setup.
For a description of using the Desktop Engine merge modules, see the topic "Using SQL Server Desktop Engine Merge Modules" in SQL Server Books Online.
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 Services
The following information applies to all components.
When Setup completes, it may prompt you to restart the system. After the system restarts (or after Setup completes without requesting a restart), use the Services application in Control Panel to make sure that 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 Applications
The following information applies to all components.
Restart the applications you closed before running the service pack Setup program.
3.10 Installing on a Failover Cluster
The following information applies only to SQL Server 2000 components that are part of a failover cluster.
To install the service pack on a failover cluster
- If any resources have been added with dependencies on SQL Server resources, those dependencies must either be removed or taken offline before you install SP3a. If you do not do this, the installation of SP3a may cause those dependent resources to fail over.
- Run the service pack from the node that owns the group containing the virtual server that you are going to upgrade. This installs the service pack files on all nodes in the failover cluster.
- In the Setup dialog box, type the name of the virtual server that you are upgrading.
- Keep all nodes of the cluster online during setup. This ensures that the upgrade is applied to each node of the cluster.
- If you removed dependencies or took resources offline in Step 1 above, add back the dependencies or bring the resources back online.
Note Setup might require rebooting of the failover cluster nodes. This replaces the files that were in use during setup with the updated files.
If you are upgrading a default (non-clustered) instance of SQL Server to a virtual server, first, you must upgrade the default (non-clustered) instance to a virtual instance, and then apply 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
- Rebuild the node in the failover cluster. For more information about rebuilding a node, see "How to recover from failover cluster failure in Scenario 1" in SQL Server Books Online.
- Run the original SQL Server 2000 Setup program to add the node back to the failover cluster.
- Run SP3a Setup on the newly added node. This will update to SP3a only the binaries on the new node.
Note If you run Setup from the node where the virtual server is running, you must reapply SP3a to all the nodes. You must also rerun the database upgrade scripts.
When installing Analysis Services SP3a on a cluster, each instance must be upgraded separately.
To install SP3a on an Analysis Services cluster
- Install SP3a on a failover node.
- Fail over to the newly upgraded node.
- Repeat steps 1 and 2 until all instances in the cluster are upgraded.
3.11 Installing on Replicated Servers
The following information applies only to SQL Server 2000 components that are part of a replication topology:
- You must upgrade the Distributor before you upgrade the Publisher
- For replication topologies based on transactional replication with read-only Subscribers, you can upgrade the Subscriber before or after the Publisher and Distributor.
- For replication topologies based on merge replication or transactional replication with updating Subscribers, you must upgrade the Subscriber after the Publisher and Distributor.
Note In many cases, especially in merge replication, the Distributor and Publisher are on the same server and are upgraded at the same time.
Installing 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.
| Server A |
Server B |
| Publisher/Distributor for merge replication |
Subscriber for merge replication |
| Subscriber for transactional replication with updating |
Publisher/Distributor for transactional replication with updating |
Example 2: Topology That Allows Sequential Upgrades
In this example, you can upgrade Server A first because the read-only transactional publication allows a Subscriber to be upgraded before the Publisher/Distributor.
| Server A |
Server B |
| Publisher/Distributor for merge replication |
Subscriber for merge replication |
| Subscriber for read-only transactional replication |
Publisher/Distributor for read-only transactional replication |
Additional Replication Installation Issues
Important Before you upgrade to 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 reapply SP3a 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 Filegroups
The following information applies only to SQL Server 2000 components that are part of a replication topology.
When non-writable databases or filegroups exist, Setup displays the following message:
Setup has detected one or more databases and filegroups which are not writable.
In general, you can ignore this warning and setup will continue. However, if any of the non-writable databases listed in the Setup log are members of a replication topology, you must make those databases writable and reapply 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
- Make the read-only database writable using the ALTER DATABASE statement, as follows:
ALTER DATABASE database SET READ_WRITE
- Repeat Step 1 for all read-only databases.
- Apply (or reapply) the service pack.
- If required, make the database read-only again using ALTER DATABASE, as follows:
ALTER DATABASE database SET READ_ONLY
To apply SP3a to a read-only filegroup
- Make the read-only filegroup writable using ALTER DATABASE, as follows:
ALTER DATABASE Database
MODIFY FILEGROUP filegroup_name READWRITE
- Repeat Step 1 for all read-only filegroups.
- Apply (or reapply) the service pack.
- Make the filegroup read-only again using ALTER DATABASE, as follows:
ALTER DATABASE Database
MODIFY FILEGROUP filegroup_name READONLY
For more information about ALTER DATABASE, see the "ALTER DATABASE" reference topic in SQL Server Books Online. For more information about reapplying SP3a, see Section 3.14 Reapplying SP3a.
3.13 Uninstalling SP3a
The 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:
- In SQL Server Enterprise Manager, expand a SQL Server group, expand a server, right-click the Replication folder, and then click Configure Publishing, Subscribers, and Distribution.
- Click the Publication Databases tab.
- Clear the check box for each database that is involved in replication. This allows the databases to be detached.
To revert to the pre-SP3a version of SQL Server
- Detach all user databases. For more information, see "How to attach and detach a database (Enterprise Manager)" in SQL Server Books Online.
- Uninstall SQL Server. In Control Panel, double-click Add/Remove Programs, and then select the instance of SQL Server that you want to uninstall.
- Install SQL Server 2000 from the CD-ROM or the location from which you originally installed SQL Server.
- Apply any service packs and QFE fixes that were installed prior to SP3a.
- Restore the databases master, msdb, and model from the last backup that was created before applying SP3a. This automatically attaches any user databases that were attached at the time the backup was created, assuming that the location of the data files has not changed.
- Attach any user databases that were created after the last backup of the master database.
- Configure replication if necessary.
Warning When you revert to the pre-SP3a version of SQL Server, all changes made to the databases master, msdb, and model since applying SP3a are lost.
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
- Uninstall SQL Server 2000 Analysis Services. In Control Panel, double-click Add/Remove Programs, click SQL Server 2000 Analysis Services, and click Remove.
- Reinstall SQL Server 2000 Analysis Services from the CD-ROM or the location from which you originally installed Analysis Services.
- Apply any service packs and QFE fixes that were installed prior to installing SP3a.
- Remove the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server registry key.
- Reinstall the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server registry key from the pre-SP3a backup.
3.14 Reapplying SP3a
The following information applies to all components.
In the following cases, you must reapply SP3a:
- If new SQL Server 2000 components are added after SP3a is applied.
- If databases or filegroups that are part of a replication topology and were read-only during the initial application of SP3a are made writable.
To reapply SP3a, follow the steps in Section 3.0 Service Pack Installation.
4.0 Additional Installation Considerations
This section documents additional service pack installation considerations that apply only in special cases.
4.1 Unattended Installations
Database Components 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:
- Sql2kdef.iss is the unattended setup file used to apply Database Components SP3a to a default instance of SQL Server 2000 running on Windows 2000 or Windows NT 4.0. The corresponding batch