SQL Server 2000 and SQL Server version 7.0

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:

• Client Network Utility and Named Instances
  
  
• Multiserver Jobs and Named Instances
  
  
• Upgrading SQL Server 6.5 Client Software
  
  
• Authentication Modes
  
  
• ROWCOUNT Setting for Operations Against Remote Tables
  
  
• Server Configuration Options
  
  
• Recovery Models and Database Options
  
  
• Reserved Keywords
  
  
• SQL Profiler Extended Stored Procedures
  
  
• Default Connection Option Settings in SQL Query Analyzer
  
  
• bcp Utility
  
  
• Database Diagrams from Earlier Versions of Visual Database Design Tools
  
  
• Data Transformation Services
  
  
• Specifying Trusted Connections
  
  
• Extended Objects in SQL-DMO
  
  
• SQL-SCM
  
  
• English Query and SQL Server 7.0 OLAP Services

Client Network Utility and Named Instances

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.

Multiserver Jobs and Named Instances

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.

Upgrading SQL Server 6.5 Client Software

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.

Authentication Modes

SQL Server 2000 can operate in one of two security (authentication) modes:

• Windows Authentication Mode (Windows Authentication)
  
  
• Mixed Mode (Windows Authentication and SQL Server Authentication)

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 Setting for Operations Against Remote Tables

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.

Server Configuration Options

These server configuration options are not supported in SQL Server 2000.

For more information about configuration options, see Setting Configuration Options and sp_configure.

Recovery Models and Database Options

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:

• Simple Recovery
  
  
• Full Recovery
  
  
• Bulk-Logged Recovery

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.

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.

Reserved Keywords

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

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.

Default Connection Option Settings in SQL Query Analyzer

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.

bcp Utility

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.

Database Diagrams from Earlier Versions of Visual Database Design Tools

For users who have database diagrams created with earlier versions of the visual database design tools:

• If the first visual database tool that was used against a SQL Server 2000 database is a version earlier than the tools in SQL Server 2000, SQL Server Enterprise Manager will not be able to open or create a database diagram in that database. Any attempt to do so results in the error:

  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.

Data Transformation Services

These are the backward compatibility issues for Data Transformation Services (DTS).

Extended DTS Objects

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.

Copy SQL Server Objects Task

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.

Running DTS Packages on SQL Server 7.0 or Earlier

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:

• "Invalid class string."
  
  
• "Parameter is incorrect."

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.

Using DTS with Different Collations, Different Code Pages, and Non-Unicode Data

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.

Specifying Trusted Connections

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.

Extended Objects in SQL-DMO

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.

SQL-SCM

The SQL-SCM (Service Control Manager) API has been removed and is no longer supported.

English Query and OLAP Services for SQL Server 7.0

For users of OLAP Services for SQL Server 7.0 who want to install or uninstall English Query, these issues apply:

• OLAP Services for SQL Server 7.0 must not be running during installation. Shut down the OLAP Services service before installing English Query. (See the Services application in Control Panel.)
  
  
• If you have installed OLAP Services for SQL Server 7.0 and you uninstall English Query, you must reinstall OLAP Services. Conversely, if you have installed English Query and you uninstall OLAP Services, you must reinstall English Query to maintain OLAP connectivity.

These issues do not occur with SQL Server 2000 Analysis Services (formerly OLAP Services).