To download and install SQL Server 2005 SP2, your computer must have 1.9 GB of available disk space. The following table describes the disk space required by the Setup components:

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

[Top]

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

SQL Server 2005 SP2 includes support for Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition), previously called Microsoft SQL Server 2005 Mobile Edition (SQL Server Mobile). For more information about SQL Server Compact Edition, see SQL Server .

Every SQL Server product (including Service Packs) has its own version number. The version number of an instance of SQL Server 2005 can be viewed two ways:

• In SQL Server Management Studio Object Explorer when connected to the instance.
  
  
• By executing the query SELECT SERVERPROPERTY('ProductVersion') against a Database Engine instance.
  
  

The following table shows the version numbers that correspond to versions of SQL Server 2005:

[Top]

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]

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. If you run Setup from the command prompt and specify /rsupgradedatabaseaccount and /rsupgradepassword for an account with permission to modify the schema on the remote computer, the database upgrade will also 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 SP2, 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]

If you use Add or Remove Programs to repair or modify SQL Server components after SP2 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)
  
  
• SQL Server Native Client (sqlncli.msi)
  
  
• Backward Compatibility (SqlServer2005_BC.msi)
  
  
• SQL Server VSS Writer (SqlWriter.msi)
  
  

[Top]

When you apply SP2, 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 SP2, you must disable these restrictions and then re-run Setup.

[Top]

Note the following information before you install SP2 on a failover cluster instance:

• Do not stop the cluster service before you run the SP2 Setup program, or while the installation program is running.
  
  
• Do not end any running processes before you run the SP2 Setup program.
  
  
• Do not take the SQL Server service off line before you run the SP2 Setup program. The SP2 Setup program will stop and start the SQL Server service.
  
  
• Run the SP2 Setup program on the primary node of the failover cluster instance.
  
  
• You must restart all failover cluster nodes after SP2 is finished installing.
  
  
• Rolling upgrade is not supported for SP2.
  
  

[Top]

Note the following changes to the SQL Writer service default startup type and default state:

• The SQL Writer service startup type is changed from Manual to Automatic.
  
  
• The SQL Writer service default state is changed from Stopped to Started.
  
  

[Top]

You cannot install SQL Server 2005 Service Pack 2 on an instance of SQL Server 2005 that is configured to use an NTFS junction point to store SQL Server data files. If you configure data files of an instance of SQL Server 2005 to be stored in an NTFS junction point that is created by using the linld.exe utility, installation of this SQL Server service pack will fail. Additionally, the NTFS junction point will be deleted after the installation. Therefore, you will not be able to start the SQL Server service after the installation. This issue occurs because Windows Installer removes the NTFS junction point during installation of the SQL Server service pack.

[Top]

Introduced in SQL Server 2005 Service Pack 1 (SP1).

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, see Updates to SQL Server 2005 Books Online.

[Top]

Introduced 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]

Introduced in SQL Server 2005 SP1.

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

[Top]

Introduced in SQL Server 2005 SP1.

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 SP1 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 about updates to Books Online, see Updates to SQL Server 2005 Books Online.

[Top]

Introduced in SQL Server 2005 SP2.

The following error message is returned if you use a tool from a previous version to open an SP2 maintenance plan created with the maintenance plan designer:

"This maintenance plan has been modified using a tool other than the maintenance plan designer. You must use SQL Server Integration Services to modify this maintenance plan from now on."

To resolve this issue, we recommend that you upgrade your tools to SQL Server 2005 SP2. We also recommend using the maintenance plan designer to modify the plan, rather than Integration Services as mentioned in the error message.

[Top]

Introduced in SQL Server 2005 SP2.

To prevent nondeterministic behavior, the OUTPUT clause does not allow the use of subqueries and user-defined functions that perform user or system data access, or are assumed to perform such access. A user-defined function is assumed to perform data access if it is not schema-bound.

[Top]

Introduced in SQL Server 2005 SP1.

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 must reapply the customization.

[Top]

Introduced in SQL Server 2005 SP1.

SQL Server SP2 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 had changed the value of this registry key from that of the default location, you must update it again after applying the service pack.

[Top]

Introduced in SQL Server 2005 SP1.

After you apply SQL Server SP2, column conversions may fail in the following cases:

Introduced in SQL Server 2005 SP1.

Data Flow components in Integration Services are now initialized differently. In earlier releases, Data Flow components were initialized by first calling the AcquireConnections 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 to time out, resulting in package failures.

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

[Top]

Introduced in SQL Server 2005 SP2.

Integration Services packages use Microsoft Visual Studio for Applications (VSA) functionality to run the custom code that Script components and Script tasks contain. Critical security updates released for the .NET Framework 2.0 results in a breaking change that causes a binary incompatibility between packages that contains scripts and the VSA execution environment. For more information, see the Microsoft Knowledge Base article, 928365.

If this incompatibility occurs at run time, Integration Services tries to identify and work around all instances of this problem so that scripts can run normally. However, in rare cases, the scripts may not run, and you may have to manually recompile the scripts. For more information, see the Microsoft Knowledge Base article, 931846. If the computer is running Windows Vista, you may also have to install a hotfix as described in the Microsoft Knowledge Base article, 928208.

[Top]

Introduced in SQL Server 2005 SP2.

To support all of the business intelligence features in Microsoft Office 2007 when connecting to an instance of Analysis Services, you must have SP2 installed. If SP2 is not installed, features of Office 2007 that require SP2 are disabled when running against an instance of Analysis Services.

[Top]

Introduced in SQL Server 2005 SP2.

Analysis Services now requires that all dimensions contained in a linked measure group have the same granularity as the dimension in the source measure group. For example, if a time dimension in the source measure group has the day attribute as the granularity attribute, the linked measure group must also use the day attribute as the granularity attribute. The most common scenario for an Analysis Services database that violates this rule is a SQL Server 2000 Analysis Services database that was migrated by using a pre-release version of SQL Server 2005.

If you have a database that violates this granularity rule, you must modify the database containing the linked measure group before applying SP2. If you don't, the database will become invalid after SP2 is applied. These invalid databases must be deleted by using the command line or Windows Explorer to delete the database folder within the data folder.

[Top]

Introduced in SQL Server 2005 SP2.

SP2 introduces the following Multidimensional Expressions (MDX) behavior changes:

• The coordinates of cells on the column and row axes are now merged with the coordinates of single tuple dimension members that are contained in a WHERE clause. For example, SELECT [USA] FROM [Cube] WHERE [Seattle] now returns the cell value for Seattle rather than for USA. Queries containing multiple dimension members from a given attribute or hierarchy in the WHERE clause and on a column or row axis are not affected by SP2 and may return unexpected results; this is behavior is subject to future change.
  
  
• Queries that contain calculations that overwrite coordinates that are defined in a subselect no longer retain visual totals. For example, WITH MEMBER Measures.ParentContribution AS (Measures.Sales, Geography.Currentmember.Parent) / Measures.Sales SELECT Measures.ParentValue ON COLUMNS, Geography.Members ON ROWS FROM (Select Geography.USA on 0 from Sales) now returns the contribution to the parent without visual totals.
  
  

[Top]

Introduced in SQL Server 2005 SP2.

Due to backward and forward compatibility changes in the Analysis Services protocol and file format in SP2, Analysis Services SP2 data folders and backups cannot be loaded by Analysis Services SP1 servers and server sync cannot be performed between Analysis Services SP2 servers and Analysis Services SP1 servers. This change was introduced in SQL Server 2005 SP1 QFE 251.

[Top]

Introduced in SQL Server 2005 SP2.

When viewing reports that have expandable content in Internet Explorer 7.0, the Back button does not load previously viewed pages.

[Top]

On a report server that runs in SharePoint integrated mode, you might encounter an ASP.NET time-out error for long-running reports that require more processing time than the default time out specifies. By default, the ASP.NET time out is 2 minutes. Reports requiring more than two minutes to process will quit unexpectedly, and the Windows SharePoint Services error page will appear with the following error: "An unexpected error has occurred."

If you think this error is due to a time-out issue, you can work around the error by adding an httpRuntime executionTimeout attribute to the Web.config file of the SharePoint Web application.

Be sure to provide a value that is sufficient to complete processing for long-running reports. For a native mode report server, the default value is 9000 seconds for a report server and Report Manager. Consider choosing a similar value when specifying executionTimeout for the SharePoint Web application.

If you have deployed multiple applications in a SharePoint farm, repeat the above steps for each Web.config file in the farm. This requirement applies to all of the SharePoint Web application Web.config files in the subdirectories under <drive>:\inetpub\wwwroot\wss\VirtualDirectories. It also applies to Web.config files for applications in the farm that are installed on different computers.

[Top]

SQL Server 2005 SP2 introduces a newer version of the Reporting Services Configuration tool. If you install SP2, you must use the SP2 version of the Reporting Services Configuration tool to configure the report server. In addition, you cannot use the SP2 version of the Reporting Services Configuration tool to configure a pre-SP2 report server. This incompatibility occurs because the Reporting Services Configuration tool uses a new version of the WMI provider, which is not backward compatible. If you attempt to use the Reporting Services Configuration tool with an unsupported version, the following error will occur: "The WMI Provider has returned an unspecified status at ReportServicesConfigUI.WMIProvider.ConfigurationItem.CreateConfigurationItem."

[Top]

If you configure the report server virtual directory and Report Manager is not installed, the Reporting Services Configuration tool will close unexpectedly and show the following error: “SQL Server 2005 Reporting Services Configuration tool has encountered a problem and needs to close.” The virtual directory is actually created, despite the error message. The error only occurs once. When you re-open the tool, you can finish server configuration without encountering the error again.

[Top]

If you are deploying and integrating a report server with a SharePoint server farm, you must use the same versions of the SharePoint product or technology on all servers in the farm, including the computer that hosts the report server. You cannot have a combination of Windows SharePoint Services 3.0 and Office SharePoint Server 2007 in the same server farm.

[Top]

The type of credentials that are specified on the report server database connection will determine whether Setup can upgrade a report server database from an SP1 or CTP version of SP2 to the final release of SP2.

If the report server connects to the report server database using database credentials or a Windows user account that differs from the one used to run the Report Server Windows service, the database will not be upgraded when the rest of the software is upgraded. If this occurs, you must use the Reporting Services Configuration tool to upgrade the report server database manually. On the Database Setup page, click Upgrade to apply the SP2 format to the report server database.

[Top]

You cannot integrate two or more report server instances that share the same report server database with different stand-alone server deployments of Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007. Doing so will eventually cause data corruption in the back-end report server database. The following scenario is unsupported: two independent installations of Windows SharePoint Services 3.0, each one integrated with a local report server instance, where both report server instances share the same report server database.

[Top]

If you upgrade your edition of SQL Server 2005 Reporting Services (for example, Standard Edition to Enterprise Edition), the Windows service identity settings in the Reporting Services Configuration tool will appear to be invalid.

To repair the settings, start the Reporting Services Configuration tool, click Windows Service Identity, re-select the account (you can choose the same account), and click Apply.

[Top]

In this release, Microsoft Visual Studio for Applications (VSA)—the scripting development environment and run-time engine that the Script task and Script component in Integration Services use—is incompatible with the final version of Windows Vista. If a computer is running the final version of Windows Vista, you cannot use that computer to edit or debug scripts in Integration Services, nor run Integration Services packages that contain scripts.

[Top]

Windows Vista includes a new feature, User Account Control (UAC), that helps administrators manage their use of elevated privileges. When running on Windows Vista, administrators do not use their administrative privileges by default. Instead they perform most actions as standard (non-administrative) users, temporarily assuming their administrative privileges only when necessary.

UAC causes some known issues. For more information, see the following Web pages on TechNet:

• Windows Vista: User Account Control
  
  
• Windows Vista User Account Control Step by Step Guide