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:
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.
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:
You may have to cancel installation by ending the process using Task Manager. Possible causes, and workarounds, are listed below.
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:
To upgrade a named instance, use the following command line:
(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:
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:
Invalid product code
During setup, you might encounter this error:
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:
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:
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.
This error usually indicates that the user running SP3 setup doesn't have adequate permissions (e.g. Administrator).
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.:
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:
Here are some possible workarounds for this error:
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:
Additionally, the following key exists (for SQL Server 2000 Full-Text Search), corresponding to the neutral wordbreaker:
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:
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:
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:
This should return:
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:
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 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:
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:
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:
And for mixed mode (SQL Authentication + Windows Authentication):
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.
Related ArticlesAre there tools available for auditing changes to SQL Server data?
Can I create an index on a BIT column?
Can I have optional parameters to my stored procedures?
Can I implement an input mask in SQL Server?
Can I make SQL Server format dates and times for me?
Can I start IDENTITY values at a new seed?
Can SQL Server tell me which row was inserted most recently?
How can I learn more about undocumented SQL Server stored procedures?
How can I make my SQL queries case sensitive?
How do I audit changes to SQL Server data?
How do I connect to a non-default instance of SQL Server?
How do I connect to SQL Server on a port other than 1433?
How do I create a cross-tab (or "pivot") query?
How do I determine if a table exists in a SQL Server database?
How do I drop a SQL Server database?
How do I find all the available SQL Servers on my network?
How do I get a list of SQL Server tables and their row counts?
How do I get rid of Named Pipes / DBNMPNTW errors?
How do I get the correct date/time from the msdb.sysjob* tables?
How do I get the nth row in a SQL Server table?
How do I get the result of dynamic SQL into a variable?
How do I handle REPLACE() within an NTEXT column in SQL Server?
How do I hide system tables in SQL Server's Enterprise Manager?
How do I know which version of SQL Server I'm running?
How do I limit the number of rows returned in my resultset?
How do I load text or csv file data into SQL Server?
How do I manage changes in SQL Server objects?
How do I monitor SQL Server performance?
How do I prevent linked server errors?
How do I reclaim space in SQL Server?
How do I recover data from SQL Server's log files?
How do I search for special characters (e.g. %) in SQL Server?
How do I start SQL Server Agent from ASP?
How do I time my T-SQL code?
How do I upsize from Access to SQL Server?
How do I upsize my MSDE database to full-blown SQL Server 2000?
How do I use a variable in a TOP clause in SQL Server?
How do I use GETDATE() within a User-Defined Function (UDF)?
How should I store an IP address in SQL Server?
Schema: How do I find all the foreign keys in a database?
SQL Server & MSDE
What are reserved Access, ODBC and SQL Server keywords?
What are the capacities of Access, SQL Server, and MSDE?
What are the main differences between Access and SQL Server?
What do I need to know about SQL Server 2000 SP4?
Where else can I learn about SQL Server?
Where is SP4 for SQL Server 2000?
Why can't I install SQL Server on Windows Server 2003?
Why can't I install SQL Server on Windows XP?
Why can't I use LIKE '%datepart%' queries?
Why do I get "Login failed for user '\'."?
Why do I get 'object could not be found' or 'invalid object name'?
Why do I get errors about master..spt_values?
Why do I get script errors in Enterprise Manager's 'taskpad' view?
Why do I get SQLSetConnectAttr Failed errors?
Why do I have problems with views after altering the base table?
Why does EM crash when I get an error in a stored procedure?
Why does Enterprise Manager return 'Invalid cursor state'?
Why does my DELETE query not work?
Why does sp_spaceused return inaccurate values?
Why is Enterprise Manager slow at expanding my database list?
Why is my app slow after upgrading from SQL Server 7 to 2000?
Why is tempdb full, and how can I prevent this from happening?
Why should I consider using an auxiliary calendar table?
Why should I consider using an auxiliary numbers table?