Breaking Changes to Database Engine Features in SQL Server 2005

This topic describes the changes made to the Database Engine in Microsoft SQL Server 2005 that could cause applications based on earlier versions of SQL Server to break.

Client/Server Connectivity

Feature	Description
Banyan VINES Sequenced Packet Protocol (SPP), Multiprotocol, AppleTalk, or NWLink IPX/SPX network protocols	SQL Server 2005 does not support the Banyan VINES Sequenced Packet Protocol (SPP), Multiprotocol, AppleTalk, or NWLink IPX/SPX network protocols. To connect to SQL Server 2005, client applications must use a supported protocol. If an alias is set up that uses one of the unsupported protocols, the alias must be modified to use one of the supported protocols. If an application connection string specifically uses or loads one of the unsupported protocols, by either specifying NETWORK=DBMSRPCN for RPC, NETWORK=DBMSADSN for Appletalk, or NETWORK=DBMSVINN for Banyan VINES property, or by using an explicit prefix such as spx:server\instance for SPX, bv:server for Banyan VINES, adsp:server for AppleTalk, or rpc:server for multiprotocol, then you must modify your application to use one of the supported protocols. For more information, see Choosing a Network Protocol.
MDAC	Versions of MDAC earlier than MDAC 2.6 do not support named instances. To allow application connections to named instances, upgrade to the current version of MDAC.
Winsock proxy	The Winsock proxy cannot be configured by using SQL Server tools. For information about how to configure the Winsock proxy, see the proxy server documentation.

Configuration Options

Feature

Description

AUTO_UPDATE_STATISTICS

Set AUTO_UPDATE_STATISTICS to ON before you upgrade any databases. Otherwise, database statistics are not updated as part of the upgrade to SQL Server 2005. Relying on statistics from an earlier version of SQL Server may cause suboptimal query plans. By setting AUTO_UPDATE_STATISTICS to ON, all statistics are updated when they are first referenced. Updating statistics increases the possibility that more optimal query plans are selected when you execute queries.

Note:

In some cases, after you set AUTO_UPDATE_STATISTICS to ON, the process of updating statistics may affect server performance of queries when statistics are first referenced.

To set the AUTO_UPDATE_STATISTICS database SET option to ON, use the ALTER DATABASE statement; or, to update statistics in the database, run sp_updatestats.

max server memory option

In SQL Server 2000, the SQL Server buffer pool can exceed the limit specified by the max server memory option if the system physical memory is available. In SQL Server 2005, the buffer pool cannot exceed the value of max server memory. When this limit is reached, the query fails with an "insufficient system memory" error.

If you receive this error and the max server memory option is set, increase the value of the option or reset the value to the default value of 2147483647. For more information, see Server Memory Options.

query governor cost limit option

Applying SET GOVERNOR_QUERY_COST_LIMIT or the query governor cost limit option of sp_configure may cause queries that run in an earlier version of SQL Server not to run in SQL Server 2005. This behavior occurs because of changes in query cost modeling.

Update the query governor cost-limit settings of the connection or server instance to an appropriate value, or set the value to 0 to specify no limit on the time period in which a query can run.

Databases, Data and Log Files

Feature	Description
Compressed drives	SQL Server 2005 cannot create or upgrade databases on compressed drives. When you install SQL Server 2005, select an uncompressed drive for system databases and verify that databases to be upgraded are not on compressed drives. However, note that after the database has been upgraded, you can put read-only databases and read-only secondary filegroups on an NTFS compressed file system.
Data files	Additional disk space is required for data files to handle the following changes: Additional system metadata is created and maintained in the PRIMARY filegroup of each user database for database objects and user permissions. For example, in earlier versions of SQL Server, the permissions associated with a grantor or grantee is stored in a single row as a bitmap. In SQL Server 2005, the bitmap is expanded into multiple rows. Large object (LOB) columns defined as text, ntext, or image data types require an additional 40 bytes of disk space per column. This one-time space increase occurs during the first update of each LOB column. The full-text document ID (DOCID) map is stored in the data file instead of in the full-text catalog. To make sure that resources can handle size increases during upgrade and subsequent production operations, we recommend setting autogrow to ON for all user data files before you upgrade to SQL Server 2005. After you have upgraded and tested your workloads, you may want to set autogrow OFF or adjust the FILEGROWTH increment accordingly. For more information, see ALTER DATABASE (Transact-SQL).
Database compatibility mode	When a database is upgraded to SQL Server 2005 from any earlier version of SQL Server, the database retains its existing compatibility level. If you change the compatibility mode to 90 after you upgrade, the differences in compatibility mode may affect your applications. For more information about these differences, see sp_dbcmptlevel (Transact-SQL).
Database ID 32767	In SQL Server 2005, this database ID is reserved. Detach the database before you upgrade.
Filegroups	All databases in the instance of SQL Server must have filegroups set to READ_WRITE before you upgrade to SQL Server 2005. To set the filegroup to READ_WRITE, use ALTER DATABASE.
Log files	In SQL Server 2005, additional disk space is required for transaction log files. During the undo phase of a crash recovery, SQL Server 2005 lets users access the database. This is possible because the transactions that were uncommitted when the crash occurred reacquire any locks they held before the crash. When transactions are being rolled back, their locks help protect them from interference by users. This additional locking information must be maintained in the transaction log. To make sure resources can handle size increases during upgrade and subsequent production operations, we recommend setting autogrow to ON for all user log files before upgrading to SQL Server 2005. After you have upgraded and tested your workloads, you may want to set autogrow OFF or adjust the FILEGROWTH increment accordingly. For more information, see ALTER DATABASE (Transact-SQL).
model database	In SQL Server 2005, the model database contains the following changes: Larger minimum size. Compatibility level is set to 90. PAGE_VERIFY database option is set to CHECKSUM.
tempdb database	Additional disk space is required for the tempdb data and log files in SQL Server 2005. To make sure resources can handle size increases during upgrade and subsequent production operations, we recommend setting autogrow to ON for all tempdb data and log files before upgrading to SQL Server 2005. After you have upgraded and tested your workloads, you may want to set autogrow OFF or adjust the FILEGROWTH increment accordingly. For more information, see Troubleshooting Insufficient Disk Space in tempdb.

Features

Feature

Description

Extended stored procedures

Extended stored procedures that are previously registered without the full path for the DLL name may not work after you upgrade to SQL Server 2005. This occurs because the old BINN directory is not added to the new path during the upgrade process. SQL Server may not be able to locate the extended stored procedures.

Before you upgrade to SQL Server 2005, follow these steps for each extended stored procedure that is not registered by using a full path name:

1. To remove the extended stored procedure, run sp_dropextendedproc.
2. To register the extended stored procedure with the full path name, run sp_addextendedproc.

Log shipping

Log shipping in earlier versions of SQL Server is incompatible with log shipping in SQL Server 2005 and cannot be upgraded directly. After you upgrade to SQL Server 2005, reconfigure log shipping by using SQL Server Management Studio or stored procedures. For more information, see Migrating a SQL Server 2000 Log Shipping Configuration to SQL Server 2005.

osql utility

The osql utility does not support the ED and !! commands. Remove references to the ED and !! commands from your scripts. To use the ED and !! commands, use the sqlcmd utility instead.

SQL-DMO WMI Provider

The SQL-DMO WMI Provider has been discontinued and is not available.

SQL Mail

SQL Server supports SQL Mail upgrade from SQL Server 7.0 or SQL Server 2000; however SQL Server 2005 requires Microsoft Outlook 2002, or a later version, as a mail client.

Note:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. To send mail from SQL Server 2005, use Database Mail.

SQL Mail

When a client that is connected by using SQL Server Authentication tries to send SQL Mail that contains an attachment, SQL Server cannot set an appropriate security context and will return an error. To avoid this problem, use Windows Authentication.

SQL Namespace API (SQL-NS)

The SQL Namespace API (SQL-NS) has been discontinued and is not available.

Trace flags

In SQL Server 2000, a trace flag set in session A does not automatically take effect in an already existing session B. Instead, the trace flag takes effect only after the first time any trace flag is set in session B. This behavior is nondeterministic in SQL Server 2000 and is deterministic in SQL Server 2005. In SQL Server 2005, global trace flags set in session A are set immediately in other concurrent sessions.

Also, in SQL Server 2005, trace flags can be specified as either local or global by using an additional argument in the DBCC TRACEON statement. If the second argument is not specified, the default value is local in SQL Server 2005. This is different from SQL Server 2000 in which the default is global.

For more information, see Trace Flags (Transact-SQL).

Trace flags

Some SQL Server 2000 trace flags do not exist in SQL Server 2005. Also, some trace flags have different functionality in SQL Server 2005. You should disable all trace flags before you upgrade to SQL Server 2005. After you upgrade, verify that the functionality of the trace flag has not changed. Also verify that the trace flag is still required before you reenable any trace flags.

Triggers

In SQL Server 2005, data definition language (DDL) statements, such as CREATE INDEX, cannot be performed on the inserted and deleted tables inside DML triggers. In earlier versions of SQL Server, some DDL statements can be performed on the inserted and deleted tables. For more information, see Using the inserted and deleted Tables.

Duplicate index names

In SQL Server 2005, duplicate table or view index names are not allowed. Rename the indexes to remove duplicates before you upgrade.

1. Locate the duplicate indexes by executing the following query:

   SELECT DISTINCT OBJECT_NAME(o.id), name
   FROM sysindexes as o
   WHERE EXISTS 
       (SELECT name FROM sysindexes  as i
         WHERE i.id = o.id
         AND i.name = o.name and i.indid < o.indid);
   

2. Use sp_rename to change one of the index names. Because the index names are the same, you cannot determine which index will be renamed. This step lets you differentiate the indexes.

   EXEC sp_rename N'table_name.index_name', N'new_index_name, N'INDEX'
   

3. Verify which index was renamed by executing the following query. The following query returns all indexes, including key column names on the specified table or view:

   SELECT i.name AS IndexName, c.name AS ColumnName, ik.colid, ik.keyno
   FROM sysindexes i
   JOIN sysindexkeys ik ON i.id = ik.id and i.indid = ik.indid 
   JOIN syscolumns c ON c.id = ik.id and ik.colid = c.colid
   WHERE i.id = OBJECT_ID('table_or_view_name')
   

4. If you have to, use sp_rename again to correct the index names.

Object names

In SQL Server 2005, you cannot use the 0xFFFF character in object names. An object name that contains this Unicode character cannot be accessed when the database is in database compatibility level 90. Rename objects that contain this character.

Table variables and column collation matching

In SQL Server 2000, columns defined in table variables are implicitly converted to the collation of the tempdb database. In SQL Server 2005, columns defined in table variables are implicitly converted to the collation of the current database. Queries that rely on SQL Server 2000 behavior may return unexpected results such as a different. number or order of returned rows.

For example, the equality comparison of columns c1 and c2 in the WHERE clause of the following SELECT statement may return fewer or more rows when the collation of the TestDB database is used rather than the tempdb collation. For example, the values 'Name' and 'name' would be evaluated as equal when the collation is case-insensitive, but not when the collation is case-sensitive.

CREATE DATABASE TestDB COLLATE Estonian_CS_AI;
GO
USE TestDB;
DECLARE @TempTable table (c1 varchar(10), c2 varchar(10);
SELECT * FROM @TempTable WHERE c1 = c2;

To use a collation other than the current database collation in a table variable, specify the collation in the definition of the columns in the DECLARE statement, or in the query that references the columns. The following example shows both methods.

USE TestDB;
DECLARE @TempTable table (c1 varchar(10)COLLATE Latin1_General_CS_AS, c2 varchar(10)COLLATE Latin1_General_CS_AS);
SELECT * FROM @TempTable WHERE c1 = c2;
GO
-- or

DECLARE @TempTable table (c1 varchar(10), c2 varchar(10));
SELECT * FROM @TempTable WHERE c1 = c2 COLLATE Latin1_General_CS_AS;
GO

Indexed Views

Feature	Description
Function determinism	The following function expressions are considered nondeterministic in SQL Server 2005 and, therefore, may interfere with indexed view creation: References to string literals that are implicitly converted to datetime and smalldatetime. Implicit conversion of non-Unicode character data between collations. Expressions that involve implicit conversion of character strings to datetime or smalldatetime are considered nondeterministic in SQL Server 2005, unless the compatibility level is set to 80 or earlier. This is because the results depend on the LANGUAGE and DATEFORMAT settings of the server session. For example, the results of the expression CONVERT (datetime, '30 listopad 1996', 113) depend on the LANGUAGE setting, because the string 'listopad' means different months in different languages. Similarly, in the expression DATEADD(mm,3,'2000-12-01'), SQL Server interprets the string '2000-12-01' based on the DATEFORMAT setting. Implicit conversion of non-Unicode character data between collations is also considered nondeterministic, unless the compatibility level is set to 80 or earlier. Creating indexes on views that contain these expressions is not allowed in database compatibility level 90. Although existing views that contain these expressions from an upgraded database are maintainable, the query optimizer will not consider them in query plans in either compatibility level 80 or 90. For information about setting the compatibility level, see sp_dbcmptlevel (Transact-SQL). In the definition of indexed views in SQL Server 2005, you should explicitly convert the literal to the preferred date type by using a deterministic date format style. For a list of which date format styles are deterministic, see CAST and CONVERT (Transact-SQL). If you use implicit string-to-date conversions in existing indexed views that have been upgraded to SQL Server 2005, you should be certain that the LANGUAGE and DATEFORMAT settings are consistent in your databases and applications to avoid possible indexed view corruption.
IGNORE_DUP_KEY	When you create a unique clustered index on a view in SQL Server 2005, the IGNORE_DUP_KEY option must be set to OFF. This is the default setting. Setting IGNORE_DUP_KEY to ON could lead to indexed view corruption. Drop the clustered index on the view and re-create it without specifying the IGNORE_DUP_KEY option.
Query hints	Query hints in the definitions of indexed views are ignored in compatibility level 80. This may cause some applications to behave differently between compatibility levels 80 and 90. For more information, see Designing Indexed Views, Creating Indexed Views, and Query Hint (Transact-SQL).

Security

Feature	Description
Login names	The following fixed server role names are reserved in SQL Server 2005 and cannot be used as user-defined login names: sysadmin serveradmin setupadmin securityadmin processadmin dbcreator diskadmin bulkadmin Before you upgrade to SQL Server 2005, follow these steps: Record the security identifiers (SIDs) of the logins by executing the following statement: SELECT name, sid FROM master.dbo.syslogins WHERE name IN('sysadmin', 'serveradmin','setupadmin', 'securityadmin','processadmin', 'dbcreator','diskadmin', 'bulkadmin') Drop the logins. To create new logins, use the sp_addlogin system procedure. In the @sid parameter for each corresponding login, specify the SID that is returned in step 1.
Login security identifier (SID)	In SQL Server 2005, duplicate security identifiers (SID) are not allowed. Remove one of the logins and associated users before you upgrade.
Remote login mapping	In earlier versions of SQL Server, logins that come from remote instances of SQL Server can be marked as trusted by using the sp_remoteoption system stored procedure. SQL Server 2005 does not support this method of labeling remote logins. After you upgrade to SQL Server 2005, remote logins will no longer be marked as trusted. For setting up and managing remote logins, use linked servers and linked server stored procedures. For more information, see Linking Servers.
SQL Server 6.5 logins	SQL Server 6.5 saves password hashes in a format that is no longer supported. The old password cannot be directly upgraded to SQL Server 2005. To enable this login, you must reset its password. You can reset the password by using ALTER LOGIN: ALTER LOGIN <login name> WITH PASSWORD = '<new password>' MUST_CHANGE The new password will be validated against the password complexity policy of your system, unless policy checking is disabled. We recommend that you use complex passwords and not disable policy checking. The MUST_CHANGE option forces the user to select a new password. This is not required, but it is recommended. You can identify dormant SQL Server 6.5 logins by using the following query: SELECT * FROM sysxlogins WHERE (xstatus & 2048) = 2048; GO
sys user name	The name sys is reserved in SQL Server 2005 and cannot be used as a user name. Rename the user before you upgrade to SQL Server 2005. If the user is not renamed, the database will be in a suspect state after the upgrade process and will be unavailable until the database is brought online. Before-Upgrade Procedure Before you upgrade to SQL Server 2005, in each database that contains user sys, do the following: Create a new user. Use the following statements to display all permissions that are granted by user sys and granted to user sys. -- Return permissions granted by user sys. SELECT * FROM sysprotects WHERE grantor = USER_ID('sys') -- Return permissions granted to user sys. SELECT * FROM sysprotects WHERE uid = USER_ID('sys') To transfer ownership of all objects that are owned by sys to the new user, use sp_changeobjectowner. Drop user sys. To restore the original permissions captured in step 2, use the AS new_user clause of the GRANT statement. Modify scripts to reference the new user. After-Upgrade Procedure If the user sys was not renamed before you upgraded, do the following: Execute the statement ALTER DATABASE db_name SET ONLINE. The database will be in SINGLE_USER mode. Follow all steps in the "Before-Upgrade Procedure" section. Execute the statement ALTER DATABASE db_name SET MULTI_USER.

System Objects and Metadata

Feature	Description
INFORMATION_SCHEMA.COLUMNS	In SQL Server 2005, the ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern that is returned by the COLUMNS_UPDATED function. To obtain a bit pattern that is compatible with COLUMNS_UPDATED, reference the ColumnID property of the COLUMNPROPERTY system function when you query the INFORMATION_SCHEMA.COLUMNS view, as shown in the following example: SELECT TABLE_NAME, COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID FROM AdventureWorks.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Contact';
INFORMATION_SCHEMA.SCHEMATA	In earlier versions of SQL Server, the INFORMATION_SCHEMA.SCHEMATA view returned all databases in an instance of SQL Server. In SQL Server 2005, the view returns all schemas in a database. This behavior complies with the SQL Standard. For more information, see SCHEMATA (Transact-SQL).
INFORMATION_SCHEMA column names that match the value '%SCHEMA'	In earlier versions of SQL Server, INFORMATION_SCHEMA column names that match the value '%SCHEMA' return the name of the user. In SQL Server 2005, these columns return the schema name. When a database is upgraded to SQL Server 2005, the schema name is always the same as the user name and any applications that reference these columns will not fail. However, users that implement SQL Server 2005 user-schema separation features in their databases should realize that their application may fail if the expected data is a user name instead of a schema name. For more information, see User-Schema Separation.
sp_helptrigger	SQL Server 2005 adds trigger_schema as the last column in the result set returned by the sp_helptrigger system stored procedure. Review the use of sp_helptrigger in applications. You may have to modify your applications to handle the additional column. Alternatively, you can use the sys.triggers catalog view.
syslockinfo and sp_lock	In SQL Server 2000, the rsc_objid and rsc_indid columns in syslockinfo and the objid and indid columns in sp_lock consistently return the object ID and index ID. In SQL Server 2005, a value of 0 may be returned. In SQL Server 2000, syslockinfo and sp_lock return a maximum of two rows for any specific lock resource in a single transaction. In SQL Server 2005, when lock partitioning is enabled, multiple rows for the same resource running under one transaction may be returned. There can be up to N + 1 rows returned, where N is the number of CPUs. Also, in SQL Server 2005, GRANTED and WAITING requests can be displayed for the same resource; in SQL Server 2000, these requests cannot be displayed for the same resource For more information, see sp_lock (Transact-SQL) and sys.syslockinfo (Transact-SQL).
System object name and system type name collation matching	In earlier versions of SQL Server, system object and system type names are matched against the collation of the master database. In SQL Server 2005, system object names and system type names are automatically cast to correspond to the collation of the current database. If references to these objects in your script or applications do not match how they appear in the catalog and the current database has a case-sensitive collation, the script or application may fail. For example, the statement EXEC SP_heLP will fail if the current database has a case-sensitive collation.
System object modification	Direct system catalog updates are not allowed in SQL Server 2005. Any attempt to do this will generate the following error: "Server: Msg 259, Level 16, State 1, Line 1" "Ad hoc updates to system catalogs are not allowed." Modify your SQL scripts to use official and documented APIs. For example, use ALTER DATABASE database_name SET EMERGENCY instead of running an UPDATE statement on the sysdatabases system table.
System object removal	Statements such as DROP TABLE, DROP PROCEDURE, and sp_dropextendedproc cannot be used to remove system objects, because these objects are deployed in the read-only Resource Database. Remove all statements that try to drop system objects from your applications. Modify your applications to either revoke or deny EXECUTE permission on system objects. Alternatively, you can use one of the Surface Area Configuration tools in SQL Server 2005 to disable some of these objects. For example, the xp_cmdshell extended stored procedure can be disabled or enabled by using one of the Surface Area Configuration tools.
sysperfinfo	In SQL Server 2005, sysperfinfo returns a bigint value for the cntr_value column. Modify applications that use sysperfinfo to make sure that they can handle the bigint values of the cntr_value column. In SQL Server 2005, sysperfinfo is a compatibility view. You should use the sys.dm_os_performance_counters dynamic management view instead.
System tables queried by using 'dbo' in the search criteria	In earlier versions of SQL Server, system objects are owned by dbo and reside in the master database. In SQL Server 2005, system objects are owned by sys and logically appear in every database. Statements that query system tables and have search criteria that specify the user dbo will fail.

Transact-SQL

Feature	Description
@@VERSION	SQL Server 2005 returns more detailed information than SQL Server 2000, in the format major.minor.build.incremental-build.
CREATE STATISTICS	Specifying WITH ROWS in CREATE STATISTICS statements is not supported in SQL Server 2005. Modify CREATE STATISTICS statements that include WITH ROWS by specifying SAMPLE number between WITH and ROWS, or by specifying other options that comply with the documented syntax.
DISK INIT	The DISK INIT statement, used in earlier versions of SQL Server to create database or transaction log devices, has been removed from SQL Server 2005. Replace all occurrences of this statement with equivalent CREATE DATABASE or ALTER DATABASE statements.
UNION inside an INSERT INTO...SELECT statement	When a UNION operator is inside an INSERT statement, SQL Server 2005 independently casts the data type of each UNION operation according to the rules of data type conversion. Then, the data types of the final result of the UNION operation are cast to the corresponding columns of the table targeted by the INSERT operation. This change in behavior may cause data-type casting errors in your applications. The following example demonstrates a data-type casting error. In compatibility levels 80 and earlier, the integer constant 1 in the first SELECT statement is cast directly to the data type of the destination column ReturnedValue, which is varchar(255). In compatibility level 90, the data type of the UNION result set is determined before casting to the destination column. For the second column in the first SELECT statement, the data type is determined to be int. For the second column in the second SELECT statement, the data type is determined to be varchar(4). Because the int data type has a higher precedence than the varchar(4) data type, when the data types of the UNION result set are then determined, the value test will be cast to the int data type causing a data type conversion error. CREATE TABLE #test(ReturnedName varchar(255) NOT NULL, ReturnedValue varchar(255) NULL) INSERT INTO #test SELECT 'col1', 1 UNION ALL SELECT 'test', 'test' DROP TABLE #test
UPDATETEXT	SQL Server 2005 does not support using text pointers in UPDATETEXT statements that read and write to the same binary large objects (BLOB) by using the same text pointer. Copy the BLOB to a temporary table or to a table variable, and then reassign the value to the original column.
WITH keyword when you are using table hints	In SQL Server 2005, with some exceptions, table hints are supported in the FROM clause of a query only when the hints are specified by using the WITH keyword. For more information, see FROM (Transact-SQL) and Table Hint (Transact-SQL).
ORDER BY in a view definition	In SQL Server 2005, the ORDER BY clause in a view definition is used only to determine the rows that are returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.
UPDATE with locking hints	In SQL Server 2000, locking hints are not checked for conflicts in an UPDATE statement when both of the following conditions are true: The table in the FROM clause is aliased. The same table is referenced as a target of the UPDATE statement without an alias. SQL Server ignores the locking hints supplied in the FROM clause and does not issue an error if the hints conflict. In SQL Server 2005, an error is returned when locking hints conflict under these conditions.

XML

Feature	Description
OPENXML	Because of changes to MSXML, OPENXML no longer supports noninteger positional predicates. In SQL Server 2005, MSXML 3.0 is the underlying engine that is used to process XPath expressions that are used within OPENXML queries. MSXML 3.0 has a more conformant XPath 1.0 engine in which the semantics of noninteger values in positional predicates has changed. For example, the following as an XPath expression a[5.1] will now return no element instead of the fifth <a> element. To correct this, use the rounded value directly. For example, revise the previous example to a[5].
OPENXML XPath expressions	MSXML 3.0 has a stricter XPath 1.0 engine in which support for the following functions has been removed: format-number() formatNumber() current() element-available() function-available() system-property() For format-number() and formatNumber(), you can use Transact-SQL. For the other unsupported functions, there is no direct workaround.
User-defined type 'xml'	In SQL Server 2005, xml is a reserved system type. Use sp_rename to rename the type either before or after you upgrade and modify the application to work with the new type name.

See Also

Reference

Behavior Changes to Database Engine Features in SQL Server 2005
Deprecated Database Engine Features in SQL Server 2005
Discontinued Database Engine Functionality in SQL Server 2005

Other Resources

SQL Server 2005 Database Engine Backward Compatibility
sp_dbcmptlevel (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 November 2008	New content: Added an entry under the Features section about column collation matching in table variables.
14 April 2006	New content: Added an entry under the Transact-SQL section about using ORDER BY in a view definition. Added an entry under the Transact-SQL section about using locking hints with UPDATE.