Article ID: 889543
This release of Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0 Service Pack 4 (SP4) provides updates for the database components of a SQL Server 7.0 or MSDE 1.0 installation. This article introduces the following information about SP4:
Because SQL Server database component service packs are cumulative, SP4 includes the fixes from all earlier SQL Server 7.0 service packs. Therefore, SP4 can be applied to an original installation of SQL Server version 7.0 or MSDE 1.0, or to any SQL Server 7.0 or MSDE 1.0 installation to which a service pack has been applied previously.
SQL Server 7.0 SP4 does not support computers that are based on the Alpha CPU chip. SQL Server 7.0 Service Pacl 3 (SP3) is the last SQL Server 7.0 service pack that supports Alpha-based computers.
For additional information about the fixes that are contained in SQL Server 7.0 Service Pack 4, click the following article number to view the article in the Microsoft Knowledge Base:
313980For additional information about information about SQL Server 7.0 Service Pack 4 that was not included in this article, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/313980/ )List of bugs fixed by SQL Server 7.0 service packs
(http://support.microsoft.com/kb/313986/ )SQL Server 7.0 Service Pack 4 Readme.htm additions
If you are not sure the edition of SQL Server 7.0 that you are running, or if you have connected to MSDE 1.0, the last line of output that is returned by the @@VERSION command reports the edition to which you have connected. For example, the output may be similar to the following:
Collapse this tableExpand this table
If you received a hotfix after February 20, 2002, the hotfix is not likely to be included in SP4. Contact your primary support provider about a hotfix update for SP4.
If you download SP4, you must extract the files to build the service pack folders before you can run the Setup program.
Note Several of the files that are in service packs are system files. Make sure that the Show all files option is set in either Microsoft Windows NT Explorer or Windows Explorer when you are working with service packs. To enable this option, follow these steps:
If you have insufficient disk space to download SP4 to drive C on your computer, you can save disk space by downloading the SP4 ZIP file to a secondary drive. Then, you can extract the ZIP file to drive C. This method requires 188 MB on drive C and 43 MB on the secondary drive.
This will extract the service pack files and then copy the two Microsoft Systems Management Server files to the C:\70sp4 folder. The rest of the service pack files will be put in an \X86 subfolder.
You can rename the database installation folder after you extract the components. However, make sure that the folder name does not contain space characters (" ").
Note The service pack is language specific. Apply the service pack with the same language as the SQL Server component that you are upgrading.
Note Before you install SP4 on the French version of Windows NT 4.0, follow the instructions in the following Microsoft Knowledge Base article:
(http://support.microsoft.com/kb/259484/ )CryptEncrypt and CryptDecrypt functions are supported with the French locale on Windows 2000
Before you start an installationThe following applies to all component installations.
The SP4 Setup program does not upgrade user databases except for those user databases that are regularly involved in replication topologies. Databases that are not involved in replication topologies do not have dependencies on SP4. For example:
Preparing for a Systems Management Server distributed installationYou can also use Microsoft Systems Management Server to install SP4 automatically on multiple computers that are running Windows NT Server. The Smssql.sms file is a package definition file that automates creating a SQL Server package in Systems Management Server. Then, the SQL Server package can be distributed and installed on computers that are running Systems Management Server. The Unattspsms.bat file is a batch file that detects the operating system of the computer and then runs the appropriate version of the Setup program.
As a precaution, back up all your SQL Server databases. This includes the master, model, and msdb databases. Installing the service pack does not modify user databases, except user databases that are involved in replication, but it does modify the master and msdb databases. Installing the service pack modifies the master, msdb, and model databases. This makes them incompatible with pre-SP4 versions of SQL Server 7.0. These backups are required if you decide to reinstall SQL Server 7.0 without SP4.
It is also prudent to back up your user databases, although SP4 will perform updates only in user databases that are members of replication topologies.
You can skip this step if the following conditions are true:
If the autogrow option is not on for either the master or the msdb database, the databases that cannot autogrow must have at least 500 KB of free space. To verify this, run the sp_spaceused system stored procedure in the context of the master and msdb databases. If the unallocated space figure in either database is less than 500 KB, increase the size of the database. To do this, follow the instructions in the "Expanding the Database" topic in SQL Server Books Online for SQL Server 7.0.
Before you install Database Components SP4 to SQL Server 7.0 Enterprise Edition in a clustered environment, make sure that the group that contains the Microsoft Distributed Transaction Coordinator (MSDTC) is owned by the node on which MSDTC was first installed.
If the group is not owned by this node, the Service Pack 4 Setup program displays an MSDTC setup error that prompts you to correct the problem and then to retry the Setup program. Correct the problem by moving the group to the node on which MSDTC was first installed. If SQL Server Enterprise Edition is running in a clustered configuration, it must be unclustered before you can apply Database Components SP4. Before you uncluster SQL Server, use Cluster Administrator to make sure that no other cluster resources depend on any one of the following five SQL Server cluster resources:
To uncluster SQL Server, follow these steps:
All SQL Server 7.0 instances that operate in a cluster must be upgraded to SP4 before they are reclustered.
For more information about running SQL Server in a clustered environment, see SQL Server clustering white paper.
Before you run the SP4 Setup program to apply Database Components SP4, shut down the following services and applications:
Also stop SQL Server Service Manager if it is running. To do this, right-click the minimized SQL Server Service Manager icon in the taskbar, and then click Exit.
Run the Setup.bat batch file from either of the following locations:
When you run the Setup.bat batch file, an InstallShield dialog box appears that prompts you for information, such as whether to use SQL Server Authentication or Windows NT Authentication. If you choose SQL Server Authentication, you must supply the Setup program with the password for the sa login. If you choose Windows NT Authentication, you must run the Setup program while you are logged onto Windows by using a Windows login account that is a member of the sysadmin fixed server role of the instance of SQL Server or MSDE that you are upgrading.
Then, the Setup program replaces the existing SQL Server or MSDE files with the service pack files. The Setup program also runs several .sql script files to update system stored procedures.
The MSDTC installer installs a new version of MSDTC on your system. In a clustered environment, it does this on all nodes of the Windows NT cluster.
When the Setup program connects to SQL Server 7.0 or MSDE 1.0 to run the .sql scripts that update system stored procedures, it displays an Authentication Mode dialog box if it detects that the installation is using Mixed Mode with a blank password for the sa login. This gives users a chance to address the potential security issue of running a system with a blank password for the sa login. Leaving the sa login password blank provides unauthorized users with easy administrative access to SQL Server or MSDE. To help protect your systems, you should require an sa password or use Windows NT Authentication. By default, the Authentication Mode dialog box does not use the current settings for the installation. The dialog box uses the following defaults:
The Setup program records the actions that it performs in the Sqlsp.log file in the Temp folder of the computer on which it is run.
When the Setup program finishes, you may be prompted to restart the system. After you restart the system, or after the Setup program finishes without requesting a restart, use the Services tool in Control Panel to make sure that the following services are running:
If you have applied SP4 in a Windows NT cluster configuration, recluster SQL Server. Before reclustering, you must apply SP4 to all instances of SQL Server 7.0 that are included in the cluster.
Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/322756/ )How to back up and restore the registry in Windows
If you removed Message Queuing dependencies on SQL Server, run Registry Editor to modify the following registry key:
If the key has the value 2 (for BSC), 4 (for PSC), or 8 (for PEC), record the current value and change the value to 1. Otherwise, leave the value of the key unchanged.
To recluster SQL Server, follow these steps:
The Message Queuing registry key is
HKEY_LOCAL_MACHINE\Software\Microsoft\MSMQ\Parameters\MachineCache\MQS. If you changed the original value of this key before you used the Failover Cluster Wizard, run Registry Editor to restore the original value of the registry key. If you removed dependencies on the SQL Server resources and resource types as described in the Prepare cluster configurations section, use Cluster Administrator to restore the dependencies. If a Message Queuing resource had dependencies on SQL Server and the SQL network name, use Cluster Administrator to restore the dependencies. Remove dependencies on any other network name that you established in the Prepare cluster configurations section. If you created IP address resources and a temporary network name in the Prepare cluster configurations section, delete them. Bring the Message Queuing resource online.
Restart the applications that you closed before you ran the SP4 Setup program.
We recommend that you apply this service pack to all the following SQL Server 7.0 participants in your replication topology:
In merge replication, the distribution database is used only to store agent history. Typically, the distribution database in a merge replication topology resides on the same computer as the published database. However, you can also have a remote distribution database for merge replication at sites that centralize agent history logging.
You may not be able to upgrade all the servers in a replication topology immediately. Therefore, replication operations are typically unaffected between servers that are running the original version of SQL Server 7.0 or previous service packs and SP4 participants. Exceptions are covered in the following sections:
When you do this, initial synchronization of any new SQL Server 7.0 merge Subscribers to which no service packs have been applied is finished.
If a Subscriber that is running the original version of SQL Server has already received the initial synchronization, this Subscriber continues to be able to merge data even without this parameter. However, if the subscription is dropped and then re-created, or if the subscription must be reinitialized, the parameter is required. The preferred solution is to upgrade the Subscriber to SP4 instead of using the parameter. If you want to make sure, you can add the parameter until you are confident that all Subscribers are upgraded to SP4.
Installing on replicated servers section.
\Program Files\Common Files\Microsoft Shared\Database ReplicationNote You do not have to follow these steps if MSDE 1.0 is serving as the Access 2000 merge Subscriber. In this case, apply Database Components SP4.
sqlredis.exe /C:"setupre.exe MDAC=0 -s -SMS"You can redistribute the Sqlredis.exe file under the same terms and conditions that are listed in the Mdacredist.txt file that accompanies this service pack.
Changes in behavior that are introduced by fixes are documented in the Microsoft Knowledge Base article for each fix. For additional information about the fixes that are contained in SQL Server 7.0 Service Pack 4, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/313980/ )List of bugs fixed by SQL Server 7.0 service packs
To add a Standard, Enterprise, or Desktop Edition of the database server to a client computer that is running the SP4 client components, follow these steps:
You cannot install a version that is older (7.00.623) than the version on your machine (7.00.1063). Uninstall the older version.
Redistributing Database Components SP4 client components section.
This applies only to servers on which you have databases that contain Japanese characters. You do not have to reapply Sqlredis.exe on client computers or on servers that do not have databases that contain Japanese characters.
Typically, this error message indicates that SP4 or a later version has already been applied to the instance of SQL Server that is on the server and an upgrade is not required.
Setup has detected newer SQL Server components than those available in the Service Pack. Setup will not continue.
However, this may not apply to customers who have received a special SQL Server build that is newer than the SP4 build from their SQL Server support vendors and users who have applied that build to a system that was not upgraded to SP4 first. These customers should contact their SQL Server support vendor to request information about the upgrade process.
Note Only follow these steps if you are preparing to install SQL Server 7.0 for the first time on a computer that is running Windows NT 4.0, Terminal Server Edition. If you previously added this support with SQL Server 7.0 SP2 or SP3, you do not have to repeat these steps when you upgrade to SP4.
SQL Server SP4 requires that you apply Windows NT Server 4.0, Terminal Server Edition SP4 to a computer that is running Terminal Server Edition, before SQL Server is installed. This service pack is specific to Terminal Server Edition and is separate from Windows NT Server 4.0 SP4. For additional information about Terminal Server Edition service packs, click the following article number to view the article in the Microsoft Knowledge Base:
152734To install SQL Server 7.0 and SQL Server SP4 on a computer that is running Windows NT 4.0, Terminal Server Edition, follow these steps:
(http://support.microsoft.com/kb/152734/ )How to obtain the latest Windows NT 4.0 service pack
Optimizing synchronization during merge replication lets you store more information at the Publisher instead of transferring that information over the network to the Subscriber. While this option may cause a larger database at the Publisher, it can improve synchronization performance over a slow link. However, more information is stored at the Publisher and additional storage space is required.
By storing additional information at the Publisher, SQL Server can quickly determine the filtered data to send to a particular Subscriber. When synchronization is optimized, SQL Server creates before image tables at the Publisher that contain information such as the schema, partition information, and generation information to determine whether changes must be propagated to other Subscribers. These before image tables let SQL Server determine how to partition data and determine which Subscribers require new generations of data.
For example, a sales organization partitions and distributes data based on sales territories. If the publication is enabled to optimize synchronization, the information about how data is partitioned is stored in before image tables at the Publisher. If sales territories shift and data must be repartitioned to multiple Subscribers, the data is updated and redistributed more quickly because the information about how data is currently partitioned is already at the Publisher.
Note To minimize the amount of data that is sent over the network, run the sp_addmergepublication system stored procedure and set the @keep_partition_changes parameter to true.
The process of generating the initial snapshot has been improved for merge publications that have static or join filters.
During snapshot generation, the Snapshot Agent creates a bulk copy file for the MSmerge_contents table. SQL Server 7.0 SP4 provides faster performance when static or join filters are used because it copies only the rows that are related to the tables in the publication that are based on the filters that are used.
In releases that are earlier than SP2, the Snapshot Agent generated a bulk copy file for the MSmerge_contents table, and the file included all rows from MSmerge_contents for a published table regardless of the filter criteria that was used. High volumes of update activity on the publishing table caused the MSmerge_contents table to grow. This behavior slowed performance when generating the initial snapshot.
With SP2 or later versions, the .bcp file that is generated is smaller. The file is smaller because rows are copied only if they relate to the partition of data that is published. Therefore, performance of the Merge Agent when you apply the snapshot to Subscribers has also improved.
This change applies only to merge publications with static or join filters because the MSmerge_contents table is not bulk copied when snapshots are generated for merge publications that have dynamic filters.
For example, a merge publication includes the Customers, Orders, and Order Details columns, and the publication is filtered to publish only the customers in the Northwest region. When the snapshot is generated, only rows in the MSmerge_contents table that are relevant to the data that is in the filtered partition of data that is being published are bulk copied. In releases that are earlier than SP2, all rows from the MSMerge_contents table were bulk copied.
The sp_mergecleanupmetadata system stored procedure lets administrators clean up metadata in the MSmerge_contents and MSmerge_tombstone system tables. Although these tables can expand indefinitely, cleaning up the metadata may improve merge performance. This procedure can save space by reducing the size of these tables at the Publisher and Subscribers.
Caution After you run the sp_mergecleanupmetadata stored procedure at the Publisher, the following events occur for all named subscriptions at Subscribers that have metadata that is stored at the Publisher in the MSmerge_contents and MSmerge_tombstone tables:
The reinitialization must be initiated at the Subscriber. Before you run this stored procedure, merge all data from Subscribers with the Publisher to load the Subscriber data changes that must be saved. Snapshot files for all merge publications that are involved at all levels must be regenerated after you run this stored procedure. If you try to merge without running the snapshot first, you are prompted to run the snapshot.
The reinitialization does not automatically propagate the merge topology. The administrator must manually reinitialize all subscriptions at every republisher.
By default, the @reinitialize_subscriber parameter of the sp_mergecleanupmetadata stored procedure is set to true, and all subscriptions are marked for reinitialization. If you set the @reinitialize_subscriber parameter to false, the subscriptions are not marked for reinitialization. However, set the parameter to false with caution, because if you choose not to have the subscriptions reinitialized, you must make sure that data at the Publisher and Subscribers is synchronized.
If you want to run the sp_mergecleanupmetadata stored procedure without marking the subscriptions for reinitialization, follow these steps:
Note No @tablename parameter exists that limits cleaning to a specified table. This was incorrectly documented for Service Packs 2 and 3.
SQL Server 7.0 SP2 introduced a new pending upgrade database option to support functionality that will be introduced in a future version of SQL Server. You can use this functionality in some migration scenarios when you upgrade SQL Server 7.0 SP2 or a later version to SQL Server 2000 or a later version. Full instructions for using this option will be included in the documentation of the future version of SQL Server.
When the pending upgrade option is set to TRUE, users cannot create indexes or statistics in the database. A user who tries to create indexes or statistics will receive the following new error message:
However, index creation succeeds if the pending upgrade option is set to TRUE while a clustered index is being created and if no nonclustered indexes exist.
Create index/Create statistics is disallowed when the database has pending upgrade enabled.
Index creation may fail, rolling back the whole operation, if the following conditions are true:
The sp_dboption system stored procedure reports the status of the pending upgrade option if the stored procedure is called without parameters or with only the database_name parameter, for example:
The DATABASEPROPERTY function supports a new IsPendingUpgrade property that is used to report the status of the pending upgrade database option, for example:
The IsPendingUpgrade property returns 0 if the pending upgrade option is FALSE, and returns 1 if the pending upgrade option is TRUE.
SQL Server 7.0 SP2 introduced a new –g command-line option for the sqlservr utility. For example, you may use the following option:
This option specifies the number of MB of memory that SQL Server will leave available for memory allocations in the SQL Server process but outside the SQL Server memory pool. The number that you specify must be an integer. The memory pool is the area that SQL Server uses for loading items, such as extended stored procedure .dll files, the OLE DB providers that are referenced by distributed queries, and Automation objects that are referenced in Transact-SQL statements. For more information about SQL Server memory management, see "Memory Architecture" in SQL Server Books Online for SQL Server 7.0.
By default, the value for this option is 128 MB. This value is appropriate for many run-time environments. Using this option may help tune memory allocation, but only when physical memory on the server exceeds the following values:
You may want to use this option for large memory configurations where the memory requirements of SQL Server are atypical and where all the memory in the virtual address space of the SQL Server process is being used. Incorrect use of this option may cause conditions where SQL Server may not start or may experience run-time errors.
You should use the default value for the -g command-line switch unless you receive the following warning error message in the SQL Server error log:
This error message indicates that SQL Server may be trying to free parts of the SQL Server memory pool to find space for items such as extended stored procedure .dll files or Automation objects. In this case, consider increasing the amount of memory that is reserved by the -g command-line switch. By using a reduced value than the default, the amount of memory that is available to the buffer pool and thread stacks increases. This may provide some performance benefit to memory-intensive workloads in systems that do not use many extended stored procedures, distributed queries, or Automation objects.
Warning: Clearing procedure cache to free contiguous memory.
In the original releases of SQL Server 7.0 and MSDE 1.0, a rollback closes all open cursors except cursors that meet all the following conditions:
When you use the DTS Export Wizard to export to Oracle, if you use DTS to create the destination tables, DTS creates the case-sensitive tables by using quotation marks around the owner and table names. This behavior must occur to support owner or table names that are keywords or that contain SPACE characters (" "). If your names are not keywords and do not contain SPACE characters and if you want to remove the quotation marks, delete them in the DTS Copy Tables dialog box.
The DTS Test feature has improved functionality and now supports global variables and lookups. The DTS user interface has a test option in the following two dialog boxes:
When you execute a DTS package in the DTS Import Wizard, the DTS Export Wizard, or the DTS Designer, additional columns are added to the Execute Package dialog box to display start time, end time, and duration. These new columns are not available if you run the package with a user password.
Additionally, transfer notification information is provided during package execution.
DTS error messages have been improved. The error messages now supply OLE DB provider-specific information and information about the operation that failed.
Microsoft ActiveX assignment of object references to DTS global variables in Microsoft Visual Basic Scripting Edition (VBScript) scripts now follows standard Visual Basic Set syntax in every case.
Use the Set keyword when you want to assign an object reference. Otherwise, the default value of the object will be assigned.
If a global variable contains an object reference, use both the Set keyword and the .Value keyword to reassign the object reference. For example:
SQL Server 7.0 SP2, SQL Server 7.0 SP3, and SQL Server 2000 introduced changes to the internal format that DTS uses to save packages. When a package is saved to a file or SQL Server without a password, the following formats are supported for the different service pack versions.
Collapse this tableExpand this table
In versions of SQL Server that are earlier than SQL Server 7.0 SP4, DTS packages that were stored on the server could not be run under the credentials of the SQL Server Agent Proxy Account unless the proxy account had access to the user TEMP folder for the account under which either the server or the agent was running. The server would be running for jobs run from the xp_cmdshell extended stored procedure. The agent would be running for agent jobs.
Because of this, users often had to adjust the TEMP environment variable for the SQL Server or SQL Agent startup account to point to a folder that was accessible to both the startup and the proxy accounts. For example, C:\TEMP. For SP4, DTS has been enhanced to use the system TEMP folder if the user TEMP folder is unavailable. This change greatly reduces the need for these adjustments.
To address a known memory issue that occurs when the two products are run at the same time, you must set the SQL Server 7.0 minimum dynamic memory, or the sp_configure min server memory option, to the amount of memory that is required to support the peak processing load of SQL Server. In this environment, SQL Server will not acquire sufficient memory to reach the maximum dynamic memory setting or the sp_configure max server memory option. Instead, SQL Server will typically run with the amount of memory that is specified in the minimum dynamic memory option. Therefore, the minimum dynamic memory setting must be sufficient to run SQL Server when it is operating at high capacity.
To determine the amount of memory that SQL Server requires, monitor the amount of memory that SQL Server uses when the following conditions are true:
The original French version of SQL Server Books Online had an incorrect table of contents. The French version of SQL Server 7.0 SP4 includes a self-extracting file that contains a new French SQL Server Books Online file with a corrected table of contents.
To install the corrected French version of SQL Server Books Online, follow these steps:
This section documents the changes that are introduced in the Repository by the service pack.
When it is using the OLE DB Scanner to import from DB2 or Informix databases, Microsoft Repository now creates tables for schema and type information. This functionality applies to databases that use the Microsoft ODBC Driver for DB2 and the Intersolv Informix ODBC Driver, the Intersolv OLE DB Driver for ODBC, and the Intersolv OLE DB Provider for Informix.
You can now retrieve the most recent object version in every case, even when the version sequence includes a deleted version. Previously, if you deleted an object version and then created a new object version, you would receive an error message if you tried to retrieve the most recent version.