SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Service Pack 1 for SQL Server 2000 Released

By Brian Knight,

Wow, here we go again!  For all those people that have been saying "I never install a Microsoft product until the first service pack" your time has come. Yes, without much fanfare Microsoft has released its much anticipated SQL Server 2000 service pack 1. What does this mean to you?  In my 6 year career as a DBA, this is on of the largest service packs I've seen as far as sheer numbers of fixes. There are over 200 bug fixes for SQL Server 2000 and over 50 for Analysis Services. Oh yeah, let's not forget the 20 fixes addressed in the MDAC 2.6.1 install that ships with this service pack. 

After reviewing the beta of this service pack and reviewing the bug fix list, this is a definite install for anyone running SQL Server 2000. I have been anxiously awaiting this service pack since I first installed SQL Server 2000 and noticed some small bugs that got under my skin. Some of the bugs were a little more severe when dealing with multi-processor machines. This service pack addressed all the issues I had with the base install of SQL Server 2000.

What Machines Do I Install SP1 On?

The service pack should be installed on every server and client machines administrating your SQL Servers. If you have a web application or any other application hitting your SQL Server, make sure you install the MDAC 2.6.1 upgrade at a minimal on the client or webserver. You can install the minimal requirements for MDAC 2.6.1 by running \x86\other\sqlredis.exe. This will also install a number of other DLL files on the client machine to fix DTS problems among other things. The sqlredis.exe is redistributable to your clients and the license terms and conditions for its use can be found in the redist.txt file in the root folder of the service pack.  By running this, you also update:

  • PivotTable Service
  • Decision Support Objects (DSO) 
  • English Query
  • Repository
  • Replication
  • Data Transformation Services (DTS) 
  • Distributed Management Objects (DMO)
  • SQL Namespace (SQLNS)
  • OSQL
  • DB-Library
  • Jet 4.0
  • Jet OLE DB 4.0
  • ODBC Driver for Desktop Databases
  • SQL Server 2000 Network Libraries
  • SQL ODBC drivers
  • SQL OLEDB provider
  • SQL XML provider

Before Installation

Before you can install the service pack, go to  http://www.microsoft.com/sql/downloads/2000/sp1.asp to download the appropriate files. The service pack comes in three flavors: SQL Server 2000, Desktop Engine, and Analysis Services. The sql2ksp1.exe file is what most people will use to update SQL Server and it will work on any edition of SQL Server except the Desktop Engine. 

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:

  • 8.00.194 - Base SQL Server 2000
  • 8.00.384 - Service Pack 1 for SQL Server

To determine the version of Analysis Services you're running, simply open Analysis Manager and select About Analysis Services under the Help menu. Here are a list of Analysis Manager's versions: 

  • 8.0.194 Base Analysis Services
  • 8.0.382 Analysis Services SP1

Before installing, extract the setup file to a directory without any spaces and backup your databases. If you're installing the Analysis Services service pack, backup all the cubes as well as your registry. Proper backups are very important because rolling back is NOT easy. I will cover rolling the system back in a moment, but I can not emphasize your backups enough here. 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. This would include the Microsoft Component Services, Microsoft Message Queuing, and Microsoft COM Transaction Integrator services if you have these on your machine. Also make sure that your control panel is closed.

One note to keep in mind before installing is the existence of any hot fixes that you may have applied. If you have applied any hot fixes, ensure that those were wrapped into this service pack. Otherwise, you risk overwriting the critical  DLL file(s) that fixed one of your problems. Most of the SQL Server executables and DLL files are replaced during this service pack install and you could cause regressions by not being careful. 


Installation of the service pack should be easy. If you are performing multiple installs, I recommend creating an unattended installation script, which allows you to install it rapidly and without interaction. After you extract the files, you can run the setup file called setupsql.exe in the \x86\setup folder.

In the first few screens you'll be asked which instance you'd like to install the service pack on and if you'd like to install it on a virtual server. By these two questions, you can see some major upgrades to SQL Server 2000. First, the service pack is instance aware. More importantly, the service pack is cluster aware. Those that have installed SQL Server service packs in the past know what a blessing this is. If you install the service pack on a clustered server, make sure that all the nodes participating in the cluster are online. Second, make sure you install the service pack on the server that owns the SQL Server resources at the time of install. A reboot may be required so prepare a maintenance window. Since the service pack is cluster aware, you'll only have to install it once on each virtual server in your cluster. Active/Active clusters will require two installations since there are two instances running. As you install the service pack, the binaries are copied to each instance automatically for you.

Next, you will be required to provide a sysadmin account to the server if you are using Windows Authentication. If you'd like to use SQL Server authentication, you can only provide the sa account password. On Windows 98 and ME machines, you'll only have SQL Server authentication available.  If SQL Server is not started already, it will be started at this point.

The first step is the MDAC upgrade, which takes only a brief moment. The next step is where the setup program files are copied. Finally, the longest step is where various scripts are run to upgrade your system catalog. This is why it is so important to have a backup. If the setup breaks anywhere in the installation, your SQL Server may be left in an unstable state. This is because your system catalog may only be half upgraded or only some of the files may have been copied. If a problem persist with the installation, do not leave the server in this state.  Instead, you must roll back the service pack using the instructions provided later in this article. After the installation completes, you will be asked to backup your master and msdb databases. This is because these databases have been upgraded during the install. You may be required to reboot the system after the service pack install.  I recommend rebooting even if you're not asked to since you will be ensured that all the services you stopped earlier are correctly started.

Rolling Back

To roll back the service pack you will have to jump through a few hoops. To remove the service pack, you must  first detach your databases using the SP_DETACHDB command. Next, uninstall SQL Server 2000 and then install the base version of SQL Server 2000. Finally, use the SP_ATTACHDB command to attach your database files back to the server.

After you reinstall SQL Server 2000, you must recreate the logins needed for the databases. In some cases, I've seen where you have to run sp_change_users_login to remap the logins back to the users. You'll then need to recreate the scheduled jobs (or restore then msdb database from an earlier backup). Finally redo any replication and changes you've made to the model database. As you can see, it's much easier just to restore from a earlier backup. If you have backups, then reinstall SQL Server and restore you backups.

Summary of Bug Fixes

The service pack has your standard flavors of fixes I see in every service pack. I can't think of a service pack where I didn't see bug fixes for the OLE automation stored procedures (sp_OA sprocs) and this one is no different. Specifically, there are severe issues when creating ADO objects with these OLE automation stored procedures. 

There are a number of memory leak and Access Violation (AV) issues that are fixed. I've noticed some of these issues myself from the year of running SQL Server 2000. One of the bug fixes corrects an issue where incomplete searches will cause a memory leak in the MSSEARCH service. Another bug fix with the service is where the CPU may be pegged at 100% if the BUILTIN\Administrators login is deleted. Most properly secured systems will have this removed. 

If you're using DTS regularly, then this service pack is a must-install. There are a number of issues you were bound to have noticed in the base install. One of these issues is where when you receive a large file using the FTP task in DTS, DTS will spend the time to download the file, then deletes it on the destination after it completes the download. This one bug made me stop using the FTP task in SQL Server 2000. There are additional bugs on SMP machines that will cause AV errors when you run DTSRUN.EXE, which is very common. Keep in mind that in DTS, you must run the service pack on each workstation running DTS. This would include the server and your workstation if you're designing a package remotely.  Along these same lines there are tool fixes and major XML issues resolved in the service pack.

For a full list of bug fixes, see the Microsoft support article.

Summary of Changes

There were some changes made in this service pack to stabilize the product and add additional functionality. In Analysis Services, you now have the ability to add third-party data mining algorithm providers.  I've been looking forward to this for a long time (not that I'm smart enough to develop a third-party provider, but the fact that others smarter than me can now do it). Finally, there are seven new enhancements in replication (mostly Transactional but a few in Merge). I won't delve into those in this article since I could spend too much time there. 


After reviewing this service pack in detail and running the beta for a month in a non-product environment, I must say that this is a definite install. As always, just because it works in my lab and production room, it doesn't mean it will work in yours. Always be careful before installing a vendor's service pack or hot fix as they can lead to regressions. Service pack 1 for SQL Server 2000 can be downloaded at : http://www.microsoft.com/sql/downloads/2000/sp1.asp. Please share your experiences with the service pack in our special Service Pack 1 forum. You can also click on the Your Opinion tab below to share any opinions on this article.

Total article views: 7927 | Views in the last 30 days: 3
Related Articles

Installing Analysis and Integration services On Virtual server

Installing Analysis and Integration services On Virtual server


SQL Server 2005 Analysis Services

SQL Server 2005 Analysis Services


(SSAS) SQL Server Analysis Services Tabular Basics

What is (SSAS) SQL Server Analysis Services Tabular Mode? SQL Server 2012 ships Analysis Services...


SQL Server Analysis Services

SQL Server Analysis services terminates unexpectedly


How to install Analysis Services on a failover cluster?

How to install Analysis Services on a failover cluster?

service packs    
sql server 7