I am having problems installing a SQL Service pack.

A. Try the following checklist :-

1. Make sure you have administrator level permissions on the machine in question, as SQL needs to create registry entries, services etc.

2. SQL Server is dependent on network functionality - even for the set-up routines. Specifically it needs to use named-pipe/mailslot functionality. These usually require a network card to be present. If you don't have a network card then go to control panel/networks, choose add adapter and then add the Microsoft loopback adapter - which is just a dummy driver, no hardware involved. This needs to have working network protocol(s) bound to it. Let whichever protocols you have use default parameters, EXCEPT for tcp-ip. If you are using this then do NOT specify dhcp assigned address, use 192.168.1.1 as the IP address and 255.255.255.0 as the subnet mask. This is a standard RFC1918 non-routed IP address so shouldn't clash with any dial-up address you may be given by an ISP.

3. Make sure that there are no quotes in the path on the machine you are setting up. Also check that the full directory path you are installing from does not include any spaces. Either of these will cause part of the setup to fail (usually the cfgchar.exe bit). Also check the registry entry [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerSetup] ends in a "" and add one if it doesn't.

4. Because a named-pipe, used by the set-up routine, is effectively a file as far as the operating system is concerned, real-time Virus scanners can cause problems. Most of these have been fixed so they don't interfere with SQL's named-pipes, but make sure you're running the latest version of whichever virus package you use. But if in doubt, then disable the virus software for the duration of the install.

5. Other software packages can also interfere with SQL's install - typically they interfere with named-pipes/mailslot connectivity and setup will terminate with an error like 'unable to write to mailslot.....'. Shut-down any of these for the duration of the install. Packages that are known to interfere with SQL's setup routines include :-

IIS - Internet Information Server - the web parts, not FTP
PWS - Personal Web Server
Exchange Server

6. If the dial-up networking icon/window appears and tries to make a network connection, then stop and disable the "Remote Access Autodial Manager" service via control panel/services. This is an NT issue rather than a SQL one, but disabling auto-dial is the easiest way around it.

7. Make sure that named-pipes is enabled and is the default net-lib on the server. Make sure that SQL is listening on .pipesqlquery which is the default.

8. If your "sa" password had a space in it then change the password so it doesn't have a space before installing the SP.

9. Make sure that SQL Server has a name. Run sp_helpserver and check what name srvid of 0 has. If it is not there then run sp_addserver '', 'LOCAL'

10. Check whether the value at HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerSetupSQLPath has a '' at the end. If you have used a long directory name you may need to add this yourself.

11. (6.5 and below). Make sure you haven't deleted the probe account from the master database. If you have :-

use master
go
exec sp_addlogin probe, NULL, master
exec sp_adduser probe
go

Then run installProcsyst.sql to grant relevant permissions back to the userid.

12. Installing SP1 to SQL 7.0 on a cluster may say "updating system files" and then error and terminate. This happens if your CD-Rom is not shared out - share it out as F$ (or whatever) and that should resolve the problem.

13. If it still isn't installing then check the *.OUT files in the install directory for clues as to what is going on.

14. If the .OUT files indicate that ISQL cannot connect to the SQL Server to run scripts then you can try the following unsupported hack (it worked for me on SP4 and SP5) by modifying the servicepack's setup.inf. After modifying the setup script run "setup /t Local = Yes"

Make a backup of the setup.inf first, then find the following line

set !ServerName = $(!ComputerName)

Now add the following 3 lines after it

ifstr(i) $(!Local) == "Yes"
set !ServerName = "."
endif

15. If the error is "Critical Error, could not open the file named D:MSSQLBINNSQLCTR60.DLL" then look for a service that has this file open. Easiest way to see which one is to get NTHANDLEX from www.sysinternals.com (free). You can retry the copy on this. If you get this error then check the version of the file at the end to see if it has indeed been updated. (The version of the file for SP5a is 6.50.410).