|
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:
-
To remove the extended stored procedure, run sp_dropextendedproc.
-
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.
.gif) 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.
-
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);
-
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'
-
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')
-
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
|