Description of Service Pack 4 for SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

Introduction

This release of Microsoft SQL Server 7.0 Service Pack 4 (SP4) and Microsoft Data Engine (MSDE) 1.0 SP4 updates the database components of a SQL Server 7.0 or MSDE 1.0 installation. These include the following components:

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

SQL Server 7.0 SP4 can be applied to computers that are running either the SQL Server 7.0 data engine or MSDE 1.0. The Service Pack 4 Setup program automatically detects the edition of SQL Server 7.0 or MSDE 1.0 that is on the computer and only upgrades the components that are in that edition. The Setup program does not try to upgrade components that are in SQL Server 7.0 Enterprise Edition unless the Setup program detects the presence of the Enterprise Edition. When the service pack is applied to a computer that is running MSDE 1.0, it does not try to upgrade components that are only included with SQL Server 7.0. When the service pack is applied to a computer that is only running the database client components, it does not try to upgrade any database server components.

Because SQL Server database component service packs are cumulative, SP4 includes the fixes from all earlier SQL Server 7.0 service packs. Therefore, SP4 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.

SQL Server 7.0 SP4 does not support computers that are based on the Alpha CPU chip. SQL Server 7.0 Service Pacl 3 (SP3) is the last SQL Server 7.0 service pack that supports Alpha-based computers.

For additional information about the fixes that are contained in SQL Server 7.0 Service Pack 4, click the following article number to view the article in the Microsoft Knowledge Base: For additional information about information about SQL Server 7.0 Service Pack 4 that was not included in this article, click the following article number to view the article in the Microsoft Knowledge Base:

Service pack versions

If you are not sure the service pack that you have installed on an instance of the SQL Server 7.0 database engine or MSDE 1.0, verify the version by running a SELECT @@VERSION command from osql, SQL Query Analyzer, or isql. The following table shows the relationship between the version string that is reported by the @@VERSION command and the SQL Server 7.0 and MSDE 1.0 service packs: If you are not sure the edition of SQL Server 7.0 that you are running, or if you have connected to MSDE 1.0, the last line of output that is returned by the @@VERSION command reports the edition to which you have connected. For example, the output may be similar to the following:Recent hotfixes

If you received a hotfix after February 20, 2002, the hotfix is not likely to be included in SP4. Contact your primary support provider about a hotfix update for SP4.

Removing SP4

You cannnot remove SP4 easily because of system table changes that the service pack requires for maintenance. To revert to a build from before SP4 was installed, you must remove and then reinstall SQL Server 7.0 or MSDE 1.0. Then, if it is required, you must apply the service pack (SP1, SP2, or SP3) that you were running before you installed SP4. Before you reinstall SQL Server 7.0 or MSDE 1.0, detach your databases on SP4. Then, reattach them after you reinstall SQL Server 7.0 or MSDE 1.0 and either SP1, SP2, or SP3. For information about using the sp_attach_db and sp_detach_db procedures, see SQL Server Books Online for SQL Server 7.0. As with any new installation, you must re-create the required logins. You must also re-create scheduled tasks that depend on information in the msdb database, in addition to any changes you have made in the model database. If you use replication, you must reconfigure replication.

Downloading and extracting SP4

This service pack is distributed in the following two formats:

• On the SP4 CD
• In a self-extracting file that is named Sql70sp4.exe that can be downloaded from the Internet

Use the Sql70sp4.exe self-extracting file to build a set of folders and files on your computer that are the same as the folders and files that are on the SP4 CD. Then, the setup process is the same for both the SP4 CD and the extracted SP4 files.

If you download SP4, you must extract the files to build the service pack folders before you can run the Setup program.

Note Several of the files that are in service packs are system files. Make sure that the Show all files option is set in either Microsoft Windows NT Explorer or Windows Explorer when you are working with service packs. To enable this option, follow these steps:

1. Start Windows Explorer or Windows NT Explorer.
2. On the View menu, click Options.
3. On the View tab, click to select Show all files.

Disk space requirements for SP4

You must have 231 MB of disk space to unpack SP4 if you download the self-extracting executable file to drive C on your computer and then you run the extraction there.

If you have insufficient disk space to download SP4 to drive C on your computer, you can save disk space by downloading the SP4 ZIP file to a secondary drive. Then, you can extract the ZIP file to drive C. This method requires 188 MB on drive C and 43 MB on the secondary drive.

Extracting Database Components SP4

To extract Database Components SP4, copy the self-extracting file to a folder that is on the computer that is running SQL Server 7.0 or MSDE 1.0. Then, run the file. The self-extraction program will prompt you for the name of the folder where you want to put the service pack files. For example, if you are running SQL Server or MSDE and you have created a folder that is named C:\70sp4, copy the Sql70sp4.exe file to your computer, run the file, and then provide the name of the C:\70sp4 folder when you are prompted.

This will extract the service pack files and then copy the two Microsoft Systems Management Server files to the C:\70sp4 folder. The rest of the service pack files will be put in an \X86 subfolder.

You can rename the database installation folder after you extract the components. However, make sure that the folder name does not contain space characters (" ").

Service pack installation

To install SP4, follow these installation instructions. Not all steps are required, depending onto which of the following SQL Server 7.0 configurations you are applying the service pack:

• Database server configuration: Applies to a computer that is running the data engine and its related software from either MSDE 1.0 or any edition of SQL Server 7.0. These computers also contain the database client components.
• Database client-only configuration: Applies to a computer that is running the following:
  • Database client connectivity components such as the OLE DB Provider for SQL Server, the SQL Server ODBC driver, and the client Net-Libraries (from either SQL Server 7.0 or MSDE 1.0).
  • Database client utilities such as SQL Server Enterprise Manager and bcp (SQL Server 7.0 only).
• Failover cluster configuration: Applies to a computer that is running SQL Server 7.0 that is configured in a failover cluster.
• Replication server configuration: Applies to a computer that is running SQL Server 7.0 on which at least one database is part of a replication topology.

For each installation step, the applicable components are listed.

Note The service pack is language specific. Apply the service pack with the same language as the SQL Server component that you are upgrading.

Note Before you install SP4 on the French version of Windows NT 4.0, follow the instructions in the following Microsoft Knowledge Base article:

Before you start an installation

The following applies to all component installations.

The SP4 Setup program does not upgrade user databases except for those user databases that are regularly involved in replication topologies. Databases that are not involved in replication topologies do not have dependencies on SP4. For example:

• You can restore a user database backup from an instance of SQL Server 7.0 or SQL Server 7.0 SP1, SP2, or SP3 to an instance of SQL Server 7.0 SP4.
• You can restore a user database backup from an instance of SQL Server 7.0 SP4 to an instance of SQL Server 7.0 or SQL Server 7.0 SP1, SP2, or SP3.
• You can detach a user database from an instance of SQL Server 7.0 or SQL Server 7.0 SP1, SP2, or SP3 and then attach it to an instance of SQL Server 7.0 SP4.
• You can detach a user database from an instance of SQL Server 7.0 SP4 and then attach it to an instance of SQL Server 7.0 or SQL Server 7.0 SP1, SP2, or SP3.

SP4 Setup does upgrade user databases that are members of a replication topology. Before you install SP4, make sure that replication databases and file groups are writeable and that the user account that is running the Setup program has permission to access the databases. For more information about how to apply SP4 to databases that are involved in replication topologies, see Installing on replicated servers.

Preparing for a Systems Management Server distributed installation

You can also use Microsoft Systems Management Server to install SP4 automatically on multiple computers that are running Windows NT Server. The Smssql.sms file is a package definition file that automates creating a SQL Server package in Systems Management Server. Then, the SQL Server package can be distributed and installed on computers that are running Systems Management Server. The Unattspsms.bat file is a batch file that detects the operating system of the computer and then runs the appropriate version of the Setup program.

Back up SQL Server databases

The following applies to all configurations except the database client-only configuration.

As a precaution, back up all your SQL Server databases. This includes the master, model, and msdb databases. Installing the service pack does not modify user databases, except user databases that are involved in replication, but it does modify the master and msdb databases. Installing the service pack modifies the master, msdb, and model databases. This makes them incompatible with pre-SP4 versions of SQL Server 7.0. These backups are required if you decide to reinstall SQL Server 7.0 without SP4.

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

Make sure the system databases have sufficient free space

The following applies to all configurations except the database client-only configuration.

You can skip this step if the following conditions are true:

• The autogrow option is on for both the master and msdb databases in the SQL Server or MSDE system on which you apply SP4.
• The disk drive that contains these databases has at least 500 KB of free space available for each database that can autogrow.

In SQL Server 7.0, you can verify that the system databases have sufficient free space. To do this, connect to SQL Server from SQL Server Enterprise Manager, right-click the icon for the database, and then click Properties. Verify that the Automatically grow file check box is selected. To verify this in MSDE, issue 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 the msdb database, 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 and msdb databases. If the unallocated space figure in either database is less than 500 KB, increase the size of the database. To do this, follow the instructions in the "Expanding the Database" topic in SQL Server Books Online for SQL Server 7.0.

Prepare cluster configurations

The following applies only to the failover clustering configuration.

Before you install Database Components SP4 to SQL Server 7.0 Enterprise Edition in a clustered environment, make sure that the group that contains the Microsoft Distributed Transaction Coordinator (MSDTC) is owned by the node on which MSDTC was first installed.

If the group is not owned by this node, the Service Pack 4 Setup program displays an MSDTC setup error that prompts you to correct the problem and then to retry the Setup program. Correct the problem by moving the group to the node on which MSDTC was first installed. If SQL Server Enterprise Edition is running in a clustered configuration, it must be unclustered before you can apply Database Components SP4. Before you uncluster SQL Server, use Cluster Administrator to make sure that no other cluster resources depend on any one of the following five SQL Server cluster resources:

• SQL network name
• SQL IP address
• SQL Server
• SQL Server Agent
• SQL VServer

Record any dependencies that you remove so that you can restore them after you install the service pack. If a Microsoft Message Queuing resource has a dependency on the SQL network name, you must take the Message Queuing resource offline. Then, 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. Then, transfer the dependency to that network name. You can delete the temporary network name after you install SP4.

To uncluster SQL Server, follow these steps:

1. In the SQL Server 7.0 program group, click Failover Cluster Wizard.
2. When you are prompted, insert your original SQL Server Enterprise Edition CD, and then follow the instructions that are displayed by the wizard.

Follow these steps on all nodes of the Windows NT or Windows 2000 cluster in which SQL Server has been clustered. Make sure that the Failover Cluster Wizard removes all the SQL Server resource names and types.

All SQL Server 7.0 instances that operate in a cluster must be upgraded to SP4 before they are reclustered.

For more information about running SQL Server in a clustered environment, see SQL Server clustering white paper.

Stop applications before you run the SP4 Setup program

The following applies to all configurations except the database client-only configuration.

Before you run the SP4 Setup program to apply Database Components SP4, shut down the following services and applications:

• The Microsoft Search, MSDTC, MSSQLServer, MSSQLServerOLAPService, and SQLServerAgent services
• Microsoft Transaction Server, Message Queuing, and the Microsoft COM Transaction Integrator (COMTI)
• All applications. This includes Control Panel

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

Also stop SQL Server Service Manager if it is running. To do this, right-click the minimized SQL Server Service Manager icon in the taskbar, and then click Exit.

Install Database Components SP4

The following applies to all configurations.

Run the Setup.bat batch file from either of the following locations:

• The folder that contains the extracted service pack files from Sql70sp4.exe
• The service pack folder on the SQL Server 7.0 SP4 CD

Note Do not run the Setup.bat batch file by using the Microsoft Terminal Services Client. Remote installation of SP4 by using Terminal Services Client is not supported.

When you run the Setup.bat batch file, an InstallShield dialog box appears 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 sa login. If you choose Windows NT Authentication, you must run the Setup program while you are logged onto Windows by using a Windows login account that is a member of the sysadmin fixed server role of the instance of SQL Server or MSDE that you are upgrading.

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

The MSDTC installer installs a new version of MSDTC on your system. In a clustered environment, it does this on all nodes of the Windows NT cluster.

When the Setup program connects to SQL Server 7.0 or MSDE 1.0 to run the .sql scripts that update system stored procedures, it displays an Authentication Mode dialog box if it detects that the installation is using Mixed Mode with a blank password for the sa login. This gives 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 unauthorized users with easy administrative access to SQL Server or MSDE. To help protect your systems, you should require an sa password or use Windows NT Authentication. By default, the Authentication Mode dialog box does not use the current settings for the installation. The dialog box uses the following defaults:

• By default, on computers that are running Microsoft Windows 95, Microsoft Windows 98, and Microsoft Windows Millennium Edition, the Authentication Mode dialog box uses Mixed Mode. Mixed Mode is the only authentication mode that is supported on these operating systems. Then, the dialog box requests that you specify a password for the sa login. If you specify a password, the Setup program changes the sa login password. If you leave the password blank, the Setup program connects without changing the password.
• By default, on computers that are running Windows NT 4.0 or Microsoft Windows 2000, the Setup program uses Windows NT Authentication. You can use the dialog box to accept the default Windows NT Authentication Mode or to switch the authentication mode to Mixed Mode with an sa login password that is not blank.

Before you change the authentication mode or the password for the sa login, make sure that this will not affect existing applications. For example, if you are running SQL Server on a Windows 2000-based computer and change from Mixed Mode to Windows NT Authentication Mode, existing applications that use SQL Server Authentication cannot connect until they are configured to use Windows NT Authentication. Also, if you change the sa login password, applications or administrative processes that use the old password cannot connect until they are configured to use the new password.

The Setup program records the actions that it performs in the Sqlsp.log file in the Temp folder of the computer on which it is run.

Restart services

The following applies to all configurations except the database client-only configuration.

When the Setup program finishes, you may be prompted to restart the system. After you restart the system, or after the Setup program finishes without requesting a restart, use the Services tool in Control Panel to make sure that the following services are running:

• Microsoft Search
• MSDTC
• MSSQLServer
• MSSQLServerOLAPService
• SQLServerAgent

Back up the upgraded master and msdb databases.

Recluster a cluster configuration

The following applies only to the failover clustering configuration.

If you have applied SP4 in a Windows NT cluster configuration, recluster SQL Server. Before reclustering, you must apply SP4 to all instances of SQL Server 7.0 that are included in the cluster.

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

If you removed Message Queuing dependencies on SQL Server, run Registry Editor to modify the following registry key: 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, follow these steps:

1. In the SQL Server 7.0 program group, click Failover Cluster Wizard.
2. When you are prompted by the wizard, insert your original SQL Server Enterprise Edition CD in the CD drive, and then follow the instructions that the wizard displays.

Follow these steps on all nodes of the Windows NT cluster that contain instances of SQL Server that you want to include in the cluster.

The Message Queuing registry key is . If you changed the original value of this key before you used the Failover Cluster Wizard, run Registry Editor to restore the original value of the registry key. If you removed dependencies on the SQL Server resources and resource types as described in the Prepare cluster configurations section, use Cluster Administrator to restore the dependencies. If a Message Queuing resource had dependencies on SQL Server and the SQL network name, use Cluster Administrator to restore the dependencies. Remove dependencies on any other network name that you established in the Prepare cluster configurations section. If you created IP address resources and a temporary network name in the Prepare cluster configurations section, delete them. Bring the Message Queuing resource online.

Restart applications

The following applies to all configurations.

Restart the applications that you closed before you ran the SP4 Setup program.

Installing on replicated servers

The following applies only to the replication server configuration.

We recommend that you apply this service pack to all the following SQL Server 7.0 participants in your replication topology:

• Publisher
• Distributor
• Subscribers

We recommend that you use the following sequence to deploy SP4 across the replication topology:

1. Distributor, if it is separate from the Publisher
2. Publisher
3. Subscriber

Note Typically, 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 used only to store agent history. Typically, the distribution database in a merge replication topology resides on the same computer as the published database. However, you can also have a remote distribution database for merge replication at sites that centralize agent history logging.

You may not be able to upgrade all the servers in a replication topology immediately. Therefore, replication operations are typically unaffected between servers that are running the original version of SQL Server 7.0 or previous service packs and SP4 participants. Exceptions are covered in the following sections:

• Installing SP4 on a server that provides a remote distribution database for merge publications
• Upgrading a merge Publisher that receives new subscriptions to SP4 from the original release of SQL Server 7.0
• Installing SP4 on a merge replication Subscriber
• Running SP4 replication with earlier versions of SQL Server
• Updating Access 2000 (Jet 4.0) merge replication Subscribers

Installing SP4 on a server that provides a remote distribution database for merge publications

If you upgrade a server to SP4 and that server contains a remote distribution database for merge publications, you must also upgrade each merge replication Publisher that uses that distribution database to SP4. This requirement makes sure that there is optimal delivery of data changes from the associated merge replication Publishers by any Merge (Push) Agents that are running on the Distributor.

Upgrading a merge Publisher that receives new subscriptions to SP4 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 -70Subscribers optional parameter to each Snapshot Agent job.

When you do this, initial synchronization of any new SQL Server 7.0 merge Subscribers to which no service packs have been applied is finished.

If a Subscriber that is running the original version of SQL Server has already received the initial synchronization, this Subscriber continues to be able to merge data even without this parameter. However, if the subscription is dropped and then re-created, or if the subscription must be reinitialized, the parameter is required. The preferred solution is to upgrade the Subscriber to SP4 instead of using the parameter. If you want to make sure, you can add the parameter until you are confident that all Subscribers are upgraded to SP4.

Installing SP4 on a merge replication Subscriber

If you upgrade a SQL Server merge replication Subscriber to SP4, you must also upgrade each of its associated merge replication Publishers to SP4. This optimizes the delivery of data changes from the associated merge replication Publishers by any Merge (Pull) Agents that are running on the Subscriber. This is best accomplished by using the Distributor-Publisher-Subscriber deployment sequence that is described in the Installing on replicated servers section.

Running SP4 replication with earlier versions of SQL Server

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

Updating Access 2000 (Jet 4.0) merge replication Subscribers

For Microsoft Jet-based Microsoft Access 2000 merge Subscribers to take advantage of improvements in SP4, you must copy the following Database Components SP4 files to your workstations that are running 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 of the workstation:Note You do not have to follow these steps if MSDE 1.0 is serving as the Access 2000 merge Subscriber. In this case, apply Database Components SP4.

Redistributing Database Components SP4 client components

SP4 for SQL Server 7.0 and MSDE 1.0 includes a self-extracting file, Sqlredis.exe, and a version of the Mdacredist.txt file. By default, when Sqlredis.exe is executed, it does the following:

1. Sqlredis.exe runs the Mdac_typ.exe file from Microsoft Data Access Components (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 were included with SP1 for SQL Server 7.0 and MSDE 1.0.
2. Sqlredis.exe replaces the SQL Server and MSDE client connectivity components with new versions from Database Components SP4.
3. Sqlredis.exe replaces the Mswstr10.dll, Mswdat10.dll, Oleaut32.dll, Olepro32.dll, and Stdole2.tlb files.

If you want to install only the Database Components SP4 client connectivity components, type the following command at a command prompt: You can redistribute the Sqlredis.exe file under the same terms and conditions that are listed in the Mdacredist.txt file that accompanies this service pack.

Documentation notes

This section discusses issues that affect sites running SP4 but are not caused by fixes that are contained in the service pack.

Changes in behavior that are introduced by fixes are documented in the Microsoft Knowledge Base article for each fix. For additional information about the fixes that are contained in SQL Server 7.0 Service Pack 4, click the following article number to view the article in the Microsoft Knowledge Base:

Upgrading client-only computers after you install SP4

Sometimes, users first install only the SQL Server client components on a computer. Later, they add a Standard, Enterprise, or Desktop Edition of the database server on the computer by running the Setup program from the SQL Server CD. If the client components on the computer are already upgraded to SP4, the SQL Server Setup program fails and you receive the following error message:To add a Standard, Enterprise, or Desktop Edition of the database server to a client computer that is running the SP4 client components, follow these steps:

1. Copy the Bcp.exe file from your original SQL Server 7.0 CD to the client computer. This overwrites the SP4 version of Bcp.exe in the C:\Mssql7\Binn directory.
2. Run the Setup program from your SQL Server 7.0 CD to install the server components.
3. Run the Database Components Service Pack 4 Setup program to upgrade all SQL Server database components on the computer to SP4.

Note To upgrade a client to a Standard or Enterprise Edition of the server, you must purchase an appropriate server license. You can install a Desktop Edition server on client computers that are covered by either a Standard or Enterprise SQL Server Client Access License (CAL).

Upgrading Japanese database servers to Windows 2000 after you install SP4

You may install Database Components SP4 on a computer that is running Windows NT, Windows 95, or Windows 98 and you later upgrade to Windows 2000. When this behavior occurs, the Windows 2000 upgrade replaces certain system files that are involved in sorting Japanese characters with versions of the files that are earlier than the versions that are required by SP4. If you use Japanese characters in your SQL Server databases, rerun the version of Sqlredis.exe that came with SP4 after you upgrade to Windows 2000. For more information about running Sqlredis.exe, see the Redistributing Database Components SP4 client components section.

This 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 that contain Japanese characters.

Applying SP4 to a later version

If you try to upgrade a SQL Server installation with database-engine executable files that are the same version or a later version than the ones that are provided with Database Components SP4, the Service Pack 4 Setup program may quit and you may receive the following error message: Typically, this error message indicates that SP4 or a later version has already been applied to the instance of SQL Server that is on the server and an upgrade is not required.

However, this may not apply to customers who have received a special SQL Server build that is newer than the SP4 build from their SQL Server support vendors and users who have applied that build to a system that was not upgraded to SP4 first. These customers should contact their SQL Server support vendor to request information about the upgrade process.

Installing SP4 on Windows NT 4.0, Terminal Server Edition

The original release of SQL Server 7.0 is not supported on Windows NT 4.0 operating system, Terminal Server Edition. SQL Server 7.0 SP1 introduced support for running SQL Server on Windows NT 4.0, Terminal Server Edition. SQL Server 7.0 SP2, SP3, and SP4 continue to support Windows NT 4.0, Terminal Server Edition.

Note Only follow these steps if you are preparing to install SQL Server 7.0 for the first time on a computer that is running Windows NT 4.0, Terminal Server Edition. If you previously added this support with SQL Server 7.0 SP2 or SP3, you do not have to repeat these steps when you upgrade to SP4.

SQL Server SP4 requires that you apply Windows NT Server 4.0, Terminal Server Edition SP4 to a computer that is running Terminal Server Edition, before SQL Server is installed. This service pack is specific to Terminal Server Edition and is separate from Windows NT Server 4.0 SP4. For additional information about Terminal Server Edition service packs, click the following article number to view the article in the Microsoft Knowledge Base: To install SQL Server 7.0 and SQL Server SP4 on a computer that is running Windows NT 4.0, Terminal Server Edition, follow these steps:

1. Make sure that Windows NT 4.0, Terminal Server Edition SP4 has been applied.
2. Use the Services tool in Control Panel to stop the Terminal Server Licensing Service.
3. Copy the SQL Server ODBC files to the System32, folder on the computer that is running Windows NT 4.0, Terminal Server Edition.
4. Insert the SQL Server 7.0 CD in the computer, and then copy the files from the X86\ODBC folder that is on the CD to the C:\Winnt\System32 folder that is on the server.
5. Use the Services tool in Control Panel to restart the Terminal Server Licensing Service.
6. Install SQL Server 7.0 by using the SQL Server 7.0 CD. You cannot install SQL Server by using a thin client. You must install SQL Server by using the console on the computer that is running Windows NT 4.0, Terminal Server Edition. You cannot use the Autorun Setup program window that appears when you insert the SQL Server CD to install SQL Server on a computer that is running Windows NT 4.0, Terminal Server Edition. Instead, follow these steps:
   1. Close the Autorun window.
   2. In Control Panel, open Add/Remove Programs.
   3. Click Install, and then follow the instructions. Run the Setup program directly from the SQL Server 7.0 CD in the following folder:
      X86\Setup\Setupsql.exe
   Follow the instructions in the SQL Server Setup program. If you want to install a database directly on the computer that is running Windows NT 4.0, Terminal Server Edition, run a full SQL Server setup to install both the client communications components and the database engine. If you only want to give the ability to connect to SQL Server databases on other servers to applications that are running on the same computer with Windows NT 4.0, Terminal Server Edition, only install the client connectivity components. For more information about how to perform a full-server or client-connectivity-only setup, see "Running SQL Server Setup" in SQL Server Books Online for SQL Server 7.0.
7. Apply SQL Server SP4 by following the instructions in this article. You must install SP4 from the console of the computer that is running Windows NT 4.0, Terminal Server Edition. Also, you must run the Service Pack 4 Setup program (Setup.bat) from the Add/Remove Programs tool in Control Panel.

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 SP4 also installs this new version of the guide. The new guide expands on the information that is in the original SQL Server Books Online for SQL Server 7.0 with the following new information from the SQL Server support teams:

• Expanded sets of frequently asked questions (FAQs)
• Additional information and tips from Product Support Engineers that can help customers become proficient with SQL Server 7.0

Database Components SP4 installs a new file that is named Tblshsp2.chm in the \Mssql7\Books folder on your computer. The SP2 Troubleshooting Guide does not physically replace the Troubleshooting Guide that is in the original version of SQL Server Books Online. You cannot browse from SQL Server Books Online to the SP2 Troubleshooting Guide. You must run the new Troubleshooting Guide separately by using one of the following methods:

• Double-click the Tblshsp2.chm file.
• Add a shortcut to your desktop. Then double-click the shortcut. When you add 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.

Replication enhancements

Replication enhancements in SP4 include the following:

• Optimization of replication synchronization
• Performance enhancements for merge replication snapshots
• Enhancements to merge replication metadata cleanup

Optimizing replication synchronization

Introduced in Service Pack 2

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

By storing additional information at the Publisher, SQL Server can quickly determine the filtered data to send 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 determine whether changes must be propagated to other Subscribers. These before image tables let SQL Server determine how to partition data and determine which Subscribers require new generations of data.

For example, a sales organization partitions and distributes data based on sales territories. If the publication is enabled to optimize synchronization, the information about how data is partitioned is stored in before image tables at the Publisher. If sales territories shift and data must be repartitioned to multiple Subscribers, the data is updated and redistributed more quickly because the information about how data is currently partitioned is already at the Publisher.

Note To minimize the amount of data that is sent over the network, run the sp_addmergepublication system stored procedure and set the @keep_partition_changes parameter to true.

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. SQL Server 7.0 SP4 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 that are based on the filters that are used.

In releases that are earlier than SP2, the Snapshot Agent generated a bulk copy file for the MSmerge_contents table, and the file included all rows from MSmerge_contents for a published table regardless of the filter criteria that was used. High volumes of update activity on the publishing table caused the MSmerge_contents table to grow. This behavior slowed performance when generating the initial snapshot.

With SP2 or later versions, the .bcp file that is generated is smaller. The file is smaller because rows are copied only if they relate to the partition of data that is published. Therefore, performance of the Merge Agent when you apply the snapshot to Subscribers has also improved.

This change 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 that have 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 the MSmerge_contents table that are relevant to the data that is in the filtered partition of data that is being published are bulk copied. In releases that are earlier than SP2, all rows from the MSMerge_contents table were bulk copied.

Merge metadata cleanup

Introduced in Service Pack 2

The sp_mergecleanupmetadata system stored procedure lets administrators clean up metadata in the MSmerge_contents and MSmerge_tombstone system tables. Although these tables can expand indefinitely, cleaning up the metadata may improve merge performance. This procedure can save space by reducing the size of these tables at the Publisher and Subscribers.

Caution After you run the sp_mergecleanupmetadata stored procedure at the Publisher, the following events occur for all named subscriptions at Subscribers that have metadata that is stored at the Publisher in the MSmerge_contents and MSmerge_tombstone tables:

• The subscriptions are marked for reinitialization.
• Changes at the Subscriber are lost.
• 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 you run this stored procedure, merge all data from Subscribers with the Publisher to load the Subscriber data changes that must be saved. Snapshot files for all merge publications that are involved at all levels must be regenerated after you run this stored procedure. If you try to merge without running the snapshot first, you are prompted to run the snapshot.

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

By default, the @reinitialize_subscriber parameter of the sp_mergecleanupmetadata stored procedure 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. However, set the parameter to false with caution, 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 the sp_mergecleanupmetadata stored procedure without marking the subscriptions for reinitialization, follow these steps:

1. Synchronize all Subscribers.
2. Stop all updates to the publishing and subscribing databases.
3. Run 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. Run the sp_mergecleanupmetadata system stored procedure. After the stored procedure runs, you can let users update the publishing and subscribing databases again.

To use this stored procedure, the Publisher and all Subscribers must be running SQL Server 7.0 SP4. Only members of the sysadmin group can use this stored procedure. To clean up merge metadata, run the sp_mergecleanupmetadata system stored procedure. This stored procedure cleans all merge metadata in the MSmerge_contents and MSmerge_tombstone system tables.

Note No @tablename parameter exists that limits cleaning to a specified table. This was incorrectly documented for Service Packs 2 and 3.

Changes in the database components

SQL Server 7.0 SP4 introduces several changes to the database components.

New database option

Introduced in Service Pack 2

SQL Server 7.0 SP2 introduced a new pending upgrade database option to support functionality that will be introduced in a future version of SQL Server. You can use this functionality in some migration scenarios when you upgrade SQL Server 7.0 SP2 or a later version to SQL Server 2000 or a later version. 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. A user who tries to create indexes or statistics will receive the following new error message: However, index creation succeeds if the pending upgrade option is set to TRUE while a clustered index is being created and if no nonclustered indexes exist.

Index creation may fail, rolling back the whole operation, if the following conditions are true:

• Nonclustered indexes already exist when the creation of a clustered index is initiated.
• The pending upgrade option is set to TRUE while the index is being created.

The pending upgrade option should always be set to FALSE, as shown in the following line of code, for any SQL Server 7.0 system that is not interoperating with the future version of SQL Server: The sp_dboption system stored procedure reports the status of the pending upgrade option if the stored procedure is called without parameters or with only the database_name parameter, for example: The DATABASEPROPERTY function supports a new IsPendingUpgrade property that is used to report the status of the pending upgrade database option, for example: The IsPendingUpgrade property returns 0 if the pending upgrade option is FALSE, and returns 1 if the pending upgrade option is TRUE.

New sqlservr startup option

Introduced in Service Pack 2

SQL Server 7.0 SP2 introduced a new –g command-line option for the sqlservr utility. For example, you may use the following option: This option specifies the number of MB of memory that SQL Server will leave available for memory allocations in the SQL Server process but outside the SQL Server memory pool. The number that you specify must be an integer. The memory pool is the area that SQL Server uses for loading items, such as extended stored procedure .dll files, the OLE DB providers that are referenced by distributed queries, and Automation objects that are referenced in Transact-SQL statements. For more information about SQL Server memory management, see "Memory Architecture" in SQL Server Books Online for SQL Server 7.0.

By default, the value for this option is 128 MB. This value is appropriate for many run-time environments. Using this option may help tune memory allocation, but only when physical memory on the server exceeds the following values:

• For SQL Server Desktop Edition or SQL Server Standard Edition: 2 GB
• For SQL Server Enterprise Edition: 3 GB

Configurations with less physical memory do not benefit from using this option.

You may want to use this option for large memory configurations where the memory requirements of SQL Server are atypical and where all the memory in the virtual address space of the SQL Server process is being used. Incorrect use of this option may cause conditions where SQL Server may not start or may experience run-time errors.

You should use the default value for the -g command-line switch unless you receive the following warning error message in the SQL Server error log: This error 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 Automation objects. In this case, consider increasing the amount of memory that is reserved by the -g command-line switch. By using a reduced value than the default, the amount of memory that is available to the buffer pool and thread stacks increases. This may provide some performance benefit to memory-intensive workloads in systems that do not use many extended stored procedures, distributed queries, or Automation objects.

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 cursors that meet all 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 behavior so cursors are closed on rollback only if one of the following conditions is true:

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

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

Changes in SQL Profiler

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

SQL Server clustering white paper

The white paper "How to Install SQL Server 7.0, Enterprise Edition, on Microsoft Cluster Server: Step by Step Instructions" supplements the clustering information that is provided in SQL Server Books Online for SQL Server 7.0. For more information about this white paper, visit the following Microsoft Web site:

Data Transformation Services

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

Exporting to Oracle databases

Introduced in Service Pack 1

When you use the DTS Export Wizard to export to Oracle, if you use DTS to create the destination tables, DTS creates the case-sensitive tables by using quotation marks around the owner and table names. This behavior must occur to support owner or table names that are keywords or that contain SPACE characters (" "). If your names are not keywords and do not contain SPACE characters and if you want to remove the quotation marks, delete them in the DTS Copy Tables dialog box.

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 the following two dialog boxes:

• In the Data Transformation Properties dialog box, click the Transformations tab. Then, right-click one of the lines that map the column transformations. The menu that appears contains a test menu item.
• Double-click the ActiveX Script Task Properties dialog box, and then click Test.

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

Changes to the Execute Package dialog box

Introduced in Service Pack 1

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

Additionally, transfer notification information is provided during package execution.

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.

Use of the Set keyword in Microsoft Visual Basic scripts

Introduced in Service Pack 1

Microsoft ActiveX assignment of object references to DTS global variables in Microsoft Visual Basic Scripting Edition (VBScript) scripts now follows standard Visual Basic Set syntax in every case.

Use the Set keyword 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 the Set keyword and the .Value keyword to reassign the object reference. For example:

• 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")

• 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")

Changes in DTS packages

Introduced in Service Pack 2 and Service Pack 3

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

SQL Server Agent proxy account improvements

Introduced in Service Pack 4

In versions of SQL Server that are earlier than SQL Server 7.0 SP4, DTS packages that were stored on the server could not be run under the credentials of the SQL Server Agent Proxy Account unless the proxy account had access to the user TEMP folder for the account under which either the server or the agent was running. The server would be running for jobs run from the xp_cmdshell extended stored procedure. The agent would be running for agent jobs.

Because of this, users often had to adjust the TEMP environment variable for the SQL Server or SQL Agent startup account to point to a folder that was accessible to both the startup and the proxy accounts. For example, C:\TEMP. For SP4, DTS has been enhanced to use the system TEMP folder if the user TEMP folder is unavailable. This change greatly reduces the need for these adjustments.

SQL Server 7.0 and Exchange 5.5

If you run both SQL Server 7.0 and Microsoft Exchange Server version 5.5 on the same computer, you must explicitly configure memory usage in SQL Server. SQL Server will not operate correctly 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 at the same time, you must set the SQL Server 7.0 minimum dynamic memory, or the sp_configure min server memory option, to the amount of memory that is required to support the peak processing load of SQL Server. In this environment, SQL Server will not acquire sufficient memory to reach the maximum dynamic memory setting or the sp_configure max server memory option. Instead, SQL Server will typically run with the amount of memory that is specified in the minimum dynamic memory option. Therefore, the minimum dynamic memory setting must be sufficient to run SQL Server when it is operating at high capacity.

To determine the amount of memory that SQL Server requires, monitor the amount of memory that SQL Server uses when the following conditions are true:

• 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 you are monitoring the memory that is used by SQL Server.
• If your SQL Server applications were developed by a third party, you may have to see the application documentation or vendor to determine the effect of the application on SQL Server memory usage.

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

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 SQL Server 7.0 SP4 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, follow these steps:

1. Copy the Sqlbolfr.exe file from the service pack folder to an empty folder on your computer.
2. Run Sqlbolfr.exe to extract the Sqlbol.chm file.
3. Copy the new Sqlbol.chm file to the C:\Mssql7\Books directory. This will overwrite the incorrect version of the file.

Repository changes

Introduced in Service Pack 2

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

Importing from DB2 and Informix databases

Introduced in Service Pack 2

When it is using the 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 that use 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.

Improved retrieval of versioned objects

Introduced in Service Pack 2

You can now retrieve the most recent object version in every case, even when the version sequence includes a deleted version. Previously, if you deleted an object version and then created a new object version, you would receive an error message if you tried to retrieve the most recent version.