Article ID: 274798 - View products that this article applies to.
This article was previously published under Q274798
This article has been archived. It is offered "as is" and will no longer be updated.
This article is a copy of the Readme.htm file included in Service Pack 3 (SP3) for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0.
Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0(c) Microsoft Corporation, 2000
1.1 Service Pack Versions
2.0 Downloading and Extracting SP3
2.1 Disk Space Requirements for SP3
2.2 Extracting Database Components SP3
2.3 Extracting OLAP Services SP3
3.0 Service Pack Installation
3.1 Back Up SQL Server Databases
3.2 Back Up OLAP Services Databases
3.3 Ensure the System Databases Have Enough Free Space
3.4 Prepare Cluster Configurations
3.5 Stop Applications Before Running Service Pack 3 Setup
3.6 Install Database Components SP3
3.7 Install OLAP Services SP3
3.7.1 Updating or Installing the Add-in and Task Kits
3.8 Restart Services
3.9 Recluster a Cluster Configuration
3.10 Restart Applications
4.0 Redistributing Database Components SP3 Client Components
5.0 Documentation Notes
5.1 Upgrading Client-Only Computers After Installing SP3
5.2 Upgrading Japanese Database Servers to Windows 2000 After Installing SP3
5.3 Applying SP3 to a Later Version
5.4 Installing SP3 on Windows NT 4.0, Terminal Server Edition
5.5 New Troubleshooting Guide
5.6 SP3 and Replication
5.6.1 Installing SP3 on a Server Providing a Remote Distribution Database for Merge Publications5.7 Change in Cursor Behavior
5.6.2 Upgrading to SP3 a Merge Publisher That Will Still Receive New Subscriptions from the Original Release of SQL Server 7.0
5.6.3 Installing SP3 on a Merge Replication Subscriber
5.6.4 Running SP3 Replication with Earlier Versions of SQL Server
5.6.5 Updating Access 2000 (Jet 4.0) Merge Replication Subscribers
5.6.6 Optimizing Replication Synchronization
5.6.7 Performance Enhancements for Merge Processing of the Initial Snapshot
5.6.8 Merge Metadata Cleanup
5.8 Changes in SQL Server Profiler
5.9 Changes in OLAP Services
5.9.1 Updated OLAP Add-in Kit 1 and DTS Task Kit 15.10 Data Transformation Services (DTS)
5.9.2 Improved Security
5.9.3 The German Version of SQL Server OLAP Services on Alpha
5.9.4 Migrating the Repository of a Remotely Administered OLAP Server
5.9.5 OLAP Services with the Korean Version of Windows NT
5.9.6 OLAP Services License Error After Changing Computer Name or Network Card
5.9.7 Reinstall SP3 After Reinstalling OLAP Services
5.10.1 Exporting to Oracle Databases5.11 SQL Server 7.0 and Microsoft Exchange Server 5.5
5.10.2 Using the DTS Test Feature
5.10.3 Changes to the Execute Package Dialog Box
5.10.4 Data Transformation Services Error Messages
5.10.5 Use of the SET Keyword in Microsoft Visual Basic Scripts
5.10.6 Changes in DTS Packages
5.12 The French Version of SQL Server 7.0 Books Online
5.13 SQL Server Clustering White Paper
5.14 New Database Option
5.15 New sqlservr Startup Option
5.16 Repository Changes
5.16.1 Importing from DB2 and Informix Databases
5.16.2 Improved Retrieval of Versioned Objects
1.0 IntroductionThis release of Service Pack 3 for Microsoft SQL Server 7.0 and MSDE 1.0 is provided in two parts:
Service Pack 3 Setup automatically detects which edition of SQL Server 7.0 or MSDE 1.0 is present on the computer and only upgrades the components present in that edition. The service pack will not attempt to upgrade components present in SQL Server 7.0 Enterprise Edition unless it detects the presence of the Enterprise Edition. When the service pack is applied to a computer running MSDE 1.0, it will not attempt to upgrade components that ship only with SQL Server 7.0.
Service Pack 3 cannot be removed easily due to system table changes the service pack requires for maintenance. To revert to a build before SP3 was installed, you must reinstall SQL Server 7.0 or MSDE 1.0 and, if required, apply the service pack (SP1 or SP2) you were running before you attempted to install SP3. Before you reinstall SQL Server 7.0 or MSDE 1.0, detach your databases on SP3 and reattach them after you reinstall SQL Server 7.0 or MSDE 1.0 and either SP1 or SP2. For information about using sp_attach_db and sp_detach_db, see SQL Server Books Online for SQL Server 7.0. As with any new installation, you must re-create the necessary logins. You must also re-create scheduled tasks dependent on information in the msdb database, as well as any changes you have made in the model database. If you use replication, you must completely reconfigure replication.
At this time, there are no fixes required for the version of Microsoft English Query shipping with SQL Server 7.0.
Because SQL Server service packs are cumulative, Service Pack 3 includes the fixes from all earlier SQL Server 7.0 service packs. Therefore, Service Pack 3 can be applied to an original installation of SQL Server version 7.0 or MSDE 1.0, or to any SQL Server 7.0 or MSDE 1.0 installation to which a service pack has been applied previously.
The list of the fixes contained in this service pack is in Microsoft Knowledge Base article 274797
(http://support.microsoft.com/kb/274797). Each fix listed in 274797 has a link to the Knowledge Base article describing the problem addressed by the fix. These Knowledge Base articles are published at http://support.microsoft.com/search/
(http://support.microsoft.com/search/). Click Search Support, select SQL Server and Specific Article ID Number as the search parameters, and enter 274797. Follow the links to the FIX Knowledge Base articles to see the information about each fix.
1.1 Service Pack VersionsIf 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.
Collapse this tableExpand this table
If you are not sure which edition of SQL Server 7.0 you are running, or if you have connected to MSDE 1.0, the last line of output returned by @@VERSION reports the edition to which you have connected:
If you are not sure which service pack you have installed on an instance of SQL Server 7.0 OLAP Services, you can verify the version in the following manner:
MSDE on Windows NT 4.0 (Build 1381: Service Pack 4) Desktop Edition on Windows NT 4.0 (Build 1381: Service Pack 4) Developer 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)
Collapse this tableExpand this table
2.0 Downloading and Extracting SP3This service pack is distributed in two formats:
If you downloaded SP3, you must extract the files to build the service pack directories before you can run Setup.
NOTE: Several of the files in service packs are system files. Make sure you have the Show All Files option is selected in either Microsoft Windows NT Explorer or Windows Explorer when working with service packs. To enable this option, on the View menu, click Options, and then on the View tab of the Options dialog box, select the option.
2.1 Disk Space Requirements for SP3The following list identifies the disk space required to unpack SP3 on Intel-based and Alpha-based computers if you download the self-extracting executable file to your C: drive and run the extraction there.
Intel-based computers require the following disk space:
Intel-based computers require the following disk space:
2.2 Extracting Database Components SP3To extract the Database Components SP3, place the self-extracting file for your processor architecture into a directory on the computer running SQL Server 7.0 or MSDE 1.0. From that directory, execute the file. The self-extraction program will prompt you for the name of the directory in which you want the service pack files placed. For example, if you are running SQL Server or MSDE on an Intel-based computer and have created a directory named C:\70sp3, copy the file Sql70sp3i.exe to your computer, execute it, and provide the name of the C:\70sp3 directory when prompted.
This will extract the service pack files and place the two Microsoft Systems Management Server files in the C:\70sp3 directory. Additionally, a subdirectory will be created for the hardware platform (Intel or Alpha) where the remainder of the service pack files will be placed.
You can rename the database installation directory after extracting the components; however, you need to make sure that the directory name does not contain space characters.
2.3 Extracting OLAP Services SP3To extract the OLAP Services SP3, create a directory on your computer to hold the OLAP Services SP3. Copy the self-extracting OLAP Services file for your processor architecture into a directory on the computer running OLAP Services. From that directory, execute the file. The self-extraction program will prompt you for the name of the directory in which you want the service pack files placed. For example, if you are running SQL Server on an Alpha-based computer and have created a directory named C:\70sp3olap, copy the file Sql70olapsp3a.exe to your computer, execute it, and provide the name of the C:\70sp3olap directory when prompted. This will extract the service pack files.
3.0 Service Pack InstallationYou may use different series of the installation steps to install the different parts of this service pack:
NOTE: Before you install SP3 on the French version of Windows NT 4.0, read and follow the instructions in Knowledge Base (KB) article 259484
(http://support.microsoft.com/kb/259484). You can access the article from http://support.microsoft.com
(http://support.microsoft.com/). Click Search Support, select SQL Server and Specific Article ID Number as the search parameters, and enter 259484.
3.1 Back Up SQL Server DatabasesAs 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 DatabasesAs a precaution, back up your OLAP Services databases before installing this Service Pack. To back up your OLAP Services databases, make a backup copy of your OLAP Services\Data folder, which is installed by default under the C:\Program Files folder. If you have not migrated your OLAP Services repository to SQL Server, also make a backup copy of the file Msmdrep.mdb, located in the OLAP Services\bin folder. Do not use the Archive and Restore Database Add-in to back up your OLAP Services databases. You can also save your OLAP server registry entries by running regedit and using the Export Registry File item on the Registry menu to export the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server to a backup file.
3.3 Ensure the System Databases Have Enough Free SpaceIf the Autogrow option is on for both the master and msdb databases in the SQL Server or MSDE system on which you apply SP3, you can skip this step. You can verify this in SQL Server 7.0 by connecting to SQL Server from SQL Server Enterprise Manager, right-clicking the icon for the database, and clicking Properties. Verify that the Automatically Grow File check box is selected. You can verify this in MSDE by issuing these SQL statements:
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 ConfigurationsBefore installing Database Components SP3 to SQL Server 7.0, Enterprise Edition in a clustered environment, ensure that the group containing Microsoft Distributed Transaction Coordinator (MS DTC) is owned by the node on which MS DTC was first installed. If the group is not owned by this node, Service Pack 3 Setup will display a MS DTC setup error prompting you to correct the problem and then retry the setup. Correct the problem by moving the group to the node on which MS DTC was first installed.
If SQL Server Enterprise Edition is running in a clustered configuration, it must be unclustered before you can apply Database Components SP3. Before unclustering SQL Server, use the Cluster Administrator to make sure no other cluster resources depend on any of the five SQL Server cluster resources: SQL Network name, SQL IP address, SQL Server, SQL Agent, and SQL VServer. Record any dependencies you remove so that you can restore them after installing the service pack. If a Microsoft Messaging Queue (MSMQ) resource has a dependency on the SQL Network name, you must take the MSMQ resource offline and transfer its dependency to another network name in the same group. If there are no other network names in the group, create a temporary network name and transfer the dependency to that network name. (You can delete this temporary network name after installing SP3.)
To uncluster SQL Server, in the SQL Server 7.0 program group, click Failover Cluster Wizard. When prompted, insert your original SQL Server 7.0 Enterprise Edition CD and follow the instructions displayed by the wizard. Do this on all nodes of the Microsoft Windows NT or Windows 2000 cluster where SQL Server has been clustered. Make sure the Failover Cluster Wizard removes all of the SQL Server resource names and types.
All SQL Server 7.0 instances operating in a cluster must be upgraded to SP3 before they are reclustered.
Additional information about running SQL Server in a clustered environment is available in a white paper. For more information about the white paper, see section 5.13, "SQL Server Clustering White Paper".
3.5 Stop Applications Before Running Service Pack 3 SetupBefore running Service Pack 3 Setup to apply Database Components SP3, stop all currently running services except for the following:
NOTE: If you are installing to computers that are nodes in a Windows NT cluster, make sure that the services are stopped on all nodes in the cluster. If any of the services running are clustered services, those clustered services must be taken offline using the cluster administrator.
If you are running Service Pack 3 Setup to apply OLAP Services SP3, you must not stop the MSSQLServer service if you have migrated your OLAP Services repository to SQL Server.
3.6 Install Database Components SP3Run the Setup.bat script from either of these locations:
The setup process then replaces the existing SQL Server or MSDE files with the service pack files. Setup also runs several .sql script files to update system stored procedures.
The MS DTC installer installs a new version of Microsoft Distributed Transaction Coordinator on your system. In a clustered environment, it does this on all nodes of the Windows NT cluster.
When Setup connects to SQL Server 7.0 or MSDE 1.0 to run the .sql scripts updating system stored procedures, it displays an Authentication Mode dialog box if it detects that the installation is using mixed mode authentication with a blank password for the sa login. This is to give users a chance to address the potential security issue of running a system with a blank password for the sa login. Leaving the sa login password blank provides users with easy administrative access to SQL Server or MSDE; sites should protect their systems by enforcing an sa password or using Windows NT Authentication. The Authentication Mode dialog box does not default to the current settings for the installation. The dialog box defaults are:
Setup places a record of the actions it performed in the Sqlsp.log file in the Temp directory of the computer on which it was run.
3.7 Install OLAP Services SP3To install OLAP Services SP3, run Setup.exe from either:
3.7.1 Updating or Installing the Add-in and Task KitsIntroduced in Service Pack 2
This service pack also includes updates to OLAP Add-in Kit 1 and OLAP Services DTS Task Kit 1. If you are running versions of these kits previous to SP2 and you install SP3, you must install the SP3 versions of the kits as well. To install the updated add-in kit, run Addins.exe. To install the updated task kit, run DTSTasks.exe. These files are in the same locations as Setup.exe, and like Setup.exe, they start an InstallShield dialog box to help you with installation. You can also run these executables to install the Add-in and Task Kits for the first time. If you have already run these executables with SP2, you do not need to do so for SP3 or later service packs.
For additional information, see section 5.9.1, "Updated OLAP Add-in Kit 1 and DTS Task Kit 1".
3.8 Restart ServicesWhen Setup completes, it may prompt you to restart the system. After the restart, or after Setup completes without requesting a restart, use the Services application in Control Panel to make sure the Microsoft Search, MSDTC, MSSQLServer, MSSQLServerOLAPService, and SQLServerAgent services are running. Back up the upgraded master and msdb databases.
3.9 Recluster a Cluster ConfigurationIf you have applied this service pack in a Windows NT cluster configuration, recluster SQL Server. Before reclustering, you must apply this service pack to all SQL Server 7.0 instances that will be included in the cluster.
If in step 3.4 you removed MSMQ dependencies on SQL Server, run Regedt32 to edit the registry key HKEY_LOCAL_MACHINE\Software\Microsoft\MSMQ\Parameters\MachineCache\MQS. If the key has the value 2 (for BSC), 4 (for PSC), or 8 (for PEC), record the current value and change the value to 1. Otherwise, leave the value of the key unchanged.
To recluster SQL Server, in the SQL Server 7.0 program group, click Failover Cluster Wizard. When prompted by the wizard, insert your original SQL Server Enterprise Edition CD and follow the instructions displayed by the wizard. Do this on all nodes of the Windows NT cluster that contain instances of SQL Server that you want to include in the cluster.
If you changed the original value of the MSMQ registry key (HKEY_LOCAL_MACHINE\Software|Microsoft\MSMQ\Parameters\MachineCache\MQS) before using the Failover Cluster Wizard, run Regedt32 to restore the original value of the registry key.
If you removed dependencies on the SQL Server resources and resource types in step 3.4, use the Cluster Administrator to restore the dependencies.
If an MSMQ resource had dependencies on SQL Server and the SQL Network name, use the Cluster Administrator to restore the dependencies. Remove any dependencies on any other network name that you established in step 3.4. If you created a temporary network name and IP address resources in step 3.4, delete them. Take the MSMQ resource online.
3.10 Restart ApplicationsRestart the applications you closed before running Service Pack 3 Setup.
4.0 Redistributing Database Components SP3 Client ComponentsService Pack 3 for SQL Server 7.0 and MSDE 1.0 includes a self-extracting file, Sqlredis.exe, and a version of Mdacredist.txt. These files are in the Database Components SP3. By default, when Sqlredis.exe is executed it:
You can redistribute the Sqlredis.exe file under the same terms and conditions noted in the Mdacredist.txt file that accompanies this service pack.
sqlredis.exe /C:"setupre.exe MDAC=0 -s -SMS"
Documentation NotesThis section covers issues that affect sites running SP3, but are not the result of fixes contained in the service pack.
Changes in behavior introduced by fixes are documented in the FIX Knowledge Base article published for each fix. Knowledge Base article 274797
(http://support.microsoft.com/kb/274797)is a list of fixes contained in the service pack. Each fix is linked to its corresponding Knowledge Base article. You can access the articles from http://support.microsoft.com
(http://support.microsoft.com/). Click Search Support, select SQL Server and Specific Article ID Number as the search parameters, and enter 274797. Follow the links to the FIX Knowledge Base articles to see the information about each fix.
5.1 Upgrading Client-Only Computers After Installing SP3Users sometimes first install only the SQL Server client components on a computer, and then later add a Standard, Enterprise, or Desktop Edition of the database server to the computer by running the Setup program from the SQL Server compact disc. If the client components on the computer have already been upgraded to SP3, the SQL Server Setup program fails with the error:
Use the following procedure to add a Standard, Enterprise, or Desktop Edition of the database server to a client computer running the SP3 client components:
You cannot install a version that is older (7.00.623) than the version on your machine (7.00.958). Uninstall the older version.
5.2 Upgrading Japanese Database Servers to Windows 2000 After Installing SP3If you install the Database Components SP3 on a server running Microsoft Windows NT, or a Windows 95 or Windows 98 operating system and later upgrade to Windows 2000, the Windows 2000 upgrade replaces certain system files involved in sorting Japanese characters with versions of the files that are earlier than the versions required by SP3. If you use Japanese characters in your SQL Server databases, rerun the version of Sqlredis.exe that came with SP3 after you perform the upgrade to Windows 2000. For more information about executing Sqlredis.exe, see section 4.0, "Redistributing Database Components SP3 Client Components".
This issue applies only to servers on which you have databases that contain Japanese characters. You do not have to reapply Sqlredis.exe on client computers or on servers that do not have databases containing Japanese characters.
5.3 Applying SP3 to a Later VersionIf you attempt to upgrade a SQL Server installation whose database engine executable files are the same date or newer than the ones provided with Database Components SP3, Service Pack 3 Setup will stop with the following message:
Usually this indicates that SP3 (or later) has already been applied to the SQL Server instance on the server and an upgrade is not required.
Setup has detected newer SQL Server components than those available in the Service Pack. Setup will not continue.
Possible exceptions to this are customers who have received from their SQL Server support vendor a special SQL Server build that is newer than the SP3 build, and have applied that build to a system that was not first upgraded to SP3. These customers should contact their SQL Server support vendor to request information about the upgrade process.
5.4 Installing SP3 on Windows NT 4.0, Terminal Server EditionThe original release of SQL Server 7.0 is not supported on Windows NT 4.0 Terminal Server Edition. SQL Server 7.0 Service Pack 1 introduced support for running SQL Server on Windows NT Server 4.0, Terminal Server Edition. SQL Server 7.0 Service Pack 2 and Service Pack 3 continue the support for Windows NT Server 4.0, Terminal Server Edition.
SQL Server 7.0 Service Pack 3 requires that Windows NT Server 4.0, Terminal Server Edition, Service Pack 4 or later be applied to a computer running Terminal Server Edition before SQL Server is installed. This service pack is specific to Terminal Server Edition and is separate from the Windows NT Server 4.0 Service Pack 4. For more information about the Terminal Server Edition service pack, see the Microsoft service pack download Web page at http://www.microsoft.com/Downloads/details.aspx?familyid=A78BD641-0687-45BC-AD0F-A866A6CA79C6&displaylang=en
Use these steps to install SQL Server 7.0 and SP3 on Windows NT Server 4.0, Terminal Server Edition:
5.5 New Troubleshooting GuideDatabase Components SP2 introduced a new version of the Troubleshooting Guide section from SQL Server Books Online for SQL Server 7.0. Database Components SP3 also installs this new version of the guide. The new guide expands the information in the original SQL Server Books Online for SQL Server 7.0 with new information from the SQL Server support teams:
5.6 SP3 and ReplicationIt is recommended that you apply this service pack to all SQL Server 7.0 participants in your replication topology: Publisher, Distributor, and Subscribers. This is the recommended sequence for deploying SP3 across the replication topology:
In merge replication, the distribution database is simply used to store agent history. Typically, the distribution database in a merge replication topology resides on the same computer as the published database. However, it is possible to also have a remote distribution database for merge replication at sites that want to centralize agent history logging.
You may not be able to immediately upgrade all the servers in a replication topology, so replication operations are generally unaffected between servers running the original version of SQL Server 7.0 or previous service packs and SP3 participants. The exceptions to this are covered in topics 5.6.1 through 5.6.5.
5.6.1 Installing SP3 on a Server Providing a Remote Distribution Database for Merge PublicationsIf you upgrade a server to SP3 and that server contains a remote distribution database for merge publications, you must also upgrade each of the merge replication Publishers that use that distribution database to SP3. This requirement ensures optimal delivery of data changes from the associated merge replication Publishers by any Merge (Push) Agents running on the Distributor.
5.6.2 Upgrading to SP3 a Merge Publisher That Will Still Receive New Subscriptions From the Original Release of SQL Server 7.0To allow new merge replication Subscribers from servers that have the original release version of SQL Server 7.0 installed, you must add the optional parameter -70Subscribers to each Snapshot Agent job.
This ensures complete initial synchronization of any new SQL Server 7.0 merge Subscribers to which no service packs have been applied.
If a Subscriber running the original version of SQL Server has already received the initial synchronization, this Subscriber will continue to be able to merge data even without this parameter. However, if the subscription is dropped and re-created, or the subscription has to be completely reinitialized, the parameter would be necessary. The preferred solution would be to upgrade the Subscriber to SP3 rather than use the parameter. If you want to be sure, you can add the parameter until you are confident all Subscribers have moved to SP3.
5.6.3 Installing SP3 on a Merge Replication SubscriberIf you upgrade a SQL Server merge replication pull Subscriber to SP3, you must also upgrade each of its associated merge replication Publishers to SP3. This ensures optimal delivery of data changes from the associated merge replication Publishers by any Merge (Pull) Agents running on the Subscriber. This is best accomplished using the Distributor-Publisher-Subscriber deployment sequence presented earlier.
5.6.4 Running SP3 Replication With Earlier Versions of SQL ServerThere are no known differences in the way SP3 works with SQL Server 6.0 or 6.5 Publishers and Subscribers compared to the way the original SQL Server 7.0 works with these earlier versions.
5.6.5 Updating Access 2000 (Jet 4.0) Merge Replication SubscribersFor Jet-based Access 2000 Merge Subscribers to take advantage of improvements in SP3, you must copy the following Database Components SP3 files to your workstations that run Microsoft Access 2000:
NOTE: These steps are not necessary if MSDE 1.0 is serving as the Access 2000 Merge Subscriber. In this case, simply apply Database Components SP3, following the instructions in this Readme.
5.6.6 Optimizing Replication SynchronizationIntroduced in Service Pack 2
Optimizing synchronization during merge replication allows you to store more information at the Publisher instead of transferring that information over the network to the Subscriber. While this option may result in a larger database at the Publisher, it can improve synchronization performance over a slow link. However, more information will be stored at the Publisher and additional storage space will be necessary.
By storing additional information at the Publisher, SQL Server can quickly determine the filtered data that should be sent to a particular Subscriber. When synchronization is optimized, SQL Server creates before image tables at the Publisher that contain information such as the schema, partition information, and generation information to check if changes need to propagated to other Subscribers. These before image tables allow SQL Server to determine how to partition data and what Subscribers need new generations of data.
For example, if a sales organization partitions and distributes data based on sales territories, and the publication is enabled to optimize synchronization, the information about how data is partitioned would be stored in before image tables at the Publisher. If sales territories shift and data needs to be repartitioned to multiple Subscribers, it will be a faster process to update and redistribute the data because the information about how data is currently partitioned is already at the Publisher.
NOTE: To minimize the amount of data sent over the network, execute the sp_addmergepublication system stored procedure and set the @keep_partition_changes parameter to 'true'.
5.6.7 Performance Enhancements for Merge Processing of the Initial SnapshotIntroduced in Service Pack 2
The process of generating the initial snapshot has been improved for merge publications that have static or join filters.
During snapshot generation, the Snapshot Agent creates a bulk copy file for the MSmerge_contents table. Microsoft SQL Server 7.0 Service Pack 3 provides faster performance when static or join filters are used because it copies only the rows that are related to the tables in the publication based on the filters used.
In releases prior to Service Pack 2, the Snapshot Agent generated a bulk copy file for the MSmerge_contents table, and the file included all the rows from MSmerge_contents for a published table regardless of the filter criteria used. High volumes of update activity on the publishing table causes the MSmerge_contents table to grow, which slows performance when generating the initial snapshot.
With SQL Server Service Pack 2 or later, the .bcp file generated is smaller (because rows are copied only if they relate to the partition of data being published), so the performance of the Merge Agent when applying the snapshot to Subscribers has also improved.
This applies only to merge publications with static or join filters because the MSmerge_contents table is not bulk copied when snapshots are generated for merge publications with dynamic filters.
For example, a merge publication includes the Customers, Orders, and Order Details columns, and the publication is filtered to publish only the customers in the Northwest region. When the snapshot is generated, only rows in MSmerge_contents that are relevant to the data in the filtered partition of data to be published are bulk copied. In releases prior to the Service Pack 2 release, all rows from the MSMerge_contents table were bulk copied.
5.6.8 Merge Meta Data CleanupIntroduced 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:
5.7 Change in Cursor BehaviorIntroduced 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:
5.8 Changes in SQL Server ProfilerThe connection id data column in SQL Server Profiler will not be supported in a future version of SQL Server. It is recommended that you use the SPID data column instead.
5.9 Changes in OLAP ServicesThis section documents the changes in OLAP Services that are included in OLAP Services SP3.
5.9.1 Updated OLAP Add-in Kit 1 and DTS Task Kit 1Introduced in Service Pack 2
This service pack includes updates for the add-in and task kits, which must be updated if you are running versions of these kits previous to SP2 and you install OLAP Services SP2 or later. To install the updated add-in kit, run Addins.exe. To install the updated task kit, run DTSTasks.exe. These files are included in the \Msolap\Install directory. The add-ins are available only for Intel-based systems. They are not available for the Alpha system. Information about these updates is included in the Readme files for the kits. DTSTaskReadMe.txt and OLAPAddinReadMe.txt are installed by default to C:\Program Files\OLAP Services. For more information, see section 3.7.1, "Updating or Installing the Add-in and Task Kits".
5.9.2 Improved SecurityIntroduced in Service Pack 1
OLAP Services SP3 includes enhanced security for OLAP Services. Cell-level security is now supported. For information about using cell-level security, see Knowledge Base (KB) article 259348
(http://support.microsoft.com/kb/259348). The KB article is published at http://support.microsoft.com/search/
(http://support.microsoft.com/search/). Click Search Support, select SQL Server and Specific Article ID Number as the search parameters, and type 259348.
If OLAP Services SP3 is applied to a server, but not to a client accessing the server, the client will not benefit from the OLAP Services SP3 fixes and will not be able to use the cell-level security introduced in OLAP Services SP1.
5.9.3 The German Version of SQL Server OLAP Services on AlphaIntroduced in Service Pack 1
Setup prevents the installation of the German version of SQL Server 7.0 OLAP Services on Alpha platforms because this is not a supported configuration. You can use the English version of OLAP Services SP3 on Alpha computers running German-version software.
5.9.4 Migrating the Repository of a Remotely Administered OLAP ServerIntroduced in Service Pack 1
After you install OLAP Services SP1, SP2, or SP3 on a server, you can migrate the repository using the instance of OLAP Manager on the server. You cannot use OLAP Manager from another computer remotely administering the server to perform this task.
5.9.5 OLAP Services with the Korean Version of Windows NTIntroduced in Service Pack 1
If you use SQL Server 7.0 OLAP Services on a server running the Korean version of Windows NT, you may not be able to connect to your OLAP server if you install OLAP Services SP1, SP2, or SP3, or separately install the database components MDAC 2.1 SP1 or later. SQL Server 7.0 SP1, SP2, and SP3 also install these database components. After you install any of these services packs on the server running OLAP Services, you must run Olaprepair.exe for x86 systems or Olaprepaira.exe for Alpha systems on the server to be able to connect to your OLAP server. Olaprepair.exe or Olaprepaira.exe is a utility in the English version of OLAP Services SP3. Running this utility may require you to restart your server twice.
NOTE: If you have previously run Olaprepair.exe, you do not have to run it again.
5.9.6 OLAP Services License Error after Changing Computer Name or Network CardIf you change the computer name or replace the network card in the computer, you will receive an error regarding the license when you attempt to start OLAP Services. To resolve this error, you must reinstall OLAP Services 7.0 and then reinstall SP3. It is recommended that you back up your data before reinstalling OLAP Services or SP3.
5.9.7 Reinstall SP3 After Reinstalling OLAP ServicesIf it is necessary to reinstall OLAP Services from the SQL Server 7.0 CD-ROM, you must then reinstall SP3 to update the installation.
5.10 Data Transformation ServicesThis section documents the changes introduced in Data Transformation Services (DTS) by the service pack.
5.10.1 Exporting to Oracle DatabasesIntroduced in Service Pack 1
When exporting to Oracle using the DTS Export Wizard, if you create the destination tables using DTS, DTS will create the case-sensitive tables using quotation marks around the owner and table names. This is required to support owner or table names that are keywords or contain spaces. If your names are not keywords and do not contain spaces, and you want to remove the quotation marks, delete them in the DTS Copy Tables dialog box.
5.10.2 Using the DTS Test FeatureIntroduced 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:
5.10.3 Changes to the Execute Package Dialog BoxIntroduced in Service Pack 1
When you execute a DTS package in the DTS Import Wizard, DTS Export Wizard, or DTS Designer, extra columns have been added to the Execute Package dialog box to display start time, end time, and duration. These new columns are not available if the package is run with a user password.
In addition, transfer notification information is provided during package execution.
5.10.4 Data Transformation Services Error MessagesIntroduced in Service Pack 1
DTS Error messages have been improved. The error messages now supply OLE DB provider-specific information and information about the operation that failed.
5.10.5 Use of the SET Keyword in Microsoft Visual Basic ScriptsIntroduced 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:
5.10.6 Changes in DTS PackagesIntroduced in Service Pack 2 and Service Pack 3
SP2 and SP3/SQL Server 2000 introduced changes to the internal format that DTS uses to save packages. When the package is saved to file or SQL Server without a password, the following formats are supported for the different service pack versions.
Collapse this tableExpand this table
5.11 SQL Server 7.0 and Exchange 5.5If 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:
This article is continued in the following Microsoft Knowledge Base article:
(http://support.microsoft.com/kb/283040)INF: Readme.txt for SQL Server 7.0 Service Pack 3 (Continued)