Article ID: 254555 - View products that this article applies to.
This article was previously published under Q254555
This article has been archived. It is offered "as is" and will no longer be updated.
This article is a copy of the Readme.txt file included in Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0.
Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0March 3, 2000
(c) Microsoft Corporation, 2000
1.1 Service Pack Versions
2.0 Downloading and Extracting SP2
2.1 Disk Space Requirements for SP2
2.2 Extracting Database Components SP2
2.3 Extracting OLAP Services SP2
3.0 Service Pack Installation
3.1 Back Up SQL Server Databases
3.2 Back Up OLAP Services Databases
3.3 Ensure the System Databases Have Enough Free Space
3.4 Prepare Cluster Configurations
3.5 Stop Applications Before Running Service Pack 2 Setup
3.6 Install Database Components SP2
3.7 Install OLAP Services SP2
3.8 Restart Services
3.9 Recluster a Cluster Configuration
3.10 Restart Applications
4.0 Redistributing Database Components SP2 Client Components
5.0 Documentation Notes
5.1 Upgrading Client-Only Computers After Installing SP2
5.2 Upgrading Japanese Database Servers to Windows 2000 After Installing SP2
5.3 Applying SP2 to a Later Version
5.4 Installing SP2 on Windows NT 4.0, Terminal Server Edition
5.5 New Troubleshooting Guide
5.6 SP2 and Replication
5.6.1 Installing SP2 on a Server Providing a Remote Distribution Database for Merge Publications5.7 Change in Cursor Behavior
5.6.2 Upgrading to SP2 a Merge Publisher That Will Still Receive New Subscriptions from the Original Release of SQL Server 7.0
5.6.3 Installing SP2 on a Merge Replication Subscriber
5.6.4 Running SP2 Replication with Earlier Versions of SQL Server
5.6.5 Updating Access 2000 (Jet 4.0) Merge Replication Subscribers
5.6.6 Optimizing Replication Synchronization
5.6.7 Performance Enhancements for Merge Processing of the Initial Snapshot
5.6.8 Merge Metadata Cleanup
5.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 Reinstall SP2 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 IntroductionService Pack 2 for Microsoft SQL Server 7.0 and MSDE 1.0 is provided in two parts:
Service Pack 2 cannot be removed easily, due to system table changes the service pack requires for maintenance. To revert to a build before SP2 was installed, you must reinstall SQL Server 7.0 and, if required, apply SP1 to the installation. Before you reinstall SQL Server 7.0, detach your databases on SP2 and reattach them after you reinstall SQL Server 7.0 and SP1. For information about using the sp_attach_db and sp_detach_db stored procedures, see SQL Server 7.0 Books Online. As with any new installation, you must re-create the necessary logins. You must also re-create scheduled tasks dependent on information in the msdb database, as well as any changes you have made in the model database. If you use replication, you must completely reconfigure replication.
At this time, there are no fixes required for the version of Microsoft English Query included with SQL Server 7.0.
Because SQL Server service packs are cumulative, Service Pack 2 includes the fixes from all earlier SQL Server 7.0 service packs. Therefore, Service Pack 2 can be applied to an original installation of SQL Server version 7.0 or to any SQL Server 7.0 installation to which a service pack has been applied previously.
The list of the fixes contained in this service pack is in Microsoft Knowledge Base article 254369. Each fix listed in 254369 has a link to the Knowledge Base article describing the problem addressed by the fix. These Knowledge Base articles are published at http://support.microsoft.com
(http://support.microsoft.com). Click the Search the Knowledge Base link, and then click the search by Article ID link. In the Enter the Knowledge Base article ID number (e.g. 123456) in the box below and click the green arrow to view it box, type 254369, and then click the green arrow. Follow the links to the FIX Knowledge Base articles to see the information about each fix.
1.1 Service Pack 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 a SELECT @@VERSION statement from osql, SQL Server Query Analyzer, or isql. The following table shows the relationship between the version string reported by @@VERSION and the SQL Server 7.0 and MSDE 1.0 service packs.
If you are not sure which edition of SQL Server 7.0 you are running, or if you have connected to MSDE 1.0, the last line of output returned by @@VERSION reports the edition to which you have connected, as well as the operating system that the server is running.
@@VERSION SQL Server Version ============================== 7.00.623 Original SQL Server 7.0 or MSDE 1.0 release 7.00.699 Database Components SP1 7.00.842 Database Components SP2
If you are not sure which service pack you have installed on an instance of SQL Server 7.0 OLAP Services, you can verify the version by doing the following:
2.0 Downloading and Extracting SP2This service pack is distributed in two formats:
If you downloaded SP2, you must extract the files to build the service pack directories before you can run Setup.
NOTE: Several of the files in service packs are system files. Make sure you have the Show All Files option selected in either Microsoft Windows NT Explorer or Windows Explorer when working with service packs. To enable this option, on the View menu, click Options. Then on the View tab of the Options dialog box, select the Show All Files option.
2.1 Disk Space Requirements for SP2The following list identifies the amount of disk space required to unpack SP2 on Intel-based and Alpha-based computers if you download the self-extracting executable file to drive C and run the extraction there.
Intel-based computers require the following amounts of free disk space:
Intel-based computers require the following disk space:
2.2 Extracting Database Components SP2To extract the Database Components SP2, place the self-extracting file for your processor architecture into a directory on the computer running SQL Server or MSDE. From that directory, execute the file. The self-extraction program will prompt you for the name of the directory in which you want the service pack files placed. For example, if you are running SQL Server or MSDE on an Intel-based computer and have created a directory named C:\70sp2, copy the Sql70sp2i.exe file to your computer, execute it, and provide the name of the C:\70sp2 directory when prompted.
This will extract the service pack files and place the two Microsoft Systems Management Server files in the C:\70sp2 directory. Additionally, a subdirectory will be created for the hardware platform (X86 or Alpha) where the remainder of the service pack files will be placed.
You can rename the database installation directory; however, you need to make sure that the directory name does not contain space characters.
2.3 Extracting OLAP Services SP2To extract the OLAP Services SP2, create a directory on your computer to hold the OLAP Services SP2. Copy the self-extracting OLAP Services file for your processor architecture into a directory on the computer running OLAP Services. From that directory, execute the file. The self-extraction program will prompt you for the name of the directory in which you want the service pack files placed. For example, if you are running SQL Server on an Alpha-based computer and have created a directory named C:\70sp2olap, copy the Sql70olapsp2a.exe file to your computer, execute it, and provide the name of the C:\70sp2olap directory when prompted. This will extract the service pack files.
3.0 Service Pack InstallationYou can use different series of the installation steps to install the different parts of this service pack:
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 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 Msmdrep.mdb file, located in the OLAP Services\Bin folder. Do not use the Archive and Restore Database Add-in option to back up your OLAP Services databases. You can also save your OLAP server registry entries by running regedit and using the Export Registry File command on the Registry menu to export the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server key to a backup file.
3.3 Ensure the System Databases Have Enough Free SpaceIf the autogrow option is on for both the master and msdb databases in the SQL Server or MSDE system on which you apply SP2, you can skip this step. You can verify this in SQL Server 7.0 by connecting to SQL Server from SQL Server Enterprise Manager, right-clicking the icon for the database, and then clicking Properties on the shortcut menu. Verify that the Automatically Grow File check box is selected. You can verify this in MSDE by issuing the following SQL statements:
In the output of these statements, verify that the Growth column is not 0.
If the autogrow option is not on for either the master or msdb databases, the databases that cannot autogrow must have at least 500 KB of free space. To verify this, run the sp_spaceused system stored procedure in the context of the master or msdb database. If the unallocated space figure in either database is less than 500 KB, increase the size of the database. Follow the instructions in the "Expanding the Database" topic of SQL Server 7.0 Books Online.
3.4 Prepare Cluster ConfigurationsBefore installing Database Components SP2 to SQL Server 7.0, Enterprise Edition in a clustered environment, ensure that the group containing Microsoft Distributed Transaction Coordinator (MS DTC) is owned by the node on which MS DTC was first installed. If the group is not owned by this node, Service Pack 2 Setup will display an MS DTC setup error prompting you to correct the problem and then retry the setup. Correct the problem by moving the group to the node on which MS DTC was first installed.
If SQL Server, Enterprise Edition is running in a clustered configuration, it must be unclustered before you can apply Database Components SP2. To uncluster SQL Server, in the SQL Server 7.0 program group, click Failover Cluster Wizard. When prompted, insert your original SQL Server, Enterprise Edition compact disc and follow the instructions displayed by the wizard. Do this on all nodes of the Microsoft Windows NT cluster where SQL Server has been clustered.
All SQL Server 7.0 instances operating in a cluster must be upgraded to SP2 before they are reclustered.
Additional information about running SQL Server in a clustered environment is available in a white paper. For more information about the white paper, see section 5.12, "SQL Server Clustering White Paper".
3.5 Stop Applications Before Running Service Pack 2 SetupBefore running Service Pack 2 Setup to apply Database Components SP2, shut down the following services and applications:
If you are installing in a Windows NT cluster, ensure that these applications and services are stopped on all nodes in the cluster.
Also stop SQL Server Service Manager if it is running. Right-click the minimized SQL Server Service Manager icon on the right side of the taskbar, and then click Exit.
3.6 Install Database Components SP2Run the Setup.bat script from either of the following 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.
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 SP2To install OLAP Services SP2, run Setup.exe from either:
This service pack also includes updates to the OLAP Add-in Kit 1 and OLAP Services DTS Task Kit 1. If you are running earlier versions of these kits and you install SP2, you must install the SP2 versions of the kits as well. To install the updated add-in kit, run Addins.exe. To install the updated task kit, run DTSTasks.exe. These files are in the same locations as Setup.exe, and like Setup.exe, they start an InstallShield dialog box to help you with installation.
3.8 Restart 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. 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 ApplicationsRestart the applications you closed before running Service Pack 2 Setup.
4.0 Redistributing Database Components SP2 Client ComponentsService 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:
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 NotesThis section covers issues that affect sites running SP2, but are not the result of fixes contained in the service pack.
Knowledge Base article 254369 is a list of fixes contained in the service pack. Each fix is linked to its corresponding Knowledge Base article. You can access the articles from http://support.microsoft.com
(http://support.microsoft.com). Click the Search the Knowledge Base link, and then click the search by Article ID link. In the Enter the Knowledge Base article ID number (e.g. 123456) in the box below and click the green arrow to view it box, type 254369, and then click the green arrow. Follow the links to the FIX Knowledge Base articles to see the information about each fix.
5.1 Upgrading Client-Only Computers After Installing SP2Users sometimes first install only the SQL Server client components on a computer, and then later add a Standard, Enterprise, or Desktop Edition of the database server to the computer by running the Setup program from the SQL Server compact disc. If the client components on the computer have already been upgraded to SP2, the SQL Server Setup program fails with the following error:
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:
You cannot install a version that is older (7.00.623) than the version on your machine (7.00.842). Uninstall the older version.
5.2 Upgrading Japanese Database Servers to Windows 2000 After Installing SP2If you install the Database Components SP2 on a server running Microsoft Windows NT, or a Windows 95 or Windows 98 operating system and later upgrade to Windows 2000, the Windows 2000 upgrade replaces certain system files involved in sorting Japanese characters with versions of the files that are earlier than the versions required by SP2. If you use Japanese characters in your SQL Server databases, rerun the version of sqlredis.exe that came with SP2 after you perform the upgrade to Windows 2000. For more information about executing sqlredis.exe, see section 4.0, Redistributing Database Components SP2 Client Components, of this Readme.
This issue applies only to servers on which you have databases that contain Japanese characters. You do not have to reapply sqlredis.exe on client computers or on servers that do not have databases containing Japanese characters.
5.3 Applying SP2 to a Later 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 SP2, Service Pack 2 Setup will stop with the following message:
Usually this indicates that SP2 (or later) has already been applied to the SQL Server instance on the server and an upgrade is not required.
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 SP2 build, and have applied that build to a system that was not first upgraded to SP2. These customers should contact their SQL Server support vendor to request information about the upgrade process.
5.4 Installing SP2 on Windows NT 4.0, Terminal Server EditionMicrosoft SQL Server 7.0 Service Pack 1 introduced support for running SQL Server on Windows NT Server 4.0, Terminal Server Edition. The original release of SQL Server 7.0 is not supported on Windows NT 4.0, Terminal Server Edition. SQL Server 7.0 Service Pack 2 continues the support for Windows NT Server 4.0, Terminal Server Edition.
Service Pack 2 requires that Windows NT Server 4.0, Terminal Server Edition, Service Pack 4 or later be applied to a computer running Terminal Server Edition before SQL Server is installed. This service pack is specific to Terminal Server Edition and is separate from the Windows NT Server 4.0 Service Pack 4 or later.
To install SQL Server 7.0 SP2 on Windows NT Server 4.0, Terminal Server Edition, do the following:
5.5 New Troubleshooting GuideDatabase Components SP2 installs a new version of the Troubleshooting Guide section from SQL Server 7.0 Books Online. The new guide expands the information in the original SQL Server 7.0 Books Online with new information from the SQL Server support teams:
5.6 SP2 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. The following is the recommended sequence for deploying SP2 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 and SP2 participants. The exceptions to this are covered in topics 5.6.1 through 5.6.5.
5.6.1 Installing SP2 on a Server Providing a Remote Distribution Database for Merge PublicationsIf you upgrade a server to SP2 and that server contains a remote distribution database for merge publications, you must also upgrade each of the merge replication Publishers that use that distribution database to SP2. This requirement ensures optimal delivery of data changes from the associated merge replication Publishers by any Merge (Push) Agents running on the Distributor.
5.6.2 Upgrading to SP2 a Merge Publisher That Will Still Receive New Subscriptions From the Original Release of SQL Server 7.0To allow new merge replication of Subscribers from servers that do not have SP2 installed, you must add the optional parameter -70Subscribers to each Snapshot Agent job.
This ensures complete initial synchronization of any new SQL Server 7.0 merge Subscribers that do not have SP2 installed.
If a Subscriber running the original version of SQL Server has already received the initial synchronization, this Subscriber will continue to be able to merge data even without this parameter. However, if the subscription is dropped and re-created, or the subscription has to be completely reinitialized, the parameter would be necessary. The preferred solution would be to upgrade the Subscriber to SP2 rather than use the parameter. If you want to be sure, you can add the parameter until you are confident all Subscribers have moved to SP2.
5.6.3 Installing SP2 on a Merge Replication SubscriberIf you upgrade a SQL Server merge replication pull Subscriber to SP2, you must also upgrade each of its associated merge replication Publishers to SP2. This ensures optimal delivery of data changes from the associated merge replication Publishers by any Merge (Pull) Agents running on the Subscriber. This is best accomplished using the Distributor-Publisher-Subscriber deployment sequence presented earlier.
5.6.4 Running SP2 Replication With Earlier Versions of SQL ServerThere are no known differences in the way SP2 works with SQL Server 6.0 or 6.5 Publishers and Subscribers compared to the way the original SQL Server 7.0 works with these earlier versions.
5.6.5 Updating Access 2000 (Jet 4.0) Merge Replication SubscribersFor 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.dllCopy each file to the following folder on the system drive in the workstation:
\Program Files\Common Files\Microsoft Shared\Database ReplicationNOTE: These steps are not necessary if MSDE 1.0 is serving as the Access 2000 Merge Subscriber. In this case, simply apply Database Components SP2, following the instructions in this Readme.
5.6.6 Optimizing Replication 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 SnapshotThe process of generating the initial snapshot has been improved for merge publications that have static or join filters.
During snapshot generation, the Snapshot Agent creates a bulk copy file for the MSmerge_contents table. Microsoft SQL Server 7.0 Service Pack 2 provides faster performance when static or join filters are used because it copies only the rows that are related to the tables in the publication based on the filters used.
In releases prior to Service Pack 2, the Snapshot Agent generated a bulk copy file for the MSmerge_contents table, and the file included all the rows from MSmerge_contents for a published table regardless of the filter criteria used. High volumes of update activity on the publishing table causes the MSmerge_contents table to grow, which slows performance when generating the initial snapshot.
With SQL Server 7.0 Service Pack 2, the .bcp file generated is smaller (because rows are copied only if they relate to the partition of data being published), so the performance of the Merge Agent when applying the snapshot to Subscribers has also improved.
This applies only to merge publications with static or join filters because the MSmerge_contents table is not bulk copied when snapshots are generated for merge publications with dynamic filters.
For example, a merge publication includes the Customers, Orders, and Order Details columns, and the publication is filtered to publish only the customers in the Northwest region. When the snapshot is generated, only rows in MSmerge_contents that are relevant to the data in the filtered partition of data to be published are bulk copied. In releases prior to the Service Pack 2 release, all rows from the MSMerge_contents table were bulk copied.
5.6.8 Merge Meta Data 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 the following 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 SP2.
5.9.1 Updated OLAP Add-in Kit 1 and DTS Task Kit 1This service pack includes updates for the add-in and task kits, which must be updated if you are running earlier versions of these kits and install OLAP Services SP2. Information about these updates is included in the readmes for the kits. DTSTaskReadMe.txt and OLAPAddinReadMe.txt are installed by default to C:\Program Files\OLAP Services. For more information, see section 3.7, Install OLAP Services SP2.
5.9.2 Improved SecurityIntroduced in Service Pack 1
OLAP Services SP2 includes enhanced security for OLAP Services. Cell-level security is now supported. For information about using cell-level security, see the "Cell-Level Security" white paper, available on http://http://www.microsoft.com/technet/prodtechnol/sql/70/maintain/cellsec.mspx
5.9.3 The German Version of SQL Server OLAP Services on 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 SP2 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 or SP2 on a server, you can migrate the repository using the instance of OLAP Manager on the server. You cannot use OLAP Manager from another computer remotely administering the server to perform this task.
5.9.5 OLAP Services with the Korean Version of Windows 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 or SP2, or separately install the database components MDAC 2.1 SP1 or later. SQL Server 7.0 SP1 and SP2 also install these database components. After you install any of these services packs on the server running OLAP Services, you must run Olaprepair.exe on the server to be able to connect to your OLAP server. Olaprepair.exe is a utility in the English version of OLAP Services SP2. Running this utility may require you to restart your server twice.
NOTE: If you have previously run Olaprepair.exe, you do not have to run it again.
5.9.6 Reinstall SP2 After Reinstalling OLAP ServicesIf it is necessary to reinstall OLAP Services from the SQL Server 7.0 CD-ROM, you must then reinstall SP2 to update the installation. If you change the computer name or replace the network card in the computer, you will receive an error regarding the license when you attempt to start OLAP Services. To resolve this error, you must reinstall OLAP Services and then reinstall SP2. It is recommended that you back up your data before reinstalling OLAP Services or SP2.
5.10 Data Transformation Services (DTS)This section documents the changes introduced in Data Transformation Services (DTS) by the service pack.
5.10.1 Exporting to Oracle 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, as in the examples below.
The following statement assigns a reference to the Connection object to the global variable x:
The following statement sets the variable o to a reference to the Connection object:
The following statement sets the variable o to a reference to a global variable that contains a reference to the Connection object:
5.10.6 Changes in DTS PackagesIntroduced in Service Pack 2
Database Components SP2 changes the internal format that DTS uses to save packages. Computers running SP1 or earlier versions of DTS cannot read a package using the SP2 version of DTS. The SP2 version of DTS can read packages saved by the SP1 or earlier versions of DTS, but if you save the package using the SP2 version of DTS, it is converted to the SP2 format and can no longer be read by SP1 or earlier versions of DTS.
5.11 SQL Server 7.0 and Microsoft Exchange Server 5.5If you run both a SQL Server 7.0 and a Microsoft Exchange Server version 5.5 server on the same computer, you must explicitly configure memory usage in SQL Server . SQL Server will not operate properly if you leave the SQL Server minimum dynamic memory option set at the default value of 0.
To address a known memory issue that occurs when the two products are run simultaneously, you must set the SQL Server 7.0 minimum dynamic memory (or sp_configure min server memory option) to the amount of memory required to support the peak processing load of SQL Server. In this environment, SQL Server will not acquire enough memory to reach the maximum dynamic memory setting (or sp_configure max server memory option). Instead, SQL Server will usually run with the amount of memory specified in the minimum dynamic memory option. The minimum dynamic memory setting must therefore be sufficient to run SQL Server when it is operating at high capacity.
To determine the memory needed by SQL Server, monitor the memory used by SQL Server under the following conditions:
5.12 The French Version of SQL Server Books OnlineIntroduced in Service Pack 1
The original French version of SQL Server Books Online had an incorrect table of contents. The French version of the Database Components SP2 includes a self-extracting file that contains a new French SQL Server Books Online file with a corrected table of contents.
To install the corrected French version of SQL Server Books Online, do the following:
5.13 SQL Server Clustering White PaperThe "How to Install SQL Server 7.0, Enterprise Edition, on Microsoft Cluster Server: Step by Step Instructions" white paper supplements the clustering information provided in SQL Server Books Online for SQL Server 7.0. For more information about the white paper, see http://www.microsoft.com/sql/prodinfo/previousversions/whitepapers.mspx
5.14 New Database OptionIntroduced 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:
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.
Create index/Create statistics is disallowed when the database has pending upgrade enabled.
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 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:
The DATABASEPROPERTY function supports a new IsPendingUpgrade property to report the status of the pending upgrade database option, for example:
IsPendingUpgrade returns 0 if pending upgrade is FALSE, 1 if pending upgrade is TRUE.
5.15 New sqlservr Startup OptionIntroduced in Service Pack 2
Database Components SP2 introduces a new -g startup switch for the sqlservr command prompt utility:
-g memory_to_reserveSpecifies an integer number of megabytes of memory SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. The memory pool is the area used by SQL Server for loading items such as extended stored procedure .dll files, the OLE DB providers referenced by distributed queries, and OLE Automation objects referenced in Transact-SQL statements. For more information about SQL Server memory management, see the "Memory Architecture" topic in the SQL Server 7.0 Books Online.
The default value for this option is 128 (MB). If this option is not specified, the default value is suitable for a wide range of run-time environments. Using this option may help tune memory allocation, but only when physical memory on the server exceeds 2 GB for the SQL Server Desktop or Standard editions, or 3 GB for SQL Server Enterprise Edition. Configurations with less physical memory will not benefit from using this option.
Use of this option may be appropriate in large memory configurations in which the memory usage requirements of SQL Server are atypical and the virtual address space of the SQL Server process is totally in use. Incorrect use of this option can lead to conditions under which SQL Server may not start or may encounter run-time errors.
You should use the default for the -g parameter unless you see the following warning in the SQL Server error log:
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.
Warning: Clearing procedure cache to free contiguous memory.
5.16 Repository ChangesThis section documents the changes introduced in the Repository by the service pack.
5.16.1 Importing from DB2 and Informix DatabasesWhen using OLE DB Scanner to import from DB2 or Informix databases, Microsoft Repository now creates tables for schema and type information. This functionality applies to databases using the Microsoft ODBC Driver for DB2 and the Intersolv Informix ODBC Driver, the Intersolv OLE DB Driver for ODBC, and the Intersolv OLE DB Provider for Informix.
5.16.2 Improved Retrieval of Versioned ObjectsYou 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.