Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
INF: Changes to SQL Server 6.5 That Affect 6.0 Apps
Article ID: 152032 - View products that this article applies to.
This article was previously published under Q152032
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.
1. System Table ChangesThere 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."
2. System Stored Procedure Changes and Changes in FormatThere 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."
3. New Installation DefaultsThe 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.
4. KeywordsThe 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
The word DISTRIBUTED is also a new keyword in SQL Server 6.5.
All database objects are automatically checked for conflicts with these new keywords by running ChkUpg65.exe.
5. FROM Clause and Table NamesIn 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.
Previously, SQL Server used string comparisons alone to determine whether two table names identified the same table. For example, pubs.dbo.authors and pubs..authors were considered to be different tables. Now if two table names are not identical, the database IDs and table IDs are compared to determine whether or not they are the same table. Previously in an Update statement SQL Server would simply find the first table in the FROM clause that matched an unqualified column name and assume that was the table the user meant. Now this type of query will cause an error, because it is uncertain which table should be updated. Trace flag 110 will disable all of these changes.
6. SELECT DISTINCT with ORDER BYPrevious 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:
SQL Server 6.5 complies with the ANSI Standard, resulting in error 145:
Order-by items must appear in the select-list if SELECT DISTINCT is specified.
Trace flag 204 enables the old, non-ANSI behavior (as well as other non- ANSI behavior involving subqueries and so forth from SQL Server 6.0).
7. REFERENCES Permission Required to Create Foreign KeyIn 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.
8. SELECT INTO or CREATE VIEW without Column NameIn 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 results in error message 4511:
Create view failed because no column name was specified for column 2.
A column alias should be specified for the second column.
SQL Server 6.0 allowed this; trace flag 246 enables the old behavior.
9. RAISERROR Sets @@ERROR to Zero if Severity is Ten or LessThe 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.
10. Startup Procedure: sp_sqlregisterIn 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.
11. Forward-Only Cursors Default to Dynamic CursorsIn 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.
12. Plans for Cursors on Stored Procedures are CachedSQL 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.
13. New Service: MSDTCA 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.
14. USE Statement in EXECUTE Resets on CompletionIn 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:"
To obtain the SQL Server 6.0 behavior in SQL Server 6.5, the following batch should be used:
This batch carries out the SELECT in the USEd database but returns to the master database upon completion.
15. ODBC Driver SettingsMicrosoft Knowledge Base Article 149921
(http://support.microsoft.com/kb/149921/EN-US/ )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:
16. JOIN Syntax and Trace Flag 204Although the outer join operators *= and =* from earlier versions of SQL Server are supported, you cannot use both outer join operators and ANSI-SQL style joined tables in the same query.
When trace flag 204 is enabled, only SELECT statement syntax from SQL Server versions 6.0 and earlier is permitted; joined tables, derived tables, and other ANSI features are not permitted.
17. Objects Created in a TransactionSQL Server 6.5 allows objects to be created within a transaction. These operations are protected by the standard transactional rules and may be committed or rolled back as necessary. However, creating an object in a transaction causes locks to be held on system tables in the database until the creating transaction commits or rolls back. Use caution when you create objects inside a transaction; this includes the creation of temporary objects in the tempdb database.
18. SELECT-INTO is Now an Atomic OperationSELECT-INTO is now an atomic operation and holds exclusive locks on sysindexes, sysobjects, and syscolumns for the duration of the SELECT-INTO, or the whole transaction if inside a transaction.
19. ISQL/W Connections Reduced Due to Larger Packet SizesThe number of ISQL/w connections on Win16 clients is affected by the Network Packet Size configuration value of SQL Server. The smaller the Network Packet Size (minimum 512 bytes), the more simultaneous connections you can make from the same DB-Library client, because the larger the network packet size, the more system resources are used on the Windows 3.x client. If the Network Packet Size on the server side is configured to be 512, you should be able to make the same number of connections as you could in SQL Server 6.0 from the same client. For more information, see Microsoft Knowledge Base article 150909
(http://support.microsoft.com/kb/150909/EN-US/ )"INF: Number of Connections for SQL Server 6.5 Win16 Clients."
20. Global Variables Disallowed in CHECK and DEFAULT ConstraintsGlobal variables (such as @@SPID and so forth) cannot be used in CHECK or DEFAULT constraints (in either CREATE TABLE or ALTER TABLE statements). This has never been documented as a valid option and is now flagged as invalid syntax, producing error 112:
Variables are not allowed in CREATE TABLE statement.
Built-in functions continue to work in constraints.
21. Books Online: New ViewerThe SQL Server 6.5 Books Online uses the InfoView.exe program to view the text rather than the MSIN32.exe that was used previously.
SQL Server does not have to be installed in order to read the Books Online; they can be installed independently by doing the following:
22. VBSQL.ocx Replaces VBSQL.vbxA Visual Basic 3.0 project that uses the old 16-bit DB-Library for Visual Basic, VBSQL.vbx, should be ported to the new DB-Library for Visual Basic OLE custom control, VBSQL.ocx. For more information, see SQL Server 6.5 Books Online, "Porting an Old DB-Library for Visual Basic Project."
23. Graphical Showplan Has Been RemovedDue to changes in the SHOWPLAN output, the graphical ShowPlan tabs are no longer available in the SQL Enterprise Manager's Query Tool and in ISQL/w.
24. Extended Stored ProceduresBecause of changes to underlying structures, all extended stored procedures written in the C programming language must be recompiled from the C source code and relinked under Microsoft SQL Server version 6.5 to OPENDS60.LIB.
Although in SQL Server 6.0 you could call back in to the server from an XP, this was unsupported. Such "loopback" connections are supported in SQL Server 6.5 through the use of bound connections in which several connections can share the same transaction lock space and the same transaction, and can work on the same data without lock conflicts.
25. Future ConcernsSQL Server 6.5 includes new features that supersede the functionality of some previous features. Although all features from SQL Server 6.0 continue to be supported in 6.5, future versions of SQL Server might not support some statements where the same functionality can be achieved using other means. For example, the following features are supported in 6.5 but might be discontinued in future versions:
Article ID: 152032 - Last Review: October 16, 2003 - Revision: 3.0