INF: Readme.txt for SQL Server 7.0 Service Pack 3 (274798)

MORE INFORMATION

Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

(c) Microsoft Corporation, 2000

Contents

1.0 Introduction
1.1 Service Pack Versions

2.0 Downloading and Extracting SP3
2.1 Disk Space Requirements for SP3
2.2 Extracting Database Components SP3
2.3 Extracting OLAP Services SP3

3.0 Service Pack Installation
3.1 Back Up SQL Server Databases
3.2 Back Up OLAP Services Databases
3.3 Ensure the System Databases Have Enough Free Space
3.4 Prepare Cluster Configurations
3.5 Stop Applications Before Running Service Pack 3 Setup
3.6 Install Database Components SP3
3.7 Install OLAP Services SP3
3.7.1 Updating or Installing the Add-in and Task Kits
3.8 Restart Services
3.9 Recluster a Cluster Configuration
3.10 Restart Applications

4.0 Redistributing Database Components SP3 Client Components

5.0 Documentation Notes
5.1 Upgrading Client-Only Computers After Installing SP3
5.2 Upgrading Japanese Database Servers to Windows 2000 After Installing SP3
5.3 Applying SP3 to a Later Version
5.4 Installing SP3 on Windows NT 4.0, Terminal Server Edition
5.5 New Troubleshooting Guide
5.6 SP3 and Replication 5.7 Change in Cursor Behavior
5.8 Changes in SQL Server Profiler
5.9 Changes in OLAP Services 5.10 Data Transformation Services (DTS) 5.11 SQL Server 7.0 and Microsoft Exchange Server 5.5
5.12 The French Version of SQL Server 7.0 Books Online
5.13 SQL Server Clustering White Paper
5.14 New Database Option
5.15 New sqlservr Startup Option
5.16 Repository Changes

1.0 Introduction

This release of Service Pack 3 for Microsoft SQL Server 7.0 and MSDE 1.0 is provided in two parts:

• Database Components SP3 provides updates for the database components of a SQL Server 7.0 or MSDE 1.0 installation, including:
  
  
  • Data engine.
  • Database client utilities such as SQL Server Enterprise Manager and b. (SQL Server 7.0 only.)
  • Database client connectivity components, such as the OLE DB Provider for SQL Server, the SQL Server ODBC driver, and the client Net-Libraries.
• OLAP Services SP3 provides updates for the OLAP Services components of a SQL Server 7.0 installation, including:
  
  
  • OLAP Services.
  • OLAP client components, such as OLAP Manager and the OLE DB Provider for OLAP Services.
  • Database client connectivity components, such as the OLE DB Provider for SQL Server, the SQL Server ODBC driver, and the client Net-Libraries.

These two parts of the service pack can be applied independently:

• SQL Server 7.0 sites can use the Database Components SP3 to upgrade their database components without upgrading their OLAP Services components.
• SQL Server 7.0 sites can also use OLAP Services SP3 to upgrade their OLAP Services components without upgrading their database components.
• SQL Server 7.0 sites wanting to upgrade both their database components and OLAP Services components must apply both parts of the service pack.

MSDE 1.0 sites can use the Database Components SP3 to upgrade their MSDE 1.0 components.

Service Pack 3 Setup automatically detects which edition of SQL Server 7.0 or MSDE 1.0 is present on the computer and only upgrades the components present in that edition. The service pack will not attempt to upgrade components present in SQL Server 7.0 Enterprise Edition unless it detects the presence of the Enterprise Edition. When the service pack is applied to a computer running MSDE 1.0, it will not attempt to upgrade components that ship only with SQL Server 7.0.

Service Pack 3 cannot be removed easily due to system table changes the service pack requires for maintenance. To revert to a build before SP3 was installed, you must reinstall SQL Server 7.0 or MSDE 1.0 and, if required, apply the service pack (SP1 or SP2) you were running before you attempted to install SP3. Before you reinstall SQL Server 7.0 or MSDE 1.0, detach your databases on SP3 and reattach them after you reinstall SQL Server 7.0 or MSDE 1.0 and either SP1 or SP2. For information about using sp_attach_db and sp_detach_db, see SQL Server Books Online for SQL Server 7.0. As with any new installation, you must re-create the necessary logins. You must also re-create scheduled tasks dependent on information in the msdb database, as well as any changes you have made in the model database. If you use replication, you must completely reconfigure replication.

At this time, there are no fixes required for the version of Microsoft English Query shipping with SQL Server 7.0.

Because SQL Server service packs are cumulative, Service Pack 3 includes the fixes from all earlier SQL Server 7.0 service packs. Therefore, Service Pack 3 can be applied to an original installation of SQL Server version 7.0 or MSDE 1.0, or to any SQL Server 7.0 or MSDE 1.0 installation to which a service pack has been applied previously.

The list of the fixes contained in this service pack is in Microsoft Knowledge Base article Q274797. Each fix listed in Q274797 has a link to the Knowledge Base article describing the problem addressed by the fix. These Knowledge Base articles are published at http://support.microsoft.com/search/. Click Search Support, select SQL Server and Specific Article ID Number as the search parameters, and enter Q274797. Follow the links to the FIX Knowledge Base articles to see the information about each fix.

For the latest year 2000 information specific to SQL Server 7.0, see the Microsoft Year 2000 Resource Center at http://www.microsoft.com/windows95/downloads/contents/wurecommended/s_wufeatured/win95y2k/default.asp?site=95.

1.1 Service Pack Versions

If you are not sure which service pack you have installed on an instance of the SQL Server 7.0 database engine or MSDE 1.0, you can verify the version by issuing SELECT @@VERSION from osql, SQL Server Query Analyzer, or isql. This table shows the relationship between the version string reported by @@VERSION and the SQL Server 7.0 and MSDE 1.0 service packs.

@@VERSION	SQL Server version
7.00.623	Original SQL Server 7.0 or MSDE 1.0 release
7.00.699	Database components SP1
7.00.842	Database components SP2
7.00.961	Database components SP3

If you are not sure which edition of SQL Server 7.0 you are running, or if you have connected to MSDE 1.0, the last line of output returned by @@VERSION reports the edition to which you have connected: If you are not sure which service pack you have installed on an instance of SQL Server 7.0 OLAP Services, you can verify the version in the following manner:

1. Point to OLAP Services in the SQL Server 7.0 program group, and then click OLAP Manager.
2. Click the OLAP Servers node in the OLAP Manager tree view.
3. Click About Microsoft SQL Server OLAP Services on the OLAP Manager Help menu. This table shows the relationship between the OLAP Manager version information and the OLAP Services service packs.

Help About	SQL Server OLAP Services version
7.0.1073	Original SQL Server 7.0 OLAP Services release
7.0.1295	OLAP Services SP1
7.0.1458	OLAP Services SP2
7.0.1508	OLAP Services SP3

2.0 Downloading and Extracting SP3

This service pack is distributed in two formats:

• On an SP3 compact disc.
• In four self-extracting files that can be downloaded from the Internet:
  
  
  • Sql70sp3i.exe: Database Components SP3 for Intel-based computers.
  • Sql70sp3a.exe: Database Components SP3 for Alpha-based computers.
  • Sql70olapsp3i.exe: OLAP Services SP3 for Intel-based computers.
  • Sql70olapsp3a.exe: OLAP Services SP3 for Alpha-based computers.

The self-extracting files are used to build a set of directories and files on your computer that are the same as those present on the SP3 compact disc. The setup process is then the same for both the SP3 compact disc and the extracted SP3 files. If you downloaded SP3, you must extract the files to build the service pack directories before you can run Setup.

If you downloaded SP3, you must extract the files to build the service pack directories before you can run Setup.

NOTE: Several of the files in service packs are system files. Make sure you have the Show All Files option is selected in either Microsoft Windows NT Explorer or Windows Explorer when working with service packs. To enable this option, on the View menu, click Options, and then on the View tab of the Options dialog box, select the option.

2.1 Disk Space Requirements for SP3

The following list identifies the disk space required to unpack SP3 on Intel-based and Alpha-based computers if you download the self-extracting executable file to your C: drive and run the extraction there.

Intel-based computers require the following disk space:

• Database Components only for SP3: 193 MB
• OLAP Services only for SP3: 187 MB
• Both Database Components and OLAP Services for SP3: 380 MB

Alpha-based computers require the following disk space:

• Database Components only for SP3: 269 MB
• OLAP Services only for SP3: 187 MB
• Both Database Components and OLAP Services for SP3: 456 MB

If you do not have enough space to download SP3 to your C: drive, you can conserve disk space by downloading the SP3 zip file to a secondary drive and extracting it to the C: drive. The following list identifies the disk space required to unpack SP3 on Intel-based and Alpha-based computers.

Intel-based computers require the following disk space:

• Database Components only for SP3: 150 MB required on the C: drive; 43 MB required to download to your secondary drive.
• OLAP Services only for SP3: 130 MB required on the C: drive; 57 MB required to download to your secondary drive.
• Both Database Components and OLAP Services for SP3: 280 MB on the C: drive; 100 MB required to download to your secondary drive.

Alpha-based computers require the following disk space:

• Database Components only for SP3: 209 MB required on the C: drive; 60 MB required to download to your secondary drive.
• OLAP Services only for SP3: 130 MB required on the C: drive; 57 MB required to download to your secondary drive.
• Both Database Components and OLAP Services for SP3: 339 MB required on the C: drive; 117 MB required to download to your secondary drive.

2.2 Extracting Database Components SP3

To extract the Database Components SP3, place the self-extracting file for your processor architecture into a directory on the computer running SQL Server 7.0 or MSDE 1.0. From that directory, execute the file. The self-extraction program will prompt you for the name of the directory in which you want the service pack files placed. For example, if you are running SQL Server or MSDE on an Intel-based computer and have created a directory named C:\70sp3, copy the file Sql70sp3i.exe to your computer, execute it, and provide the name of the C:\70sp3 directory when prompted.

This will extract the service pack files and place the two Microsoft Systems Management Server files in the C:\70sp3 directory. Additionally, a subdirectory will be created for the hardware platform (Intel or Alpha) where the remainder of the service pack files will be placed.

You can rename the database installation directory after extracting the components; however, you need to make sure that the directory name does not contain space characters.

2.3 Extracting OLAP Services SP3

To extract the OLAP Services SP3, create a directory on your computer to hold the OLAP Services SP3. Copy the self-extracting OLAP Services file for your processor architecture into a directory on the computer running OLAP Services. From that directory, execute the file. The self-extraction program will prompt you for the name of the directory in which you want the service pack files placed. For example, if you are running SQL Server on an Alpha-based computer and have created a directory named C:\70sp3olap, copy the file Sql70olapsp3a.exe to your computer, execute it, and provide the name of the C:\70sp3olap directory when prompted. This will extract the service pack files.

3.0 Service Pack Installation

You may use different series of the installation steps to install the different parts of this service pack:

• To apply the entire service pack (both Database Components SP3 and OLAP Services SP3), complete Steps 3.1 through 3.10.
• To apply only Database Components SP3 to a server running both the server and client components, complete Steps 3.1, 3.3 through 3.6, and 3.8 through 3.10.
• To apply only Database Components SP3 to a computer that is running only the database client components, complete Steps 3.6 and 3.8 through 3.10.
• To apply only OLAP Services SP3, complete only Steps 3.2, 3.5, 3.7, 3.8, and 3.10. Use the same steps for computers running only the client OLAP Services components, or computers running both the client and server OLAP Services components.

You can also use Systems Management Server to install SP3 automatically on multiple computers running Windows NT Server. The Smssqlsp3.sms file is a Package Definition File that automates the creation of a SQL Server package in Systems Management Server. The SQL Server package can then be distributed and installed on computers running Systems Management Server. The Smssq702.cmd file is a batch file that detects the platform of the computer and runs the appropriate version of the Setup program.

NOTE: Before you install SP3 on the French version of Windows NT 4.0, read and follow the instructions in Knowledge Base (KB) article Q259484. You can access the article from http://support.microsoft.com/servicedesks/directaccess. Click Search Support, select SQL Server and Specific Article ID Number as the search parameters, and enter Q259484.

3.1 Back Up SQL Server Databases

As a precaution, back up all of your SQL Server databases, including the master and msdb databases. Installation of the service pack does not make modifications to user databases, but it does make modifications to the master and msdb databases.

3.2 Back Up OLAP Services Databases

As a precaution, back up your OLAP Services databases before installing this Service Pack. To back up your OLAP Services databases, make a backup copy of your OLAP Services\Data folder, which is installed by default under the C:\Program Files folder. If you have not migrated your OLAP Services repository to SQL Server, also make a backup copy of the file Msmdrep.mdb, located in the OLAP Services\bin folder. Do not use the Archive and Restore Database Add-in to back up your OLAP Services databases. You can also save your OLAP server registry entries by running regedit and using the Export Registry File item on the Registry menu to export the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server to a backup file.

3.3 Ensure the System Databases Have Enough Free Space

If the Autogrow option is on for both the master and msdb databases in the SQL Server or MSDE system on which you apply SP3, you can skip this step. You can verify this in SQL Server 7.0 by connecting to SQL Server from SQL Server Enterprise Manager, right-clicking the icon for the database, and clicking Properties. Verify that the Automatically Grow File check box is selected. You can verify this in MSDE by issuing these SQL statements:

• sp_helpdb master
• sp_helpdb msdb

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

If the Autogrow option is not on for either master or msdb databases, the databases that cannot autogrow must have at least 500 KB of free space. To verify this, run the sp_spaceused system stored procedure in the context of the master or msdb database. If the unallocated space figure in either database is less than 500 KB, increase the size of the database. Follow the instructions in the "Expanding the Database" topic in SQL Server Books Online for SQL Server 7.0.

3.4 Prepare Cluster Configurations

Before installing Database Components SP3 to SQL Server 7.0, Enterprise Edition in a clustered environment, ensure that the group containing Microsoft Distributed Transaction Coordinator (MS DTC) is owned by the node on which MS DTC was first installed. If the group is not owned by this node, Service Pack 3 Setup will display a MS DTC setup error prompting you to correct the problem and then retry the setup. Correct the problem by moving the group to the node on which MS DTC was first installed.

If SQL Server Enterprise Edition is running in a clustered configuration, it must be unclustered before you can apply Database Components SP3. Before unclustering SQL Server, use the Cluster Administrator to make sure no other cluster resources depend on any of the five SQL Server cluster resources: SQL Network name, SQL IP address, SQL Server, SQL Agent, and SQL VServer. Record any dependencies you remove so that you can restore them after installing the service pack. If a Microsoft Messaging Queue (MSMQ) resource has a dependency on the SQL Network name, you must take the MSMQ resource offline and transfer its dependency to another network name in the same group. If there are no other network names in the group, create a temporary network name and transfer the dependency to that network name. (You can delete this temporary network name after installing SP3.)

To uncluster SQL Server, in the SQL Server 7.0 program group, click Failover Cluster Wizard. When prompted, insert your original SQL Server 7.0 Enterprise Edition CD and follow the instructions displayed by the wizard. Do this on all nodes of the Microsoft Windows NT or Windows 2000 cluster where SQL Server has been clustered. Make sure the Failover Cluster Wizard removes all of the SQL Server resource names and types.

All SQL Server 7.0 instances operating in a cluster must be upgraded to SP3 before they are reclustered.

Additional information about running SQL Server in a clustered environment is available in a white paper. For more information about the white paper, see section 5.13, "SQL Server Clustering White Paper".

3.5 Stop Applications Before Running Service Pack 3 Setup

Before running Service Pack 3 Setup to apply Database Components SP3, stop all currently running services except for the following:

NOTE: If you are installing to computers that are nodes in a Windows NT cluster, make sure that the services are stopped on all nodes in the cluster. If any of the services running are clustered services, those clustered services must be taken offline using the cluster administrator.

• Alerter
• Computer Browser
• Cluster Service
• Distributed File System
• Distributed Link Tracking Client
• Distributed Link Tracking Server
• DNS Client
• Event Log
• IPSEC Policy Agent
• License Logging Service
• Logical Disk Manager
• Messenger
• Net Logon
• NT LM Security Support Provider
• Plug and Play
• Process Control
• Remote Procedure Call (RPC) Locator
• Remote Procedure Call (RPC) Service
• Remote Registry Service
• Removable Storage
• Security Accounts Manager
• Server
• Spooler
• TCP/IP NetBIOS Helper
• Windows Management Instrumentation Driver Extensions
• Windows Time Service
• Workstation
• stop SQL Server Service Manager if it is running. To do this, right-click the minimized SQL Server Service Manager icon in the systray on the right side of the taskbar, and then click Exit.

unless a specific service is required by your hardware vendor for basic functionality. Special monitoring or management services should be stopped. Not all services listed above may be installed on your system depending on your operating system version.

If you are running Service Pack 3 Setup to apply OLAP Services SP3, you must not stop the MSSQLServer service if you have migrated your OLAP Services repository to SQL Server.

3.6 Install Database Components SP3

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

• The directory containing the extracted service pack files from either Sql70sp3i.exe or Sql70sp3a.exe.
• The service pack directory on the SQL Server 7.0 SP3 compact disc.

Setup.bat starts an InstallShield dialog box that prompts you for information, such as whether to use SQL Server Authentication or Windows NT Authentication. If you choose SQL Server Authentication, you must supply the Setup program with the password for the system administrator (sa) login. If you choose Windows NT Authentication, you must be running Setup while logged on to Microsoft Windows using a Microsoft Windows logon account that is in the sysadmin fixed server role of the instance of SQL Server or MSDE you are upgrading.

The setup process then replaces the existing SQL Server or MSDE files with the service pack files. Setup also runs several .sql script files to update system stored procedures.

The MS DTC installer installs a new version of Microsoft Distributed Transaction Coordinator on your system. In a clustered environment, it does this on all nodes of the Windows NT cluster.

When Setup connects to SQL Server 7.0 or MSDE 1.0 to run the .sql scripts updating system stored procedures, it 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. This is to give users a chance to address the potential security issue of running a system with a blank password for the sa login. Leaving the sa login password blank provides users with easy administrative access to SQL Server or MSDE; sites should protect their systems by enforcing an sa password or using Windows NT Authentication. The Authentication Mode dialog box does not default to the current settings for the installation. The dialog box defaults are:

• On computers running Microsoft Windows 95, Windows 98, and Windows Millennium, the Authentication Mode dialog box defaults to Mixed Mode authentication (the only authentication mode supported on these operating systems) and requests that you specify a password for the sa login. If you specify a password, Setup will change the sa login password. If you leave the password blank, Setup will connect and not change the password.
• On computers running Microsoft Windows NT 4.0 or Windows 2000, Setup defaults to Windows NT Authentication. You can use the dialog box to switch the authentication mode to Windows NT Authentication, or Mixed Mode with an sa login password that is not blank.

Before changing the authentication mode or the password for the sa login, ensure that this will not affect existing applications. For example, if you change a Windows 2000 SQL Server to using only Windows NT Authentication, existing applications that attempt to connect using SQL Server Authentication will not be able to connect until changed to use Windows NT Authentication. Also, if you change the sa login password, applications or administrative processes using the old password will not be able to connect until changed to use the new password.

Setup places a record of the actions it performed in the Sqlsp.log file in the Temp directory of the computer on which it was run.

3.7 Install OLAP Services SP3

To install OLAP Services SP3, run Setup.exe from either:

• The Msolap\Install subdirectory in the directory containing the extracted OLAP Services service pack files from either Sql70olapsp3i.exe or Sql70olapsp3a.exe.
• The Msolap\Install directory on the SP3 compact disc.

Setup.exe starts an Installshield dialog box that prompts you for information and then completes the installation.

3.7.1 Updating or Installing the Add-in and Task Kits

Introduced in Service Pack 2

This service pack also includes updates to OLAP Add-in Kit 1 and OLAP Services DTS Task Kit 1. If you are running versions of these kits previous to SP2 and you install SP3, you must install the SP3 versions of the kits as well. To install the updated add-in kit, run Addins.exe. To install the updated task kit, run DTSTasks.exe. These files are in the same locations as Setup.exe, and like Setup.exe, they start an InstallShield dialog box to help you with installation. You can also run these executables to install the Add-in and Task Kits for the first time. If you have already run these executables with SP2, you do not need to do so for SP3 or later service packs.

For additional information, see section 5.9.1, "Updated OLAP Add-in Kit 1 and DTS Task Kit 1".

3.8 Restart Services

When Setup completes, it may prompt you to restart the system. After the restart, or after Setup completes without requesting a restart, use the Services application in Control Panel to make sure the Microsoft Search, MSDTC, MSSQLServer, MSSQLServerOLAPService, and SQLServerAgent services are running. Back up the upgraded master and msdb databases.

3.9 Recluster a Cluster Configuration

If you have applied this service pack in a Windows NT cluster configuration, recluster SQL Server. Before reclustering, you must apply this service pack to all SQL Server 7.0 instances that will be included in the cluster.

If in step 3.4 you removed MSMQ dependencies on SQL Server, run Regedt32 to edit the registry key HKEY_LOCAL_MACHINE\Software\Microsoft\MSMQ\Parameters\MachineCache\MQS. If the key has the value 2 (for BSC), 4 (for PSC), or 8 (for PEC), record the current value and change the value to 1. Otherwise, leave the value of the key unchanged.

To recluster SQL Server, in the SQL Server 7.0 program group, click Failover Cluster Wizard. When prompted by the wizard, insert your original SQL Server Enterprise Edition CD and follow the instructions displayed by the wizard. Do this on all nodes of the Windows NT cluster that contain instances of SQL Server that you want to include in the cluster.

If you changed the original value of the MSMQ registry key (HKEY_LOCAL_MACHINE\Software|Microsoft\MSMQ\Parameters\MachineCache\MQS) before using the Failover Cluster Wizard, run Regedt32 to restore the original value of the registry key.

If you removed dependencies on the SQL Server resources and resource types in step 3.4, use the Cluster Administrator to restore the dependencies.

If an MSMQ resource had dependencies on SQL Server and the SQL Network name, use the Cluster Administrator to restore the dependencies. Remove any dependencies on any other network name that you established in step 3.4. If you created a temporary network name and IP address resources in step 3.4, delete them. Take the MSMQ resource online.

3.10 Restart Applications

Restart the applications you closed before running Service Pack 3 Setup.

4.0 Redistributing Database Components SP3 Client Components

Service Pack 3 for SQL Server 7.0 and MSDE 1.0 includes a self-extracting file, Sqlredis.exe, and a version of Mdacredist.txt. These files are in the Database Components SP3. By default, when Sqlredis.exe is executed it:

1. Executes the Mdac_typ.exe from MDAC 2.1 SP2. This installs the MDAC 2.1 SP2 core components and the versions of the SQL Server and MSDE client connectivity components that shipped with Service Pack 1 for SQL Server 7.0 and MSDE 1.0.
2. Replaces the SQL Server and MSDE client connectivity components with the new versions from the Database Components SP3.
3. Replaces Mswstr10.dll, Mswdat10.dll, Oleaut32.dll, Olepro32.dll, and Stdole2.tlb.

If you want to install only the updated Database Components SP3 client connectivity components, use this syntax at the command prompt: You can redistribute the Sqlredis.exe file under the same terms and conditions noted in the Mdacredist.txt file that accompanies this service pack.

Documentation Notes

This section covers issues that affect sites running SP3, but are not the result of fixes contained in the service pack.

Changes in behavior introduced by fixes are documented in the FIX Knowledge Base article published for each fix. Knowledge Base article Q274797 is a list of fixes contained in the service pack. Each fix is linked to its corresponding Knowledge Base article. You can access the articles from http://support.microsoft.com/servicedesks/directaccess. Click Search Support, select SQL Server and Specific Article ID Number as the search parameters, and enter Q274797. Follow the links to the FIX Knowledge Base articles to see the information about each fix.

5.1 Upgrading Client-Only Computers After Installing SP3

Users sometimes first install only the SQL Server client components on a computer, and then later add a Standard, Enterprise, or Desktop Edition of the database server to the computer by running the Setup program from the SQL Server compact disc. If the client components on the computer have already been upgraded to SP3, the SQL Server Setup program fails with the error: Use the following procedure to add a Standard, Enterprise, or Desktop Edition of the database server to a client computer running the SP3 client components:

1. Copy the Bcp.exe file from your SQL Server 7.0 Setup compact disc to the client computer, overwriting the SP3 version of Bcp.exe in the C:\Mssql7\Binn directory.
2. Run the Setup program from your SQL Server 7.0 Setup compact disc to install the server components.
3. Run Database Components SP3 Service Pack 3 Setup, by following the instructions in this Readme.txt file, to upgrade all SQL Server database components on the computer to SP3.

NOTE: Upgrading a client to a Standard or Enterprise Edition of the server requires the purchase of an appropriate server license. Client computers covered by either a Standard or Enterprise SQL Server Client Access License (CAL) can install a Desktop Edition server.

5.2 Upgrading Japanese Database Servers to Windows 2000 After Installing SP3

If you install the Database Components SP3 on a server running Microsoft Windows NT, or a Windows 95 or Windows 98 operating system and later upgrade to Windows 2000, the Windows 2000 upgrade replaces certain system files involved in sorting Japanese characters with versions of the files that are earlier than the versions required by SP3. If you use Japanese characters in your SQL Server databases, rerun the version of Sqlredis.exe that came with SP3 after you perform the upgrade to Windows 2000. For more information about executing Sqlredis.exe, see section 4.0, "Redistributing Database Components SP3 Client Components".

This issue applies only to servers on which you have databases that contain Japanese characters. You do not have to reapply Sqlredis.exe on client computers or on servers that do not have databases containing Japanese characters.

5.3 Applying SP3 to a Later Version

If you attempt to upgrade a SQL Server installation whose database engine executable files are the same date or newer than the ones provided with Database Components SP3, Service Pack 3 Setup will stop with the following message: Usually this indicates that SP3 (or later) has already been applied to the SQL Server instance on the server and an upgrade is not required.

Possible exceptions to this are customers who have received from their SQL Server support vendor a special SQL Server build that is newer than the SP3 build, and have applied that build to a system that was not first upgraded to SP3. These customers should contact their SQL Server support vendor to request information about the upgrade process.

5.4 Installing SP3 on Windows NT 4.0, Terminal Server Edition

The original release of SQL Server 7.0 is not supported on Windows NT 4.0 Terminal Server Edition. SQL Server 7.0 Service Pack 1 introduced support for running SQL Server on Windows NT Server 4.0, Terminal Server Edition. SQL Server 7.0 Service Pack 2 and Service Pack 3 continue the support for Windows NT Server 4.0, Terminal Server Edition.

SQL Server 7.0 Service Pack 3 requires that Windows NT Server 4.0, Terminal Server Edition, Service Pack 4 or later be applied to a computer running Terminal Server Edition before SQL Server is installed. This service pack is specific to Terminal Server Edition and is separate from the Windows NT Server 4.0 Service Pack 4. For more information about the Terminal Server Edition service pack, see the Microsoft service pack download Web page at http://www.microsoft.com/ntserver/terminalserver/downloads/recommended/tsesp6/default.asp

Use these steps to install SQL Server 7.0 and SP3 on Windows NT Server 4.0, Terminal Server Edition:

1. Ensure that Windows NT 4.0, Terminal Server Edition, Service Pack 4 or later has been applied.
2. Use the Services application in Control Panel to stop the Terminal Server Licensing Service.
3. Copy the SQL Server ODBC files to the System32 directory on the computer running Terminal Server Edition. Place the SQL Server 7.0 compact disc in the computer. Copy the files from the ODBC directory on the compact disc to the C:\Winnt\System32 directory of the server:
   
   
   • For Alpha-based servers, copy the files in the Alpha\ODBC directory. Do not copy the Symbols subdirectory.
   • For Intel-based servers, copy the files in the X86\ODBC directory. Do not copy the Symbols subdirectory.
4. Use the Services application in Control Panel to restart the Terminal Server Licensing Service.
5. Install SQL Server 7.0 using the SQL Server 7.0 compact disc. SQL Server cannot be installed by a thin client; it must be installed using the console on the computer running Terminal Server Edition. You cannot install SQL Server on a computer running Terminal Server Edition using the Autorun setup window that is displayed when you insert the SQL Server compact disc. Close the window, and then open the Add/Remove Programs application in Control Panel. Click the Install button and follow the instructions. Run the Setup program directly from the SQL Server 7.0 compact disc:
   
   
   • For Intel-based servers, run X86\Setup\Setupsql.exe.
   • For Alpha-based servers, run Alpha\Setup\Setupsql.exe.
     
     Follow the instructions displayed by SQL Server Setup. If you want to install a database directly on the computer running Terminal Server Edition, you can run a full SQL Server setup to install both the client communications components and the database engine. If you only want to give applications running on the same computer with Terminal Server Edition the ability to connect to SQL Server databases on other servers, you can install only the client connectivity components. For more information about performing full-server or client connectivity-only setups, see Running SQL Server Setup in SQL Server Books Online for SQL Server 7.0.
6. Apply SP3 by following the instructions in this file. SP3 must be installed from the console of the computer running Terminal Server Edition. Also, you must run the Service Pack 3 Setup (Setup.bat) from the Add/Remove Programs application in Control Panel.

5.5 New Troubleshooting Guide

Database Components SP2 introduced a new version of the Troubleshooting Guide section from SQL Server Books Online for SQL Server 7.0. Database Components SP3 also installs this new version of the guide. The new guide expands the information in the original SQL Server Books Online for SQL Server 7.0 with new information from the SQL Server support teams:

• Links from the SP2 Troubleshooting Guide to interactive problem-solving guides on the Microsoft Product Support Web site.
• Expanded sets of frequently asked questions (FAQs).
• Additional information and tips that Product Support Engineers have found help customers become proficient with SQL Server 7.0.

Database Components SP3 installs a new file, Tblshsp2.chm, to the \Mssql7\Books folder on your computer. The SP2 Troubleshooting Guide does not physically replace the Troubleshooting Guide in the original version of SQL Server Books Online. You cannot link from SQL Server Books Online to the SP2 Troubleshooting Guide. You must execute the new Troubleshooting Guide separately using one of these methods:

• Double-clicking the Tblshsp2.chm file.
• Adding a shortcut to your desktop, and then double-clicking the shortcut. When adding the shortcut, point it to the \Mssql7\Books\Tblshsp2.chm file.

The SP2 Troubleshooting Guide requires that you have SQL Server Books Online for SQL Server 7.0 installed on your computer.

5.6 SP3 and Replication

It is recommended that you apply this service pack to all SQL Server 7.0 participants in your replication topology: Publisher, Distributor, and Subscribers. This is the recommended sequence for deploying SP3 across the replication topology:

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

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

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

You may not be able to immediately upgrade all the servers in a replication topology, so replication operations are generally unaffected between servers running the original version of SQL Server 7.0 or previous service packs and SP3 participants. The exceptions to this are covered in topics 5.6.1 through 5.6.5.

5.6.1 Installing SP3 on a Server Providing a Remote Distribution Database for Merge Publications

If you upgrade a server to SP3 and that server contains a remote distribution database for merge publications, you must also upgrade each of the merge replication Publishers that use that distribution database to SP3. This requirement ensures optimal delivery of data changes from the associated merge replication Publishers by any Merge (Push) Agents running on the Distributor.

5.6.2 Upgrading to SP3 a Merge Publisher That Will Still Receive New Subscriptions From the Original Release of SQL Server 7.0

To allow new merge replication Subscribers from servers that have the original release version of SQL Server 7.0 installed, you must add the optional parameter -70Subscribers to each Snapshot Agent job.

This ensures complete initial synchronization of any new SQL Server 7.0 merge Subscribers to which no service packs have been applied.

If a Subscriber running the original version of SQL Server has already received the initial synchronization, this Subscriber will continue to be able to merge data even without this parameter. However, if the subscription is dropped and re-created, or the subscription has to be completely reinitialized, the parameter would be necessary. The preferred solution would be to upgrade the Subscriber to SP3 rather than use the parameter. If you want to be sure, you can add the parameter until you are confident all Subscribers have moved to SP3.

5.6.3 Installing SP3 on a Merge Replication Subscriber

If you upgrade a SQL Server merge replication pull Subscriber to SP3, you must also upgrade each of its associated merge replication Publishers to SP3. This ensures optimal delivery of data changes from the associated merge replication Publishers by any Merge (Pull) Agents running on the Subscriber. This is best accomplished using the Distributor-Publisher-Subscriber deployment sequence presented earlier.

5.6.4 Running SP3 Replication With Earlier Versions of SQL Server

There are no known differences in the way SP3 works with SQL Server 6.0 or 6.5 Publishers and Subscribers compared to the way the original SQL Server 7.0 works with these earlier versions.

5.6.5 Updating Access 2000 (Jet 4.0) Merge Replication Subscribers

For Jet-based Access 2000 Merge Subscribers to take advantage of improvements in SP3, you must copy the following Database Components SP3 files to your workstations that run Microsoft Access 2000:

• x86\binn\replres.dll
• x86\binn\replrec.dll
• x86\binn\replprov.dll
• x86\jet\msrpjt40.dll

Copy each file to this folder on the system drive in the workstation: \Program Files\Common Files\Microsoft Shared\Database Replication

NOTE: These steps are not necessary if MSDE 1.0 is serving as the Access 2000 Merge Subscriber. In this case, simply apply Database Components SP3, following the instructions in this Readme.

5.6.6 Optimizing Replication Synchronization

Introduced in Service Pack 2

Optimizing synchronization during merge replication allows you to store more information at the Publisher instead of transferring that information over the network to the Subscriber. While this option may result in a larger database at the Publisher, it can improve synchronization performance over a slow link. However, more information will be stored at the Publisher and additional storage space will be necessary.

By storing additional information at the Publisher, SQL Server can quickly determine the filtered data that should be sent to a particular Subscriber. When synchronization is optimized, SQL Server creates before image tables at the Publisher that contain information such as the schema, partition information, and generation information to check if changes need to propagated to other Subscribers. These before image tables allow SQL Server to determine how to partition data and what Subscribers need new generations of data.

For example, if a sales organization partitions and distributes data based on sales territories, and the publication is enabled to optimize synchronization, the information about how data is partitioned would be stored in before image tables at the Publisher. If sales territories shift and data needs to be repartitioned to multiple Subscribers, it will be a faster process to update and redistribute the data because the information about how data is currently partitioned is already at the Publisher.

NOTE: To minimize the amount of data sent over the network, execute the sp_addmergepublication system stored procedure and set the @keep_partition_changes parameter to 'true'.

5.6.7 Performance Enhancements for Merge Processing of the Initial Snapshot

Introduced in Service Pack 2

The process of generating the initial snapshot has been improved for merge publications that have static or join filters.

During snapshot generation, the Snapshot Agent creates a bulk copy file for the MSmerge_contents table. Microsoft SQL Server 7.0 Service Pack 3 provides faster performance when static or join filters are used because it copies only the rows that are related to the tables in the publication based on the filters used.

In releases prior to Service Pack 2, the Snapshot Agent generated a bulk copy file for the MSmerge_contents table, and the file included all the rows from MSmerge_contents for a published table regardless of the filter criteria used. High volumes of update activity on the publishing table causes the MSmerge_contents table to grow, which slows performance when generating the initial snapshot.

With SQL Server Service Pack 2 or later, the .bcp file generated is smaller (because rows are copied only if they relate to the partition of data being published), so the performance of the Merge Agent when applying the snapshot to Subscribers has also improved.

This applies only to merge publications with static or join filters because the MSmerge_contents table is not bulk copied when snapshots are generated for merge publications with dynamic filters.

For example, a merge publication includes the Customers, Orders, and Order Details columns, and the publication is filtered to publish only the customers in the Northwest region. When the snapshot is generated, only rows in MSmerge_contents that are relevant to the data in the filtered partition of data to be published are bulk copied. In releases prior to the Service Pack 2 release, all rows from the MSMerge_contents table were bulk copied.

5.6.8 Merge Meta Data Cleanup

Introduced in Service Pack 2

The sp_mergecleanupmetadata system stored procedure allows administrators to clean up meta data in the MSmerge_contents and MSmerge_tombstone system tables. Although these tables can expand indefinitely, in some cases it improves merge performance to clean up the meta data. This procedure can be used to save space by reducing the size of these tables at the Publisher and Subscribers.

CAUTION: After sp_mergecleanupmetadata is executed, all named subscriptions at the Subscribers of publications that have meta data stored in the two tables are marked for reinitialization, changes at the Subscriber are lost, and the current snapshot is marked obsolete. SQL Server 7.0 does not support reinitialization of anonymous subscriptions from the Publisher. The reinitialization must be initiated at the Subscriber.

Before executing this stored procedure, merge all data from Subscribers with the Publisher to load all the Subscriber data changes that must be saved. Snapshot files for all merge publications involved at all levels must be regenerated after executing this stored procedure. If you try to merge without running the snapshot first, you are prompted to run the snapshot.

The reinitialization does not propagate the merge topology automatically. The administrator must reinitialize all subscriptions at every republisher manually.

By default, the @reinitialize_subscriber parameter of sp_mergecleanupmetadata is set to TRUE, and all subscriptions are marked for reinitialization. If you set the @reinitialize_subscriber parameter to FALSE, the subscriptions are not marked for reinitialization. Setting the parameter to FALSE should be used with caution, however, because if you choose not to have the subscriptions reinitialized, you must make sure that data at the Publisher and Subscribers is synchronized.

If you want to run sp_mergecleanupmetadata without the subscriptions being marked for reinitialization, you should:

1. Synchronize all Subscribers.
2. Stop all updates to the publishing and subscribing databases.
3. Execute a merge that validates the Subscriber data with the Publisher by running the Merge Agent with the -Validate command line option at each Subscriber.
4. Execute the sp_mergecleanupmetadata system stored procedure. After the stored procedure has executed, you can allow users to update the publishing and subscribing databases again.

To use this stored procedure, the Publisher and all Subscribers must be running Microsoft SQL Server 7.0 with Service Pack 3. Only members of the sysadmin role can use this stored procedure. To clean up merge meta data, execute the sp_mergecleanupmetadata system stored procedure. If you specify a @tablename parameter, only the merge meta data for that table will be cleaned. If no table name is specified, all merge meta data in MSmerge_contents and MSmerge_tombstone will be cleaned.

5.7 Change in Cursor Behavior

Introduced in Service Pack 1

In the original releases of SQL Server 7.0 and MSDE 1.0, a rollback closes all open cursors except for cursors that meet all of these conditions:

• CURSOR_CLOSE_ON_COMMIT is OFF.
• The cursor is static.
• The cursor has finished populating its worktable.

To increase compatibility with earlier versions of SQL Server, Database Components SP1 changed this so that cursors are only closed on rollback if one of the following is true:

• CURSOR_CLOSE_ON_COMMIT is ON.
• An asynchronously populated cursor is currently populating.

The behavior introduced in Database Components SP1 is carried forward in later service packs.

5.8 Changes in SQL Server Profiler

The connection id data column in SQL Server Profiler will not be supported in a future version of SQL Server. It is recommended that you use the SPID data column instead.

5.9 Changes in OLAP Services

This section documents the changes in OLAP Services that are included in OLAP Services SP3.

5.9.1 Updated OLAP Add-in Kit 1 and DTS Task Kit 1

Introduced in Service Pack 2

This service pack includes updates for the add-in and task kits, which must be updated if you are running versions of these kits previous to SP2 and you install OLAP Services SP2 or later. To install the updated add-in kit, run Addins.exe. To install the updated task kit, run DTSTasks.exe. These files are included in the \Msolap\Install directory. The add-ins are available only for Intel-based systems. They are not available for the Alpha system. Information about these updates is included in the Readme files for the kits. DTSTaskReadMe.txt and OLAPAddinReadMe.txt are installed by default to C:\Program Files\OLAP Services. For more information, see section 3.7.1, "Updating or Installing the Add-in and Task Kits".

5.9.2 Improved Security

Introduced in Service Pack 1

OLAP Services SP3 includes enhanced security for OLAP Services. Cell-level security is now supported. For information about using cell-level security, see Knowledge Base (KB) article Q259348. The KB article is published at http://support.microsoft.com/search/. Click Search Support, select SQL Server and Specific Article ID Number as the search parameters, and type Q259348.

If OLAP Services SP3 is applied to a server, but not to a client accessing the server, the client will not benefit from the OLAP Services SP3 fixes and will not be able to use the cell-level security introduced in OLAP Services SP1.

5.9.3 The German Version of SQL Server OLAP Services on Alpha

Introduced in Service Pack 1

Setup prevents the installation of the German version of SQL Server 7.0 OLAP Services on Alpha platforms because this is not a supported configuration. You can use the English version of OLAP Services SP3 on Alpha computers running German-version software.

5.9.4 Migrating the Repository of a Remotely Administered OLAP Server

Introduced in Service Pack 1

After you install OLAP Services SP1, SP2, or SP3 on a server, you can migrate the repository using the instance of OLAP Manager on the server. You cannot use OLAP Manager from another computer remotely administering the server to perform this task.

5.9.5 OLAP Services with the Korean Version of Windows NT

Introduced in Service Pack 1

If you use SQL Server 7.0 OLAP Services on a server running the Korean version of Windows NT, you may not be able to connect to your OLAP server if you install OLAP Services SP1, SP2, or SP3, or separately install the database components MDAC 2.1 SP1 or later. SQL Server 7.0 SP1, SP2, and SP3 also install these database components. After you install any of these services packs on the server running OLAP Services, you must run Olaprepair.exe for x86 systems or Olaprepaira.exe for Alpha systems on the server to be able to connect to your OLAP server. Olaprepair.exe or Olaprepaira.exe is a utility in the English version of OLAP Services SP3. Running this utility may require you to restart your server twice.

NOTE: If you have previously run Olaprepair.exe, you do not have to run it again.

5.9.6 OLAP Services License Error after Changing Computer Name or Network Card

If you change the computer name or replace the network card in the computer, you will receive an error regarding the license when you attempt to start OLAP Services. To resolve this error, you must reinstall OLAP Services 7.0 and then reinstall SP3. It is recommended that you back up your data before reinstalling OLAP Services or SP3.

5.9.7 Reinstall SP3 After Reinstalling OLAP Services

If it is necessary to reinstall OLAP Services from the SQL Server 7.0 CD-ROM, you must then reinstall SP3 to update the installation.

5.10 Data Transformation Services

This section documents the changes introduced in Data Transformation Services (DTS) by the service pack.

5.10.1 Exporting to Oracle Databases

Introduced in Service Pack 1

When exporting to Oracle using the DTS Export Wizard, if you create the destination tables using DTS, DTS will create the case-sensitive tables using quotation marks around the owner and table names. This is required to support owner or table names that are keywords or contain spaces. If your names are not keywords and do not contain spaces, and you want to remove the quotation marks, delete them in the DTS Copy Tables dialog box.

5.10.2 Using the DTS Test Feature

Introduced in Service Pack 1

The DTS Test feature has improved functionality and now supports global variables and lookups. The DTS user interface has a test option in two dialog boxes:

• In the Data Transformation Properties dialog box, click the Transformations tab and right-click one of the lines mapping the column transformations. The menu presented has a test menu item.
• Double-click an ActiveX Script Task Properties dialog box, and then click the Test button.

These tests now support global variables and lookups. The tests are not supported on lineage columns.

5.10.3 Changes to the Execute Package Dialog Box

Introduced in Service Pack 1

When you execute a DTS package in the DTS Import Wizard, DTS Export Wizard, or DTS Designer, extra columns have been added to the Execute Package dialog box to display start time, end time, and duration. These new columns are not available if the package is run with a user password.

In addition, transfer notification information is provided during package execution.

5.10.4 Data Transformation Services Error Messages

Introduced in Service Pack 1

DTS Error messages have been improved. The error messages now supply OLE DB provider-specific information and information about the operation that failed.

5.10.5 Use of the SET Keyword in Microsoft Visual Basic Scripts

Introduced in Service Pack 1

ActiveX assignment of object references to DTS global variables in Microsoft Visual Basic, Scripting Edition scripts now follows standard Visual Basic Set syntax in all cases.

Use Set when you want to assign an object reference; otherwise, the default value of the object will be assigned.

If a global variable contains an object reference, use both Set and .Value to reassign the object reference, for example:

• This statement assigns a reference to the Connection object to the global variable x:
  
  

  Set DTSGlobalVariables("x").value =
                     CreateObject("ADO.Connection")
  						

• This statement sets the variable o to a reference to the Connection object:
  
  

  SET o = DTSGlobalVariables("x").Value
  						

• And, this statement sets the variable o to a reference to a global variable that contains a reference to the Connection object:
  
  

  SET o = DTSGlobalVariables("x")
  						

5.10.6 Changes in DTS Packages

Introduced in Service Pack 2 and Service Pack 3

SP2 and SP3/SQL Server 2000 introduced changes to the internal format that DTS uses to save packages. When the package is saved to file or SQL Server without a password, the following formats are supported for the different service pack versions.

Service pack	Reads	Writes
SP1	SQL 7.0/SP1	SQL 7.0/SP1
SP2	SQL 7.0/SP1, SP2	SP2
SP3	SQL 7.0/SP1, SP2, SP3/SQL 2000	SP2, SP3/SQL Server 2000

5.11 SQL Server 7.0 and Exchange 5.5

If you run both a SQL Server 7.0 and a Microsoft Exchange version 5.5 server on the same computer, you must explicitly configure memory usage in SQL Server. SQL Server will not operate properly if you leave the SQL Server minimum dynamic memory option set at the default value of 0.

To address a known memory issue that occurs when the two products are run simultaneously, you must set the SQL Server 7.0 minimum dynamic memory (or sp_configure 'min server memory' option) to the amount of memory required to support the peak processing load of SQL Server. In this environment, SQL Server will not acquire enough memory to reach the maximum dynamic memory setting (or sp_configure 'max server memory' option). Instead, SQL Server will usually run with the amount of memory specified in the minimum dynamic memory option. The minimum dynamic memory setting must therefore be sufficient to run SQL Server when it is operating at high capacity.

To determine the memory needed by SQL Server, monitor the memory used by SQL Server under the following conditions:

• If your database is running in production, monitor the SQL Server memory usage when the system is running at high capacity.
• If your SQL Server applications were developed in-house, run stress tests while monitoring the memory used by SQL Server.
• If your SQL Server applications were developed by a third party, you may need to consult the application documentation or vendor to determine its impact on SQL Server memory.

For more information about monitoring SQL Server memory or setting SQL Server memory options, see the "Monitoring Memory Usage" and "Server Memory Options" topics in SQL Server Books Online for SQL Server 7.0.

This article is continued in the following Microsoft Knowledge Base article: