Analysis Services

Database Engine

Integration Services

Replication

Reporting Services

Shared Tools

• The 2007 version of Microsoft Office requires the installation of SQL Server 2005 Analysis Services SP2 to support all its business intelligence features. If you run against an instance of Analysis Services that does not have SP2 installed, features of Microsoft Office that require SP2 will be disabled.
  
• The functionality of local and session cubes has been greatly improved.
  
• Performance and scalability has been improved with regard to subselects, running sum calculations, NUMA optimizations for partition processing, partition query scalability, ragged hierarchies, visual totals, ROLAP dimensions, cell writeback, many-to-many dimensions, drillthrough, semi-additive measures, unary operators, and stored procedures.
  
• A warning message now appears when a user-defined hierarchy is not defined as a natural hierarchy.
  
• The MDX Drilldown* functions have a new argument that lets you specify drill down on specified tuples only.
  
• The SCOPE_ISOLATION property has been added to the MDX CREATE method.
  This property enables session-scoped and query-defined calculations to be resolved before calculations in the cube, rather than after.
  
• Numerous functionality and performance-related improvements have been incorporated. Specifically, improvements have been made to incremental processing, usage-based aggregation design algorithms, backward and forward compatibility, parent-child security, partition query scalability, cell writeback, and the Time Intelligence Wizard.
  
• The performance and functionality of the neural network viewer has been improved, and support for multiple nested tables has been added.
  
• The performance of naïve bayes predictions have been improved through caching of commonly used attributes.
  
• Neural network training has been improved through better utilization of memory, with sparse training data sets and better utilization of multiple threads during error computation (SQL Server 2005 Enterprise Edition feature).
  
• Limited support for data mining viewers with local mining models has been added.
  
• The redistribution of data mining viewer controls is now dependent upon ADOMD.NET.
  The new redist file will be available in a feature pack that will ship soon after Service Pack 2.
  

Important:
For more information about SP2 in the Microsoft OLAP blog by Mosha Pasumansky, see the Additional Resources section on the SQL Server 2005 – Analysis Services page on the Microsoft TechNet Web site.

Back to Top

• Maintenance plans are now supported by the SQL Server Database Services installation. Before SP2, you were required to install SQL Server 2005 Integration Services (SSIS) to run maintenance plans on a server-only installation.
  
• Maintenance plans now support multiserver environments, logging to remote servers, and multiple schedules. For more information, see How to: Create Multiserver Maintenance Plans, How to: Create a Maintenance Plan, and How to: Add or Modify Maintenance Plan Subplan Schedules.
  
• Added new functionality in the SQL Server 2005 Enterprise Edition to provide another storage format that can be used to minimize the disk space that is needed to store existing decimal and numeric data types. No application changes are required to use its benefits. This new storage format, known as vardecimal storage format, stores decimal and numeric data as variable length columns and can be enabled or disabled at a table level on new or existing tables. If you have declared a decimal or numeric column with high precision but most values in the column do not require many digits of precision, you can potentially save a large amount of the disk space that is needed to store the table. A new stored procedure is provided to estimate the reduction in average row size with the new storage format. For more information, see Storing Decimal Data As Variable Length.
  
• Added logon triggers and a common criteria compliance enabled Option for sp_configure to support Common Criteria in the SQL Server 2005 Enterprise Edition. For more information, see Common Criteria Certification.
  
• The sqllogship application is now supported. This application performs a backup, copy, or restore operation, and associated clean-up tasks for a log shipping configuration.
  
• Plan cache improvements that provide improved system performance, better use of the available physical memory for database pages, and the ability to return text XML query plans that contain an XML nesting level greater than or equal to 128 by using the new sys.dm_exec_text_query_plan table-valued function. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• SMO for Relational Engine Features
  
  • Table.CheckIdentityValue() correctly generates the schema name for the fully qualified object name. This feature is supported in SQL Server 2005 Express Edition SP2.
    
  • Column.AddDefaultConstraint() works against table columns for SQL Server 2000 database instances. This feature is supported in SQL Server 2005 Express Edition SP2.
    
• Added five columns to sys.dm_exec_sessions for Common Criteria compliance: original_security_id, original_login_name, last_successful_logon, last_unsuccessful_logon, and unsuccessful_logons.
  
• Added the parameter database_id to the OBJECT_NAME syntax. Added the built-in function OBJECT_SCHEMA_NAME (Transact-SQL).
  

Back to Top

• You can now troubleshoot a package's interaction with external data sources by enabling logging and selecting the package's Diagnostic event for logging. Many package failures occur during interaction with external data providers. However, those providers often do not return messages to Integration Services that provide enough information to begin troubleshooting. The Integration Services components listed below have now been instrumented to write a message to the log before and after every call to an external data provider. This message includes the name of the method being called; for example, the Open method of an OLE DB Connection object, or the ExecuteNonQuery method of a Command object. You can view these messages by enabling logging and selecting the package's Diagnostic event.
  
  • OLE DB Connection Manager, Source, and Destination
    
  • ADO.NET Connection Manager and DataReader Source
    
  • Execute SQL Task
    
  • Lookup Transformation
    
  For more information, see Troubleshooting Package Execution.
  
• Integration Services and the SQL Server Import and Export Wizard now support the Microsoft Office 12.0 Access Database Engine OLE DB Provider for connectivity to Microsoft Office Access 2007 and Excel 2007 data sources.
  You cannot use the Microsoft Jet OLE DB Provider to connect to Access 2007 and Excel 2007 data sources. To connect to Excel 2007 data sources, you must use the OLE DB Connection Manager and the OLE DB Source and Destination; to connect to Excel 2003 and earlier data sources, you continue to use the Excel Connection Manager and the Excel Source and Destination.
  For more information about connection to Excel data sources, see Excel Connection Manager.
  
• The value of the BypassPrepare property of the Execute SQL task has been changed to True by default.
  In earlier versions, the value of the BypassPrepare property was false, which indicated that statements were always prepared. In SP2, by default queries are not prepared. This eliminates errors with certain providers when you try to prepare a statement that uses parameter placeholders ("?").
  For more information about the Execute SQL task, see Execute SQL Task.
  
• You can now use a combo box to select variables for properties of certain data flow components that require a variable name.
  In earlier versions, you had to type a variable name as free text to provide a value for properties that expected a variable name, such as the OpenRowsetVariable property of the OLE DB Source. The combo box now contains all the available variables, including system variables.
  
• The Lookup transformation now reports the final count of cached rows.
  In full cache mode, the Lookup transformation uses a function activated by a timer to report the count of rows that have been cached. In previous versions, the function did not always report the final count of cached rows. In SP2, the Lookup transformation provides a new informational message that reports the final number of rows that were cached in full-cache mode. This message appears in the Progress window and is logged by the OnInformation event.
  For information about the Lookup transformation, see Lookup Transformation.
  
• The Execute SQL task now has a ParameterSize property for string parameters.
  In earlier versions, the Execute SQL task failed when using an ADO.NET connection to execute a stored procedure that returned a string output parameter, because the size of the string parameter was set to 0 by default. In SP2, parameters used with the Execute SQL task have a ParameterSize property that can be used to allocate the memory required for a string output parameter. The Script component now raises an error when a <columnname>_IsNull property is set to True.
  
• The IsNull property of columns in the Script Component now raises a warning when it is used incorrectly.
  The code generated by the Script component contains a <columnname> and a <columnname>_IsNull property for each input and output column. The intention of the <columnname>_IsNull property is to allow the user to specify a Null value for the column by setting the value of the property to True. To specify a non-null value, the user should assign a value to the <columnname> property. In earlier versions, if you set <columnname>_IsNull to False, the property was silently reset to True. In SP2, the Script component raises a warning message about the incorrect usage.
  

Back to Top

• In Enterprise Edition, you can now initialize subscriptions to snapshot and transactional publications by using a database snapshot. To do this, specify a value of database snapshot or database snapshot character for the sync_method publication property. Database snapshots provide the foundation for a robust snapshot processing mechanism that can reduce lock contention at the publication database during snapshot generation. For more information, see sp_addpublication.
  
• Merge replication now provides a stored procedure that regenerates the triggers, stored procedures, and views that are used to track data changes. For more information, see sp_vupgrade_mergeobjects.
  

Back to Top

• You can integrate a report server instance with Windows SharePoint Services 3.0 or Microsoft Office 2007 SharePoint Server to store, secure, access, and manage report server items from a SharePoint site. Integration features are provided jointly through SP2 and a special Reporting Services Add-in that you download and install on an instance of the SharePoint technology you are using.
  The new Report Viewer Web Part is included in the Reporting Services Add-in that you install on a SharePoint technology instance. For more information about the Web Part and other integration features, see Reporting Services and SharePoint Technology Integration and Features Supported by Reporting Services in SharePoint Integration Mode.
  
• In SQL Server 2005 Reporting Services (SSRS), a Select All check box was automatically added when you created an available values list for a multivalue report parameter. If you upgraded to SQL Server 2005 Service Pack 1 (SP1), the Select All check box was no longer available. In SQL Server 2005 Service Pack 2 (SP2), the Select All check box has been restored. This feature is supported in SQL Server 2005 Express Edition SP2.
  For more information, see Breaking Changes in SQL Server 2005 Reporting Services.
  
• To access a Hyperion System 9.3 BI+ Enterprise Analytics data source, Reporting Services now provides the Microsoft .NET Data Provider for Hyperion Essbase. This new data processing extension provides a graphical query designer that enables you to interactively build Multidimensional Expressions (MDX) queries. The Microsoft .NET Data Provider for Hyperion Essbase sends these MDX queries to the Hyperion System 9 BI+ Analytic Provider Services 9.3, which builds the structures necessary to query and retrieve data from a Hyperion Essbase OLAP repository. The Microsoft .NET Data Provider for Hyperion Essbase requires Hyperion System 9.3 Beta 2. You must install Hyperion System 9.3 Beta 2 on the data source server before you can use Hyperion Essbase as a data source. For information about how to obtain Hyperion System 9.3 Beta 2, contact hyperion@microsoft.com.
  For more information, see Defining Report Datasets for Multidimensional Data from a Hyperion Essbase Database and Configuring Reporting Services to Use the Microsoft .NET Data Provider for Hyperion Essbase.
  
• Report model generation from Oracle data sources that run on version 9.2.0.3 or later is supported. You can generate Oracle-based models by using Report Manager, Management Studio, or Model Designer.
  The Oracle client must be installed on the report server and on any client computers that will access the report server remotely. The Oracle client directory must be located in the system path and both the Report Server Windows service and the Report Server Web service must have permissions to access the files in this directory.
  For more information, see Creating and Using Oracle-Based Report Models in SQL Server Books Online.
  

Back to Top

Maintenance Plans

Management Studio Reports

Scripting

Database Mail

Copy Database Wizard

SQL Server Management Studio Express

Service Pack Setup and Hotfix Installer

Miscellaneous

Concepts

Help and Information