Connecting Early Version Clients to SQL Server 2000
Microsoft® SQL Server™ 2000 supports connections from client applications that use the client software of SQL Server version 7.0 or earlier. SQL Server 2000 supports these connections in a compatibility mode. If a database is migrated to SQL Server 2000 from SQL Server 4.21a, 6.0, 6.5, or 7.0, the applications using the database can continue working with few, if any, changes.
SQL Server uses an application level protocol called Tabular Data Stream (TDS) to communicate between applications and instances of SQL Server. SQL Server 2000 supports these versions of TDS:
- Applications using the SQL Server 2000 versions of the OLE DB provider for SQL Server or the SQL Server ODBC driver communicate using TDS 8.0.
- Applications using the SQL Server 7.0 versions of the OLE DB provider for SQL Server or the SQL Server ODBC driver communicate using TDS 7.0.
- Applications using the SQL Server ODBC driver from SQL Server versions 6.5, 6.0, or 4.21a communicate using TDS 4.2. These versions of SQL Server did not include OLE DB providers.
- Applications using any version of DB-Library or Embedded-SQL for C communicate using TDS 4.2. The DB-Library .dll used by these two APIs has not been enhanced since SQL Server 6.5, so even the .dll versions included with SQL Server 2000 and SQL Server 7.0 still use TDS 4.2.
Applications using TDS 7.0 cannot access all features introduced in SQL Server 2000. The new features not available to TDS 7.0 clients include:
- TDS 7.0 does not support sql_variant data. sql_variant data values are returned as nvarchar(4000) values to TDS 7.0 clients.
- TDS 7.0 does not support bigint data. bigint data values are returned as decimal(19,0) values to TDS 7.0 clients.
- TDS 7.0 does not support column-level collations. SQL Server 2000 always reports the instance default collation back to TDS 7.0 clients.
Applications using TDS 4.2 cannot access all features introduced in SQL Server 7.0 and SQL Server 2000. The new features that are not available to TDS 4.2 clients include:
- TDS 4.2 does not support sql_variant data. sql_variant data values are returned as varchar(255) values to TDS 4.2 clients.
- TDS 4.2 does not support bigint data. bigint data values are returned as float values to TDS 4.2 clients.
- TDS 4.2 does not support column-level collations. SQL Server 2000 always reports the instance default collation back to TDS 4.2 clients.
- TDS 4.2 does not support XML document processing. TDS 4.2 applications attempting to execute SELECT statements with a FOR XML clause will receive an error.
- char, varchar, nchar, nvarchar, binary, and varbinary values longer than 255 bytes are truncated to 255 bytes.
- TDS 4.2 does not support Unicode. nchar and nvarchar values are converted to char and varchar using the non-Unicode Windows® code page of the server, with possible loss of extended characters. ntext values cannot be retrieved.
- uniqueidentifier data types are converted to varbinary(16).
- NULL values in bit columns are returned as 0. Catalog and meta data functions report all bit columns as NOT NULL because NULL was not allowed for bit columns in SQL Server version 6.5 and earlier.
- Catalog and meta data functions do not report the SQL Server 2000 data types (nchar, nvarchar, ntext, or uniqueidentifier).
- In SQL Server 6.5 and earlier, sysname was defined as varchar(30). In SQL Server 2000, sysname is defined as nvarchar(128) to allow for longer identifiers that contain more extended characters. TDS 4.2 clients cannot access SQL Server 2000 objects that have names more than 30 characters in length or that include characters not represented in the Windows code page on the client computer.
If any of these features are introduced into a SQL Server database, applications running with earlier versions of the SQL Server client software must be upgraded before they can access these new features.
Warning While the DB-Library and Embedded SQL for C APIs are still supported in Microsoft SQL Server 2000, no future versions of SQL Server will include the files needed to do programming work on applications that use these APIs. Connections from existing applications written using DB-Library and Embedded SQL for C will still be supported in the next version of SQL Server, but this support will also be dropped in a future release. When writing new applications, avoid using these components. When modifying existing applications, you are strongly encouraged to remove dependencies on these technologies. Instead of DB-Library or Embedded SQL for C, you can use ADO, OLE DB, or ODBC to access data in SQL Server.