Tag Archives: hotfix

How to extract the contents of .MSP files

I had encountered an issue while installing a SQL Server service pack. The resolution for it was to replace the existing SQL Server binaries from that of a Service Pack. To extract the contents of a Service Pack executable we need to just type “ServicePackExeName /extract” as I explained in this post.

I was looking for the sqlservr.exe but it wasn’t directly available in the extracted folder. The folder structure looked something like this.

ExtractedFolder

From my past experience I knew that the Service Pack installer would copy sqlservr.exe by extracting sqlrun_sql.msp file.

sqlrun_sql.msp

Now how do I get the sqlservr.exe from the .msp file? The command “sqlrun_sql.msp /extract” wouldn’t work here.

One of the options is to extract the contents of the .msp file using a file archiver like 7-zip. After extracting the contents, look for a file which has the name CAB in it. In this case, the file name is “PCW_CAB_Family01”.

extracted

Again extract this file to a folder and the contents would look something like this.

sqlservr.exe

Now just rename the sqlservr.exe.******** to sqlservr.exe. That’s all. You have just extracted the requisite file from an .msp file.

The other alternative is to use the MsiX utility by Heath Stewart. The usage of this tool is very straight forward. Just type the following command in the Command Prompt and it would extract the contents of the .msp file

MsiX sqlrun_sql.msp

MsiX_cmdprompt

After this navigate to the folder where the file was extracted and extract the contents of the *CAB* file.

MsiX_extractedFollow the same steps and rename the file that you need (sqlservr.exe.****) to the requisite file.

Mission accomplished!

MSP Error: 29528 The setup has encountered an unexpected error while Setting reporting service exclusion path

Yet another post related to SQL Server patching, eh? Yes. I feel I have a lot more lined up because in the next couple of months we will be applying Service Packs on close to 200 SQL Server instances.

Last Sunday, Service Pack 4 was supposed to be applied on one of the SQL Server 2005 instance. All components of SQL Server were successfully patched except for SQL Server Reporting Services. The following error was logged in the Summary.txt file.

Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\RS9_Hotfix_KB2463332_sqlrun_rs.msp.log
Error Number : 29528
Error Description : MSP Error: 29528 The setup has encountered an unexpected error while Setting reporting service and share point exclusion path. The error is: Fatal error during installation.

The RS9_Hotfix_KB2463332_sqlrun_rs.msp.log file was 6MB in size and quickly scrolling through it did not provide much useful information. The Event Logs also did not have much information apart from the message indicating the Service Pack installation failure. Instead of scrolling through the log file which had 61037 lines (yes, you read it right), I took a step back and analyzed the prerequisites.

Since it is SQL Server 2005, the Reporting Service depends on Internet Information Services (IIS). The Reporting Service Configuration Manager makes use of IIS to create the Virtual Directories along with the database hosted in SQL Server, to render the reports. My plan was to check IIS for any issues and then move on to look at the Reporting Services configuration.

Hence I opened up the Service Manager (services.msc). Surprise! Here is what I saw.

The IIS Admin Service was disabled. Obviously the Service Pack installer was unable to fetch the settings on IIS. Immediately, I enabled the IIS Admin Service and launched the Service Pack installer again and this is what I got.

Nice! Mission accomplished.

Why was the IIS service disabled? It was a test box. Do I need to say anything more?

Mising MSI or MSP files while installing SQL Server service packs

Last week we were applying SQL Server 2005 Service Pack 4 (SP4) on one of the instances. While patching the Database Services, the following screen popped up.

This issue is very well known by know. SQL Server update fails because of missing cached .msi files in C:\WINDOWS\Installer folder. In this post I had explained how can we resolve this issue by manually copying the files.

Then why am I posting about the same issue again? Earlier whenever SQL Server update installer did not find relevant files in C:\WINDOWS\Installer folder, it would fail silently without giving any clue as to why it failed. Then one had to manually go through the error logs in SetupBootStrap folder to understand the exact reason for the failure.

Starting SQL Server 2005 SP4, the installer pops up a dialogue box as seen in the above screenshot. With this the DBA can locate the path of the missing file (SQLRun_SQL.msi in this case), so that the patching progresses without interruption.

This is a very useful new feature in SQL Server service pack installer. I hope in the coming days, the installer lists all the missing .msi/.msp files in the initial stage so that the DBA can resolve the issues and then resume with the patch installation.

SQL Service Pack installation through Automatic Updates

My responses to 3 questions in the past 2 weeks has prompted this post. The issue is something like this.

  • Windows Automatic Updates is enabled on an instance running SQL Server
  • Windows Update prompts that a new SQL Service Pack/Hotfix is available to be installed and the user installs it through Windows Update.
  • Installation completes successfully and the correct version is reflected in SQL Server
  • After the next reboot, again the Windows Automatic Updates prompts to install the Service Pack which was previously installed. The installation would obviously fail since it is already applied.
  • Windows Update keeps prompting for the Service Pack to be installed.

This issue happens because the some of the cached files of the Windows Automatic Updates are corrupt. These files are located in [Default Windows Directory]\SoftwareDistribution\Download folder.

The following steps are useful to workaround this issue.

  • Stop the Automatic Updates service in the Service Control Manager (services.msc)
  • Open the folder C:\WINDOWS\SoftwareDistribution\Downloads
  • Delete all the files in this folder
  • Start the Automatic Update service

Since the cached files were cleared manually, when the Automatic Update service starts for the next time, it will recreate all the files required for its functionality and builds the cache afresh.

Server is in script upgrade mode. Only administrator can connect at this time

Yesterday I replied to a question in which the user was not able to connect to the SQL Server 2008 instance after installing Service Pack 2. The error message is

Login failed for user ‘LoginName’
Reason: Server is in script upgrade mode.
Only administrator can connect at this time.

I was able to quickly respond to this question because I had faced this situation when I had installed SQL Server 2008 SP2 on my VM. Here is what I had done during the installation of SP2.

  • SQL Server service was stopped prior to SP2 installation (this service is in manual mode in my VM, hence I forgot to start it)
  • SP2 was successfully installed on the instance
  • I started the SQL Server service
  • Immediately I launched SSMS to connect to the instance and I got the above error.

This issue happens because the SQL Server service was stopped when the SP2 installation was in progress. Service Pack installation completes successfully but certain scripts (most of the times sqlagent100_msdb_upgrade.sql) in the Service Pack will be applied only after the SQL Server service starts the next time. Hence when I started the SQL Server service, these scripts were still being applied to the instance and I was trying to connect to the instance during that time. Hence the error message.

What is the resolution? Just wait! Wait for few minutes after the SP is installed and you start the SQL Server service for the first time. Once these scripts are applied to the instance, all the users will be able to connect to the instance normally.

There is already a BUG reported for this issue and it is under “consideration”. Even I feel that the installer should finish its work before reporting that it is “successful”.

I have seen many DBAs stopping the SQL Server services before applying any Service Packs/hotfixes. In fact there is no need to do it. The Service Pack installer will take care of stopping and starting the instance as and when required. This issue is a reason for not stopping the service before applying the SP. Even though there is not much difference, for sure it will prevent a few anxious moments while trying to connect to the instance for the first after installing the SP

SQL Server 2005 installation fails with MSXML 6 SP2 error

I have responded to numerous questions in forums where the SQL Server 2005 Installation would fail because MSXML 6 Service Pack 2 was already installed on the computer. This error would only occur on computers running Windows XP. Yesterday one of my team members faced the same issue on his laptop, so I thought of putting together the information here. The error message in the SQL Server Setup log would be something like this.

This package is not supported on this operating system.
SkipInstallCA. Return value 3.
Fatal error during installation
MainEngineThread is returning 1603
Error code 1603
Product: MSXML 6.0 Parser (KB933579)
Installation failed.
Created Custom Action Server with PID 2868 (0xB34).
Running as a service.
Hello, I'm your 32bit Impersonated custom action server.
SkipInstallCA.
This package is not supported on this operating system.
SkipInstallCA. Return value 3.

Till recently, the following were the only workarounds to fix this issue.

  • Install Windows Installer Cleanup Utility (MSICUU2.exe) from this KB article and remove MSXML 6 SP2. But this utility caused some irreversible issues to other applications in some occassions. Subsequently this utility was removed from this page.
  • Uninstall MSXML 6 SP2, reboot the computer. Then install MSXML parser from here. This workaround used to work sometimes.
  • If both of these does not work, the only option left was to reach out to Microsoft PSS and ask for a script that they would provide to work around this issue (which of course would come with a disclaimer).

Finally on November 8, 2010 Microsoft had published this KB article in which they have provided MSXMLFix.exe which easily fixes this issue quickly. Even though this fix is a bit late, better late than never.

Cannot uninstall SQL Server 2005

Last week one of the team members was finding it difficult to uninstall SQL Server 2005. It was failing due to missing .msi/.msp files and setup error log had these entries.

Local cached package 'C:\WINDOWS\Installer\7f6522.msi' could not be opened as a storage file.

Couldn't find local patch 'C:\WINDOWS\Installer\7f660a.msp'. Looking for it at its source.

I had posted on how to workaround these issues earlier. One of my fellow SMEs worked on this issue and with all the approvals in place, I am posting this here!

By going through the error logs, a list of files missing from the C:\Windows\Installer folder was created. Then these files were renamed after copying. The installation proceeded further but this time failed with a new error. Only the Database Engine was not getting un-installed.

Disallowing uninstallation of patch {14ED24FB-0FAF-4B76-82EB-EF6A2F79E62D}
because the patch is marked as not uninstallable

Product: Microsoft SQL Server 2005 (64-bit) - Update 'Service Pack 3 for SQL Server Database Services 2005 (64-bit) ENU (KB955706)'
could not be removed. Error code 1646.

The setup was failing because the SP3 for SQL Server 2005 was marked as Uninstallable! After trying lot of work around, Windows Regsitry was the last ray of hope. Hence navigated to the hive which had the product id

14ED24FB-0FAF-4B76-82EB-EF6A2F79E62D in it.

All the installed products will be under

The hive which had the product id mentioned above, had a key named Uninstallable and it had a value of 0.

Gotcha! This was causing the un-installer to fail. Quickly changed the value of Uninstallable to 1 and the Uninstaller completed successfully!

At the end it seemed to be “such a small thing” but on that day it took almost two hours to track this down.