INF: Readme.txt for SQL Server 7.0 SP2 Beta (248999)

MORE INFORMATION

Table of Contents

1.0 Introduction 2.0 Downloading and Extracting SP2 3.0 Service Pack Installation 4.0 Redistributing Database Components and SP2 Client Components
5.0 Documentation Notes

1.0 Introduction

The beta release of 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 the OLAP Services components without upgrading the database components.
• SQL Server 7.0 sites wanting to upgrade both the 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 ship only with SQL Server 7.0.

Once the service pack has been applied to a computer, there is no procedure to remove the service pack. This applies to both Database Components SP2 and OLAP Services SP2.

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

Because SQL Server service packs are cumulative, Service Pack 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.

For the latest year 2000 information specific to SQL Server 7.0, see the Microsoft Year 2000 Resource Center at:

1.1 Service Pack Versions

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

@@VERSION	SQL Server Version
7.00.623	Original SQL Server 7.0 or MSDE 1.0 release
7.00.699	Database Components SP1
7.00.835	Database Components SP2, beta version

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:

   MSDE on Windows NT 4.0 (Build 1381: Service Pack 4)
   Desktop Edition on Windows NT 4.0 (Build 1381: Service Pack 4)
   Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 4)
   Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 4)
				

If you are not sure which service pack you have installed on an instance of SQL Server 7.0 OLAP Services, you can verify the version in the following manner:

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

Help About	SQL Server OLAP Services Version
7.0.1073	Original SQL Server 7.0 OLAP Services release
7.0.1295	OLAP Services SP1
7.0.1325	OLAP Services SP2, beta version

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 is selected in either Microsoft Windows NT Explorer or Windows Explorer when working with service packs. To enable this option, on the View menu, click Options, and then on the View tab of the Options dialog box select the option.

2.1 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 prompts 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 file Sql70sp2i.exe to your computer, execute it, and provide the name of the C:\70sp2 directory when prompted.

This extracts 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.2 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 prompts 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 file Sql70olapsp2a.exe to your computer, execute it, and provide the name of the C:\70sp2olap directory when prompted. This extracts the service pack files.

3.0 Service Pack Installation

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

• To apply the entire service pack (both Database Components 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 file Msmdrep.mdb, located in the OLAP Services\bin folder. Do not use the Archive and Restore Database Add-in to back up your OLAP Services databases. You can also save your OLAP server registry entries by running regedit and using the Export Registry File 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 may skip this step. You can verify this in SQL Server 7.0 by connecting to SQL Server from SQL Server Enterprise Manager, right-clicking the icon for the database, and clicking Properties. Verify that the Automatically Grow File check box is selected. You can verify this in MSDE by issuing 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 master or msdb databases, the databases that cannot autogrow must have at least 500 KB of free space. To verify this, run the sp_spaceused system stored procedure in the context of the master or msdb database. If the unallocated space figure in either database is less than 500 KB, increase the size of the database. Follow the instructions in the Expanding the Database topic in SQL Server Books Online for SQL Server 7.0.

3.4 Prepare Cluster Configurations

Before installing Database Components SP2 to SQL Server 7.0, Enterprise Edition in a clustered environment, ensure that the group containing Microsoft Distributed Transaction Coordinator (DTC) is owned by the node on which DTC was first installed. If the group is not owned by this node, Service Pack 2 Setup displays a 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 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 SQL Server clustering white paper located at:

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 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 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 these locations:

• The Msolap\Install subdirectory in the directory containing the extracted OLAP Services service pack files from either Sql70olapsp2i.exe or Sql70olapsp2a.exe.
• 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 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.

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 shipped with Service Pack 1 for SQL Server 7.0 and MSDE 1.0.
2. Replaces the SQL Server and MSDE client connectivity components with the new versions from the Database Components SP2.
3. Replaces Mswstr10.dll, Mswdat10.dll, Oleaut32.dll, Olepro32.dell, and Stdole2.tlb.

If you want to install only the updated Database Components SP2 client connectivity components, use this 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.

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 error: 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 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 terminate with this message: Usually this indicates that SP2 (or higher) 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.3 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 be applied to a computer running Terminal Server Edition before SQL Server is installed. This service pack is specific to Terminal Server Edition and is separate from the Windows NT Server 4.0 Service Pack 4. For more information about the Terminal Server Edition service pack, see the Microsoft service pack download web page at:
Use these steps to install SQL Server 7.0 and SP2 on Windows NT Server 4.0, Terminal Server Edition:

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

5.4.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.4.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 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.4.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.4.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.4.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 this folder on the system drive in the workstation: \Program Files\Common Files\Microsoft Shared\Database Replication
NOTE: These steps are not necessary if MSDE 1.0 is serving as the Access 2000 Merge Subscriber. In this case, simply apply Database Components SP2 following the instructions in this Readme.

5.4.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.4.7 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.5 Change in Cursor Behavior

Introduced in Service Pack 1

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

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

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

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

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

5.6 Changes in OLAP Services

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

5.6.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.6.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://www.microsoft.com/sql/70/gen/olap.htm.

5.6.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.6.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.6.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.7 Data Transformation Services

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

5.7.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.7.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.7.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.7.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.7.5 Use of the Set Keyword in Microsoft Visual Basic Scripts

Introduced in Service Pack 1

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

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

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

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

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

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

  SET o = DTSGlobalVariables("x").Value
  						

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

  SET o = DTSGlobalVariables("x")
  						

5.7.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.8 SQL Server 7.0 and Exchange 5.5

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

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

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

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

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

5.9 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:

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

5.10 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 provided in SQL Server Books Online for SQL Server 7.0. For more information about the white paper, see http://support.microsoft.com/support/sql/content/70papers/70clstr.asp

5.11 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.12 New sqlservr Startup Option

Introduced in Service Pack 2

Database Components SP2 introduces a new -g startup switch for the sqlservr command prompt utility: The -g switch 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 Memory Architecture in the SQL Server Books Online for SQL Server 7.0.

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 error log: 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.13 Repository Changes

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

5.13.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.13.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.