Microsoft® SQL Server™ 2000 is compatible with SQL Server 7.0 in most ways. The section describes backward compatibility issues when upgrading from SQL Server 7.0 to SQL Server 2000:
When using the SQL Server client connectivity components from SQL Server 7.0 or earlier, you must set up an alias using the Client Network Utility before you connect to a named instance of SQL Server 2000. For example, on a SQL Server 7.0 client, to connect to a named instance of SQL Server 2000, you must add an alias that points to \\computername\pipe\MSSQL$instancename\sql\query. If you use an alias name of computername\instancename, clients can connect by specifying this name in the same way as SQL Server 2000 clients do. For the TCP/IP Sockets and NWLink IPX/SPX Net-Libraries, you must use the Client Network Utility to define an alias on the client that specifies the port address on which the named instance is listening.
When using Master Servers and Target Servers, SQL Server 7.0 cannot interoperate with named instances of SQL Server 2000. To use an instance of SQL Server 7.0 with an instance of SQL Server 2000 for MSX/TSX operations, you must use a default instance, not a named instance, of SQL Server 2000.
When running an instance of SQL Server version 6.5 on a server, this issue applies:
If you are upgrading from SQL Server 6.5 client software to SQL Server 2000 client software (and you have an application that uses the default Net-Library), you must use the Client Network Utility to make either Named Pipes or Multiprotocol the default Net-Library to make Windows Authentication connections.
SQL Server 2000 can operate in one of two security (authentication) modes:
Mixed Mode allows users to connect using Windows Authentication or SQL Server Authentication. Users who connect through a Microsoft Windows NT® 4.0 or Windows 2000 user account can make use of trusted connections (connections validated by Windows NT 4.0 or Windows 2000) in either Windows Authentication Mode or Mixed Mode.
SQL Server Authentication is provided for backward compatibility. An example of SQL Server Authentication would be if you create a single Microsoft Windows® 2000 group, add all necessary users to that group, and then grant the Windows 2000 group login rights to SQL Server and access to any necessary databases.
ROWCOUNT is not supported for INSERT statements against remote tables in SQL Server 2000 when the database compatibility level is set to 80. For these INSERT operations, the SET ROWCOUNT option is ignored.
The ROWCOUNT setting for INSERT statements against remote tables was supported in SQL Server 7.0.
These server configuration options are not supported in SQL Server 2000.
| default sortorder id | resource timeout |
| extended memory size | spin counter |
| language in cache | time slice |
| language neutral full-text | unicode comparison style |
| max async IO | unicode locale id |
For more information about configuration options, see Setting Configuration Options and sp_configure.
Microsoft® SQL Server™ 2000 provides the following recovery models to simplify recovery planning, simplify backup and recovery procedures, and to clarify tradeoffs between system operational requirements:
Each model addresses different needs for performance, disk and tape space, and protection against data loss.
In SQL Server 7.0 and earlier, similar functionality was provided through the combined settings of the trunc. log on chkpt and select into/bulkcopy database options, which could be set using the sp_dboption stored procedure.
This table maps the settings of trunc. log on chkpt and select into/bulkcopy to the new recovery models.
If trunc. log on chkpt is: |
And select into/bulkcopy is: | The recovery model is: |
|---|---|---|
| FALSE | FALSE | FULL |
| FALSE | TRUE | BULK-LOGGED |
| TRUE | TRUE | SIMPLE |
| TRUE | FALSE | SIMPLE |
Note If you upgrade a database in which the trunc. log on chkpt and select into/bulkcopy options are set to TRUE, select into/bulkcopy is set to FALSE, forcing the database into the simple recovery model.
The trunc. log on chkpt and select into/bulkcopy database options are supported in SQL Server 2000 for backward compatibility purposes, but may not be supported in future releases.
In SQL Server 2000, the ALTER DATABASE Transact-SQL statement provides a SET clause for specifying database options, including recovery models. For more information about database options, see Setting Database Options and ALTER DATABASE.
These words are no longer reserved keywords in SQL Server 2000: AVG, COMMITTED, CONFIRM, CONTROLROW, COUNT, ERROREXIT, FLOPPY, ISOLATION, LEVEL, MAX, MIN, MIRROREXIT, ONCE, ONLY, PERM, PERMANENT, PIPE, PREPARE, PRIVILEGES, REPEATABLE, SERIALIZABLE, SUM, TAPE, TEMP, TEMPORARY, UNCOMMITTED, WORK.
These words are reserved keywords in SQL Server 2000: COLLATE, FUNCTION, OPENXML.
SQL Profiler extended stored procedures, such as xp_trace_addnewqueue and xp_trace_generate_event, are not supported in SQL Server 2000. They have been replaced by a set of new stored procedures and system user-defined functions. For more information, see Creating and Managing Traces and Templates.
In SQL Server version 7.0 and earlier, the default setting for SET QUOTED_IDENTIFIER in SQL Query Analyzer was OFF. In SQL Server 2000, the default setting in SQL Query Analyzer is ON, which is also the default setting for ODBC and OLE DB. Moreover, several new features in SQL Server 2000, such as indexed views and indexes on computed columns, require this option to be ON.
Note If you use double quotation marks for strings when QUOTED_IDENTIFIER is ON, you will receive a syntax error.
To read character files created by earlier versions of DB-Library bcp in SQL Server 2000, use the -V switch. For more information, see bcp Utility.
For users who have database diagrams created with earlier versions of the visual database design tools:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'dbo.dt_getobjwithprop_u'.
There are several visual database tools that can put a database into this state. These include the Query Designer, the View Designer, the Database Designer, and the Table Designer in SQL Server 7.0 and earlier, as well as many tools that enumerate the objects in a database. These tools are also in Microsoft Access 2000 and Microsoft Visual Studio® 6.
Running the following script on the database allows SQL Server Enterprise Manager to work with the database diagrams in that database:
alter table dbo.dtproperties add uvalue nvarchar(255) null
go
if exists(select * from dbo.dtproperties) exec('update dbo.dtproperties set uvalue = convert(nvarchar(255), value)')
go
After this script has been run, both the SQL Server Enterprise Manager in SQL Server 2000 and the earlier versions of the visual database tools can jointly access the database diagrams in the database. There are additional issues to consider when using the earlier versions of the database tools against a SQL Server 2000 database. For more information, see Hardware and Software Requirements for Installing SQL Server 2000.
These are the backward compatibility issues for Data Transformation Services (DTS).
Some objects in Data Transformation Services (DTS) are extended in SQL Server 2000. For more information about using new Data Transformation Services objects, methods, and properties with SQL Server 7.0 and earlier, see Extended DTS Objects.
There are restrictions on using the Copy SQL Server Objects task (Transfer SQL Server Objects task in SQL Server version 7.0) when copying database objects between an instance of SQL Server 2000 and SQL Server 7.0. For more information, see Copy SQL Server Objects Task.
DTS packages created on an instance of SQL Server 2000 cannot be loaded or run on an instance of SQL Server version 7.0 or earlier. If you attempt to do this, you may receive one of the following messages:
Both messages indicate that the current server does not contain all the components necessary to load the package and cannot support objects defined in the DTS package, such as tasks and transformations.
However, if you receive one of these messages, you can still open and run the package on an instance of SQL Server 2000.
When using the Copy SQL Server Objects task and Copy Column transformation to copy non-Unicode data between an instance of SQL Server 2000 and SQL Server 7.0, issues arise when using different code pages and collations. For more information, see Data Conversion and Transformation Considerations.
In SQL Server 7.0, you did not have to code "trusted_connection=yes" in your connection strings for ADO, OLE DB, or ODBC to obtain a trusted connection. If you did not specify a UID and PASSWORD, SQL Server would default to trying a trusted connection. In SQL Server 2000, you must code "trusted_connection=yes" to obtain trusted connection.
Some objects in SQL-DMO are extended in SQL Server 2000. For more information about using extended SQL-DMO objects, methods, and properties with SQL Server 7.0 or earlier, see Programming Extended SQL-DMO Objects.
The SQL-SCM (Service Control Manager) API has been removed and is no longer supported.
For users of OLAP Services for SQL Server 7.0 who want to install or uninstall English Query, these issues apply:
These issues do not occur with SQL Server 2000 Analysis Services (formerly OLAP Services).