Why am I having problems with SQL Server 2000 SP3 / SP3a?

SP4 is now the service pack that is available (see Article #2531. You should install SP4 if you are able. (It includes all of the fixes in SP3/SP3a, as well as several fixes that have been published since then.) If you are not ready for SP4, then read on. 
 
There was a little hiccup in the release of SP3, and SP3a followed shortly thereafter. This raised a lot of questions, such as, "I already have SP3, do I need SP3a?" If you already have SP3, there is no reason to install SP3a. The fixes made to SP3a were in the setup routine, not the actual service pack. There is a slightly newer version of MDAC—but you can download that separately if you wish. (To see what version you currently have, see Article #2057; to download a newer or older version, start at MDAC Download Page.) 
 
So, install SP3a only on machines that don't already have SP3. Having said that, SP3a deals with a lot of the issues you might come across below, so if you have local copies of SP3, replace them with SP3a before making any more deployments. To see the differences between SP3 and SP3a, please see the support webcast in KB #822144. You can also confirm our advice—to stick with SP3 if you've already installed it—in KB #306908. 
 
NOTE: DO NOT ATTEMPT TO INSTALL SP3 ON INSTALLATIONS OF SQL SERVER 7.0 OR EARLIER, OR MSDE 1.0 
 
Note that the W32.Slammer worm only affects SQL Server 2000—including MSDE. SQL Server 7.0 and MSDE 1.0 installations, as well as earlier versions of SQL Server, are not affected by this exploit. 
 
Will the product not install? 
 
Make sure you are installing the right version of SP3. I have seen dozens and dozens of people who have Standard or Developer Edition and try to install the SP3 files for MSDE and Analysis Services. Please see this breakdown to understand which file(s) you should download and install: 
 

For SQL Server Standard, Enterprise, Personal, Developer:        sql2ksp3.exe    For MSDE:        sql2kdesksp3.exe    If and *only* if you are running Analysis Services:        sql2kasp3.exe

 
Please do not just download all three files and install them, assuming a CYA theory. Verify what version / edition you have (see Article #2160) and install ONLY the file(s) that you need. You can also see Microsoft's SQL Server 2000 Service Pack 3a page for confirmation. 
 
If you are running the Evaluation Edition, they have released a new version called Evaluation Edition Release A. Please make plans to uninstall your existing installation and reinstall this new version, to ensure you are protected. 
 

---

Getting started 
 
You can download SP3 from Microsoft TechNet: SQL Server 2000 - Dow.... Before continuing, please read the readme and its addendum (KB #330022) 
 
Note that SP3 is cumulative, meaning that it includes SP1 and SP2. So, you do not need to install a prior SP before installing SP3—except in a couple of cases. It is looking like SP3 for the version of MSDE that ships with the .NET Framework SDK requires SP2 to be installed first; also, if you install the hotfix from MS02-039 without ever installing SP2, you should install SP2 first to avoid potential problems during SP3 setup. 
 
When you download / run the EXE, its only job is to extract the setup files. Many people have complained that they double-clicked the EXE, but SELECT @@VERSION told them they were still running a pre-SP3 build. To install the service pack, you need to navigate to the folder where you extracted the files from SP3, and double-click setup.bat. If you are using MSDE, however, see the notes  
 
If you are running Windows 2000 Terminal Server in Application Server mode, make sure to run the install directly on the Terminal Server machine, not on a TS client. See KB #830629 for more information. 
 
If you are installing on a cluster, please see the following article: 
 
    KB #811168 SQL Server 2000 Service Pack 3, Service Pack 3a, or Service Pack 4 pre- 
    installation checklist for SQL Server 2000 virtual server for failover clustering 
 

---

Error occurs or system hangs while validating user 
 
Many people have complained about errors or unresponsiveness while the SP3 dialog states that it is "validating a user." 
 
You will likely get this error message: 
 

Setup was unable to validate the logged on user. Press Retry to enter another option, or Cancel to exit setup.

 
You may have to cancel installation by ending the process using Task Manager. Possible causes, and workarounds, are listed below.

• One workaround is to install a later version of MDAC (e.g. 2.7) prior to installing SP3. See Article #2057 for information on determining which version of MDAC you have, and see Article #2342 for information on obtaining a more recent version. 
   
  
• Another possible problem is that TCP/IP and/or Named Pipes are not enabled. Check that the relevant protocols are enabled through the Client Network Utility. You can also look at the following key: 
   
  

  HKLM\software\microsoft\mssqlserver\client\connectto\dsquery

   
  If the value is DBNMPNTW, change it to DBNETLIB and restart the SQL Server service before trying again to install SP3. 
   
  
• Mary Henning sent in a message about this error as well. The conflict is with the currently installed versions of SQLSERVR.EXE (any version earlier than 8.00.534) and SSNETLIB.DLL (8.00.636, which came with the patch for MS02-039).  
   
  Per Microsoft's Security Update Bulletin for MS02-039, this patch can be installed on systems running SQL Server 2000 SP2. If you install the patch on systems running earlier versions of SQL Server 2000, you'll be able to restart the SQL Server—but executing any T-SQL statement on that server instance will cause a SQL dump (access violation) in SSNETLIB.DLL. If you try to upgrade that SQL Server instance to SP3, a SQL dump will occur when the setup procedure executes a T-SQL statement from an osql command. Shortly after, the installation may hang. 
   
  So, if the version of SQLSERVR.EXE on your SQL Server 2000 instance is less than 8.00.534 (SP2), and the version of SSNETLIB.DLL is 8.00.636 (or higher!), you should roll back the SSNETLIB.DLL and SSNETLIB.PDB files for that server instance to the versions that were installed before you applied the MS02-039 patch. Once you do that, test Query Analyzer or osql to satisfy yourself that T-SQL queries execute without error. Then you can install SP3 as usual.  
   
  The Readme.rtf file that comes with the patch for MS02-039 includes instructions for installing the patch on either clustered or standalone systems—and no steps for rolling back the patch. But the instructions are clear—they include steps for stopping services or failing over, making backup copies of SSNETLIB.DLL and SSNETLIB.PDB; renaming the files out of the way; copying the new files into the same directories; and restarting services or failing over. The rollback should be easy, too. Just consult the Readme.rtf to review steps for replacing the 2 files; make sure you still have the earlier versions of these files; and use the instructions to manually put the earlier versions of the files back in place. If you don't have older versions of the files on disk, find them from a backup or SQL Server distribution kit. 
   
  
• A workaround that might get you past this error is to stop SQL Server, and start it again in single user mode. To do so, you can stop SQL Server using the services applet in the control panel (or net stop from the command line), then start SQL Server from the command line, as follows: 
   
  

  sqlservr.exe -c -m

   
  
• Finally, a KB article might be of assistance: KB #814844.

---

Instance Name Specified is Invalid 
 
Another common problem is that some servers haven't been able to verify the instance name that you pass (usually when using a default instance). To apply SP3 to a default (unnamed) instance, use the following command line: 
 

setup /upgradesp sqlrun

 
To upgrade a named instance, use the following command line: 
 

setup /upgradesp sqlrun INSTANCENAME=foo

 
(Where "foo" is the name of the instance you want to modify.) 
 
To use SQL Server authentication, let's say your userid is sa and your password is as, use this command: 
 

setup.exe /upgradesp sqlrun securitymode=sql upgradeuser=sa upgradepwd=as

 
If you'd like to log errors during setup (this can be really useful to pass along to other users trying to help you diagnose), use the following switch: 
 

setup /upgradesp sqlrun /l*v C:\SP3Log.txt

 

---

Invalid product code 
 
During setup, you might encounter this error: 
 

Invalid product code was found. Check if the product was installed.

 
This usually means you tried to install the standard SP3 against MSDE that was installed through the .NET SDK setup or through a custom merge module. See the MSDE section below for information on obtaining / generating the proper setup files. 
 

---

Other errors during setup 
 
You might get this error during setup: 
 

Error running script: sp3_serv_uni.sql (1)

 
Microsoft support suggests renaming the dbmslpcn.dll in the C:\Windows\system32 directory, and replacing it from the SP3 files, and rerun setup to resolve the problem. You can also apply the remaining scripts manually, by restarting SQL Server, connecting via Query Analyzer, and executing these scripts in this order: 
 

sp1_serv_uni.sql  80sp1-tools.sql  sp2_serv_uni.sql  80sp2-tools.sql  sp3_serv_uni.sql  80sp3-tools.sql  sp3_repl.sql

 
Make sure you're in the context of Master in between each script. 80sp1-tools.sql left me in msdb on at least one occasion, and this broke the subsequent script. Also, you can ignore the 'cannot add rows to sysdepends' errors in the output of the last script. 
 
### 
 

Error running script replcom.sql

 
This error usually indicates that the user running SP3 setup doesn't have adequate permissions (e.g. Administrator). 
 
### 
 

Error running script sp3_serv_uni.sql

 
This can happen on a server with case-sensitive collation, and has to do with the value 'Cross DB Ownership Chaining' in the comment column in master..sysconfigures. The setup routine is actually looking for 'Cross Db Ownership Chaining' (notice the lower case b in 'Db'). To avoid this error, try SP3a or, and I say this with much hesitation, delete the problem row from master..sysconfigures, e.g.: 
 

USE MASTER  GO    EXEC sp_configure 'allow updates', 1  GO    RECONFIGURE WITH OVERRIDE  GO    DELETE sysconfigures WHERE config = 400  GO    EXEC sp_configure 'allow updates', 0  GO    RECONFIGURE WITH OVERRIDE  GO

 
### 
 

Setup was unable to verify the state of the server for an upgrade. Verify the server is able to start and that you provided a valid sa password and restart setup.    or    This operation returned because the timeout period expired.    or    Installation of the Microsoft Data Access Components package failed.

 
If you are running the Korean version of SQL Server 2000, see KB #816985. 
 
Otherwise, Microsoft support has posted this series of steps to try:

1. Check the MDAC versions present following the instructions in KB #307255 (Editor's note: also see Article #2057). 
    
   
2. Backup the registry, then delete the following key if it is present: 
    
   

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Setup\Exception Components

    
   You may need to restore the key after SP3 installation completes. The key may indicate a previous MDAC installation either did not complete successfully or was partially rolled back. 
    
   
3. Look for setup diagnostic information in a file dasetup.log. 
    
   
4. Try executing the MDAC setup manually, then redoing the SP3 setup. Execute the file x86/other/sqlredis.exe in the extraction folders created by the Database Components SP3 download. 
    
   
5. Try installing SP3 manually, then later installing the MDAC components. At the command prompt, go to the folder x86/setup in the extraction folders created by the Database Components SP3 download. For Standard or Enterprise Edition, run: 
    
   

   setupsql k=dbg

    
   This will cause setup to pause and request confirmation before installing various components. Instruct setup to skip the MDAC installation, but install all other components. After setup completes, install MDAC from MDAC Download Page.

You can also see if any of the workarounds listed in KB #328290 resolve your issue (even though that KB was written for SP2). 
 
### 
 

LoadLibrary GetLastError(): 193  LoadLibrary failed on sqlsut.dll

 
Here are some possible workarounds for this error:

• Make sure that the SP3 folder name you're running from does not contain a period (see KB #324468 for detailed information on this LoadLibrary error). 
   
  
• Disable any Anti-Virus software before running SP3. 
   
  
• Make sure the user running SP3 has sufficient registry permissions. Run regedt32.exe, select HKEY_CLASSES_ROOT, choose Edit/Permissions... make sure the user full control, and try SP3 again.

---

Full-Text Search produces 80004002 errors 
 
Here is a post from Andrew Cencini, a Program Manager in the SQL Server group: 
 
We have been investigating an issue with Full-Text Search populations not working after installing SP3. The development team spent time isolating and understanding the problem, and is in the process of generating a supported fix that can be obtained from Microsoft. In the meantime, here an explanation of the problem, and a workaround that may be acceptable (as the fix is not yet available). 
 
A problem exists where Full-Text catalogs are unable to be automatically repopulated after upgrading to SQL Server 2000 SP3 (A message similar to the following appears in the event logs: The crawl seed <MSSQL75://SQLServer/xxxxxxxx> in project <xxxxxx> cannot be accessed. Error: 80004002 - No such interface supported). Other symptoms include: a Full-Text population finishing quickly without an error being returned when started through Query Analyzer, though itemcount is zero afterwards, or 7619 error being returned for Full-Text queries. This problem appears to be isolated to machines that have SQL Server 2000 (upgrading to SP3) where SQL Server 7.0 (with Full-Text Search installed) exists or previously existed. 
 
SQL Server 7.0 Full-Text Search uses a different registration mechanism than SQL Server 2000 Full-Text Search for wordbreakers. As such, the following registry key exists (for SQL Server 7.0 Full-Text Search), corresponding to the neutral wordbreaker: 
 

HKLM\Software\microsoft\search\1.0\Language\neutral

 
Additionally, the following key exists (for SQL Server 2000 Full-Text Search), corresponding to the neutral wordbreaker: 
 

HKLM\Software\microsoft\contentindexcommon\LanguageResources\Default\Language Neutral

 
The first key (for SQL Server 7.0) points to query.dll as the neutral wordbreaker. In all SQL Server 7.0 versions, and all versions of SQL Server 2000 up to SP2, this is where the neutral wordbreaker resides. In SQL Server 2000 SP3, the neutral wordbreaker moves to a different location: tquery.dll. The second key (above), indicates the correct location of the neutral wordbreaker in SQL Server 2000 SP3. However, the way MSSearch loads wordbreakers is to honor the SQL Server 7.0 registration mechanism first (in this case, taking the old location for the neutral wordbreaker), followed by the SQL Server 2000 registration mechanism. 
 
As such, when repopulation of Full-Text catalogs begins in this scenario, MSSearch attempts to load the neutral wordbreaker using the SQL Server 7.0 location (which previously would have succeeded), and therefore, the repopulation is unable to complete. 
 
The workaround (until a fix is supplied) is as follows:

1. Verify that your system meets the criteria and exhibits the symptoms described above (if you are unsure, contact Microsoft Product Support Services) 
    
   
2. Verify that the registry keys specified above do exist 
    
   
3. Back up the registry 
    
   
4. Remove the following key: 
    
   

   HKLM\Software\microsoft\search\1.0\Language\neutral

    
   
5. Restart Microsoft Search 
    
   
6. Start any populations that failed

Modifying the registry can potentially be dangerous. Therefore, do so carefully, and be sure to back up the registry and understand how to restore it if anything goes wrong. 
 

---

After Installation 
 
You might be experiencing EXCEPTION_ACCESS_VIOLATION errors which didn't occur before SP3. These might be accompanied by Event ID 17052 errors in the event log, and text like "Process <processID> generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process." Please see the following KB articles, which may help resolve the issue: KB #270061, KB #309435, KB #315523, KB #316331, KB #319048, and KB #817359. 
 
You might find that SQL Server Service Manager will not load, or you get access violation errors, after installing SP3. This is usually caused by enabling the 'hide server' option in the Server Network Utility. As described in KB #814064, Microsoft recommends not using this option. 
 
You may have experienced a disappearing Enterprise Manager and/or DTS Designer. If so, see KB #814113 for information about a hotfix that may fix this issue. 
 
You might find that debugging is broken for clients that are using pre-SP3 tools. The best fix for this is to update all clients to SP3/SP3a, and if this isn't possible, you can kludge it by running the following command to enable legacy debugging clients: 
 

EXEC sp_sdidebug 'legacy_on'

 
Another problem you might have is performance degradation with ODBC clients. See KB #814410 for a potential hotfix from Microsoft Support, after making sure ODBC logging is disabled (Control Panel / ODBC Data Sources), or you can use the component checker to rollback to an earlier version of MDAC than the one that ships with SP3 (see KB #307255 for more information). 
 
Did the install not complete successfully? 
 
There has been much confusion surrounding the installation of SP3, and many people have reported problems which either left their server in a bad state, or simply didn't upgrade the server to protect them. 
 
Before assuming that the service pack install didn't work, run the following query: 
 

SELECT SERVERPROPERTY('productversion')    SELECT SERVERPROPERTY('productlevel')

 
This should return: 
 

8.00.760 -- or higher    SP3

 
If this is what it returns, then you have installed SP3 successfully (if you don't get 760 as the build number, see Article #2160 to identify which version you have). 
 
If you are seeing slower query plans after installing SP3, see KB #830298. 
 
You might also see the following error in the event log: 
 

Event ID: 17055  Source: MSSQLSERVER  DESC: 17310:  FRunCm: Process 2844 gen fatal except c0000005  EXCEPTION_ACCESS_VIOLATION.  SQL Server is terminating the process.

 
To resolve this issue, please see KB #814572 for a workaround (which also suggests using SP3a, so if it fails or seems like too much work, just grab the newer download). 
 

---

MSDE 
 
MSDE has its own version of SP3, shipped later than other editions of SQL Server 2000. Before you go any further, please see KB #831862 for an update on the changes in MSDE 2000 Release A (SP3a)—updated January 2005. You may be vulnerable to W32.Slammer if you have any of the products listed here. You should also check this comprehensive list to see products from other vendors that may have made you vulnerable. (To verify for certain whether or not you are vulnerable, please see Article #2441.) 
 
In addition to requiring a different SP installer than other editions of SQL Server 2000, the way you use the EXE you download really depends on how MSDE was initially installed. Many of the problems people are having with SP3 for MSDE is that they've been trying to apply the service pack meant for either the Standard and Enterprise editions or for different 'flavors' of MSDE. 
 
One thing to make sure is that you use the /upgradesp switch, as per above. If you don't use this switch, you will be installing a new instance of MSDE instead of upgrading your existing instance. This means that, in most cases, you will need to be running this setup from the command line. 
 
The version of MSDE that is supplied with ASP.NET Web Matrix (available from ASP.NET Web: The Official Microsoft ...) has already been updated to include SP3. 
 
The version of MSDE that ships with Windows 2003 Server RC2 does not include SP3, but has the relevant security hotfixes so that it is not vulnerable. 
 
If you are using an embedded MSDE application, you will have to build and deploy a new version of your setup program using SP3 merge modules. For more information, see MSDN Online and KB #810826. Your setup command line will become: 
 

setup /upgradesp drive:\path\customMSI.msi

 
To identify which MSI file was used to install MSDE, see KB #311762. 
 
If you don't meet any of these 'special' cases, then you can install SQL2KDeskSP3.exe from the regular SP3 download page. 
 
If you get an error about not supplying a strong sa password, you need to do one of two things. If you are using the standard setup routines to install MSDE, you need to add a strong password to setup.ini. If you are using an MSI installer, you need to use the ORCA tool to add the following to the property table: 
 

Property=SQLSaPWD  Value=<password>

 
If you can't connect to Port 1433 after installing SP3/SP3a, this might be because of the new DISABLENETWORKPROTOCOLS switch (see KB #814130). You can correct the issue by using the Server Network Utility (you may have to open both 1433 and 1434), or by setting DISABLENETWORKPROTOCOLS = 0 in setup.ini before installation. 
 
Here is how the [Options] section of setup.ini should look. For Windows Authentication only: 
 

[Options]  DISABLENETWORKPROTOCOLS=0  INSTANCENAME="<instance name>"  SAPWD="5tr0ng_pa55w0rd"

 
And for mixed mode (SQL Authentication + Windows Authentication): 
 

[Options]  DISABLENETWORKPROTOCOLS=0  INSTANCENAME="<instance name>"  SECURITYMODE=SQL  SAPWD="5tr0ng_pa55w0rd"

 

---

Uninstalling SP3 
 
If you wish to uninstall SP3, there is a section in the readme and a KB article (KB #314823) that explain the steps you should take. 
 
If you do decide to uninstall SP3, *PLEASE* be sure to add all of the known security patches, both pre- and post-SP3. See Article #2151, which details many security bulletins for both the operating system and SQL Server, Article #2441, which deals with the SQL Slammer worm specifically, and Article #2160, which shows various security patches available for SQL Server specifically. 
 
The most important patches are the ones that protect you against slammer, Microsoft security bulletin MS02-039 and Microsoft security bulletin MS02-061. If you can't be at SP3, you should be as close as possible.