Updated: 11/10/2006

The most up-to-date version of this file is available online at the Microsoft Download Center.

The SQL Server documentation team cannot answer technical support questions, but welcomes your suggestions and comments about this readme documentation. You can quickly and directly send e-mail feedback by using the link below.

To submit written feedback about this document, click here: Submit feedback.

Contents

1.0 Introduction

This file describes how to use Microsoft® SQL Server™ 2005 Service Pack 1 (SP1) to upgrade instances of SQL Server 2005 to SQL Server 2005 SP1. This service pack can be used to selectively upgrade one or more instances of SQL Server 2005, SQL Server 2005 shared tools, and other components installed by SQL Server 2005 Setup.

Important:
This service pack can be used to upgrade all editions of SQL Server 2005 to SP1 except Express Edition. You must use Microsoft SQL Server 2005 Express Edition (SQL Server Express) Service Pack 1 (SP1) to upgrade instances of SQL Server Express to SP1. SQL Server Express SP1 can be obtained from the Microsoft Download Center Web site. This service pack cannot be applied to any pre-release versions of SQL Server 2005.

[Top]

1.1 Overview of SQL Server 2005 SP1 Installation

During installation, SP1 Setup will list all the components of SQL Server 2005 that are installed and allow you to select the components to upgrade to SP1, including components that have already been upgraded to SP1. The general process for installing SP1 is as follows:

  1. Download and extract the service pack installation files. Section 2.0 describes how to obtain the SP1 installation files.

  2. Prepare for upgrade to SP1. Section 3.1 details the preparatory steps to take before you install SP1.

  3. Install SP1. Section 3.2 details options for running SP1 Setup.

For more information, see the SQL Server 2005 SP1 download page.

[Top]

1.2 Installation Requirements

This section documents additional requirements for SQL Server 2005 SP1.

Disk Space Requirements for SQL Server 2005 SP1

To download and install SQL Server 2005 SP1, your computer must have 1.9 GB of available disk space. The following table describes the disk space usage:

Disk Usage (MB) Location Type

50

SQL Server 2005 installation directory.

Permanent.

1000

System drive.

Permanent.

600

temp directory.

Temporary.

250

Download location.

Temporary.

After installation, you may remove the 250 MB download package from your computer. Permanent disk space usage of SQL Server 2005 SP1 after installation is approximately 1 GB.

[Top]

1.3 Uninstalling SQL Server 2005 SP1

SQL Server 2005 SP1 cannot be uninstalled.

To remove SP1 and revert to the previous version of SQL Server 2005:
  1. Using Add or Remove Programs, uninstall the instance of SQL Server 2005 .

  2. Reinstall SQL Server 2005.

  3. Apply any hotfixes that were previously installed.

    Note:
    Additional steps are required to ensure that you are able to revert to the previous version of SQL Server 2005. For more information, see sections 3.1.1 Back Up Your SQL Server Databases and 3.1.2 Back Up Your Analysis Services Databases, Repository, and Registry Settings.

[Top]

1.4 Additional Information About SQL Server 2005 SP1

A list of the fixes contained in this service pack is provided in Microsoft Knowledge Base article 913090. Each fix listed in 913090 has a link to a Knowledge Base article about the problem that the fix addresses. Follow the links to the individual Knowledge Base articles to see information about each fix.

The Knowledge Base articles mentioned in this Readme are available in the Microsoft Support Knowledge Base.

To find an article in the Knowledge Base:
  1. Under Advanced Search, in the For text box, type the number of the article you want.

  2. Under Search Type, select Article ID.

  3. Click the right-arrow button.

[Top]

Hotfixes

All publicly issued SQL Server 2005 security bulletins released before March 2, 2006 have been addressed in SP1. If you received a SQL Server 2005 hotfix after March 2, 2006, that hotfix might not be included in this release of SP1. Contact your primary support provider about obtaining the same hotfix for SQL Server 2005 SP1.

[Top]

1.5 Updates to SQL Server 2005 Books Online Are Available

The April 2006 update of SQL Server 2005 Books Online is the primary documentation for SQL Server 2005 SP1. The April 2006 update of Books Online reflects all product upgrades included in this service pack, and also includes other improvements to the documentation. We strongly recommend installing this update to keep the information in your local copy of the documentation current with SQL Server 2005 SP1. The April 2006 update is available at this Microsoft Web site. For more information, see "Downloading and Updating Books Online" in SQL Server Books Online.

[Top]

Updated SQL Server 2005 Samples Are Available

New and updated samples for SQL Server 2005 are available. You can download these updated samples from this Microsoft Web site. For more information about installing the updated samples, see "Installing Samples" in the updated SQL Server 2005 Books Online.

[Top]

2.0 Obtaining SQL Server 2005 SP1

SQL Server 2005 SP1 is available for download at the Microsoft Download Center Web site. A separate downloadable installation package is available for each platform supported by SQL Server 2005.

Note:
The 32-bit version of SP1 can be used to update 32-bit instances that run in Windows-on-Windows 64 (WOW64) x86 emulation mode on a 64-bit system. The 32-bit version will not upgrade any components of a 64-bit instance of SQL Server 2005. To upgrade all components of a 64-bit instance of SQL Server 2005, use the 64-bit version of SP1.

SP1 Package Name Operating Systems1

SQLServer2005SP1-KB913090-x86-LLL.exe

All supported 32-bit Windows operating systems, including 32-bit instances on 64-bit operating systems.

SQLServer2005SP1-KB913090-x64-LLL.exe

Supported Windows 2003 64-bit X64 editions.

SQLServer2005SP1-KB913090-IA64-LLL.exe

Supported Windows 2003 64-bit Itanium editions.

1For information on supported operating systems for SQL Server 2005, see "Hardware and Software Requirements for Installing SQL Server 2005" in Books Online.

Note:
LLL represents a designator that varies by language.

Follow the instructions provided at the download site for downloading SQL Server 2005 SP1.

[Top]

3.0 Installing SQL Server 2005 SP1

This section details how to install SQL Server 2005 SP1, as well as any late-breaking information or setup issues that can affect successful installation of the service pack. The steps to install SQL Server 2005 SP1 are as follows:

  1. Prepare for a SQL Server 2005 SP1 installation

  2. Install SQL Server 2005 SP1

  3. Restart services and applications

[Top]

3.1 Prepare for a SQL Server 2005 SP1 Installation

You must do the following before you install SP1:

  1. Back up your SQL Server 2005 databases

  2. Back up your Analysis Services databases, repository, and registry settings

  3. Verify that the system databases have enough free space

  4. Verify that SQL Server 2005 service accounts are not disabled

  5. Stop services and applications

[Top]

3.1.1 Back Up Your SQL Server 2005 Databases

Before installing SP1, back up the master, msdb, and model databases. Installation of SP1 modifies the master, msdb, and model databases, making them incompatible with pre-SP1 versions of SQL Server 2005. Backups of these databases are required if you decide to reinstall SQL Server 2005 without SP1.

It is also prudent to back up your user databases.

Important:
When you apply SP1 to instances that participate in a replication topology, you must back up your replicated databases along with your system databases before applying SP1.

[Top]

3.1.2 Back Up Your Analysis Services Databases, Configuration File, and Repository

Before you upgrade an instance of Analysis Services to SP1, you should back up the following:

  • Analysis Services databases, which are installed by default to C:\Program Files\Microsoft SQL Server\MSSQL.1\OLAP\Data\.

  • Analysis Server configuration setting in the msmdsrv.ini configuration file, which by default is located in the C:\Program Files\Microsoft SQL Server\MSSQL.1\OLAP\Config\ directory.

  • (Optional) The database that contains the Analysis Services repository. This step is only required if the Analysis Server has been configured to work with the Decision Support Objects (DSO) library.

Note:
Failure to back up your Analysis Services databases, configuration file, and repository will prevent you from reverting an upgraded instance of Analysis Service to the previous version.

[Top]

3.1.3 Verify That the System Databases Have Enough Free Space

If the autogrow option is not selected for the master and msdb system databases, these databases each must have at least 500 kilobytes (KB) of free space. To verify that the databases have sufficient space, execute the sp_spaceused system stored procedure on the master and msdb databases. If the unallocated space in either database is less than 500 KB, increase the size of the database. For more information, see "Expanding a Database" in SQL Server 2005 Books Online.

[Top]

3.1.4 Verify That SQL Server 2005 Service Accounts Are Not Disabled

Setup may fail if the service startup type for the MSSQLServer and MSSQLServerOLAPService services is set to Disabled.

To verify and enable service accounts:
  1. In Control Panel, double-click Administrative Tools, and then double-click Services.

  2. Locate the MSSQLServer service in the list and note the value for Startup Type.

  3. If the value for Startup Type is Disabled, double-click the service name and change Startup Type to either Manual or Automatic.

  4. Repeat steps 2 and 3 for the MSSQLServerOLAPService service.

  5. Click OK.

[Top]

3.1.5 Stop Services and Applications

You should stop all applications and services that make connections to the instances of SQL Server being upgraded, including SQL Server Management Studio, Business Intelligence Development Studio, Control Panel, and Add and Remove Programs, before installing SP1.

Note:
You cannot stop services in a failover cluster environment. For more information, see Section 3.2.1 Failover Cluster Installation.

If you begin to apply SP1 without first shutting down services, Setup will attempt to stop the SQL Server 2005 services for you. If Setup determines that other applications and services must be stopped, a warning dialog box will be displayed that lists the applications and services that must be stopped. After you stop these applications and services, click Try Again to resume Setup. If you cannot stop an application or service, click Continue. Setup will continue, but you may need to restart your computer after Setup is complete. If you click Cancel, the current component will not be upgraded to SP1, but Setup will continue.

Note:
Setup cannot always determine which applications and services must be stopped. Stop all applications and services that make connections to SQL Server 2005, including Control Panel, before installing SP1.

[Top]

3.2 Install SQL Server 2005 SP1

This section describes the installation process during SP1 Setup.

Important:
SP1 Setup must be run under an account that has administrator privileges on the computer where SP1 will be installed.

Starting SP1 Setup

To install SQL Server 2005 SP1, simply run the self-extracting SP1 package file SQLServer2005SP1-KB913090-XXX-LLL.exe, where XXX indicates the specific platform and LLL represents a designator that varies by language.

Note:
To apply SP1 to SQL Server 2005 components that are part of a failover cluster, see Section 3.2.1 Failover Cluster Installation. To run SP1 Setup in unattended mode, see Section 4.3 Unattended Installations.

Installation Process

Depending on the options selected, the following dialog boxes are displayed during Setup:

  1. Welcome – Lists the SQL Server 2005 components that can be updated with this service pack.

  2. End User License Agreement – Provides license terms for this service pack.

  3. Feature Selection – Lists SQL Server 2005 features that can be upgraded to SQL Server 2005 SP1, grouped by instance. Check all features to upgrade to SP1.

  4. Authentication Mode – Allows users to provide credentials to connect to the SQL Server 2005 instances that are to be updated. When you upgrade a Reporting Services instance, this dialog is also used to provide the SQL Server login and password that Setup uses to update the schema of the report server database. Specifying a SQL Server login is necessary only if the report server database connection is configured to use SQL Server Authentication.

  5. Ready to Install – Setup has all the information needed to upgrade the selected SQL Server 2005 instances to SP1.

Installation Considerations

The following considerations apply when running Setup.

  • All features that belong to an instance will be upgraded at the same time. For example, when you upgrade Database Services you must also upgrade Analysis Services, if it is installed on the same instance. If a component or instance in the feature tree is not selected, the component or instance will not be upgraded to SP1.

  • Use Windows Authentication, whenever possible.

  • When you use SQL Server Authentication, the password supplied for the sa login cannot contain double quotation marks. If the password contains double quotation marks, use Windows Authentication instead of SQL Server Authentication when applying SP1, or change the password for the sa login before you run Setup.

  • When you upgrade a Reporting Services instance, Setup connects to the report server to upgrade the report server database. If the Windows account of the user who runs Setup is not a member of the sysadmin fixed server role in the report server, you must provide a SQL Server login and password that has sufficient privileges. To do this, select the Reporting Services instance in the Authentication Mode dialog, click Edit, supply the credentials of an account that has the required privileges, and click OK.

  • SP1 Setup log files are saved to %windir%\Hotfix\.

For more information about issues that might affect the successful installation of SP1, see section 3.4 Setup Issues.

[Top]

3.2.1 Failover Cluster Installation

The following information applies only to SQL Server 2005 components that are part of a failover cluster.

To install the service pack on a failover cluster:
  1. If any resources have been added that have dependencies on SQL Server resources, those dependencies must either be removed or taken offline before you install SP1. If you do not remove the dependencies, the installation of SP1 will take those resources offline.

    Note:
    When a clustered resource is taken offline, all dependent resources are also taken offline by the cluster service.

  2. Run the SP1 executable package file from the node (active node) that owns the group containing the virtual server that you plan to upgrade. This installs the service pack files on all nodes in the failover cluster. You cannot install SP1 on any other nodes (passive nodes) in the cluster.

  3. In the Feature Selection page, select the virtual server that you plan to upgrade. Setup will prompt you for login credentials used to connect to other nodes in the cluster.

    Note:
    Keep all nodes of the cluster online during Setup. This ensures that the upgrade is applied to each cluster node.

  4. If you removed dependencies or took resources offline in Step 1, restore the dependencies or bring the resources online.

    Note:
    Setup might require restarting of the failover cluster nodes. This restart replaces the files that were in use during Setup.

For an example of how to install SP1 in unattended mode with remote authentication for failover clusters, see Section 4.3 Unattended Installations.

Rebuild a SQL Server 2005 SP1 Failover Cluster Node

If you must rebuild a node in the failover cluster after SP1 has been applied, perform the following steps

To rebuild a node in the failover cluster:
  1. Rebuild the node in the failover cluster. For more information about rebuilding a node, see "How to: Recover from Failover Cluster Failure in Scenario 1" in SQL Server 2005 Books Online.

  2. Run the original SQL Server 2005 Setup program to restore the node to the failover cluster.

  3. Run SP1 Setup on the active cluster node.

[Top]

3.3 Restart Services and Applications

When Setup completes, it may prompt you to restart the computer. Section 3.1.5 Stop Services and Applications provides guidelines on when a restart is required. After the system restarts, or after Setup completes without requesting a restart, use the Services application in Control Panel to make sure that any services you stopped before applying SP1 are now running. This includes services like DTC and the Microsoft Search services, or instance-specific equivalents. Restart the applications you closed before running the SP1 Setup program. You may wish to make another backup of the upgraded master, msdb, and model databases immediately after successful installation.

[Top]

3.4 Setup Issues

This section details Setup issues for SP1.

[Top]

3.4.1 Considerations for Upgrading Servers in a Replication Topology

When upgrading instances of SQL Server 2005 in a merge replication topology or in a transactional replication topology with updating Subscribers, you must upgrade the instances in the following order:

  1. Distributor

  2. Publisher

  3. Subscribers

[Top]

3.4.2 Upgrading a Remote Report Server Database

If you are upgrading a Reporting Services installation that includes a remote report server database, you might encounter problems when upgrading the database schema. By default, Setup uses the security token of the user who is running the Setup program to connect to the remote SQL Server instance and update the schema. If you have administrator permissions on both the local and remote computer, the database upgrade will succeed. Similarly, if you run Setup from the command prompt and specify the /rsupgradedatabaseaccount and /rsupgradepassword switches for an account that has permission to modify the schema on the remote computer, the database upgrade will succeed.

If you do not have permission to update the schema on the remote computer, the connection will be refused with the following error:

"Setup was not able to upgrade the report server database schema. You must run the Reporting Services Configuration tool and on the Database Setup tab upgrade the report server database to the current database schema version."

The report server program files will be upgraded to SP1, but the report server database will be in the format of the previous version. The report server will be unavailable while the report server database is in the older format.

To upgrade the database manually, run the Reporting Services Configuration tool after upgrade is finished. Connect to the upgraded report server, and then use the Upgrade option on the Database Setup page to update the database schema. The report server will be available once these steps are complete.

[Top]

3.4.3 Repairing or Modify SQL Server Components May Fail After the Service Pack Is Installed

If you use Add or Remove Programs to repair or modify SQL Server components after SP1 installation, you may see the following error message:

"The feature you are trying to use is on a network resource that is unavailable."

The following SQL Server components are affected:

  • MSXML (msxml6.msi)

  • SQLXML (sqlxml4.msi)

  • Microsoft SQL Server Native Client (sqlncli.msi)

  • Backwards Compatibility (SqlServer2005_BC.msi)

  • Microsoft SQL Server VSS Writer (SqlWriter.msi)

To repair or modify SQL server components after the service pack is installed
  1. Use Add or Remove programs to remove the component from the computer.

  2. Install the component from the SQL Server 2005 installation media. On the SQL Server 2005 CD, files are located in the \Setup directory on the second SQL Server 2005 installation disc. On the SQL Server 2005 DVD and for network installations, files are located in the Tools\Setup directory. To install the component, double-click the .msi file name.

  3. Reinstall SQL Server 2005 SP1.

[Top]

3.4.4 Restrictions on ALTER DATABASE May Cause Upgrade to Fail

When you apply SP1, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:

  • Explicitly denying the ALTER DATABASE statement.

  • A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.

If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP1, you must disable these restrictions and then re-run Setup.

[Top]

4.0 Additional Information

This section describes additional considerations for using SP1.

[Top]

4.1 Getting SQL Server 2005 SP1 Assistance

There are three principal sources of information about SQL Server 2005 and SP1:

For more information, see "Getting SQL Server 2005 Assistance" in SQL Server Books Online.

[Top]

4.2 Providing Feedback on SQL Server 2005 SP1

You can provide suggestions and bug reports on SP1 in the following ways:

  • Make suggestions and file bug reports about the features and user interface of SP1 at the MSDN Product Feedback Center.

  • Send suggestions and report inaccuracies about the documentation by using the feedback functionality in SQL Server Books Online.

  • Choose to send error reports and feature usage data automatically to Microsoft for analysis. For more information, see "Error and Usage Report Settings" in SQL Server Books Online.

For more information, see "Providing Feedback on SQL Server 2005" in SQL Server Books Online.

[Top]

4.3 Unattended Installations

SQL Server 2005 SP1 Setup supports unattended installation from the command prompt. Unattended setup is run from the command prompt by using switches to specify specific setup options. The /quiet switch suppresses the Setup dialog boxes and allows unattended installation.

Note:
By using the /quiet switch you are agreeing that you have read and accepted the software license terms for SQL Server 2005 SP1. To review the license terms, run an attended installation of SQL Server 2005 SP1 and go to the End User License Agreement page of the Setup wizard.

[Top]

4.3.1 Unattended Installation Considerations

The following considerations relate to unattended installations:

  • When you specify only the /quiet switch, SP1 is applied to all components that are not instance-specific. The instance-specific components of SQL Server 2005 are Database Services, Analysis Services, and Reporting Services.

  • Use the /allinstances switch to upgrade all SQL Server 2005 components and instances.

  • When you use the /instancename switch, the only the components that belong to the specified instance will be upgraded. Client components and components that are not instance-specific, such as Integration Services and Notification Services, are not upgraded.

  • The following components are always upgraded during an unattended installation:

    • SQL Server Native Client

    • Microsoft Core XML Services (MSXML) 6.0

    • SQLXML 4.0

    • SQL Server 2005 Backward Compatibility Components

    • Microsoft SQL Server VSS Writer

  • Run unattended installations using Windows Authentication whenever possible.

  • To view the results of an unattended installation, review the Hotfix.log file in %windir%\Hotfix\.

[Top]

4.3.2 Unattended Installation Examples

The following procedures show command prompt syntax to apply SP1 in typical unattended mode scenarios. Modify and test these examples to meet the needs of your organization.

Security Note:
When possible, supply security credentials at run time. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

To upgrade all qualifying components and instances of SQL Server 2005:
  1. Open a command prompt window and navigate to the location of the self-extracting SP1 package file.

  2. Type the following at the command prompt and press ENTER:

    SQLServer2005SP1-KB913090-XXX-LLL.exe /quiet /allinstances
To upgrade a specified SQL Server 2005 instance:
  1. Open a command prompt window and navigate to the location of the self-extracting SP1 package file.

  2. Type the following at the command prompt and press ENTER:

    SQLServer2005SP1-KB913090-XXX-LLL.exe /quiet /instancename=<InstanceName>
    Note:
    InstanceName specifies the target instance, and instance names are case sensitive.

To upgrade only shared components and components that are not instance-specific:
  1. Open a command prompt window and navigate to the location of the self-extracting SP1 package file.

  2. Type the following at the command prompt and press ENTER:

    SQLServer2005SP1-KB913090-XXX-LLL.exe /quiet 
To upgrade all qualifying instances of SQL Server 2005 on a failover cluster:
  1. Open a command prompt window and navigate to the location of the self-extracting SP1 package file.

  2. Type the following at the command prompt and press ENTER:

    SQLServer2005SP1-KB913090-XXX-LLL.exe /quiet /allinstances /user=<Domain\UserName> /password=<Password>

    Domain\Username is the login used to connect to all nodes of the cluster, and Password is the password for the specified login.

To upgrade all qualifying components and instances using SQL Server Authentication:
  1. Open a command prompt window and navigate to the location of the self-extracting SP1 package file.

  2. Type the following at the command prompt and press ENTER:

    SQLServer2005SP1-KB913090-XXX-LLL.exe /quiet /allinstances /SAPWD=<SaLoginPassword>

    SaLoginPassword is the password for the sa login account.

To enumerate all SQL Server 2005 components and instances without installing SP1:
  1. Open a command prompt window and navigate to the location of the self-extracting SP1 package file.

  2. Type the following at the command prompt and press ENTER:

    SQLServer2005SP1-KB913090-XXX-LLL.exe /reportonly
Note:
In these examples, XXX indicates the specific platform and LLL represents a designator that varies by language.

[Top]

4.3.3 Command Prompt Reference

The following command prompt switches are supported for unattended installation of SP1.

Security Note:
When possible, supply security credentials at run time. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

Switch Description

/?

Displays unattended installation command prompt help.

/allinstances

Applies SP1 to all SQL Server 2005 instances, to all SQL Server 2005 shared components, and to components that are not instance-specific.

/instancename=InstanceName

Applies SP1 to an instance of SQL Server 2005 named InstanceName.

/norestart

Prevents Setup from restarting the computer following installation, if Setup determines that a reboot is required.

/password=Password

Passes the remote administrator password.

/quiet

Runs SP1 Setup in unattended mode.

/reportonly

Only the discovery and enumeration portion of Setup is run, and a report enumerating the local instances and components of SQL Server 2005 is displayed.

Note:
When using this switch, SP1 is not applied.

/rsupgradedatabaseaccount=rsAccountName

Specifies an account with permissions to modify the schema on a report server database when using SQL Server Authentication. This parameter is used when upgrading an instance of Reporting Services that includes a report server database that requires a SQL login. For more information, see 3.4.2 Upgrading a Remote Report Server Database.

/rsupgradepassword=rsPassword

Specifies the password for an account with permissions to modify the schema on a remote report server database. This parameter is used when upgrading an instance of Reporting Services that includes a remote report server database.

/sapwd=SaLoginPassword

Passes the SQL Authentication password for the sa login.

Note:
Do not escape characters for /sapwd. Escaping characters will cause Setup to fail.

/user=Domain\UserName

Passes the remote administrator domain name and user name.

[Top]

5.0 Documentation Notes

This section covers significant issues that can occur after applying SQL Server 2005 SP1 and any late-breaking items that are not reflected in the updated SQL Server 2005 Books Online documentation. For more information, see Section 1.5 Updates to SQL Server 2005 Books Online Are Available. This section does not describe all of the fixes provided in SP1. For a complete list of these fixes, see Microsoft Knowledge Base article 913090.

[Top]

5.1 Database Engine

The following enhancements apply to instances of the Database Engine on which SP1 is installed.

[Top]

5.1.1 Unique Nonclustered Indexes Can Be Created Online

Unique nonclustered indexes can now be created online. The following statements are affected:

  • CREATE UNIQUE NONCLUSTERED INDEX

  • CREATE UNIQUE NONCLUSTERED INDEX WITH DROP_EXISTING

  • ALTER TABLE ADD CONSTRAINT PRIMARY KEY NONCLUSTERED

  • ALTER TABLE ADD CONSTRAINT UNIQUE NONCLUSTERED

For more information about the online index operations that are supported, see an updated version SQL Server 2005 Books Online, beginning with the April 2006 release. For more information, see 1.5 Updates to SQL Server 2005 Books Online Are Available.

[Top]

5.1.2 DBCC Error Reporting

In SQL Server 2005 SP1, a mini-dump file (SQLDUMPnnnn.txt) is created in the SQL Server LOG directory whenever a DBCC CHECKDB, DBCC CHECKALLOC, DBCC CHECKFILEGROUP, or DBCC CHECKTABLE command detects a corruption error. When the Feature Usage data collection and Error Reporting features are enabled for the instance of SQL Server, the file is automatically forwarded to Microsoft. The collected data is used to improve SQL Server functionality. For more information, see "Error and Usage Report Settings" in SQL Server 2005 Books Online.

The dump file contains the results of the DBCC command and additional diagnostic output. The file has restricted discretionary access-control lists (DACLs). Access to the dump file is limited to the SQL Server service account and members of the sysadmin role. By default, the sysadmin role contains all members of the Windows BUILTIN\Administrators group and the local administrator's group. The DBCC command does not fail if the data collection process fails.

[Top]

5.1.3 Change to the Behavior of Uncommittable Transactions in a Batch

When a batch is completed, the Database Engine automatically rolls back any active uncommittable transactions. Before the release of SP1, uncommittable transactions had to be rolled back manually.

[Top]

5.1.4 SQL Server Agent Job Steps That Use Tokens Fail

In SQL Server 2005 Service Pack 1, the SQL Server 2005 Agent job step token syntax has changed. As a result, you must insert an escape macro with all tokens used in job steps, or else those job steps will fail with the following error message:

"The job step contains one or more tokens. For SQL Server 2005 Service Pack 1 or later, all job steps with tokens must be updated with a macro before the job can run."

This is a change from previous SQL Server 2005 behavior, which did not require escape macros with tokens. For more information about updating your jobs to use the new syntax and using escape macros, see "Using Tokens in Job Steps" in SQL Server 2005 Books Online (April 2006) or later. For more information, see Section 1.5 Updates to SQL Server 2005 Books Online Are Available.

[Top]

5.2 Integration Services

The following enhancement applies to Integration Services when SP1 is installed.

[Top]

5.2.1 DCOM Permissions are Reset

Installation of this service pack resets DCOM permissions for launching and accessing Integration Services service. The permissions are reset to default permissions. If you have customized DCOM permissions, you will need to reapply the customization.

To review the start and access permissions:
  1. In Control Panel, select Administrative Tools, then Component Services.

  2. In the Component Services snap-in, expand Component Services, Computers, MyComputer, and DCOM Config.

  3. Locate the MsDtsServer entry in list, right-click it, and then click Properties.

  4. Click the Security tab and review the permissions. To customize permissions, click Edit.

  5. Click OK.

[Top]

5.2.2 Integration Services Configuration File Location May Change

Installing SP1 resets the value of the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile to its default value. This registry key specifies the location of the configuration file for the Integration Services service (MsDtsSrvr.ini.xml). If you have updated the value of this registry key to a location other than the default location, you will need to update the value of the registry key again after applying the service pack.

Caution:
Incorrectly editing the registry can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from editing the registry incorrectly can be resolved. Before editing the registry, back up any valuable data. For information about how to back up, restore, and edit the registry, see Microsoft Knowledge Base article 256986.

[Top]

5.2.3 Previously Successful Column Conversions May Fail

After you apply SP1, column conversions may fail in the following cases:

Conversions from String to Unsigned Integer

If you have negative numbers in string columns (DT_STR or DT_WSTR) and convert the column data type to an unsigned integer data type (DT_UI1, DT_UI2, DT_UI4 or DT_UI8) the package previously ran successfully, and the negative values were converted to 0. After you apply SP1, the package no longer converts negative values to 0, but instead returns an error and the package may fail. This change in behavior affects the Lookup transformation, Fuzzy Lookup transformation, Fuzzy Grouping transformation, and the Flat File source.

Conversions from String to Binary

If you have characters in string columns (DT_STR or DT_WSTR) that are not convertible to a byte array, and you convert the column data type to the DT_BYTES data type, data might be silently corrupted; that is, the value of the column is converted to an array of zeroes. The installation of SP1 adds the UseBinaryFormat property to the Flat File source to help interpret binary data. After you apply SP1, when UseBinaryFormat is set to false (the default value), if a column contains characters that cannot be converted, the conversion generates an error, and packages that previously ran successfully now may fail. This change in behavior affects the Flat File source.

[Top]

5.2.4 Connections in Packages with Complex Data Flows Might Time-Out

Beginning with the SP1 release, Data Flow components in SSIS are initialized differently. In earlier releases, Data Flow components were initialized by first calling the AquireConnections method, and then calling the PreExecute method for each associated connection. In this release, AquireConnections is called for all associated connections before calling PreExecute. The new initialization process enables more robust transaction enlistment and thus more resilient data integration.

In some cases, the new initialization process can cause a time-out error to occur. For example, complex Data Flow tasks can contain many components that use connections such as multiple sources, destinations, and lookups. Components at the end of the list cannot use their associated connections until all the previous components’ PreExecute calls complete. As a result, under certain package designs, a package may experience a longer delay between the acquisition and first use of a connection. This longer delay may cause some connections with time-outs to expire, resulting in package failures.

To correct this problem, simply increase the value of the Timeout property for the failing connections to allow for the additional initialization time. Performance of the package after initialization is not affected.

[Top]

5.3 Data Access

The following issue applies to data access components delivered with SP1.

[Top]

5.3.1 Connection String Attributes for Database Mirroring

The April 2006 update to SQL Server 2005 Books Online incorrectly documents several connection string attributes that support database mirroring. The following table indicates the correct connection string attributes to use with the SQL Native Client providers:

Provider Attribute

SQL Native Client ODBC driver

Failover_Partner

SQL Native Client OLE DB provider

FailoverPartner

Note:
For the OLE DB provider, FailoverPartner is used with the DBPROP_INIT_PROVIDERSTRING property.

[Top]