Tis' the season! The SQL Server 7.0 Service Pack season has begun with the announcment that Service Pack 3 has been released. In this article, we'll cover the bug fixes that Microsoft has implemented in this release as well as how to roll back in case a problem occurs. Service Pack 3 had a short beta run with little issues reported but watch out admins-this service pack is a monster. It fixes nearly 190 SQL Server bugs and 15 OLAP issues. It also touches some of the data access components.
Not sure what service pack you're on currently? You can open Query Analyzer and execute the command SELECT @@VERSION to determine this. Below is a list of versions:
- 7.00.623 Original SQL Server 7.0
- 7.00.699 Database components SP1
- 7.00.842 Database components SP2
- 7.00.961 Database components SP3
To determine the version of OLAP Services you're running, simply open OLAP Manager and select About Microsoft SQL Server OLAP Services under the Help menu.
- 7.0.1073 Original OLAP Services release
- 7.0.1295 OLAP Services SP1
- 7.0.1458 OLAP Services SP2
- 7.0.1507 OLAP Services SP3
Service Pack 3 includes all the fixes from the two previous service packs. As in service pack 2, you can install the OLAP Services service pack independently of the SQL Server service pack. You must download the service packs independently on the Microsoft website at http://www.microsoft.com/sql/downloads/sp3.htm. Then select SQL Server from the right column box and select any OS. Like service pack 2, this service pack follows the trend of beefy installs. For both installs (OLAP and SQL Server), you will need 389 MB of hard drive space. For just the SQL Server install, 193 MB is needed.
Before installing, extract the setup to a directory without any spaces and backup your databases. Proper backups are very important because rolling back is NOT easy. If you're installing the service pack in a replicated system, make sure that you install the service pack on the Distributor first, then the Publisher and any Subscribers. Also before you begin the setup, make sure all services related to SQL Server are stopped. Also make sure that your control panel is closed.
Very few additions have been made to this service pack. DTS has undergone some slight structural changes that can be seen in the below chart. The method that DTS uses to save packages has changed slightly if saving a package without a password the following chart applies.
| Service pack | Reads | Writes |
|---|---|---|
| SP1 | SQL 7.0/SP1 | SQL 7.0/SP1 |
| SP2 | SQL 7.0/SP1, SP2 | SP2 |
| SP3 | SQL 7.0/SP1, SP2, SP3/SQL 2000 | SP2, SP3/SQL Server 2000 |
Everything else the service pack provides are bug fixes. Some of the notorious regression errors that came out right after the service pack 2 release have been corrected including the the errors where SQL Server maintenance plans would place databases in single user mode when the "Attempt to Repair Minor Errors" option was selected. More serious errors include many Access Violation (AV) errors.
This service pack wraps into it many hot fixes for many important bugs. For example, there was one security hole in SQL Server 7.0 and still exists in SQL Server 2000 that allows people to write malitious extended stored procedures to use the srv_paraminfo API to gain control of your system. There is a hot fix on the Micorosoft support site for SQL Server 2000 to fix this problem. There are also many memory leak bug fixes in the service pack. One bug fix (Q282175) actually made some queries written in Natural Language Search (NLS) run slower on a 4 processor machine than a 2 processor machine. For a complete list of bug fixes, see http://support.microsoft.com/support/kb/articles/Q274/7/97.ASP.
Rolling Back
To roll back the service pack you will have to jump through a few hoops. You cannot now reinstall Service Pack 2 and be done since modifications are made to your system tables. You will first need to detach your databases using the SP_DETACHDB command. Next, reinstall SQL Server 7.0 and then install the proper service pack. Lastly, use the SP_ATTACHDB command to attach your database files back to the server.
