INF: Changes to SQL Server 6.5 That Affect 6.0 Apps

• Microsoft SQL Server, version 6.5

SUMMARY

This article contains a list of changes made to Microsoft SQL Server version 6.5 that may affect your existing SQL Server version 6.0 applications.

Contents:

1. System Table Changes 2. System Stored Procedure Changes and Changes in Format 3. New Installation Defaults 4. Keywords 5. FROM Clause and Table Names 6. SELECT DISTINCT with ORDER BY 7. REFERENCES Permission Required to Create Foreign Key 8. SELECT INTO or CREATE VIEW without Column Name 9. RAISERROR Sets @@ERROR to 0 if Severity is 10 or Less

MORE INFORMATION

1. System Table Changes

There have been some changes to system tables: new columns have been added, the meaning or contents of some columns have been changed, and some new system tables have been added. For more information, see SQL Server 6.5 Books Online, What's New for SQL Server 6.5, Part 4: "What's New for Transact-SQL."

1. System Stored Procedure Changes and Changes in Format

There have been several changes to system stored procedures. In many cases the functionality has been expanded to include new SQL Server version 6.5 features. In some cases, the format of the output has been modified to provide a clearer presentation of the information. For more information, see SQL Server 6.5 Books Online, What's New for SQL Server 6.5, Part 4: "What's New for Transact-SQL."

1. New Installation Defaults

The default root directory on new installations is MSSQL rather than SQL60 (as in 6.0) or SQL (as in 4.2x). When you upgrade a 4.2x or 6.0 installation, the existing directory name is preserved.

1. Keywords

The following words, reserved in SQL Server 6.0, are keywords in SQL Server 6.5:

   AUTHORIZATION     FULL      OUTER          SCHEMA
   CASCADE           INNER     PRIVILEGES     WORK
   CROSS             JOIN      RESTRICT
   ESCAPE            LEFT      RIGHT

All database objects are automatically checked for conflicts with these new keywords by running ChkUpg65.exe.

1. FROM Clause and Table Names

In SQL Server 6.5, errors are reported when redundant table names appear in the FROM clause. For example, the SELECT statements given below were supported in earlier releases but generate errors in SQL Server 6.5. In the first SELECT statement, the tables were treated as two different tables. In the second SELECT statement the second author's reference is discarded.

   SELECT * FROM pubs..authors, pubs.dbo.authors
   SELECT * FROM authors, authors

1. SELECT DISTINCT with ORDER BY

Previous versions of SQL Server allowed SELECT DISTINCT queries containing sort columns in the ORDER BY clause that were not in the select list. For example:

   SELECT DISTINCT au_id FROM authors ORDER BY au_lname

   Order-by items must appear in the select-list if SELECT DISTINCT is
   specified.

1. REFERENCES Permission Required to Create Foreign Key

In SQL Server 6.5, if you create a foreign key on a table that you do not own, you must have REFERENCES permission on the table; this complies with the ANSI standard. In SQL Server 6.0, only SELECT permission was required on the referenced table. Trace flag 237 enables the old behavior.

1. SELECT INTO or CREATE VIEW without Column Name

In SQL Server version 6.5, an error occurs if no column name is given to a column created by a SELECT INTO or CREATE VIEW statement. For example,

  CREATE VIEW testview AS SELECT au_id, upper(au_lname) FROM authors

   Create view failed because no column name was specified for column 2.

   A column alias should be specified for the second column.

1. RAISERROR Sets @@ERROR to Zero if Severity is Ten or Less

The RAISERROR statement now sets @@ERROR to zero if the severity is between one and ten inclusive (messages with severity levels ten and under are not errors, but they do provide additional information). If you set the msg_id by using the WITH SETERROR option, the RAISERROR statement assigns the msg_id to @@ERROR regardless of severity.

In SQL Server version 6.0, @@ERROR is set to 50,000 for messages with severity levels ten and under.

To revert to SQL Server version 6.0 behavior, use either the SETERROR option or trace flag 2701.

1. Startup Procedure: sp_sqlregister

In SQL Server 6.5 the stored procedure sp_sqlregister is installed as a default startup procedure. At startup, sp_sqlregister gathers basic configuration information from the operating system, network, and SQL Server, and then broadcasts the SQL Server's presence on the network. Any server carrying out xp_sqlinventory can collect the information into a table. The stored procedure sp_unmakestartup can be run to remove sp_sqlregister as a startup stored procedure.

1. Forward-Only Cursors Default to Dynamic Cursors

In SQL Server 6.5, forward-only cursors are dynamic by default, which allows faster cursor opening and also allows the results set to display updates made to the underlying tables. Dynamic cursors are faster in version 6.5 and no longer require unique indexes.

Trace flag 7501 disables the dynamic cursor enhancements and reverts to version 6.0 behavior.

1. Plans for Cursors on Stored Procedures are Cached

SQL Server 6.5 caches plans for cursors for some extended stored procedures; this provides a gain in performance for many cursor operations. However it will also use procedure cache to hold these plans, perhaps affecting an application's caching behavior (and performance). Trace flag 7502 disables this caching of cursor plans.

1. New Service: MSDTC

A new service is installed with SQL Server 6.5. The MSDTC service is the Distributed Transaction Coordinator which provides cross-server transaction capabilities (automatic two-phase commit). Normally the MSDTC service should not affect existing applications (other than the automatic transactional consistency), however it does require some memory and some processing time, so it might affect existing applications. The service can be stopped if its features are not required.

1. USE Statement in EXECUTE Resets on Completion

In SQL Server 6.5, upon completion of EXECUTEing a string that contains a USE statement, the "current database" will automatically be reset to the database that was being USEd before the EXECUTE. In SQL Server 6.0, the "current database" setting persisted after the EXECUTE.

To cause the same behavior as in SQL Server 6.0, each statement that should be executed in the USEd database must be EXECUTEd as a string with the USE statement preceding it. The following batch would output "pubs" in SQL Server 6.0; in SQL Server 6.5 it outputs "master:"

   GO
   USE master
   GO
   DECLARE @mydb VARCHAR(30)
   SELECT @mydb = 'pubs'
   EXECUTE('USE ' + @mydb)
   go
   SELECT db_name()
   go

   GO
   USE master
   GO
   DECLARE @mydb VARCHAR(30)
   SELECT @mydb = 'pubs'
   EXECUTE('USE ' + @mydb + 'SELECT db_name()')
   go

1. ODBC Driver Settings

Microsoft Knowledge Base Article Q149921 discusses some of the ANSI setting changes in the Microsoft SQL Server 2.65.0201 ODBC driver that might affect applications. Generally, these are all caused by the following SET options that force ANSI compliance:

   SET TEXTSIZE 2147483647
   SET ANSI_DEFAULTS ON
   SET CURSOR_CLOSE_ON_COMMIT OFF
   SET IMPLICIT_TRANSACTIONS OFF