Readme.txt for SQL Server 7.0 Service Pack 2

Article translations Article translations
Article ID: 254555 - View products that this article applies to.
This article was previously published under Q254555
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

Summary

This article is a copy of the Readme.txt file included in Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0.

More information

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

March 3, 2000
(c) Microsoft Corporation, 2000

Contents

1.0 Introduction
1.1 Service Pack Versions

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

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 2 Setup
3.6 Install Database Components SP2
3.7 Install OLAP Services SP2
3.8 Restart Services
3.9 Recluster a Cluster Configuration
3.10 Restart Applications

4.0 Redistributing Database Components SP2 Client Components

5.0 Documentation Notes
5.1 Upgrading Client-Only Computers After Installing SP2
5.2 Upgrading Japanese Database Servers to Windows 2000 After Installing SP2
5.3 Applying SP2 to a Later Version
5.4 Installing SP2 on Windows NT 4.0, Terminal Server Edition
5.5 New Troubleshooting Guide
5.6 SP2 and Replication
5.6.1 Installing SP2 on a Server Providing a Remote Distribution Database for Merge Publications
5.6.2 Upgrading to SP2 a Merge Publisher That Will Still Receive New Subscriptions from the Original Release of SQL Server 7.0
5.6.3 Installing SP2 on a Merge Replication Subscriber
5.6.4 Running SP2 Replication with Earlier Versions of SQL Server
5.6.5 Updating Access 2000 (Jet 4.0) Merge Replication Subscribers
5.6.6 Optimizing Replication Synchronization
5.6.7 Performance Enhancements for Merge Processing of the Initial Snapshot
5.6.8 Merge Metadata Cleanup
5.7 Change in Cursor Behavior
5.8 Changes in SQL Server Profiler
5.9 Changes in OLAP Services
5.9.1 Updated OLAP Add-in Kit 1 and DTS Task Kit 1
5.9.2 Improved Security
5.9.3 The German Version of SQL Server OLAP Services on Alpha
5.9.4 Migrating the Repository of a Remotely Administered OLAP Server
5.9.5 OLAP Services with the Korean Version of Windows NT
5.9.6 Reinstall SP2 After Reinstalling OLAP Services
5.10 Data Transformation Services (DTS)
5.10.1 Exporting to Oracle Databases
5.10.2 Using the DTS Test Feature
5.10.3 Changes to the Execute Package Dialog Box
5.10.4 Data Transformation Services Error Messages
5.10.5 Use of the SET Keyword in Microsoft Visual Basic Scripts
5.10.6 Changes in DTS Packages
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
5.16.1 Importing from DB2 and Informix Databases
5.16.2 Improved Retrieval of Versioned Objects

1.0 Introduction

Service Pack 2 for Microsoft SQL Server 7.0 and MSDE 1.0 is provided in two parts:
  • Database Components SP2 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 osql. (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 SP2 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 SP2 to upgrade their database components without upgrading their OLAP Services components.
  • SQL Server 7.0 sites can also use OLAP Services SP2 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 SP2 to upgrade their MSDE 1.0 components.
Service Pack 2 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 are included only with SQL Server 7.0.

Service Pack 2 cannot be removed easily, due to system table changes the service pack requires for maintenance. To revert to a build before SP2 was installed, you must reinstall SQL Server 7.0 and, if required, apply SP1 to the installation. Before you reinstall SQL Server 7.0, detach your databases on SP2 and reattach them after you reinstall SQL Server 7.0 and SP1. For information about using the sp_attach_db and sp_detach_db stored procedures, see SQL Server 7.0 Books Online. 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 included with SQL Server 7.0.

Because SQL Server service packs are cumulative, Service Pack 2 includes the fixes from all earlier SQL Server 7.0 service packs. Therefore, Service Pack 2 can be applied to an original installation of SQL Server version 7.0 or to any SQL Server 7.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 254369. Each fix listed in 254369 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. Click the Search the Knowledge Base link, and then click the search by Article ID link. In the Enter the Knowledge Base article ID number (e.g. 123456) in the box below and click the green arrow to view it box, type 254369, and then click the green arrow. Follow the links to the FIX Knowledge Base articles to see the information about each fix.

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 a SELECT @@VERSION statement from osql, SQL Server Query Analyzer, or isql. The following 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 
				
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, as well as the operating system that the server is running.

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 by doing the following:
  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. On the OLAP Manager Help menu, click About Microsoft SQL Server OLAP Services.

    The following 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 
    						

2.0 Downloading and Extracting SP2

This service pack is distributed in two formats:
  • On an SP2 compact disc.
  • In four self-extracting files that can be downloaded from the Internet:

    Sql70sp2i.exe: Database Components SP2 for Intel-based computers
    Sql70sp2a.exe: Database Components SP2 for Alpha-based computers
    Sql70olapsp2i.exe: OLAP Services SP2 for Intel-based computers
    Sql70olapsp2a.exe: OLAP Services SP2 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 SP2 compact disc. The setup process is then the same for both the SP2 compact disc and the extracted SP2 files.

If you downloaded SP2, 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 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. Then on the View tab of the Options dialog box, select the Show All Files option.

2.1 Disk Space Requirements for SP2

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

Intel-based computers require the following amounts of free disk space:
  • DB components only for SP2: 193 MB
  • OLAP Services only for SP2: 187 MB
  • Both DB components and OLAP Services for SP2: 380 MB
Alpha-based computers require the following disk space:
  • DB components only for SP2: 269 MB
  • OLAP Services only for SP2: 187 MB
  • Both DB components and OLAP Services for SP2: 456 MB
If you do not have enough space to download SP2 to drive C, you can conserve disk space by downloading the SP2 zip file to a secondary drive and extracting it to drive C. The following list identifies the disk space required to unpack SP2 on Intel-based and Alpha-based computers.

Intel-based computers require the following disk space:
  • DB components only for SP2: 150 MB required on the C: drive; 43 MB required to download to your secondary drive
  • OLAP Services only for SP2: 130 MB required on the C: drive; 57 MB required to download to your secondary drive
  • Both DB components and OLAP Services for SP2: 280 MB on the C: drive; 100 MB required to download to your secondary drive
Alpha-based computers require the following disk space:
  • DB components only for SP2: 209 MB required on the C: drive; 60 MB required to download to your secondary drive
  • OLAP Services only for SP2: 130 MB required on the C: drive; 57 MB required to download to your secondary drive
  • Both DB components and OLAP Services for SP2: 339 MB required on the C: drive; 117 MB required to download to your secondary drive

2.2 Extracting Database Components SP2

To extract the Database Components SP2, place the self-extracting file for your processor architecture into a directory on the computer running SQL Server or MSDE. 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:\70sp2, copy the Sql70sp2i.exe file to your computer, execute it, and provide the name of the C:\70sp2 directory when prompted.

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

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

2.3 Extracting OLAP Services SP2

To extract the OLAP Services SP2, create a directory on your computer to hold the OLAP Services SP2. 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:\70sp2olap, copy the Sql70olapsp2a.exe file to your computer, execute it, and provide the name of the C:\70sp2olap directory when prompted. This will extract the service pack files.

3.0 Service Pack Installation

You can 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 SP2 and OLAP Services SP2), complete Steps 3.1 through 3.10.
  • To apply only Database Components SP2 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 SP2 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 SP2, 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.
If OLAP Services SP2 is applied to a server, but not to a client accessing the server, the client will not benefit from the OLAP Services SP2 fixes and it will not be able to use the cell-level security introduced in OLAP Services SP2.

If you install OLAP Services SP2 on a computer, and subsequently reinstall the original version of SQL Server 7.0 OLAP Services, you must install OLAP Services SP2 again.

You can also use Systems Management Server to install SP2 automatically on multiple computers running Windows NT Server. The Smssq702.pdf file is a Package Definition File (PDF) that automates the creation of a SQL Server package in Systems Management Server. The SQL Server package can then be distributed and installed on computers 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.

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 Msmdrep.mdb file, located in the OLAP Services\Bin folder. Do not use the Archive and Restore Database Add-in option 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 command on the Registry menu to export the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server key 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 SP2, 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 then clicking Properties on the shortcut menu. Verify that the Automatically Grow File check box is selected. You can verify this in MSDE by issuing the following 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 the 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 of SQL Server 7.0 Books Online.

3.4 Prepare Cluster Configurations

Before installing Database Components SP2 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 2 Setup will display an 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 SP2. To uncluster SQL Server, in the SQL Server 7.0 program group, click Failover Cluster Wizard. When prompted, insert your original SQL Server, Enterprise Edition compact disc and follow the instructions displayed by the wizard. Do this on all nodes of the Microsoft Windows NT cluster where SQL Server has been clustered.

All SQL Server 7.0 instances operating in a cluster must be upgraded to SP2 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.12, "SQL Server Clustering White Paper".

3.5 Stop Applications Before Running Service Pack 2 Setup

Before running Service Pack 2 Setup to apply Database Components SP2, shut down the following services and applications:
  • The Microsoft Search, MSDTC, MSSQLServer, MSSQLServerOLAPService, and SQLServerAgent services
  • Microsoft Transaction Server, Microsoft Message Queue Server, and Microsoft COMTI
  • All other applications, including Windows NT Control Panel
Before running Service Pack 2 Setup to apply OLAP Services SP2, shut down the services and applications listed above; however, you must not stop the MSSQLServer service if you have migrated your OLAP Services repository to SQL Server.

If you are installing in a Windows NT cluster, ensure that these applications and services are stopped on all nodes in the cluster.

Also stop SQL Server Service Manager if it is running. Right-click the minimized SQL Server Service Manager icon on the right side of the taskbar, and then click Exit.

3.6 Install Database Components SP2

Run the Setup.bat script from either of the following locations:
  • The directory containing the extracted service pack files from either Sql70sp2i.exe or Sql70sp2a.exe

    -or-
  • The service pack directory on the SQL Server 7.0 SP2 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 Windows NT using a Windows NT login 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.

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 SP2

To install OLAP Services SP2, run Setup.exe from either:
  • The Msolap\Install subdirectory in the directory containing the extracted OLAP Services service pack files from either Sql70olapsp2i.exe or Sql70olapsp2a.exe.

    -or-
  • The Msolap\Install directory on the SP2 compact disc.
Setup.exe starts an InstallShield dialog box that prompts you for information and then completes the installation.

This service pack also includes updates to the OLAP Add-in Kit 1 and OLAP Services DTS Task Kit 1. If you are running earlier versions of these kits and you install SP2, you must install the SP2 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.

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. You must apply this service pack to all SQL Server 7.0 instances that will be included in the cluster before reclustering.

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 compact disc and follow the instructions displayed by the wizard. Do this on all nodes of the Windows NT cluster containing instances of SQL Server that you want to include in the cluster.

3.10 Restart Applications

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

4.0 Redistributing Database Components SP2 Client Components

Service Pack 2 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 SP2. 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 came 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 SP2.
  3. Replaces the Mswstr10.dll, Mswdat10.dll, Oleaut32.dll, Olepro32.dll, and Stdole2.tlb files.
If you want to install only the updated Database Components SP2 client connectivity components, use the following syntax at the command prompt:
sqlredis.exe /C:"setupre.exe MDAC=0 -s -SMS"
				
You can redistribute the Sqlredis.exe file under the same terms and conditions noted in the Mdacredist.txt file that accompanies this service pack.

5.0 Documentation Notes

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

Knowledge Base article 254369 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. Click the Search the Knowledge Base link, and then click the search by Article ID link. In the Enter the Knowledge Base article ID number (e.g. 123456) in the box below and click the green arrow to view it box, type 254369, and then click the green arrow. Follow the links to the FIX Knowledge Base articles to see the information about each fix.

5.1 Upgrading Client-Only Computers After Installing SP2

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 SP2, the SQL Server Setup program fails with the following error:
You cannot install a version that is older (7.00.623) than the version on your machine (7.00.842). Uninstall the older version.
Use the following procedure to add a Standard, Enterprise, or Desktop Edition of the database server to a client computer running the SP2 client components:
  1. Copy the Bcp.exe file from your SQL Server 7.0 Setup compact disc to the client computer, overwriting the SP2 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 SP2 Service Pack 2 Setup, by following the instructions in this Readme.txt file, to upgrade all SQL Server database components on the computer to SP2.
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 SP2

If you install the Database Components SP2 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 SP2. If you use Japanese characters in your SQL Server databases, rerun the version of sqlredis.exe that came with SP2 after you perform the upgrade to Windows 2000. For more information about executing sqlredis.exe, see section 4.0, Redistributing Database Components SP2 Client Components, of this Readme.

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 SP2 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 SP2, Service Pack 2 Setup will stop with the following message:
Setup has detected newer SQL Server components than those available in the Service Pack. Setup will not continue.
Usually this indicates that SP2 (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 SP2 build, and have applied that build to a system that was not first upgraded to SP2. These customers should contact their SQL Server support vendor to request information about the upgrade process.

5.4 Installing SP2 on Windows NT 4.0, Terminal Server Edition

Microsoft SQL Server 7.0 Service Pack 1 introduced support for running SQL Server on Windows NT Server 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 2 continues the support for Windows NT Server 4.0, Terminal Server Edition.

Service Pack 2 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 or later.

To install SQL Server 7.0 SP2 on Windows NT Server 4.0, Terminal Server Edition, do the following:
  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.
  4. Place the SQL Server 7.0 compact disc in the computer.
  5. 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.
  6. Use the Services application in Control Panel to restart the Terminal Server Licensing Service.
  7. Install SQL Server 7.0 using the SQL Server 7.0 compact disc.
  8. 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.
  9. 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 the "Running SQL Server Setup" topic in SQL Server 7.0 Books Online.
  10. Apply SP2 by following the instructions in this Readme file. SP2 must be installed from the console of the computer running Terminal Server Edition. Also, you must run the Service Pack 2 Setup (Setup.bat) from the Add/Remove Programs application in Control Panel.

5.5 New Troubleshooting Guide

Database Components SP2 installs a new version of the Troubleshooting Guide section from SQL Server 7.0 Books Online. The new guide expands the information in the original SQL Server 7.0 Books Online 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 Services 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 SP2 installs a new file, Tblshsp2.chm, into 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 the following 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 7.0 Books Online installed on your computer.

5.6 SP2 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. The following is the recommended sequence for deploying SP2 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 and SP2 participants. The exceptions to this are covered in topics 5.6.1 through 5.6.5.

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

If you upgrade a server to SP2 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 SP2. 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 SP2 a Merge Publisher That Will Still Receive New Subscriptions From the Original Release of SQL Server 7.0

To allow new merge replication of Subscribers from servers that do not have SP2 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 that do not have SP2 installed.

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 SP2 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 SP2.

5.6.3 Installing SP2 on a Merge Replication Subscriber

If you upgrade a SQL Server merge replication pull Subscriber to SP2, you must also upgrade each of its associated merge replication Publishers to SP2. 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 SP2 Replication With Earlier Versions of SQL Server

There are no known differences in the way SP2 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 SP2, you must copy the following Database Components SP2 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 the following 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 SP2, 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

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 2 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 7.0 Service Pack 2, 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 2. 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 the following 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 SP2.

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

This service pack includes updates for the add-in and task kits, which must be updated if you are running earlier versions of these kits and install OLAP Services SP2. Information about these updates is included in the readmes 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, Install OLAP Services SP2.

5.9.2 Improved Security

Introduced in Service Pack 1

OLAP Services SP2 includes enhanced security for OLAP Services. Cell-level security is now supported. For information about using cell-level security, see the "Cell-Level Security" white paper, available on http://http://www.microsoft.com/technet/prodtechnol/sql/70/maintain/cellsec.mspx.

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 SP2 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 or SP2 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 or SP2, or separately install the database components MDAC 2.1 SP1 or later. SQL Server 7.0 SP1 and SP2 also install these database components. After you install any of these services packs on the server running OLAP Services, you must run Olaprepair.exe on the server to be able to connect to your OLAP server. Olaprepair.exe is a utility in the English version of OLAP Services SP2. 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 Reinstall SP2 After Reinstalling OLAP Services

If it is necessary to reinstall OLAP Services from the SQL Server 7.0 CD-ROM, you must then reinstall SP2 to update the installation. 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 and then reinstall SP2. It is recommended that you back up your data before reinstalling OLAP Services or SP2.

5.10 Data Transformation Services (DTS)

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 select 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, as in the examples below.

The following statement assigns a reference to the Connection object to the global variable x:
SET DTSGlobalVariables("x").value =
   CreateObject("ADO.Connection")
				
The following statement sets the variable o to a reference to the Connection object:
SET o = DTSGlobalVariables("x").Value
				
The following 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

Database Components SP2 changes the internal format that DTS uses to save packages. Computers running SP1 or earlier versions of DTS cannot read a package using the SP2 version of DTS. The SP2 version of DTS can read packages saved by the SP1 or earlier versions of DTS, but if you save the package using the SP2 version of DTS, it is converted to the SP2 format and can no longer be read by SP1 or earlier versions of DTS.

5.11 SQL Server 7.0 and Microsoft Exchange Server 5.5

If you run both a SQL Server 7.0 and a Microsoft Exchange Server 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 7.0 Books Online.

5.12 The French Version of SQL Server Books Online

Introduced in Service Pack 1

The original French version of SQL Server Books Online had an incorrect table of contents. The French version of the Database Components SP2 includes a self-extracting file that contains a new French SQL Server Books Online file with a corrected table of contents.

To install the corrected French version of SQL Server Books Online, do the following:
  1. Copy the Sqlbolfr.exe file from the service pack directory to an empty folder on your computer.
  2. Execute Sqlbolfr.exe to extract the Sqlbol.chm file.
  3. Copy this new Sqlbol.chm to the C:\Mssql7\Books directory. This will overwrite the incorrect version of the file.

5.13 SQL Server Clustering White Paper

The "How to Install SQL Server 7.0, Enterprise Edition, on Microsoft Cluster Server: Step by Step Instructions" white paper supplements the clustering information provided in SQL Server Books Online for SQL Server 7.0. For more information about the white paper, see http://www.microsoft.com/sql/prodinfo/previousversions/whitepapers.mspx.

5.14 New Database Option

Introduced in Service Pack 2

Database Components SP2 introduces a new pending upgrade database option to support functionality that will be introduced in a future version of SQL Server. Full instructions for using this option will be included in the documentation of the future version of SQL Server.

When the pending upgrade option is set to TRUE, users cannot create indexes or statistics in the database. Attempts to create indexes or statistics receive the new error message 1931:
Create index/Create statistics is disallowed when the database has pending upgrade enabled.
However, if the pending upgrade option is set to TRUE while a clustered index is being created and there are no existing nonclustered indexes, the index creation will succeed.

On the other hand, if nonclustered indexes already exist when the creation of clustered index is initiated, and the pending upgrade option is set to TRUE while index creation is going on, the index creation may fail, rolling back the whole operation.

The pending upgrade option should always be set to FALSE for any SQL Server 7.0 system that is not inter-operating with the future version of SQL Server:
sp_dboption 'database_name', 'pending upgrade', 'FALSE'
				
sp_dboption reports the status of the pending upgrade option if the stored procedure is called with no parameters, or with only the dbname parameter, for example:
sp_dboption 'Northwind'
				
The DATABASEPROPERTY function supports a new IsPendingUpgrade property to report the status of the pending upgrade database option, for example:
DATABASEPROPERTY(Northwind, IsPendingUpgrade)
				
IsPendingUpgrade returns 0 if pending upgrade is FALSE, 1 if pending upgrade is TRUE.

5.15 New sqlservr Startup Option

Introduced in Service Pack 2

Database Components SP2 introduces a new -g startup switch for the sqlservr command prompt utility:
-g memory_to_reserve
Specifies an integer number of megabytes of memory SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. The memory pool is the area used by SQL Server for loading items such as extended stored procedure .dll files, the OLE DB providers referenced by distributed queries, and OLE Automation objects referenced in Transact-SQL statements. For more information about SQL Server memory management, see the "Memory Architecture" topic in the SQL Server 7.0 Books Online.

The default value for this option is 128 (MB). If this option is not specified, the default value is suitable for a wide range of run-time environments. Using this option may help tune memory allocation, but only when physical memory on the server exceeds 2 GB for the SQL Server Desktop or Standard editions, or 3 GB for SQL Server Enterprise Edition. Configurations with less physical memory will not benefit from using this option.

Use of this option may be appropriate in large memory configurations in which the memory usage requirements of SQL Server are atypical and the virtual address space of the SQL Server process is totally in use. Incorrect use of this option can lead to conditions under which SQL Server may not start or may encounter run-time errors.

You should use the default for the -g parameter unless you see the following warning in the SQL Server error log:
Warning: Clearing procedure cache to free contiguous memory.
This message indicates that SQL Server may be trying to free parts of the SQL Server memory pool to find space for items such as extended stored procedure .dll files or OLE Automation objects. In this case, consider increasing the amount of memory reserved by the -g switch. Using a lower value than the default will increase the amount of memory available to the buffer pool and thread stacks, which may, in turn, provide some performance benefit to memory intensive workloads in systems that do not use many extended stored procedures, distributed queries, or OLE Automation objects.

5.16 Repository Changes

This section documents the changes introduced in the Repository by the service pack.

5.16.1 Importing from DB2 and Informix Databases

When using OLE DB Scanner to import from DB2 or Informix databases, Microsoft Repository now creates tables for schema and type information. This functionality applies to databases using the Microsoft ODBC Driver for DB2 and the Intersolv Informix ODBC Driver, the Intersolv OLE DB Driver for ODBC, and the Intersolv OLE DB Provider for Informix.

5.16.2 Improved Retrieval of Versioned Objects

You can now retrieve the most recent object version in all cases, even when the version sequence includes a deleted version. Previously, if you deleted an object version and then created a new object version, attempting to retrieve the most recent version produced an error.

Properties

Article ID: 254555 - Last Review: October 26, 2013 - Revision: 5.0
Applies to
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbfix kbinfo kbreadme KB254555

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com