Service Pack 4 for Microsoft SQL Server 2000

Desktop Engine (MSDE 2000)

March 29, 2005

© Copyright Microsoft Corporation, 2004. 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. Please submit your feedback in English.

To submit written feedback about this document, click here:  Submit feedback.
 

Contents

1.0 Introduction

    1.1 System Requirements

    1.2 Before You Upgrade to MSDE 2000 SP4

    1.3 Security Considerations for MSDE 2000 SP4

    1.4 Determine the Instance Name

    1.5 Verify the Version of Microsoft Data Access Components

    1.6 Identify the Current Version of MSDE 2000

    1.7 Additional Information About SP4

    1.8 Updates to SQL Server 2000 Books Online Are Available

2.0 Where to Find and Download MSDE 2000 SP4

    2.1 Choosing the Correct Language

    2.2 Downloading MSDE 2000 SP4

    2.3 Extracting the MSDE 2000 SP4 Files

    2.4 Download and Extraction Phase Guidelines

3.0 Service Pack Installation

    3.1 Prepare for MSDE 2000 SP4 Installation

    3.2 Run MSDE 2000 SP4 Setup

    3.3 Restart Services and Applications

    3.4 Install MSDE 2000 on Replicated Servers

    3.5 Apply MSDE 2000 SP4 to Read-Only Databases or Filegroups in a Replication Topology

    3.6 Upgrade the Catalog of Linked Servers

    3.7 Uninstall MSDE 2000 SP4

    3.8 Reapply MSDE 2000 SP4

4.0 Additional Installation Considerations

    4.1 Redistributing MSDE 2000 SP4

    4.2 MSDE 2000 SP4 File Locations

5.0 Documentation Notes

    5.1 MSDE 2000 Enhancements

    5.2 Replication Enhancements

    5.3 SQL Server Agent and Shared Tools Enhancements

    5.4 XML Enhancements

    5.5 DB-Library and Embedded SQL for C

    5.6 MSDE 2000 Setup Enhancements

    5.7 Serviceability Enhancements

[Top]

1.0 Introduction

This Readme file describes how to use the SQL Server Desktop Engine (MSDE) part of Microsoft® SQL Server™ 2000 Service Pack 4 (SP4). You can use Desktop Engine SP4 either to upgrade existing instances of MSDE to MSDE 2000 SP4 or to install a new instance of MSDE 2000 SP4.

The general process for installing SQL Server SP4 is as follows:

1. Determine whether you can use SP4 and if so, which part or parts of SP4 you need to install. Make sure to review all parts of Section 1.0 of this Readme before you download and install SP4.
   
   
2. Download and extract the service pack installation files. Section 2.0 describes how to obtain the SP4 installation files.
   
   
3. Prepare an instance for upgrade to SP4. Section 3.0, and Sections 3.1 through 3.3 detail the preparatory steps to take before you install SP4.
   
   
4. Install SP4. Section 3.2 details options for running SP4 Setup.

Note   Unless specified otherwise, all references to SQL Server 2000 Desktop Engine in this Readme are to MSDE 2000 Release A. The MSDE 2000 Release A download is functionally equivalent to SQL Server 2000 Desktop Engine SP3a, but includes a new End-User License Agreement (EULA) that gives users rights that are different from those in earlier MSDE 2000 licenses.

SQL Server 2000 SP4 has four parts. You use each part to apply SP4 to different SQL Server components:

• MSDE 2000 SP4 updates instances of SQL Server 2000 MSDE 2000 and MSDE 2000 Release A. The components updated by MSDE 2000 SP4 include:
  • The MSDE 2000 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.
    
    
  • Those parts of replication and Data Transformation Services (DTS) that are included with MSDE 2000.
• Database Components SP4 updates an instance of the database engine for SQL Server 2000 Enterprise Edition, Standard Edition, Developer Edition, Enterprise Evaluation Edition, or Personal Edition.
  
  
• Analysis Services SP4 updates the various parts of SQL Server 2000 Analysis Services in a SQL Server 2000 installation.
  
  
• SQL Server 2000 SP4 (64-bit) includes all updates for SQL Server 2000 (64-bit). This is the first service pack for SQL Server 2000 (64-bit).

All SQL Server service packs are cumulative. SQL Server SP4 includes the fixes delivered in SP1, SP2, SP3, and SP3a.

MSDE 2000 SP4 can only be used on instances of SQL Server 2000 Desktop Engine or MSDE 2000 Release A. The other parts of SQL Server 2000 SP4 apply SP4 to the other SQL Server 2000 components, such as Analysis Services or the database engine. Separate Readme files describe how to use Database Components SP4 and Analysis Services SP4. The other Readme files are available at this Microsoft Web site.

[Top]

Uses of MSDE 2000 SP4

The service pack for MSDE 2000 is intended for developers who create redistributable applications that use MSDE. MSDE 2000 SP4 can be used to do the following:

• Upgrade an existing instance of 2000 MSDE 2000 or MSDE 2000 Release A to MSDE 2000 SP4, if the instance was originally installed by using the MSDE 2000 Setup program.
  
  
• Install a new instance of MSDE 2000 SP4, if you have an MSDE 2000 license that lets you install instances of MSDE 2000.
  
  
• Upgrade an instance of MSDE 1.0 to MSDE 2000, if you have an MSDE 2000 license that lets you run instances of MSDE 2000.
  
  
• Provide the files for a developer to write an application setup program that can install an instance of MSDE 2000 SP4 as part of the installation of the application. The developer must have a license to distribute MSDE with applications.
  
  
• Provide the files needed for a developer to build a patch module to distribute to customers whose applications installed an instance of MSDE 2000 by using the MSDE 2000 merge modules.

For more information about MSDE 2000 licensing, see Uses of MSDE 2000. If you do not already have a license to install or run MSDE 2000, you can obtain one by registering on the MSDE 2000 Release A Web page.

[Top]

Before You Begin Installation of MSDE 2000 SP4

To install MSDE 2000 SP4, review the material in Section 1 and Section 2 of this Readme, and follow the instructions in Section 3 if the following apply:

• You have determined that you can apply MSDE 2000 SP4 to your existing instance of MSDE.
  
  
• You are installing a new instance of MSDE 2000.
  
  
• You are incorporating the MSDE 2000 SP4 files into the setup utility of an application.

[Top]

What to Expect During Upgrade and Installation

The following sections in this Readme contain examples of the most common scenarios to upgrade an existing instance of MSDE to MSDE 2000 SP4 or install a new instance of MSDE 2000:

• 3.2.2 Upgrading Existing Instances of MSDE 2000 To MSDE2000 SP4
  
  
• 3.2.3 Installing a New Instance of MSDE 2000 SP4
  
  
• 3.2.4 Upgrading MSDE 1.0 to MSDE 2000 SP4

[Top]

1.1 System Requirements

Your computer must meet these hardware and software requirements before you attempt to run MSDE 2000 SP4 Setup.

Hardware Requirements

This table shows the hardware requirements for installing and running MSDE 2000.

MSDE 2000 does not have a hardware compatibility list (HCL). If your computer meets the minimum requirements listed in the preceding table, MSDE 2000 software works on the hardware certified for use with the Windows operating system. For more information about hardware certified for use with the Windows operating system, see the Windows Hardware Compatibility List on this Microsoft Web site.

[Top]

Operating System Requirements

To use MSDE 2000, you must have installed one of these operating systems:

• Windows Server 2003 Standard Edition, Windows Server 2003 Enterprise Edition, Windows Server 2003 Datacenter Edition, Windows Server 2003 Web Edition.
  
  
• Windows 2000 Server, Windows 2000 Advanced Server, Windows 2000 Datacenter Server.
  
  
• Windows XP Professional, Windows XP Home Edition.
  
  
• Windows 2000 Professional.

Important   SQL Server 2000 SP4 is not supported on Windows NT 4.0, Windows Millennium Edition, and Windows 98 platforms. SQL Server 2000 SP3a installations on Windows NT 4.0, Windows Millennium Edition and Windows 98 platforms continue to receive critical hotfix support for 12 months after the final release of SQL Server 2000 SP4.

[Top]

Software and System Requirements

File and print sharing must be active if you want to run MSDE 2000 Setup.

To verify that file and print sharing are active

1. In Control Panel, double-click Network Connections.
   
   
2. On the Advanced menu, click Advanced Settings.
   
   
3. On the Adaptors and Bindings tab, ensure that File and Print Sharing for Microsoft Networks is selected.

An MSDE 2000 SP4 installation fails 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 you install MSDE 2000 SP4. If necessary, you can return the policy to its previous setting after installation is complete.

Note   Do not allow installation is not the default setting for these security policies.

To set security policies

1. In Control Panel, double-click Administrative Tools.
   
   
2. Double-click Local Security Policy.
   
   
3. Expand Local Policies.
   
   
4. Select Security Options.
   
   
5. Ensure that the following option in the right pane is set to Silently Succeed before you install MSDE 2000 SP4:
   • On Windows XP and Windows 2003: Devices: Unsigned driver installation behavior.
     
     
   • On Windows 2000: Unsigned non-driver installation behavior.

You can install the upgrade file for MSDE 2000 SP4 from a CD-ROM only if you use Microsoft Windows Installer 2.0.2600.0 or later. If you need to upgrade Windows Installer, MSDE 2000 SP4 contains the necessary files.

To upgrade Windows Installer

1. In Windows Explorer, navigate to the \MSDE\MSI folder, which is located either on the SQL Server 2000 SP4 CD or in the folders that you extracted from the self-extracting Service Pack 4 download file SQL2000.MSDE-KB884525-SP4-x86-LLL.exe, where LLL varies by language.
   
   
2. Run InstMsi20.exe.
   
   
3. When prompted, reboot the computer.

[Top]

Application Requirements

If your instance of MSDE is being used by an application, before you upgrade to MSDE 2000 SP4 ask the provider of the application whether any MSDE upgrade considerations apply to that application.

[Top]

1.2 Before You Upgrade to MSDE 2000 SP4

This section describes issues that you must address and tasks you must perform before using MSDE 2000 SP4 to upgrade an existing instance of MSDE to MSDE 2000 SP4.

Databases or database backups created on an instance of Database Components SP4 can be attached or restored on an earlier version of SQL Server 2000. However, there are restrictions for databases in a replication topology. For more information, see Section 1.2.3 Considerations for an Instance in a Replication or Log Shipping Topology.

[Top]

1.2.1 Determine if You Can Apply MSDE 2000 SP4 to an Existing Instance of MSDE 2000

The procedures to apply MSDE 2000 SP4 to an existing instance of MSDE 2000 vary according to the manner in which the instance was installed. Most applications install MSDE 2000 in one of the following ways:

• By running MSDE 2000 Setup. Instances of MSDE 2000 installed this way can be upgraded to SP4 by using the MSDE 2000 SP4 files. The MSDE 2000 setup utility can be run from the command prompt, or it can be called by the setup utility of an application.
  
  
• By directly consuming the MSDE 2000 merge modules. This method of installation is possible when the setup utility of the application uses Windows Installer technology. Instances of MSDE 2000 installed this way cannot be upgraded by means of the MSDE 2000 SP4 files. To upgrade, you must ask the application vendor for a patch file. MSDE 2000 SP4 provides merge modules to support existing applications that use merge modules.

  Note   Setup utilities for new applications must be written to call the MSDE 2000 Setup utility rather than written to consume the MSDE 2000 merge modules directly. MSDE 2000 SP4 includes merge modules. However, these merge modules can be used only by application vendors who must build patch files for MSDE instances that they originally installed by means of utilities that directly consumed merge modules.

If multiple instances of MSDE 2000 are on a computer, you must evaluate each instance individually to determine whether you can apply MSDE 2000 SP4. You must also apply SP4 separately to each instance.

Note   Microsoft does not support configurations where more than 16 instances of the SQL Server database engine are on one computer. These include instances of SQL Server 6.5, SQL Server 7.0, SQL Server 2000, MSDE 1.0, and MSDE 2000.

Microsoft Knowledge Base Article 311762 contains instructions to determine the way in which an instance of MSDE 2000 was installed. After you follow the procedures in article 311762, keep the following points in mind:

• If information indicates that the instance was installed by using the MSDE 2000 setup files SqlRun01.msi through SqlRun16.msi, you can upgrade your instance of MSDE 2000 by using the SQL Server 2000 SP4 download files.
  
  
• If the information indicates that the instance was installed by a Microsoft application, see the instructions to upgrade that instance of MSDE 2000 at this Microsoft Web site.
  
  
• Knowledge Base Article 311762 directs you to read the value of a ProductCode field in a registry key and to find the value in a table of known values. If the value in your registry key is not listed in article 311762, then the instance was installed by the setup utility of an application. You cannot use the download files of SQL Server 2000 SP4 to apply SP4 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 ask 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 is kept up to date with information about how to upgrade these instances of MSDE 2000): Microsoft MSDE Applications Web page
  
  
• If article 311762 indicates that the original package name for your ProductCode value was Sample.msi or SampleUpg.msi, see Knowledge Base article 314131 for instructions on how to build a patch for this instance of MSDE 2000.

[Top]

1.2.2 Determine if the Original MSDE 2000 Installation Files Are Required

You can run the MSDE 2000 SP4 Setup program from your hard disk, a network share, or a CD-ROM. The original MSDE 2000 installation files are not required if you run Setup from your hard disk. But if you upgrade an instance of MSDE 2000 to MSDE 2000 SP4 from a network share or a CD, the files that were used to install MSDE 2000 originally must be in the same location they occupied during that original installation. If the original installation was from a CD, MSDE 2000 SP4 setup will request the original CD during the upgrade. If the original files are no longer in their original location on the network share, or the original CD-ROM is not available, you must copy the MSDE 2000 SP4 files to your hard drive and run Setup there.

[Top]

1.2.3 Considerations for an Instance in a Replication or Log Shipping Topology

MSDE 2000 SP4 Setup upgrades user databases that are members of a replication topology. This upgrade factor can affect backup-and-restore functionality of replicated user databases. Before you install MSDE 2000 SP4, ensure that replication databases and filegroups are writable.

For more information about applying SP4 to databases that are included in replication topologies, see Section 3.4 Install MSDE 2000 on Replicated Servers. Additional backup and restore considerations for replication are detailed in Section 5.2.4 Backup and Restore Issues for Merge Replication.

Note   If an instance of MSDE 2000 is not part of a replication topology, you can back up a user database and restore it on any other release of SQL Server 2000 or MSDE 2000.

If Setup detects user databases or filegroups that are not writable, it does the following:

• Applies the SP4 replication updates to all writable user databases.
  
  
• Writes a list of the non-writable databases to the Setup log, which is located at Winnt\Sqlsp.log.
  
  
• Displays the following warning message:

  Setup has detected one or more databases and filegroups which are not writable.

You can ignore this warning unless some of the databases listed in the Setup log are members of a replication topology. If any of the non-writable databases listed in the Setup log are members of a replication topology, you must make those databases writable and reapply SP4 to that instance of MSDE 2000.

For information about making a database writable, see Section 3.5 Apply MSDE 2000 SP4 to Read-Only Databases or Filegroups in a Replication Topology. For more information about reapplying SP4, see Section 3.8 Reapply MSDE 2000 SP4.

[Top]

1.2.4 Determine How to Remove MSDE 2000 SP4

Before you use MSDE 2000 SP4 to upgrade an existing instance of MSDE, it is advisable to plan how to return the instance to its previous state, in case that becomes necessary later. When MSDE 2000 SP4 is installed, it makes changes to the system tables for maintenance purposes. It also upgrades user and distribution databases that are members of a replication topology. Because of the nature of these changes, MSDE 2000 SP4 cannot be removed easily. To revert to the build that you were running before you installed MSDE 2000 SP4, you must first uninstall the instance of MSDE 2000 and then reinstall that instance. Second, if you ran a previous SQL Server 2000 service pack or applied any hotfixes, you must reapply the service pack and hotfixes to the instance being reverted.

Important   To restore your system safely to its pre-MSDE 2000 SP4 state, you must have made backups of the master, model, and msdb databases immediately before you installed MSDE 2000 SP4. For more information, see Section 3.1.1 Back Up Your SQL Server Databases.

For more information, see Section 3.7 Uninstall MSDE 2000 SP4.

[Top]

1.3 Security Considerations for MSDE 2000 SP4

The behavior of MSDE 2000 Setup was changed in SP3a so that the default settings resulted in a more secure configuration. These changes are carried forward in SP4, as follows:

• Network Support is Disabled by Default

  SP4 disables the network support by default when new instances of MSDE 2000 are installed. When an existing instance is upgraded, its network support settings are retained. If no application on another computer connects to your instance of MSDE 2000, the instance has no need for network support. It is prudent to turn off a resource that is not being used. You can enable network support during setup if you specify a value of 0 for the DISABLENETWORKPROTOCOLS setup parameter. If you enable network support when you install an instance of MSDE 2000 SP4, you can later reconfigure the instance to disable the support. For more information about how to disable and restore network access, see Microsoft Knowledge Base article 814130.

• Setup Requires Strong sa Password

  The MSDE 2000 SP4 Setup does not install a new instance of MSDE 2000 unless you specify a strong sa password. Use the SAPWD parameter to specify the strong sa password. The MSDE 2000 SP4 Setup does not upgrade an existing instance of MSDE 2000 unless you assign a strong password to the sa login. You must assign a strong password to the sa login, even when you upgrade an existing instance, unless the application that uses your instance of MSDE depends in some way on a null sa password. Even if the instance of MSDE 2000 uses Windows Authentication, sa login immediately becomes active if the instance is switched to Mixed Mode Authentication. A null, blank, simple, or well-known sa password is vulnerable to use for unauthorized access. If you need to assign a strong sa password before you upgrade your instance of MSDE 2000 to MSDE 2000 SP4, see Microsoft Knowledge Base article 322336.

• Windows Authentication

  Whenever possible, for greater security, use Windows Authentication with your MSDE 2000 installation. Consider switching from Mixed Mode Authentication to Windows Authentication if both of the following are true:

  • The applications that use MSDE 2000 can run if the Windows Authentication option is selected.
    
    
  • You have defined Windows Authentication logins for all users who need to connect to the instance. For more information about how to add logins, see "Adding a Windows User or Group" in SQL Server 2000 Books Online.

  For more information on changing an instance of MSDE 2000 from Mixed Mode Authentication to Windows Authentication, see Microsoft Knowledge Base article 322336.

[Top]

1.4 Determine the Instance Name

You must know the instance names if you want to install multiple copies, or instances, of MSDE 2000 and the SQL Server 2000 database engine on one computer. You can have up to 16 instances on a computer. One instance has no instance name and is called the default instance. The other 15 instances must have unique instance names and are called named instances.

You use the MSDE SP4 Setup program to install or upgrade instances of MSDE. 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. You cannot use MSDE 2000 SP4 to upgrade an instance of the SQL Server 2000 database engine.

To find the existing instances on your computer:

1. In Windows Explorer, right-click My Computer, and then click Manage.
   
   
2. Expand Services and Applications.
   
   
3. Click Services.

A default instance appears in the right list pane as a service named MSSQLSERVER. Named instances are listed as services named MSSQL$InstanceName, where InstanceName is the name of the instance.

Instance names must follow the rules that are on this Microsoft Web page.

[Top]

1.5 Verify the Version of Microsoft Data Access Components

MSDE 2000 SP4 setup determines whether to upgrade an installed version of Microsoft Data Access Components (MDAC) to MDAC 2.8 SP1:

• Computers that run Windows 2000 are upgraded to MDAC 2.8 SP1.
  
  
• Computers that run Windows XP or Windows Server 2003 are not upgraded to MDAC 2.8 SP1. If your system requires any of the fixes included in MDAC 2.8 SP1, upgrade your system to Windows XP SP2 or Windows Server 2003 SP1 to obtain MDAC 2.8 SP2.
  
  
• MDAC 2.8 SP1 is not installed on computers that already have installed an equal or later version of MDAC.

  Note   If a computer on which MSDE 2000 SP4 is installed is upgraded to a newer operating system platform, the version of MDAC installed by SP4 is no longer present.

Note   See Knowledge Base article 301202 for instructions on determining the version of MDAC on your computer.

When MSDE 2000 SP4 installs MDAC 2.8 SP1, the MDAC language version is the same as the language version of MSDE 2000 SP4. If you want to maintain a language version of MDAC that is different from that of MSDE 2000 SP4, you must download and install the intended language version of MDAC 2.8 SP1 before you run MSDE 2000 SP4 Setup. You can download language-specific versions of MDAC 2.8 SP1 from the Microsoft Data Access Downloads page.

MDAC 2.8 SP1 includes an upgrade to MSXML 3.0 SP7. MDAC 2.81 also updates SQLXML 1.0, which shipped with Microsoft SQL Server 2000. This service pack does not install or update SQLXML 3.0. If your application requires SQLXML 3.0, you must download and install it from this Microsoft Web site. For more information about MDAC 2.8 SP1, see the Microsoft Data Access Downloads page. For more information about MDAC versions, see Knowledge Base article 822758. Fixes included in MDAC 2.8 SP1 are documented in Knowledge Base article 884930.

All versions of Windows that are supported for use with MSDE 2000 include a version of the MDAC software that works with MSDE 2000 SP4. If you configure an instance of MSDE 2000 to support network communications and to operate as a database server, you need not install client software on any Windows computers to enable applications to connect from that computer to the instance of MSDE 2000. For more information about network communications, see this Microsoft Web page.

Note   Prerelease versions of SQL Server 2000 SP4 installed a prerelease version of MSXML 3.0 SP7. If you installed a prerelease version of SQL Server 2000 SP4, it is recommended that you download and install the final release version of MSXML 3.0 SP7 from this Microsoft Web site.

[Top]

1.6 Identify the Current Version of MSDE 2000

Before running Setup, identify the version of the MSDE 2000 instance that is being upgraded. If the version of MSDE 2000 is already at or greater than SP4, you do not need to install SP4.

To identify the installed version of MSDE 2000:

1. Execute one of the following queries against an instance of the database engine by using isql, osql, or Query Analyzer.
   • SELECT SERVERPROPERTY('ProductLevel')
     
     
   • SELECT @@VERSION
     
     
   • SELECT SERVERPROPERTY('ProductVersion')
2. Find out your version of MSDE by means of the following table.

   SQL Server 2000 Version and Level	@@VERSION	Product Level
   SQL Server 2000 Original Release	8.00.194	RTM
   Desktop Engine SP1	8.00.384	SP1
   Desktop Engine SP2	8.00.534	SP2
   Desktop Engine SP3, SP3a, or MSDE 2000 Release A.	8.00.760	SP3
   MSDE 2000 SP4	8.00.2039	SP4

   
   

   Note   Your product version may be different than these values if you applied a hotfix after installing the product or after installing a previous service pack. For example, @@VERSION returns a value of 8.00.818 after you apply the security fix MS03-031 to MSDE 2000 Release A.

3. (Optional) If you are not sure whether you have installed an edition of the SQL Server 2000 database engine or MSDE 2000, use isql, osql, or Query Analyzer to execute the following query against the instance in question.

   SELECT SERVERPROPERTY('Edition')

   When the value Desktop Engine is returned, the instance is MSDE 2000.

[Top]

1.7 Additional Important Information About SP4

A list of the fixes contained in this service pack will be provided in Microsoft Knowledge Base article 888799. Each fix listed in 888799 has a link to a Knowledge Base article about the problem that is addressed by the particular fix. Follow the links to individual Knowledge Base articles to see information about each fix.

Any information relevant to SQL Server 2000 Service Pack 4 that was not available in time to be included in this Readme file will be published in Microsoft Knowledge Base article 884525.

The Knowledge Base articles mentioned in this Readme are available in the Microsoft Knowledge Base.

To find an article in the Knowledge Base

1. Under Advanced Search, in the For text box, type the number of the article you want.
   
   
2. From the Search Type drop-down list, select Article ID.
   
   
3. Click the Run the search right-arrow button.

Hotfixes

All publicly issued SQL Server 2000 SP3a and SQL Server 2000 (64-bit) security bulletins have been addressed in SP4.

If you received a SQL Server 2000 hotfix after December 2, 2004, that hotfix is unlikely to be included in SP4. Contact your primary support provider about obtaining the same hotfix for SQL Server 2000 SP4.

SQL Server 2000 SP4 includes serviceability enhancements that enable you to uninstall future hotfixes. For more information, see Section 5.7 Serviceability Enhancements.

Slammer Worm-Related Fixes

SQL Server 2000 SP4 incorporates changes to MSDE 2000 that address issues raised by the Slammer worm:

• SP4 addresses setup issues that some customers encountered with the version of the Microsoft Data Access (MDAC) components in SQL Server 2000 SP3.
  
  
• When an instance of MSDE 2000 SP4 or SP3a is configured to not support network connections, the instance also stops using UDP port 1434.

SQL Server CE and SQL Mobile Server Tools Updates

Users of Microsoft SQL Server 2000 Windows® CE Edition (SQL Server CE) and SQL Server 2005 Mobile Edition (SQL Mobile) who have upgraded or plan to upgrade SQL Server 2000 database and publisher servers to SP4 should also update the server replication components on Microsoft Internet Information Services (IIS) servers. Updated server tools installers are available for SQL Server CE and for SQL Mobile.

Note   Even if you updated your server replication components after you upgraded to SQL Server 2000 SP3 or SP3a, you must install the latest SP4-specific updates to server tools components.

OPENXML Update

SQL Server 2000 SP4 removes the OPENXML dependency on the version of MSXML installed by the operating system. MSDE 2000 SP4 installs an internal version of the MSXML technology that is backward compatible with MSXML 2.6.

[Top]

1.8 Updates to SQL Server 2000 Books Online Are Available

SQL Server 2000 Books Online is the primary user documentation for MSDE 2000. Books Online is updated periodically with fixes and new information. In January 2004, Books Online was updated to include additional information about MSDE 2000. You are strongly encouraged to download and install the latest version of Books Online for the following reasons:

• The MSDE 2000 Setup documentation is updated to reflect the changes introduced in SQL Server 2000 SP3 and SP3a. The setup documentation is also current for the SP4 version of MSDE 2000 Setup, except for the SAVESYSDB parameter, which was introduced in SP4. For more information, see Section 5.6.1 New MSDE 2000 Setup SAVESYSDB Parameter.
  
  
• Usage of the osql utility is described. This material is intended primarily for new MSDE 2000 customers.
  
  
• A new section explains which parts of SQL Server 2000 Books Online apply to MSDE 2000.
  
  
• The description of the MSDE 2000 workload governor has been updated.
  
  
• Topics specific to MSDE 2000 are indexed so that they appear with MSDE 2000. All these topics also contain the string MSDE 2000 so that they appear in searches for MSDE 2000 material.

Current versions of Books Online are available at the following locations:

• The latest version of Books Online is available as a set of downloadable files at this Books Online download site.
  
  
• The latest version of Books Online is also available in the MSDN Library. An English-language copy of the latest version of Books Online is available at this Web site.

[Top]

Updated SQL Server 2000 Samples Are Available

Samples for the SQL Server 2000 database engine that were updated for SQL Server 2000 SP3 and SP3a are available from this Microsoft Web site. All samples that reference the SQL Server 2000 components that are included in MSDE 2000 also apply to MSDE 2000, except for features that MSDE 2000 does not support. The included features are the database engine, database client connectivity components and programming APIs, Replication, and Data Transformation Services (DTS).

[Top]

2.0 Where to Find and Download SQL Server 2000 SP4

Before you prepare to download and extract SQL Server 2000 SP4, read "Downloading and Extraction Phase Guidelines," later in this section. SQL Server 2000 SP4 is distributed in the following ways:

• On a SQL Server 2000 Service Pack 4 CD-ROM that contains service packs for the following components:
  • MSDE 2000 SP4
    
    
  • Database Components SP4
    
    
  • Analysis Services SP4
    
    
  • SQL Server 2000 SP4 (64-bit)

  If you have a SQL Server 2000 SP4 CD, you can upgrade an instance of MSDE 2000 to MSDE 2000 SP4 by using the self-extracting file SQL2000.MSDE-KB884525-SP4-x86-LLL.exe from the CD.

• In four self-extracting installation package files that can be downloaded from the Internet at this Microsoft Web site:
  • SQL2000.MSDE-KB884525-SP4-x86-LLL.exe (Desktop Engine (MSDE 2000) SP4)
    
    
  • SQL2000-KB884525-SP4-x86-LLL.exe (Database Components SP4)
    
    
  • SQL2000.AS-KB884525-SP4-x86-LLL.exe (Analysis Services SP4)
    
    
  • SQL2000-KB884525-SP4-ia64-LLL.exe (SQL Server 2000 SP4 (64-bit))

  Note   LLL represents a designator that varies by language.

After you have acquired SQL2000.MSDE-KB884525-SP4-x86-LLL.exe from either the download site or SP4 CD, you can then run it to extract the MSDE 2000 SP4 files onto your computer. SQL2000.MSDE-KB884525-SP4-x86-LLL.exe creates a set of folders and files on your hard disk that you can use to install MSDE 2000 SP4.

[Top]

2.1 Choosing the Correct Language

SQL Server 2000 Desktop Engine service packs are language-specific. To upgrade an instance of MSDE 2000, you must obtain the service pack that has the same language as your instance. You get the service pack either on a SQL Server 2000 SP4 CD or by downloading the MSDE 2000 SP4 files. For example, if you upgrade an instance of MSDE 2000 that uses Japanese, you must get the Japanese version of MSDE 2000 SP4.

Note   MSDE 2000 SP4 is the only part of the service pack that is available in Portuguese (Brazil), Swedish, and Dutch because SQL Server 2000 Desktop Engine is the only component of SQL Server 2000 that is produced for those languages. The SQL Server 2000 components that Database Components SP4 or Analysis Services SP4 upgrades are not available in those languages.

If you are unsure of the language of an instance of MSDE 2000:

• Click Start, then click Run.
  
  
• In the Open box, type Regedit, and then click OK.
  
  
• Locate and select one of the following keys:
  • For a default instance of MSDE 2000, locate and select this subkey:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion

  • For a named instance of MSDE 2000, locate and select this subkey:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\CurrentVersion

    where InstanceName is the name of the instance.

• In the right list pane, read the value of the Language entry. Compare that value to those in the following table to determine the language for your instance of 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
  0x00000804	2052	Simplified Chinese

  
  

[Top]

2.2 Downloading MSDE 2000 SP4

If your instance of MSDE 2000 was installed by using the MSDE setup utility, you can upgrade your instance of MSDE 2000 by using a SQL Server 2000 SP4 download file. The original package name of the setup utility was SqlRun01.msi through SqlRun16.msi.

To download MSDE 2000 SP4:

• Go to this Microsoft Web site.
  
  
• From the Full Download drop-down list in the upper-right portion of this page, select the language that matches the language for your instance of SQL Server 2000, and click Go.
  
  
• On the Microsoft Download Center page that corresponds to the language chosen, select the file SQL2000.MSDE-KB884525-SP4-x86-LLL.exe, where LLL varies by language.

[Top]

2.3 Extracting the MSDE 2000 SP4 Files

You must first extract the installation files from SQL2000.MSDE-KB884525-SP4-x86-LLL.exe before you can install MSDE 2000 SP4. You can execute SQL2000.MSDE-KB884525-SP4-x86-LLL.exe directly from the SP4 CD, or from the folder into which you either downloaded it or copied it from the CD.

To extract the MSDE 2000 SP4 files:

• Open Windows Explorer and navigate to the folder that contains SQL2000.MSDE-KB884525-SP4-x86-LLL.exe.
  
  
• Double-click the downloaded file to extract the MSDE 2000 SP4 files. When asked, specify the folder into which you want the SP4 files to be extracted.

[Top]

2.4 Download and Extraction Phase Guidelines

When you download and extract MSDE 2000 SP4 installation files from the Internet, use the following guidelines:    

• When running SQL2000.MSDE-KB884525-SP4-x86-LLL.exe to place the SP4 files into a folder, make sure you have free space that is approximately three times the size of the executable file in order to extract and store the service pack installation files. You must have space enough to store the self-extracting file and the extracted service pack files, and temporary working space for the self-extraction program.
  
  
• When you run the self-extracting file SQL2000.MSDE-KB884525-SP4-x86-LLL.exe, the file prompts you for the name of the folder into which you want the service pack files placed.

  Note   When you extract the service pack to a network shared directory, the path to the folder that you specify is relative to the folder from which you ran SQL2000.MSDE-KB884525-SP4-x86-LLL.exe.

• You can rename or move the folder after you extract the components. However, make sure that the folder path name does not contain blank spaces.
  
  
• You can download all four SP4 self-extracting files and extract them into the same target folder to create a location from which you can install each part of SQL Server 2000 SP4. The four self-extraction files will not overwrite each other or interfere with each other.

[Top]

3.0 Service Pack Installation

To install MSDE 2000 SP4, use the installation instructions in the following sections. Review the material in Section 1.0 Introduction, before you install MSDE 2000 SP4. The installation stages for MSDE 2000 SP4 are as follows:

1. Prepare for MSDE 2000 SP4 Installation
   
   
2. Install the service pack by running MSDE 2000 SP4
   
   
3. Restart Services and Applications

MSDE 2000 SP4 contains a complete set of the files required to install or upgrade instances of SQL Server 2000 Desktop Engine. You can perform all the MSDE 2000 setup actions by using the files from MSDE 2000 SP4, if you have a license to install or upgrade an instance of MSDE 2000. For more information about MSDE 2000 licensing, see this Microsoft Web site.

If you need additional information about running Setup, the primary source of documentation is SQL Server 2000 Books Online. For more information about installing the latest version of SQL Server 2000 Books Online or accessing the copy that is online in the MSDN Library, see section 1.8 Updates to SQL Server 2000 Books Online Are Available.

Here is a link to the reference topic for the Setup executable file in the copy of Books Online in the MSDN Library: Customizing Desktop Engine Setup.exe.

In the latest version of SQL Server 2000 Books Online, "Customizing Desktop Engine Setup.exe" documents the functionality of the version of the Setup executable file that is included in Desktop Engine SP3a and MSDE 2000 Release A. The documentation is also current for the SP4 version of Desktop Engine Setup, except for the SAVESYSDB parameter, which was introduced in SP4. For more information, see Section 5.6.1 New MSDE 2000 Setup SAVESYSDB Parameter.

Note   In versions of Setup for MSDE before SQL Server 2000 SP3, users were required to specify the .msi installation package file that they 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.

[Top]

3.1 Prepare for MSDE 2000 SP4 Installation

You must do the following before you install MSDE 2000 SP4:

1. Back Up Your SQL Server Databases
   
   
2. Verify That the System Databases Have Enough Free Space
   
   
3. Stop Services and Applications

3.1.1 Back Up Your SQL Server Databases

Before you install MSDE 2000 SP4, back up the master, msdb, and model databases. Installation of MSDE 2000 SP4 modifies the master, msdb, and model databases, making them incompatible with pre-SP4 versions of MSDE 2000. Backups of these databases are required if you decide to reinstall MSDE 2000 without SP4.

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

An existing backup scheme accounts for replication and thus allows you to restore a database, after a failure, to a known point after the SP4 upgrade. After applying SP4, a log backup or full database backup is recommended for any user database that is included in a replication topology. If you perform these database backups, and a replication database later fails, you do not have to reapply SP4 after the database is restored.

[Top]

3.1.2 Verify That the System Databases Have Enough Free Space

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 much space, run the sp_spaceused system stored procedure for the master database or msdb database. If the unallocated space in either database is less than 500 KB, increase the size of the database. For more information, see "Expanding a Database" in SQL Server 2000 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 the preceding space-verification step.

To verify that the autogrow 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.

[Top]

3.1.3 Stop Services and Applications Before You Run MSDE 2000 SP4 Setup

You should stop all applications and services, including Control Panel, Add and Remove Programs, SQL Server 2000 Reporting Services, SQL Server 2000 Notification Services, and all applications that make connections to the instance of MSDE being upgraded, before installing MSDE 2000 SP4.

You can apply MSDE 2000 SP4 without first shutting down services, but then some services will not start again without a system reboot. If you do not shut down services, you are prompted to reboot the computer when Setup is complete. If you do not reboot the system, the following services may fail to start:

• Microsoft Distributed Transaction Coordinator (DTC) and the Microsoft Search and MSSQLServerOLAPService services.
  
  
• The MSSQLServer and SQLServerAgent services for the instance that is being upgraded (for example, MSSQL$NamedInstance).
  
  
• Microsoft Component Services, Microsoft Message Queuing, and Microsoft COM Transaction Integrator.

You can reduce the likelihood that you will need to restart your computer after you install MSDE 2000 SP4. To reduce this likelihood, stop the services and applications in the preceding list before you run Setup.

[Top]

3.2 Run MSDE 2000 SP4 Setup

This section includes general guidelines for running the MSDE 2000 SP4 Setup utility. The section then gives examples of the most common MSDE 2000 SP4 scenarios:

• 3.2.2 Upgrading Existing Instances of MSDE 2000 To SP4
  
  
• 3.2.3 Installing a New Instance of MSDE 2000 SP4
  
  
• 3.2.4 Upgrading MSDE 1.0 to MSDE 2000 SP4

To install MSDE 2000 SP4, run Setup.exe from one of the following locations:

• The folder on the local computer that contains the extracted service pack files from SQL2000.MSDE-KB884525-SP4-x86-LLL.exe, where LLL varies by language.
  
  
• The folder, on the network share, that contains the extracted service pack files from SQL2000.MSDE-KB884525-SP4-x86-LLL.exe.

This starts the setup installation process.

The Setup executable file that is included in MSDE 2000 SP4 is the SP4 version of the Desktop Engine Setup program for MSDE 2000. Except for the SAVESYSDB parameter, the operation of the MSDE 2000 SP4 Setup is documented in the latest version of SQL Server 2000 Books Online. For information about installation of the latest version of SQL Server 2000 Books Online, see section 1.8 Updates to SQL Server 2000 Books Online Are Available. This is an English-language copy of the reference topic that documents the behavior of Setup.exe: Customizing Desktop Engine Setup.exe.

[Top]

3.2.1 General Guidelines for Running Setup

MSDE 2000 SP4 is designed to be distributed with applications and installed by the setup program of an 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. The application setup handles any required interaction with the end user. MSDE 2000 has two installation mechanisms:

• A command prompt Setup utility (Setup.exe). 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 the actions the utility takes.

  Important   Always install or upgrade instances of MSDE by running Setup.exe. Do not try to start Setup indirectly through an .msi file; for example, do not double-click one of the MSDE 2000 .msi files.

• You should always use the /L*v switch to generate a Setup log output file.
  
  
• A set of Windows Installer merge modules. Developers who write setup utilities based on Windows Installer can program their application setup to install an instance of 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.

[Top]

Setup Parameters

You must run Setup.exe from the command prompt to install or upgrade any instance of MSDE. Users control the behavior of the MSDE 2000 Setup program by specifying parameters. The setup parameters can be specified in one of two ways:

• At the command prompt.
  
  
• In an .ini file that is read by the Setup utility.

You must enclose the values for MSDE Setup parameters in quotation marks if the value specified has special characters, such as blanks. Otherwise, quotation marks are optional.

Sections 3.2.2, 3.2.3, and 3.2.4 give examples of the parameters to use for the most common scenarios for installing MSDE 2000 SP4. The parameters that can be specified for Setup are documented in the latest version of SQL Server 2000 Books Online. For information about installing the latest version of SQL Server 2000 Books Online, see section 1.8 Updates to SQL Server 2000 Books Online Are Available. This is an English-language copy of the reference topic documenting the behavior of Setup.exe: Customizing Desktop Engine Setup.exe.

Note   Problems can occur if you use a Terminal Services connection to attempt to upgrade an existing instance of MSDE to MSDE 2000 SP4, or to install a new instance of MSDE 2000 SP4. If you encounter problems, restart Setup from the local computer.

[Top]

Using an .ini File

You can specify MSDE 2000 Setup.exe parameters in an .ini file whose location is specified by a /settings switch. An .ini file is a text file; for example, a file you create by using Notepad and save with a file name that has an extension of .ini. In the .ini file, the first line is [Options]. Next, you specify the parameters, one parameter per line.

Security Note  If you use an .ini file during setup, avoid storing security credentials in it.

The following example specifies the parameters at the command prompt:

setup SAPWD="AStrongPassword" INSTANCENAME="InstanceName"
TARGETDIR="C:\MyInstanceFolder"

To run Setup with the same parameters in an .ini file, use Notepad to create a file named MyParameters.ini with the following content:

[Options]
INSTANCENAME="InstanceName"
TARGETDIR="C:\MyInstanceFolder"

Then run Setup, using the /settings switch to point to the .ini file:

setup /settings "MyParameters.ini" SAPWD="AStrongPassword"

[Top]

Requesting a Setup Log

Use a verbose log to verify that N installation succeeded or to help troubleshoot when problems.

To generate a verbose log, specify /L*v <LogFileName>, where <LogFileName> is the name of a log file where Setup records 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 a CD-ROM, you must specify the full path to a folder on your hard disk.

The following example creates a log file, MSDELog.log, in the root folder of the C: drive:

setup SAPWD="AStrongSAPassword" /L*v C:\MSDELog.log

If installation succeeds, an entry similar to the following appears at the end of the log:

=== Logging stopped: 5/16/03  0:06:10 ===
MSI (s) (BC:7C): Product: Microsoft SQL Server Desktop Engine
 -- Installation operation completed successfully.

If the installation fails, an entry similar to the following appears at the end of the log:

=== Logging stopped: 5/15/03  23:50:34 ===
MSI (c) (6A:CE): Product: Microsoft SQL Server Desktop Engine
 -- Installation operation failed.

If the installation failed, search for the string "value 3" in the error log. Within 10 lines of the string is a failure notice for a custom action. The notice contains additional information about the nature of the failure.

[Top]

3.2.2 Upgrading Existing Instances of MSDE 2000 to MSDE 2000 SP4

The examples in this section show you how to upgrade an existing instance of MSDE 2000 to MSDE 2000 SP4, and to disable the network connectivity for that instance of MSDE 2000. If the instance must accept connections from applications that are running on other computers, do not specify the DISABLENETWORKPROTOCOLS parameter.

The examples in this section incorporate the assumption that the sa login has a strong password. For more information about the sa login password, see section 1.3 Security Considerations for MSDE 2000 SP4.

To upgrade an existing MSDE 2000 instance to MSDE 2000 SP4

1. Open a command prompt window.
   
   
2. From the command prompt, use the cd command to navigate to the folder that contains the MSDE 2000 SP4 setup utility:

   cd c:\MSDESP4Folder\MSDE

   where c:\MSDESP4Folder is either the path to the folder into which you extracted the MSDE 2000 SP4 files or the MSDE 2000 SP4 folder on the SQL Server 2000 SP4 CD.

3. Execute one of the following commands:
   • For a default instance that uses Windows Authentication and has its network protocols disabled, execute:

     setup /upgradesp sqlrun /L*v C:\MSDELog.log

   • For a default instance that uses Windows Authentication and has its network protocols enabled, execute:

     setup /upgradesp sqlrun DISABLENETWORKPROTOCOLS=0 /L*v C:\MSDELog.log

   • For a named instance that uses Windows Authentication and has its network protocols disabled, execute:

     setup /upgradesp sqlrun INSTANCENAME=InstanceName /L*v C:\MSDELog.log

   • For a default instance that uses Mixed Mode Authentication and has its network protocols disabled, where AnAdminLogin is a member of the sysadmin fixed server role, execute:

     setup /upgradesp sqlrun SECURITYMODE=SQL UPGRADEUSER=AnAdminLogin 
     UPGRADEPWD=AdminPassword /L*v C:\MSDELog.log

   • For a named instance that uses Mixed Mode Authentication and has its network protocols disabled, where AnAdminLogin is a member of the sysadmin fixed server role, execute:

     setup /upgradesp sqlrun INSTANCENAME= InstanceName SECURITYMODE=SQL 
     UPGRADEUSER=AnAdminLogin UPGRADEPWD=AdminPassword /L*v C:\MSDELog.log

   Line breaks were included in these examples for readability. Commands must be executed without line breaks.

   Note   If the instance of MSDE 2000 being upgraded was previously upgraded from MSDE 1.0 using an earlier SQL Server 2000 service pack, you must also append UPGRADE=1 to the setup command.

   Security Note  If you use an .ini file during setup, do not store credentials in the .ini file.

[Top]

3.2.3 Installing a New Instance of MSDE 2000 SP4

The examples in this section show you how to install a new instance of MSDE 2000 SP4 that has been configured with its network connectivity disabled, which is the default behavior. If the instance must accept connections from applications that are running on other computers, you must also specify DISABLENETWORKPROTOCOLS=0.

The following examples install instances by 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 upon Setup, see Customizing Desktop Engine Setup.exe.

To install a new instance of Desktop Engine

1. Open a command prompt window.
   
   
2. From the command prompt, use the cd command to navigate to the folder that contains the MSDE 2000 SP4 Setup utility:

   cd c:\MSDESP4Folder\MSDE

   where c:\MSDESP4Folder is either the path to the folder into which you extracted the MSDE 2000 SP4 files or the MSDE 2000 SP4 folder on the SQL Server 2000 SP4 CD.

3. Execute one of the following commands:
   • To install a default instance that is configured to use Windows Authentication, execute:

     setup SAPWD="AStrongSAPwd" /L*v C:\MSDELog.log

     where AStrongSAPwd is a strong password to be assigned to the sa login.

   • To install a named instance that is configured to use Windows Authentication, execute:

     setup INSTANCENAME="InstanceName" SAPWD="AStrongSAPwd"
     /L*v C:\MSDELog.log

     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 that is configured to use Mixed Mode Authentication, execute:

     setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL
     /L*v C:\MSDELog.log

     where AStrongSAPwd is a strong password to be assigned to the sa login.

   • To install a named instance that is configured to use Mixed Mode Authentication, execute:

     setup INSTANCENAME="InstanceName" SECURITYMODE=SQL
     SAPWD="AStrongSAPwd" /L*v C:\MSDELog.log

     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 use an .ini file during setup, do not store 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.

[Top]

3.2.4 Upgrading MSDE 1.0 to MSDE 2000 SP4

The examples in this section show you how to upgrade an existing instance of MSDE 1.0 to MSDE 2000 SP4, and to disable the network connectivity for the instance. If the instance must accept connections from applications that are 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.

Note   Instances of MSDE 1.0 in a replication topology cannot be upgraded to MSDE 2000 SP4.

To upgrade MSDE 1.0 instance to MSDE 2000 SP4

1. Open a command prompt window.
   
   
2. From the command prompt, use the cd command to navigate to the folder that contains the MSDE 2000 SP4 Setup utility:

   cd c:\MSDESP4Folder\MSDE

   where c:\MSDESP4Folder is either the path to the folder into which you extracted the MSDE 2000 SP4 files, or the MSDE 2000 SP4 folder on the SQL Server 2000 SP4 CD.

3. Execute one of the following commands:
   • When you upgrade from an instance of MSDE 1.0 that uses Windows Authentication, execute:

     setup UPGRADE=1 DISABLENETWORKPROTOCOLS=1
     /L*v C:\MSDELog.log

   • When you upgrade from an instance of MSDE 1.0 that uses Mixed Mode Authentication (where AnAdminLogin is a member of the sysadmin fixed server role), execute:

     setup UPGRADE=1 SECURITYMODE=SQL UPGRADEUSER=AnAdminLogin
     UPGRADEPWD=AdminPassword DISABLENETWORKPROTOCOLS=1
     /L*v C:\MSDELog.log

Important  If you use an .ini file during setup, do not store credentials in the .ini file.

Security Note  The use of blank passwords is strongly discouraged because blank passwords add a significant vulnerability to security breaches.

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

[Top]

3.3 Restart Services and Applications

When Setup completes, it may prompt you to restart the system. Section 3.1.3 Stop Services and Applications Before You Run MSDE 2000 SP4 Setup provides guidelines on when a restart is required. 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 you applied the service pack are now running. Services you might have stopped before you applied the service pack include DTC and the MSSQLServer and SQLServerAgent services or their instance-specific equivalents.

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

It is prudent also to back up the upgraded master and msdb databases at this time.

[Top]

3.4 Install MSDE 2000 SP4 on Replicated Servers

The following information applies only to existing MSDE 2000 instances that are part of a merge replication topology.

• Before you upgrade to SP4, ensure that the Windows account on which the SQL Server service runs is a member of the sysadmin fixed server role. You must verify this because replication distribution databases are upgraded in the context of the SQL Server service account. After upgrading to SP4, remove the Windows account from the sysadmin role.
  
  
• You must upgrade the Distributor before you upgrade the Publisher.
  
  
• When you need to upgrade an instance of MSDE 2000 in a replication topology based on transactional replication with read-only Subscribers, you can upgrade the Subscriber before or after the Publisher and Distributor.
  
  
• When you need to upgrade an instance of MSDE 2000 in a replication topology 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.

• If you use merge replication and the Distributor is located on another computer or database instance (a remote Distributor), your must generate a new snapshot after you apply SP4.
  
  
• You cannot upgrade instances of MSDE 1.0 in a replication topology to MSDE 2000 SP4.

[Top]

Installing MSDE 2000 SP4 on a Server that Acts as a Publisher and a Subscriber

You need to quiesce the system (stop all updates) and upgrade all servers simultaneously in the following cases.

• For replication topologies based on merge replication
  
  
• For replication topologies based on transactional replication, which updates Subscribers, among which are one or more servers that act as both a Subscriber and a Publisher (or Distributor).

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 in the preceding section, you must follow the upgrade order Distributor, Publisher, Subscriber for topologies that allow updates at the Subscriber. This order requires that you 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.

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

[Top]

3.5 Apply MSDE 2000 SP4 to Read-Only Databases or Filegroups in a Replication Topology

The following information applies only to instances of MSDE 2000 that are part of a merge 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 continues. 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 SP4 Setup to that instance of SQL Server 2000.

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 in a replication topology is non-writable during setup, you must reapply the service pack to upgrade this database. For more information about how to bring a database online, see the topic "Attaching and Detaching a Database" in SQL Server 2000 Books Online. For more information about diagnosing suspect databases, see the topic "Server and Database Troubleshooting" in SQL Server 2000 Books Online.

To apply MSDE 2000 SP4 to a read-only database

1. Make the read-only database writable by using the ALTER DATABASE statement, as follows:

   ALTER DATABASE database SET READ_WRITE

2. Repeat Step 1 for all read-only databases.
   
   
3. Apply (or reapply) SP4.
   
   
4. If required, make the database read-only again using ALTER DATABASE, as follows:

   ALTER DATABASE database SET READ_ONLY

To apply SP4 to a read-only filegroup

1. Make the read-only filegroup writable by using ALTER DATABASE, as follows:

   ALTER DATABASE Database 
   MODIFY FILEGROUP filegroup_name READWRITE 

2. Repeat Step 1 for all read-only filegroups.
   
   
3. Apply (or reapply) the service pack.
   
   
4. Make the filegroup read-only again by 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 SP4, see Section 3.8 Reapply MSDE 2000 SP4.

[Top]

3.6 Upgrade the Catalog of Linked Servers

When you upgrade an instance of MSDE 2000 to MSDE 2000 SP4, you might need to verify that some of the system stored procedures are updated in other instances of SQL Server or MSDE.

MSDE 2000 SP4 includes an upgrade of the Microsoft Data Access Components (MDAC) to MDAC 2.8 SP1. MDAC 2.8 SP1 includes updates to the SQLOLEDB provider and the SQL Server ODBC driver. For more information, see Section 1.5 Verify the Version of Microsoft Data Access Components. When either the provider or driver connects to an instance of SQL Server or MSDE, the provider or driver uses a set of system stored procedures known as the catalog stored procedures. The versions of the catalog stored procedures on the instance must be the same version or later than the provider and driver uses. If you attempt to connect to an instance of SQL Server or MSDE that has earlier versions of the catalog stored procedures, you receive the following error:

The ODBC catalog stored procedures installed on server <ServerName>
are version <OldVersionNumber>; version <NewVersionNumber> or later
is required to ensure proper operation. Please contact your system
administrator.

Run the Instcat.sql Script

Each version of the provider and driver is shipped with a script named Instcat.sql. Instcat.sql upgrades the catalog stored procedures in any instance of SQL Server or MSDE that has an earlier version of the catalog.

After installing MSDE 2000 SP4, you must run the Instcat.sql script from MSDE 2000 SP4 against any instance of SQL Server or MSDE that is an earlier version than SQL Server 2000 SP4 and has the following characteristics:

• Is accessed by applications that use the SQLClient managed namespace, ADO, OLE DB, or ODBC and run on the computer on which MSDE 2000 SP4 is installed.
  
  
• Is referenced by distributed queries that run on an instance of MSDE 2000 SP4. Distributed queries include references to linked server entries that you define by using sp_addlinkedserver and the OPENROWSET and OPENQUERY functions.

To upgrade the catalog stored procedures on an instance of MSDE 2000 that has the Windows Authentication option selected

1. Log on to Windows using a login that is a member of the SQL Server sysadmin fixed server role.
   
   
2. Open a command prompt window.
   
   
3. Run the osql utility:
   • For a default instance, execute:

     osql -E -SComputerName -ilocation\instcat.sql

   • For a named instance, execute:

     osql -E -SComputerName\InstanceName -ilocation\instcat.sql

To upgrade the catalog stored procedures on MSDE 2000 instance that has the Mixed Mode Authentication option selected:

1. Log on to Windows using any login.
   
   
2. Open a command prompt window.
   
   
3. Run the osql utility:
   • For a default instance, execute:

     osql -UAnAdminLogin -PAdminPassword 
     -SComputerName -ilocation\instcat.sql

   • For a named instance, execute:

     osql -UAnAdminLogin -PAdminPassword 
     -SComputerName\InstanceName -ilocation\instcat.sql

where:

• AnAdminLogin is a SQL Server login that is a member of the sysadmin fixed server role.
  
  
• AdminPassword is the password for AnAdminLogin.
  
  
• ComputerName is the name of the computer on which the instance of SQL Server or MSDE is running.
  
  
• InstanceName is the name of a named instance of SQL Server 2000 or MSDE 2000.
  
  
• location is the full path to the folder containing instcat.sql. The default location for an installed instance of SQL Server 2000 is c:\program files\Microsoft SQL Server\MSSQL\Install.

The Instcat.sql script generates many messages. Typically, the messages do not indicate any errors. They only inform you how many rows were affected by each Transact-SQL statement in the script. The final message should indicate whether the script ran successfully.

[Top]

3.7 Uninstall MSDE 2000 SP4

To be able return to pre-SP4 versions of MSDE 2000, you must back up the master, msdb, and model databases before you install SP4. For more information, see Section 3.1.1 Back Up Your SQL Server Databases.

To return to a pre-SP4 version of MSDE 2000

1. Detach all user databases. For more information, see "How to attach and detach a database (Enterprise Manager)" in SQL Server 2000 Books Online.
   
   
2. Uninstall MSDE 2000: In Control Panel, double-click Add/Remove Programs, select the instance of MSDE 2000 that you want to uninstall, and click Remove.
   
   
3. Reinstall MSDE 2000 from the CD-ROM or from the location where you originally installed MSDE 2000.
   
   
4. Apply any service packs and hotfixes that were installed before MSDE 2000 SP4.
   
   
5. Restore the databases master, msdb, and model from the last backup that was created before you installed SP4. If the location of the data files has not changed, this restoration automatically attaches any user databases that were attached at the time the backup was created.
   
   
6. Attach any user databases that were created after the last backup of the master database.
   
   
7. Configure replication if necessary.

   Warning  When you revert to the pre-SP4 version of SQL Server, all changes made to the databases master, msdb, and model after you installed SP4 are lost.

   Note   MDAC updates are not uninstalled when you revert to a pre-SP4 version of MSDE 2000. For more information, see Section 1.5 Verify the Version of Microsoft Data Access Components.

[Top]

3.8 Reapply MSDE 2000 SP4

You must reapply MSDE 2000 SP4 if databases or filegroups are made writable that are part of a replication topology and were read-only during the initial application of SP4.

To reapply MSDE 2000 SP4, follow the steps in Section 3.0 Service Pack Installation.

[Top]

4.0 Additional Installation Information

This section documents additional service pack installation considerations.

4.1 Redistributing MSDE 2000 SP4

MSDE 2000 SP4 contains all the files necessary to redistribute MSDE 2000 with applications, if the application vendor has a license to distribute MSDE 2000. You can distribute the MSDE 2000 SP4 files as documented in the latest version of SQL Server 2000 Books Online. You can also register for MSDE 2000 redistribution rights at this Microsoft Web site.

For more information about installing the latest version of SQL Server 2000 Books Online, see Section 1.8 Updates to SQL Server 2000 Books Online Are Available.

[Top]

Distributing MSDE 2000 SP4 Patches

If an application has a Windows Installer-based setup utility, the application can install an instance of MSDE 2000 by consuming the MSDE 2000 merge modules. MSDE 2000 SP4 provides merge modules to support existing applications that use merge modules. The Setup utilities for new applications must be written to call the MSDE 2000 Setup utility rather than directly consuming the MSDE 2000 merge modules.

Vendors who choose to install instances of MSDE 2000 by using the MSDE 2000 merge modules must supply all subsequent MSDE 2000 patches to their customers. Instances of MSDE 2000 that are installed by direct consumption of merge modules by the application are marked with a product code GUID that Windows Installer associates with the application. 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 MSDE 2000 service packs supplied by Microsoft cannot be applied to those instances. The application vendor must build patch files from the MSDE 2000 SP4 files and distribute those patch files to any of the vendor’s MSDE customers who need the fixes in SP4.

For more information about how to create patch file packages, see 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 MSDE 2000 Setup, that instance of MSDE 2000 is marked with the MSDE 2000 product code GUID. Customers can patch these instances by using the standard MSDE 2000 service pack files. The application vendor can choose one of the following ways to distribute MSDE 2000 SP4:

• Instruct customers to install MSDE 2000 SP4 from either the SQL Server 2000 SP4 download page or a SQL Server 2000 SP4 CD. Application vendors who instruct their customers to apply SP4 directly must provide instructions that are specific to their customers.
  
  
• For examples of the types of MSDE 2000 service pack instructions that Microsoft application teams prepared for their customers, see this Microsoft MSDE Applications Web page
  
  
• Download the SQL2000.MSDE-KB884525-SP4-x86-LLL.exe download file and distribute it to customers with instructions about how to apply the service pack.
  
  
• Using the MSDE 2000 SP4 files, build a service pack application utility that installs SP4 on the instances of MSDE 2000 that were installed by the application.

[Top]

4.2 MSDE 2000 SP4 File Locations

All MSDE SP4 installation files and folders are in the \MSDE folder, which is in one or more of the following locations:

• On the SQL Server 2000 SP4 CD-ROM.
  
  
• In the folder that contains the extracted service pack files from SQL2000.MSDE-KB884525-SP4-x86-LLL.exe, where LLL varies by language.

The \MSDE folder holds the Readmesql2k32desksp4.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 that are required to install Windows Installer, or to upgrade a version of Windows Installer earlier than 2.0.2600.0.
  
  
• \MSM: Contains the merge modules needed for a merge module setup.
  
  
• \Setup: Contains the .msi installation package files that are required by MSDE 2000 Setup to install a new instance of MSDE 2000 SP4, or the .msp patch package files that are required to upgrade existing instances of MSDE 2000 to SP4. \Setup also contains the sqlrun.cab cabinet file containing the files installed by Setup.

For further instructions about how to use the MSDE merge modules, see the topic "Using SQL Server Desktop Engine Merge Modules" in SQL Server 2000 Books Online.

Note   Merge modules are not supported for new installations. They are provided in SP4 for servicing instances of MSDE 2000 that were previously installed using merge modules.

If your application setup calls MSDE 2000 Setup, build a folder that has the following structure and sets of files. The folder MSDEInstallFolder represents an example folder name:

MSDEInstallFolder

Copy to this location the following files from the MSDE 2000 SP4 \MSDE folder: Setup.exe, Setup.ini, Setup.rll, and sqlresld.dll.

MSDEInstallFolder\Msi

Copy to this location the following all the files from the MSDE 2000 SP4 \MSDE\Msi folder.

MSDEInstallFolder\Setup

Copy to this location the following all the files from the MSDE 2000 SP4 \MSDE\Setup folder.

You can then execute Setup.exe to install or upgrade instances of MSDE 2000 SP4.

Note   If Windows Installer has not been installed on the computer, or is a version earlier than the supported version for MSDE 2000 SP4 Setup, Setup will use the files in the MSDEInstallFolder\Msi folder to upgrade Windows Installer.

[Top]

5.0 Documentation Notes

This section covers issues that can occur after you apply MSDE 2000 SP4 and new features that are available when you run SP4. These issues apply when you run the service pack to upgrade from any earlier version of MSDE 2000, including MSDE 2000 Release A. This section does not describe of all of the fixes provided in SP4. For a complete list of these fixes, see Knowledge Base article 888799.

Any information relevant to SQL Server 2000 Service Pack 4 that was not available in time to be included in this Readme file will be published in Microsoft Knowledge Base article 884525.

[Top]

5.1 MSDE 2000 Enhancements

The following enhancements apply to instances of MSDE 2000 on which Database Components SP4 is installed. They also apply to MSDE 2000 Release A instances on which MSDE 2000 SP4 is installed.

[Top]

5.1.1 Hash Teams Removed

Introduced in SP1

Hash teams have been removed. Because of certain enhancements to MSDE 2000, hash teams no longer produce the performance benefits they offered in MSDE 1.0. In addition, removing hash teams makes MSDE 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.

[Top]

5.1.2 Affinity Mask Switches Added

Introduced in SP1

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

Affinity Mask I/O Switch

With this service pack, you can specify which CPUs are used to run threads for disk I/O operations. This switch must be used in conjunction with the affinity mask option. For more information, see Knowledge Base Article 298402.

Affinity Mask Connection Switch

With this service pack, you can configure systems that are enabled for Virtual Interface Architecture (VIA) to bind the MSDE 2000 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 Knowledge Base Article 299641.

[Top]

5.1.3 Syntax Changes for sp_change_users_login

Introduced in SP3

When you run sp_change_users_login with the @Action=Auto_Fix argument, you must now specify a password. sp_change_users_login assigns the password to any new login it creates for the user. The following example shows the new @Password argument:

sp_change_users_login [ @Action = ] 'action' 
    [ , [ @UserNamePattern = ] 'user' ] 
    [ , [ @LoginName = ] 'login' ]
    [ , [ @Password = ] 'password' ]

Use the @Password argument only with @Action=Auto_Fix. The following example shows the new syntax for the sp_change_users_login command when using Auto_Fix. Other examples in SQL Server Books Online remain unchanged.

USE pubs
go
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'
go

[Top]

5.1.4 Ad Hoc Access to OLE DB Providers Disabled by Default

Introduced in SP3

If the DisallowAdhocAccess registry option is not explicitly set, by default, ad hoc access to OLE DB providers is not allowed. This means ad hoc query syntax, such as OPENDATASOURCE and OPENROWSET, will not work against remote servers. To allow ad hoc access, you must explicitly set the DisallowAdhocAccess option to 0.

[Top]

5.1.5 New SqlServerLike Provider Option

Introduced in SP3

To enable more efficient processing of remote queries that include LIKE predicates, the SqlServerLike option was added in SP3. MSDE 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 MSDE 2000 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.

Security Note  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..

1. Open Regedit32.
   
   
2. Locate the proper registry key:
   • For named instances, locate this subkey:

     HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<Instance Name>\Providers\<Provider Name>

   • For default instances, locate this subkey:

     HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Providers\<Provider Name>

3. In the <Provider Name> key, add a DWORD value named SqlServerLIKE and set its value to 1.

[Top]

5.1.6 Expanded Error Messages for Distributed Queries

Introduced in SP3

For distributed queries, MSDE 2000 returns provider error information in addition to server error information. When a query between linked servers results in an error, MSDE 2000 checks whether the provider supports the IErrorRecords OLE DB interface. If this interface is supported, MSDE 2000 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 MSDE 2000 behavior: MSDE 2000 returns a generic error.

For example, run the following query against a server that uses MSDASQL, which does not support sql_variant:

SELECT * FROM remote2k.dqtable.dbo.sqlvariantnotnull 
--Remote2k is a loopback server.

Prior to SP3, MSDE 2000 returned the following error message:

Server: Msg 7356, Level 16, State 1, Line 1

OLE DB provider 'msdasql' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.

After you apply SP3 or later, MSDE 2000 returns the following error message:

Server: Msg 7356, Level 16, State 1, Line 1

OLE DB provider 'msdasql' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.

OLE DB error trace [Non-interface error:  Column 'sql_variant' (compile-time
ordinal 3) of object '"dqtable"."dbo"."sqlvariantnotnull"' was reported
to have a DBCOLUMNFLAGS_ISFIXEDLENGTH of 16 at compile time and 0 at run time].

[Top]

5.1.7 New Function fn_get_sql Returns SQL Statement

Introduced in SP3

SP3 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: sql_handle, stmt_start, and stmt_end.

fn_get_sql is documented in the latest copy of the SQL Server 2000 Books Online. For information about installing the latest version of the SQL Server 2000 Books Online, see section 1.8 Updates to SQL Server 2000 Books Online Are Available. This is an English-language copy of the reference topic for fn_get_sql.

[Top]

5.1.8 Cross-Database Ownership Chaining

Introduced in SP3

This service pack provides new options for turning cross-database ownership chaining on and off.

When installing MSDE 2000 SP4, you can use the ALLOWXDBCHAINING setup parameter to enable cross-database ownership chaining for all databases. ALLOWXDBCHAINING is documented in this topic in the latest copy of the SQL Server 2000 Books Online: Customizing Desktop Engine Setup.exe. For information about installing the latest version of the SQL Server 2000 Books Online, see section 1.8 Updates to SQL Server 2000 Books Online Are Available.

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

After installation, you can use the following methods to turn cross-database ownership chaining on and off for all databases in the instance:

• Use the new Cross DB Ownership Chaining argument of the sp_configure system stored procedure.
  
  
• Use the Allow Cross-Database Ownership Chaining option on the Security tab of the SQL Server Properties dialog box in Enterprise Manager.
  
  
• Use the SQLServer.Configuration.ConfigValues Database Management Objects (DMO) collection. When the SQLServer object references an instance of MSDE 2000 2000 SP3 or later, this collection contains a ConfigValue object named Cross DB Ownership Chaining.

If cross-database ownership chaining is turned off for the instance, you can configure it for individual databases. Use the following methods to turn cross-database ownership chaining on and off for a database:

• Use the new db chaining option of the sp_dboption system stored procedure.
  
  
• Use the Allow Cross-Database Ownership Chaining option on the Options tab of the Database Properties dialog box in Enterprise Manager.
  
  
• Use the DBChaining property of the DBOption2 DMO object.

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.

[Top]

5.1.9 Enhancement for Trace Flag 1204

Introduced in SP3

Trace flag 1204 returns the type of locks participating in the deadlock and the current command affected. In SP3 and later, when this trace flag is on, the deadlock information is automatically written to the error log.