Export (0) Print
Expand All

Chapter 4 - Backward Compatibility

You have devoted time and financial commitments to your current installations of Microsoft SQL Server and SQL Server applications. Microsoft understands the implications of a major upgrade like SQL Server version 7.0 and what this can mean to you and your customers.

Microsoft has made great efforts to ensure that your databases upgrade successfully and your applications run unchanged in SQL Server 7.0. You will have all the product functionality that you had prior to SQL Server 7.0. In other words, you do not need to change your applications in order to achieve the same level of functionality you had in versions prior to SQL Server 7.0. Your applications take advantage of most new SQL Server 7.0 capabilities automatically.

SQL Server 7.0 is highly backward compatible. Most 6.x applications work unchanged after being upgraded to SQL Server 7.0 by the SQL Server Upgrade Wizard. If your application does not have any of the following characteristics, you are unlikely to experience compatibility problems.

  • Configuration Options 

    If your application sets 6.x configuration options directly using sp_configure or relies directly on configuration settings, see the discussion of configuration in "SQL Server Backward Compatibility Details."

  • SQL-DMO, Tasks, and Replication 

    Task, replication, and device objects have changed. SQL Server 7.0 uses jobs instead of tasks, and provides new system tables and system stored procedures.

    • If your application uses SQL-DMO, see the discussion of SQL-DMO in "SQL Server Backward Compatibility Details."

    • If your application administers tasks directly, see the discussion of tasks in "SQL Server Backward Compatibility Details."

    • If your application administers replication directly using replication stored procedures, see the discussion of replication in "SQL Server Backward Compatibility Details."

  • Segments and Devices 

    SQL Server 7.0 uses files and filegroups instead of segments and devices for storing indexes or tables. Unless your application depends upon the physical layout of segments within devices, this does not create compatibility problems for your application. For more information, see the discussions of segments and devices in "SQL Server Backward Compatibility Details" in this volume, and "CREATE DATABASE," "CREATE TABLE," and "CREATE INDEX" in Microsoft SQL Server Transact-SQL and Utilities Reference.

  • System Tables 

    SQL Server 6.x provided system stored procedures as the correct way to access information stored in system tables. These system stored procedures are also provided as part of SQL Server 7.0 and are compatible with earlier versions of SQL Server. If your application bypasses the recommended system stored procedures by accessing system tables directly (for example, a query performing a SELECT involving a system table), see the discussion of system tables in "SQL Server Backward Compatibility Details."

    Here are the SQL Server 6.x system tables that are not included with SQL Server 7.0.

    master.dbo.spt_datatype_info 

    sysprocedures 

    sysbackupdetail 

    sysrestoredetail 

    sysbackuphistory 

    sysrestorehistory 

    syshistory 

    syssegments 

    syskeys 

    systasks 

    syslocks 

    sysusages 

  • Backup and Restore 

    SQL Server 7.0 uses BACKUP and RESTORE statements in place of DUMP and LOAD. DUMP and LOAD are supported for backward compatibility, but with some limitations. If your application uses DUMP or LOAD with the VOLUME option, or uses diskettes as the backup medium, see the discussion of BACKUP and RESTORE in "SQL Server Backward Compatibility Details."

  • System Stored Procedures 

    Some system stored procedures relating to the subjects discussed above are no longer available. If you have any of the above backward compatibility issues, see the discussion of stored procedures in "SQL Server Backward Compatibility Details."

  • Transact SQL 

    Any administrative tools using Transact-SQL syntax will continue to use Transact-SQL syntax properly. However, accessing system catalogs directly may not work. Most system catalogs from earlier versions of SQL Server are still available as views, but some of the system catalogs previously in the master database are no longer available. You do not need to change your scripts immediately; however, your applications and scripts will not yield all the benefits of the new syntax until you do.

  • Merge Replication 

    Merge replication is implemented using recursive triggers and requires that the new sp_dboption recursive triggers option be enabled. Before adding merge replication to an existing database that uses triggers, make sure the triggers operate correctly with the recursive triggers option enabled. For more information, see the discussion of triggers in "Programming Triggers" in Microsoft SQL Server Database Developer's Companion.

For more information, see the discussion of specific backward compatibility issues in "SQL Server Backward Compatibility Details" in this volume.

Setting a Backward Compatibility Level 

When running at its default settings, Microsoft SQL Server version 7.0 implements SQL-92 behaviors for some Transact-SQL statements whose behaviors differed from the standard in earlier versions of SQL Server. SQL Server 7.0 also enforces reserved keywords that were not keywords in earlier versions of SQL Server. If upgrading existing systems with existing applications, you can use the database compatibility level settings to retain the earlier behaviors if your existing applications depend on those behaviors. This gives you time to upgrade their applications in a planned, orderly fashion. Most applications, however, are not affected by the changes in behavior and work at the version 7.0 compatibility level.

The compatibility level is specified for each database using the sp_dbcmptlevel system stored procedure. The database compatibility level can be set to 60 (version 6.0 compatibility), 65 (version 6.5 compatibility) and the default of 70 (version 7.0 compatibility). The effects of the compatibility level settings are generally limited to the behaviors of a small number of Transact-SQL statements that also existed in earlier versions of SQL Server. Even when the database compatibility level is set to 60 or 65, applications gain almost all of the benefits of the new performance enhancements of SQL Server 7.0. Applications still benefit from features such as dynamic row-level locking and the improved query processor. For more information, see the discussion of specific behaviors controlled by the different settings in "sp_dbcmptlevel" in Microsoft SQL Server Transact-SQL and Utilities Reference. 

An upgrade from an earlier version of SQL Server to version 7.0 sets the compatibility level of existing user-defined databases to the version of SQL Server from which you are upgrading. For example, if your server was a version 6.5 server before you upgraded to SQL Server 7.0, the compatibility level for all your existing user-defined databases is set to 65. This helps existing applications run unchanged after an upgrade.

Important Do not change the compatibility level setting for the master database. SQL Server 7.0 requires that the compatibility level of the master database always operates at the 7.0 level. If you have added any user-defined objects to master, you must ensure they work correctly at the 7.0 compatibility level.

The model database is set automatically to the SQL Server 7.0 compatibility level setting during an upgrade. All new user-defined databases are created with the same compatibility level setting as model. If you do not want to use any SQL Server 7.0 behavior in new databases created after an upgrade, use sp_dbcmptlevel to change the compatibility level setting in model. For more information, see the discussion of changing compatibility level settings in "sp_dbcmptlevel" in Microsoft SQL Server Transact-SQL and Utilities Reference. 

Certain SQL Server 6.5 and 7.0 behaviors are not enabled at lower compatibility levels. For example, the keywords LEFT, OUTER, and JOIN are not keywords at compatibility level 60. This means the database compatibility level must be set to 65 or higher before the LEFT OUTER JOIN clause becomes legal. Before any applications can take advantage of features only available at a higher compatibility level, all applications using the database must be upgraded to work correctly at the higher compatibility level.

Note While running at compatibility level 60 or 65 preserves legacy behaviors on SQL Server 7.0, support for these behaviors may be dropped in future versions of SQL Server. It is recommended that you plan to upgrade your applications to work correctly with the compatibility level set to 70 as soon as is practicable.

See Also 

In Other Volumes 

"ALTER TABLE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"ALTER TRIGGER" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"CREATE TRIGGER" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Reserved Keywords" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"SET ANSI_NULL_DFLT_ON" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"sp_dboption" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"UPDATE" in Microsoft SQL Server Transact-SQL and Utilities Reference 

SQL Server Backward Compatibility Details

Microsoft understands that introducing a major release of Microsoft SQL Server can be both exciting and challenging, and has worked hard at making the SQL Server version 7.0 upgrade as easy as possible by:

  • Adding functionality, either new to SQL Server 7.0 or changed from earlier versions, which makes it easier, faster, and more efficient for you to accomplish your tasks related to SQL Server. 

  • Minimizing the time and effort needed to upgrade. 

SQL Server 7.0 adds many new features and architectural changes. Most of the changes are internal and will not affect your database scripts or applications. All Transact-SQL statements are compatible, however, administration tools or scripts should be updated to work with SQL Server 7.0.

For more information about upgrading your databases to SQL Server 7.0, see "Upgrading from an Earlier Version of SQL Server" in this volume.

These backward compatibility topics contain a complete and detailed list of changed features and behavior that could possibly affect your administration tool or script and are not controlled by the backward compatibility level.

To indicate their potential effect on administration tools or scripts, feature changes have been grouped into four levels.

Level

Consists of

1

Administrative statements, stored procedures, or SQL Server items that have been removed from, or are no longer supported in, SQL Server 7.0. Administrative tools or scripts using these items must be fixed prior to using SQL Server 7.0.

2

Important changes that produce different behavior from earlier versions of SQL Server. For example, items in this category are those that have changed behavior in data type conversion or usage of selected functions, changed behavior of clauses in selected Transact-SQL statements and stored procedures, changed column names in selected system tables, and changed behavior due to the database compatibility setting.

3

Items supported for backward compatibility only. Any item included in this category is fully supported, but may be removed or unsupported in a future release. SQL Server 7.0 provides features that accomplish these tasks more efficiently and have ongoing support.

4

Minor changes that produce different behavior from earlier versions of SQL Server. For example, items in this category are either ignored or have one or more ignored parameters, changed byte lengths, added parameters or columns, or changed data type columns.

Backward Compatibility Details (Level 1)

Backward Compatibility Details (Level 1) consists of administrative statements, stored procedures, or Microsoft SQL Server items that have been removed from, or are no longer supported in, SQL Server version 7.0. Administrative tools or scripts using these items must be fixed prior to using SQL Server 7.0. This topic covers backward compatibility details for these items.

This subheading

Relates to

Backup and Restore

Upgrading from an Earlier Version of SQL Server
BACKUP
RESTORE
DUMP
LOAD
sysbackuphistory
sysbackupdetail
sysrestorehistory
sysrestoredetail
backupfile
backupmediafamily
backupmediaset
backupset
restorefile
restorefilegroup
restorehistory

Configuration Options

sp_configure (backup buffer size, backup threads, database size, free buffers, hash buckets, LE threshold maximum, LE threshold minimum, LE threshold percent, logwrite sleep, max lazywrite IO, memory, open databases, procedure cache, RA cache hit limit, RA cache miss limit, RA delay, RA pre-fetches, RA slots per thread, RA worker threads, recovery flags, recovery interval, remote conn timeout, SMP concurrency, sort pages, min memory per query, index create memory, tempdb in ram, user connections, and user options options)
trace flag 204
Trace Flags

Custom Sort Orders

Character sets, sort orders, and Unicode collations

Databases

ALTER DATABASE

Database Options

sp_dboption (subscribe and no chkpt. on recovery options)
sp_addsubscription
RESTORE

Data Access Objects (DAO)

odbccmpt utility

DBCC

DBCC DBREPAIR
DBCC DBREINDEX
DROP DATABASE
DBCC MEMUSAGE
DBCC SHRINKDB
DBCC SHRINKDATABASE

DB-Library

Two-Phase Commit
DB-Library for Visual Basic

DECnet Network Library

DECnet Sockets Net-Library

Disk Commands

DISK REINIT
ALTER DATABASE

Disk Mirroring

DISK MIRROR
DISK REMIRROR
DISK UNMIRROR

Indexes

CREATE INDEX

Open Data Services

Windows NT Component Services
SRV_CONFIG
SRV_PROC
SRV_SERVER
srv.h
Opends60.lib

Program Group Tools and Utilities

Client Network Utility
ISQL_w
MS Query
Server Network Utility
SQL Enterprise Manager
SQL Help
SQL Security Manager
SQL Trace
SQL Performance Monitor
SQL Service Manager
SQL Setup
SQL Server Query Analyzer
SQL Server Enterprise Manager
SQL Server Profiler
SQL Server Performance Monitor
SQL Server Service Manager
SQL Server Startup

Replication

Restricted publications
DBOption object
ReplicationDatabase object EnablePublishing property
repl_publisher login

Security

DENY
Using Identifiers

Segments

CREATE INDEX
CREATE TABLE
sp_addsegment
sp_dropsegment
sp_extendsegment
sp_helpsegment
CREATE DATABASE
ALTER DATABASE

Services

SQL Executive

SET DISABLE_DEF_CNST_CHK

SET DISABLE_DEF_CNST_CHK

SET SHOWPLAN

SET SHOWPLAN
SET SHOWPLAN_ALL
SET SHOWPLAN_TEXT

SQL Alerter

SQLALRTR.exe

System Stored Procedures (General Extended Procedures)

xp_snmp_getstate
xp_snmp_raisetrap

System Stored Procedures (Replication)

sp_replica
sp_replsync
sp_helppublicationsync
sp_subscribe
sp_unsubscribe
@@ERROR
sp_changepublication
sp_addpublisher
sp_adddistpublisher
sp_droppublisher
sp_distcounters

System Stored Procedures (System)

ALTER TABLE
CREATE TABLE
sp_help
sp_helpconstraint
sp_commonkey
sp_dropkey
sp_foreignkey
sp_helpjoins
sp_helpkey
sp_primarykey
sp_placeobject
sp_dbinstall
sp_attach_db
sp_makestartup
sp_unmakestartup
sp_procoption
sp_helplogins
sp_helprotect
sp_tableoption
sp_serveroption (fallback option)
sp_setlangalias
sp_droplanguage
sp_fallback_activate_svr_db
sp_fallback_deactivate_svr_db
sp_fallback_enroll_svr_db
sp_fallback_help
sp_fallback_permanent_svr
sp_fallback_upd_dev_drive
sp_fallback_withdraw_svr_db
sp_devoption
sp_diskdefault
sp_helplog
sp_helpstartup
sp_help_revdatabase
sp_sqlexec
sp_addlanguage

System Stored Procedures (Tasks)

sp_addalert
sp_addnotification
sp_addoperator
sp_dropalert
sp_dropnotification
sp_dropoperator
sp_helpalert
sp_helphistory
sp_helpnotification
sp_helpoperator
sp_purgehistory
sp_runtask
sp_stoptask
sp_updatealert
sp_updatenotification
sp_updateoperator
sp_add_alert
sp_add_notification
sp_add_operator
sp_delete_alert
sp_delete_notification
sp_delete_operator
sp_help_alert
sp_help_jobhistory
sp_help_notification
sp_help_operator
sp_purge_jobhistory
sp_start_job
sp_stop_job
sp_update_alert
sp_update_notification
sp_update_operator

System Tables

Information Schema Views
System Stored Procedures (Catalog Procedures)
sysdevices (mirrorname and stripeset columns)
syshistory
sysjobhistory
sysindexes (distribution, segment, rowpage, keys1, and keys2 columns)
syskeys
syslocks
syslockinfo
syslogssysprocesses (gid column)
syssegments
CREATE DATABASE
ALTER DATABASE
CREATE TABLE
ALTER TABLE
CREATE INDEX
systasks
sysjobs
sysjobsteps
sysjobservers
sysusages
master.dbo.spt_datatype_info

Transactions

Data type conversions

Utilities

probe login

Backup and Restore (Level 1) 

Because backups are not compatible between servers running Microsoft SQL Server 7.0 and servers running earlier versions of SQL Server, SQL Server 6.x database dumps (backups) cannot be restored onto a SQL Server 7.0 server. For more information about upgrading your databases to SQL Server 7.0, see "Upgrading from an Earlier Version of SQL Server" in this volume.

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The VOLUME clause of the DUMP and LOAD statements indicated the volume ID for a dump device.

The VOLUME keyword has been replaced by the MEDIANAME clause. Use of the VOLUME clause results in an error.

Remove all references of the VOLUME keyword in all BACKUP, DUMP, LOAD, or RESTORE statements and replace with references to MEDIANAME.

The DUMP and LOAD statements supported the use of diskettes.

Backing up to diskette is not supported.

Back up to hard disk, and then copy the backup file to one or more diskettes.

The sysbackuphistory, sysbackupdetail, sysrestorehistory, and sysrestoredetail system tables tracked DUMP and LOAD history information.

The DUMP and LOAD history tracking system tables have been removed and replaced by a new set of system tables.

Remove all references to sysbackuphistory, sysbackupdetail, sysrestorehistory, and sysrestoredetail. Because the structure and contents of the backup system tables have changed significantly, familiarize yourself with these new system tables before referencing them: backupfile, backupmediafamily, backupmediaset, backupset, restorefile, restorefilegroup, and restorehistory.

Configuration Options (Level 1) 

Administrative scripts may have used these configuration options. For more information about configuration options, see "sp_configure" in Microsoft SQL Server Building Applications and "Setting Configuration Options" in Microsoft SQL Server Administrator's Companion. 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

backup buffer size specified the size of the dump and load buffer (used to increase backup speed).

Removed; no longer supported.

Remove or comment out all references to backup buffer size.

backup threads specified the number of threads to be reserved for striped dump and load operations.

Removed; no longer supported.

Remove or comment out all references to backup threads.

database size set the default number of megabytes (MB) allocated to each new user database.

Removed; no longer supported.

Remove or comment out all references to database size.

free buffers determined the threshold of free buffers available to the system.

Removed; no longer supported.

Remove or comment out all references to free buffers.

hash buckets set the number of buckets used for hashing pages to buffers in memory.

Removed; no longer supported.

Remove or comment out all references to hash buckets.

LE threshold maximum determined the maximum number of page locks to hold before escalating to a table lock.

Removed; no longer supported.

Remove or comment out all references to LE threshold maximum.

LE threshold minimum determined the minimum number of page locks required before escalating to a table lock.

Removed; no longer supported.

Remove or comment out all references to LE threshold minimum.

LE threshold percent specified the percentage of page locks needed on a table before a table lock is requested.

Removed; no longer supported.

Remove or comment out all references to LE threshold percent.

logwrite sleep specified the number of milliseconds that a write to the log will be delayed if the buffer is not full.

Removed; no longer supported.

Remove or comment out all references to logwrite sleep.

max lazywrite IO tuned the priority of batched asynchronous I/O operations performed by the lazy writer.

Removed; no longer supported.

Remove or comment out all references to max lazywrite IO.

memory set the size of available memory, in 2K units.

Removed; no longer supported. Memory is configured automatically based on need and available memory. To control the range of memory configured automatically, use the min server memory and max server memory options. For more information about the min server memory and max server memory option see "Server Memory Options" in Microsoft SQL Server Administrator's Companion.

Remove or comment out all references to memory.

open databases set the maximum number of databases that can be open at one time on SQL Server.

Removed; no longer supported.

Remove or comment out all references to open databases.

procedure cache specified the percentage of memory allocated to the procedure cache after the SQL Server memory needs are met.

Removed; no longer supported.

Remove or comment out all references to procedure cache.

RA cache hit limit specified the number of cache hits that a read-ahead request could have before it was canceled.

Removed; no longer supported.

Remove or comment out all references to RA cache hit limit.

RA cache miss limit specified the number of cache misses that occurred during a horizontal traversal before read-ahead started for that command.

Removed; no longer supported.

Remove or comment out all references to RA cache miss limit.

RA delay specified the delay of read-ahead, in milliseconds.

Removed; no longer supported.

Remove or comment out all references to RA delay.

RA pre-fetches determined how far ahead the read-ahead manager read (on an extent basis) before the pre-fetch manager idled.

Removed; no longer supported.

Remove or comment out all references to RA pre-fetches.

RA slots per thread specified the number of simultaneous requests each read-ahead service thread managed.

Removed; no longer supported.

Remove or comment out all references to RA slots per thread.

RA worker threads specified the number of threads used to service read-ahead requests.

Removed; no longer supported.

Remove or comment out all references to RA worker threads.

recovery flags determined what information SQL Server displayed in the error log during recovery.

Removed; no longer supported.

Remove or comment out all references to recovery flags.

recovery interval set the maximum number of minutes per database that SQL Server needed to complete its recovery procedures in case of a system failure.

Configured automatically by SQL Server.

Remove or comment out all references to recovery interval.

remote conn timeout specified a time limit to break a server-to-server connection.

Removed; no longer supported.

Remove or comment out all references to remote conn timeout.

SMP concurrency specified the maximum number of CPUs that would be used by SQL Server.

Removed; no longer supported.

Remove or comment out all references to SMP concurrency.

sort pages specified the maximum number of pages to be allocated to sorting per user.

Replaced by min memory per query. For more information about the min memory per query option see "Server Memory Options" in Microsoft SQL Server Administrator's Companion.

Replace all references of sort pages with min memory per query and index create memory.

tempdb in ram placed the tempdb database in RAM, if needed.

No longer supported because few situations experienced significant server performance improvement. SQL Server 7.0 has been optimized for maximum performance. This option may be available in a future release.

Remove or comment out all references to tempdb in ram.

trace flag 204 supported queries containing sort columns in the ORDER BY clause not included in the select list when the DISTINCT keyword was supplied.

No longer supported.

Remove all references to trace flag 204. For more information about supported trace flags, see "Trace Flags" in Microsoft SQL Server Transact-SQL and Utilities Reference.

user connections set the maximum number of simultaneous connections to SQL Server allowed.

Now an advanced option. Default value of 0 indicates automatic growth.

Expect different results when using compared to earlier versions of SQL Server. Remove or comment out all references to user connections.

user options specified that the user options system configuration parameter is to be queried or modified.

No longer supported.

Remove or comment out all references to user options.

Custom Sort Orders (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Custom sort orders were installed from definition files (usually with an .srt file extension).

Removed; no longer available or supported.

Remove all references to custom sort orders. During SQL Server installation, select a supported character set, sort order, and Unicode collation combination. For more information, see "Character Set," "Sort Order," and "Unicode Collation" in this volume.

Databases (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The ON database_device = size clause of ALTER DATABASE specified the amount of space, in MB, allocated to the database extension and could be used following DISK INIT to alter the database device size.

If the file was not created originally by DISK INIT, the ON database_device = size syntax cannot be specified with ALTER DATABASE. Instead, use the MODIFY FILE clause of ALTER DATABASE to alter the size of a database file.

Remove all references of the ON database_device = size clause of ALTER DATABASE and replace with references to the MODIFY FILE clause of ALTER DATABASE.

Database Options (Level 1) 

Administrative scripts may have used these database options. For more information about configuration options, see "sp_dboption" in Microsoft SQL Server Building Applications and "Setting Database Options" in Microsoft SQL Server Database Developer's Companion. 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The subscribe option of sp_dboption enabled or disabled a database for subscriptions.

Removed; no longer available. Use sp_addsubscription to enable or disable a database for subscriptions.

Remove all references of the subscribe option of sp_dboption and replace with references to sp_addsubscription.

The no chkpt. on recovery option of sp_dboption defined whether or not a checkpoint record was added to a database recovered during a SQL Server startup.

Removed; no longer available. When using a warm standby server, use the WITH STANDBY clause of the RESTORE statement.

Remove all references of the no chkpt. on recovery option of sp_dboption and replace with references to the WITH STANDBY clause of the RESTORE statement when using warm standby servers.

Data Access Objects (DAO) (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Version 3.x of the Data Access Objects (DAO) functioned properly when accessing SQL Server version 6.x servers.

Because the ODBC version 3.70 driver shipped with SQL Server 7.0 exposes new GUID and Unicode data types when connecting to SQL Server 7.0, DAO version 3.x does not work properly with SQL Server 7.0. However, SQL Server 7.0 does provide the odbccmpt utility, which can enable SQL Server version 6.x ODBC compatibility for a DAO application.

Use the \mssql7\binn\odbccmpt.exe utility. For more information, see "odbccmpt utility" in Microsoft SQL Server Building Applications.

DBCC (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

DBCC DBREPAIR dropped the specified database.

Removed; no longer supported or available.

Remove all references of DBCC DBREPAIR and replace with references to DROP DATABASE.

DBCC DBREINDEX used the SORTED_DATA and SORTED_DATA_REORG clauses. The SORTED_DATA clause eliminated the sort performed when a clustered index was created and physically reorganized the data. The SORTED_DATA_REORG clause eliminated the sort performed when a clustered index was created.

Removed; no longer supported.

Remove all references to either the SORTED_DATA or the SORTED_DATA_REORG clauses of DBCC DBREINDEX and replace with references to the DROP_EXISTING clause of CREATE INDEX.

DBCC SHRINKDB either returned the minimum size to which a database could shrink, or shrank the size of the specified database to the specified value.

Removed; no longer supported or available.

Remove all references of DBCC SHRINKDB and replace with references to DBCC SHRINKDATABASE. Consider shrinking databases automatically by using the autoshrink option of sp_dboption.

DBCC MEMUSAGE provided detailed reports on memory use.

Removed; no longer supported or available.

Remove all references of DBCC MEMUSAGE and replace with references to these Performance Monitor Counters.

Performance Monitor object name

Performance Monitor counter name

SQL Server: Buffer Manager Object

Procedure Cache Pages In Use

 

Procedure Cache Size (pages)

SQL Server: Cache Manager Object

Procedure Cache Hit Ratio

 

Procedure Cache Pages

 

Procedure Cache Object Counts*

* These counters are available for various categories of cache objects including adhoc sql, prepared sql, procedures, triggers, and so on.

DB-Library (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

DB-Library's two-phase commit special library managed transactions distributed across two or more servers.

The DB-Library two-phase commit is no longer supported. Use Microsoft Distributed Transaction Coordinator (MS DTC) to accomplish simultaneous updates on two servers.

Remove all references to DB-Library's two-phase commit. Use MS DTC instead of the DB-Library two-phase commit.

DB-Library applications could be developed in Visual Basic.

The development libraries for DB-Library for Visual Basic are not supplied. Existing DB-Library for Visual Basic applications will run against SQL Server 7.0, but must be maintained using the development libraries for SQL Server 6.5.

All new Visual Basic applications written to access SQL Server should use the Visual Basic data APIs such as ActiveX Data Objects (ADO) and Remote Data Objects (RDO).

DECnet Network Library (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

For Intel-based, MIPS-based, and Alpha AXP-based computers, server DECnet Sockets Net-Libraries provided connectivity with PATHWORKS networks by allowing clients running on VMS to connect to SQL Server.

Removed; no longer supported.

Remove all references to the DECnet Sockets Net-Libraries.

Disk Commands (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

DISK REINIT and DISK REFIT restored usage information from system tables when a device existed (the file was present) but the entries in sysusages no longer existed.

Removed; no longer supported or available.

Remove all references to DISK REINIT. Replace all references of DISK REFIT with references to ALTER DATABASE, which adds and drops filegroups included in a database, and modifies the size of each database filegroup.

Disk Mirroring (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

DISK MIRROR, DISK REMIRROR, and DISK UNMIRROR performed SQL Server disk mirroring.

No longer supported because SQL Server mirroring is no longer supported.

Use Microsoft Windows NT or hardware-based RAID. For more information, see your Windows NT or hardware documentation.

Indexes (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The SORTED_DATA_REORG clause of CREATE INDEX eliminated the sort performed when a clustered index was created.

Replaced by the DROP_EXISTING clause of CREATE INDEX.

Remove all references to the SORTED_DATA_REORG clause of CREATE INDEX and replace with references to DROP_EXISTING.

The SORTED_DATA clause of CREATE INDEX eliminated the sort performed when a clustered index was created and physically reorganized the data.

Removed; no longer available.

Remove or comment out all references to the SORTED_DATA clause of CREATE INDEX.

bcp could import an already sorted data file into a SQL Server table. Creating a clustered index on an ordered table could be optimized by using the SORTED_DATA clause of CREATE INDEX. The SORTED_DATA clause forced SQL Server not to sort or reorganize the previously ordered table.

SQL Server returns an error message stating that the SORTED_DATA clause of CREATE INDEX is ignored and no longer supported.

Remove or comment out all references to the SORTED_DATA clause of CREATE INDEX. Consider creating the clustered index before using bcp to import the data. bcp uses improved index maintenance strategies to make data importation with a preexisting index faster than earlier releases and avoids resorting of data after importation.

The ALLOW_DUP_ROW and IGNORE_DUP_ROW clauses of the CREATE INDEX statement allowed data to be updated into tables with a unique index and without having to filter out duplicates first.

No longer supported. Using either ALLOW_DUP_ROW or IGNORE_DUP_ROW in the CREATE INDEX statement generates a warning message. If there is no unique clustered index and there is a need to avoid duplicate rows, create a unique constraint on one or more columns other than the clustering key.

Remove all references to both ALLOW_DUP_ROW and IGNORE_DUP_ROW in CREATE INDEX statements.

The SORTED_DATA clause of CREATE INDEX eliminated the sort performed when a clustered index was created.

No longer supported. If the SORTED_DATA clause is specified, SQL Server returns an error message in addition to completing the CREATE INDEX statement successfully.

Expect different results as compared to earlier versions of SQL Server. Remove any references to the SORTED_DATA clause of CREATE INDEX.

Open Data Services (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The ODBC client driver for Open Data Services gateways (ODSGT32.DLL) and associated resource files were used by ODBC clients to connect to Open Data Services gateway servers.

Not shipped with SQL Server version 7.0. The SQL Server version 6.x ODSGT32.DLL and associated resource files work against an Open Data Services gateway recompiled with SQL Server version 7.0 headers and libraries.

Use the SQL Server version 6.x ODBC client driver for Open Data Services (ODSGT32.DLL) and associated resource files to connect from a ODBC client to an Open Data Services gateway.
Consider redesigning your application using Windows NT Component Services.

Open Data Services data structures such as SRV_CONFIG, SRV_PROC, and SRV_SERVER were exposed in the Open Data Services header file.

These data structures are no longer exposed, and the data structure members have changed.

Applications that reference these data structures directly or their members must be changed and recompiled using the SQL Server 7.0 Open Data Services header file (srv.h) and relinked using the SQL Server 7.0 Open Data Services library file (opends60.lib). These changes should be made to avoid the possibility of server failures.

Earlier versions of SQL Server could make remote stored procedure calls against gateways compliant with 6.x and/or 4.x versions of Open Data Services.

SQL Server 7.0 does not support remote stored procedure calls against gateways compliant with 6.x and 4.x versions of Open Data Services. SQL Server 7.0 does support remote stored procedure calls against gateways compliant with 7.0

Gateways compiled and linked with earlier versions of Open Data Services should be recompiled with SQL Server 7.0 version of Open Data Services. Consider using distributed query if your target data source has an ODBC or an OLE DB provider on Windows NT or Windows 95/98.

Program Group Tools and Utilities (Level 1) 

In Microsoft SQL Server 7.0, these tools have been renamed or replaced as shown. In addition, all command prompt utilities presented in SQL Server 6.5 use the same names. Use the SQL Server 7.0 tool and utility names from the SQL Server 7.0 Program Group.

Pre-SQL Server 7.0

SQL Server 7.0

ISQL_w

SQL Server Query Analyzer

MS Query

N/A

SQL Client Configuration

Client Network Utility

SQL Enterprise Manager

SQL Server Enterprise Manager

SQL Help

N/A

SQL Security Manager

N/A

SQL Trace

SQL Server Profiler

SQL Performance Monitor

SQL Server Performance Monitor

SQL Service Manager

SQL Server Service Manager

SQL Setup

SQL Server Setup

Replication (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Restricted publications could be created through the user interface and used in replicating data.

Restricted publications cannot be created through the user interface and are no longer supported.

Remove all references to restricted publications. A replacement for restricted publications will be available in a later release. For more information, see "Overview of Replication" in Microsoft SQL Server Distributed Data Operations and Replication.

Publish and subscribe properties could be set using the DBOption object.

No longer available.

Remove all references to the DBOption object and replace with references to the EnablePublishing property of the ReplicationDatabase object.

The repl_publisher login allowed replication processes on the distributor to connect to a subscription server and replicated table schema and data to destination databases.

No longer available.

Remove all references to the repl_publisher login and replace with references to a publication access list (PAL). For more information about PALs, see "Restricting Logins to a Publication" in Microsoft SQL Server Distributed Data Operations and Replication.

Security (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

DENY was not a reserved keyword and could be used as an object identifier.

DENY is a reserved keyword. If DENY is used as an object identifier, all references to the object must use delimited identifiers.

Rename any object named DENY or [DENY]. Change all Transact-SQL statements and scripts referencing the object to use the new object name. If the object name is not changed, ensure that all Transact-SQL statements and scripts use delimited using identifiers.

Segments (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Indexes could be placed on segments using the CREATE INDEX statement.

Segments are no longer supported. However, CREATE INDEX can create an index on a filegroup.

Remove all references of segments and replace with references to filegroups within a CREATE INDEX statement.

Tables could be created on a particular segment by using the CREATE TABLE statement.

CREATE TABLE references files and filegroups instead of segments.

Remove all references of segments and replace with references to files and filegroups within a CREATE TABLE.

User-defined segments allowed the placement of database objects on certain devices for performance reasons.

Segments are no longer supported. Multidisk RAID devices generally provide a greater increase in performance with a lower associated administrative cost.

Use filegroups for user-defined PLACEMENT of data, indexes, or text. Remove all references to these segment-related system stored procedures:
sp_addsegment
sp_dropsegment
sp_extendsegment
sp_helpsegment
Create, modify, or drop files and filegroups; and place indexes on files or filegroups using CREATE TABLE, CREATE DATABASE, ALTER DATABASE, and CREATE INDEX.

Services (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

SQL Executive provided the SQL Server scheduling engine. SQL Executive offered extensive and varied task scheduling and alerting abilities, and was capable of handling large client/server environments.

SQL Executive tasks are now performed by SQL Server Agent.

Use SQL Server Agent for scheduling purposes. For more information, see "Configuring the SQL Server Agent Service" in Microsoft SQL Server Administrator's Companion. 

SET DISABLE_DEF_CNST_CHK (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The SET DISABLE_DEF_CNST_CHK setting controlled interim constraint checking.

Removed; no longer available.

Remove or comment out all references to SET DISABLE_DEF_CNST_CHK.

SET SHOWPLAN (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

SET SHOWPLAN generated a description of the processing plan for the query and processed it immediately unless the SET NOEXEC setting was enabled.

SET SHOWPLAN has been replaced with SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT. The SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT statements return only query or statement execution plan information and do not execute the query or statement. To execute the query or statement, turn the appropriate showplan statement OFF. The query or statement will then execute.

Remove all references to either SET SHOWPLAN ON or SET SHOWPLAN OFF and replace with references to either SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET SHOWPLAN_ALL OFF, or SET SHOWPLAN_TEXT OFF. Expect differences in behavior as compared to earlier versions of SQL Server. Only when either SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT is OFF will the query or statement be executed.

SQL Alerter (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

SQL Alerter, SQLALRTR.exe, was used to integrate the alert engine with the Windows NT Performance Monitor alerter.

Removed; no longer supported or available. Replaced by SQL Server Performance Condition Alerts.

Remove all references to SQL Alerter and replace with references to SQL Server Performance Condition Alerts.

SQL-DMO (Level 1) 

Microsoft SQL Server 7.0 uses a SQL-DMO component that has major changes to task, replication, and device objects. Except for new methods and properties, most other SQL-DMO objects are unchanged from earlier versions of SQL Server. SQL-DMO applications are administrative tools, and should be updated to work with SQL Server 7.0. It is recommended that code be recompiled and any error messages returned from the build process be used to track any necessary changes.

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

SQL-DMO is implemented in Sqlole.dll. The SQL-DMO objects exhibit properties, methods and events that automate administrative tasks for SQL Server version 6.5 and earlier. SQL-DMO, implemented in Sqlole.dll, cannot connect to and operate against SQL Server 7.0.

SQL-DMO is implemented in Sqldmo.dll. The SQL-DMO objects exhibit properties, methods and events that automate administrative tasks for SQL Server 7.0. SQL Server 7.0 SQL-DMO cannot connect to and operate against a version 6.5 or earlier SQL Server.

Rewrite SQL-DMO applications.
If the application must operate against both SQL Server 7.0 and earlier versions of SQL Server, reference both Sqldmo.dll and Sqlole.dll components in the application. Develop new, separate subroutines referencing the SQL Server 7.0 SQL-DMO objects from the existing subroutines.
If the application will work against SQL Server 7.0 only, rewrite existing subroutines to reference SQL Server 7.0 SQL-DMO objects.
If the application will not be used against your new SQL Server 7.0 server(s), continue to use the application unchanged.

System Stored Procedures (General Extended Procedures) (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

xp_snmp_getstate returned the state of the SQL Server Simple Network Management Protocol (SNMP) agent. xp_snmp_raisetrap permitted a client to define and send a trap (an SNMP alert) to an SNMP client.

Removed; no longer available.

Remove or comment out all references to either xp_snmp_getstate or xp_snmp_raisetrap.

System Stored Procedures (Replication) (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

sp_replica remotely set (on a Subscriber) a sysobjects category bit that marked the table as a replica.

Removed; no longer supported or available.

Remove or comment out all references to sp_replica.

sp_replsync acknowledged completion of a manual synchronization when used from a Subscriber.

Removed; no longer supported or available.

Remove or comment out all references to sp_replsync.

sp_helppublicationsync provided information about a scheduled synchronization task for a publication.

No longer supported. An error message is returned if this stored procedure is used in SQL Server version 7.0.

Remove or comment out all references to sp_helppublicationsync.

sp_subscribe and sp_unsubscribe remotely added or canceled a subscription to a particular article within a publication, to a whole publication, or to all publications.

No longer supported. An error message is returned if this stored procedure is used in SQL Server version 7.0.

Remove or comment out all references to either sp_subscribe or sp_unsubscribe, or use the @@ERROR function to test for errors.

name value of the value parameter in sp_changepublication was used to provide the new publication name.

Removed; no longer supported or available.

Remove all references to the name value parameter of sp_changepublication.

sp_addpublisher added a Publisher at the Subscriber and added a Distribution Publisher at the Distributor.

Replaced by sp_adddistpublisher.

Remove all references to sp_addpublisher and replace with references to sp_adddistpublisher.

sp_droppublisher dropped a publication server.

Removed; no longer supported or available.

Remove all references to sp_droppublisher. To drop a Publisher at a Distributor, use sp_dropdistpublisher.

sp_discounters was used to query for delivered or undelivered commands.

Removed; no longer supported.

Remove all references to sp_ditcounters and replace with references to MSdistribution_status.

System Stored Procedures (System) (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Several system stored procedures were used for documenting keys.

Removed; no longer supported or available.

Use declarative referential integrity by implementing keys and constraints with either ALTER TABLE or CREATE TABLE. Remove all references to these system stored procedures and replace with references to either sp_help or sp_helpconstraint:
sp_commonkey
sp_dropkey
sp_foreignkey
sp_helpjoins
sp_helpkey
sp_primarykey

sp_placeobject put future space allocations for a table or index on a particular segment.

sp_placeobject is no longer available because segments no longer exist. Use the ON FILEGROUP syntax of the CREATE TABLE statement to place table or index information on a separate filegroup.

Remove all references of sp_placeobject and replace with references to the ON FILEGROUP clause of the CREATE TABLE statement.

sp_dbinstall installed a database and its devices, and was used for removable media.

Removed; no longer supported or available.

Remove or comment out all references to sp_dbinstall and replace with references to sp_attach_db.

sp_makestartup and sp_unmakestartup set a stored procedure for auto execution and discontinued auto execution of the stored procedure, respectively.

Removed; no longer supported or available.

Remove all references of either sp_makestartup or sp_unmakestartup and replace with references to sp_procoption.

The sp_helplogins, sp_helprotect, and sp_tableoption system stored procedures supported pattern matching (using wildcard characters) allowed flexibility in specific parameters.

Pattern matching using the wildcard characters is no longer supported in these system stored procedures because any system stored procedure identifier may contain a pattern matching character.

Remove or comment out all references to pattern matching in sp_helplogins, sp_helprotect, and sp_tableoption.

The fallback setting of sp_serveroption indicated a fallback server.

The fallback option of sp_serveroption is no longer available because fallback support is no longer supported using the fallback system stored procedures.

Remove or comment out all references to the fallback option of sp_serveroption.

sp_setlangalias assigned or changed the alias for an alternate language.

Removed; no longer supported. Use the aliases provided in syslangauges.

Remove or comment out all references to sp_setlangalias.

sp_droplanguage dropped an alternate language from the server and removed its row from master.dbo.syslogins.

Removed; no longer supported.

Remove or comment out all references to sp_droplanguage.

Fallback support was provided by executing system stored procedures that shifted control of databases and devices from a broken primary server to a fallback server.

Fallback support is no longer supported using the fallback system stored procedures. Support for fallback servers is supported using Microsoft Windows NT Clustering Services.

Remove or comment out all references to these fallback system stored procedures:
sp_fallback_activate_svr_db,
sp_fallback_deactivate_svr_db,
sp_fallback_enroll_svr_db,
sp_fallback_help,
sp_fallback_permanent_svr,
sp_fallback_upd_dev_drive,
sp_fallback_withdraw_svr_db.

sp_devoption displayed or set device status.

Removed; no longer available.

Remove or comment out all references to sp_devoption.

sp_diskdefault set a database device status to indicate whether the device can be used for database storage when the user does not specify a database device or specifies DEFAULT with the CREATE DATABASE or ALTER DATABASE statements.

Removed; no longer available.

Remove or comment out all references to sp_diskdefault.

sp_helplog reported the name of the device that contains the first page of the log in the current database.

Removed; no longer available.

Remove or comment out all references to sp_helplog.

sp_helpstartup reported a listing of all auto-start stored procedures.

Removed; no longer available.

Remove or comment out all references to sp_helpstartup and replace with references to sp_procoption.

sp_sqlexec provided a convenient way for SQL Server database clients and servers to send a language statement of any format to an Open Data Services server application.

Removed; no longer available.

Remove or comment out all references to sp_sqlexec.

sp_helprevdatabase analyzed an existing database and created a script that could be used to replicate the database structure on another server.

Removed; no longer available. Use the SQL-DMO Script Method of the Database Object to generate similar information.

Remove or comment out all references to sp_helprevdatabase. If applicable, use the Script method of the Database object.

sp_addlanguage added an alternate language to a server.

Removed; no longer available.

Remove or comment out all references to sp_addlanguage.

System Stored Procedures (Tasks) (Level 1) 

Replace the following unsupported Microsoft SQL Server 6.x task-related system stored procedure with the corresponding SQL Server 7.0 job-related system stored procedure.

Pre-SQL Server 7.0

SQL Server 7.0

sp_addalert

sp_add_alert

sp_addnotification

sp_add_notification

sp_addoperator

sp_add_operator

sp_dropalert

sp_delete_alert

sp_dropnotification

sp_delete_notification

sp_dropoperator

sp_delete_operator

sp_helpalert

sp_help_alert

sp_helphistory

sp_help_jobhistory

sp_helpnotification

sp_help_notification

sp_helpoperator

sp_help_operator

sp_purgehistory

sp_purge_jobhistory

sp_runtask

sp_start_job

sp_stoptask

sp_stop_job

sp_updatealert

sp_update_alert

sp_updatenotification

sp_update_notification

sp_updateoperator

sp_update_operator

Task management has been changed to job management.

System Tables (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

System tables were used internally by SQL Server for a wide range of uses, including maintaining the list of character sets that SQL Server could use and containing information about active locks.

System tables have changed significantly. Most pre-SQL Server 7.0 system tables will continue to work properly. Views provided allow applications referencing SQL Server 6.x system tables to continue functioning properly. However, some SQL Server 7.0 data cannot be referenced through these views.

Use the provided Information Schema Views or ODBC catalog stored procedures to obtain system table information. Modify scripts as appropriate. Any scripts referencing pre-SQL Server 7.0 system tables will not be converted properly.

sysdevices contained one row for each disk dump, tape dump, and database device.

The mirrorname and stripeset columns have been removed. sysdevices is retained only for dump devices and also for backward compatibility (supporting DISK INIT and pre-SQL Server 7.0 CREATE DATABASE syntax).

Remove or comment out all references to the mirrorname and stripeset columns of sysdevices.

syshistory contained one row for each scheduled event, alert, or task that occurred.

Replaced by sysjobhistory.

Remove or comment out all references to syshistory and replace with references to sysjobhistory.

sysindexes contained one row for each clustered index and one row for each nonclustered index.

The distribution, segment, rowpage, keys1, and keys2 columns have been removed.

Remove or comment out all references to the distribution, segment, rowpage, keys1, and keys2 columns of sysindexes.

syskeys used for objects

Removed; no replacement.

Remove or comment out all references to syskeys.

syslocks contained information about active locks.

Replaced by syslockinfo.

Remove or comment out all references to syslocks and replace with references to syslockinfo.

syslogs contained the transaction log.

Removed; no replacement. The database log is now an operating system file.

Remove or comment out all references to syslogs.

sysprocesses contained information about SQL Server processes.

The gid column has been removed.

Remove or comment out all references to the gid column of sysprocesses.

sysprocedures contained entries for each view, default, rule, trigger, CHECK constraint, DEFAULT constraint, and stored procedure.

Removed. SQL Server 7.0 obtains procedure text from syscomments when procedures need to be compiled.

Remove all references to sysprocedures and replace with references to syscomments.

syssegments contained one row for each segment (named collection of disk fragments).

Removed; no replacement. Segments are no longer supported. Use filegroups instead.

Remove all references to syssegments. Use filegroups instead by using CREATE DATABASE, ALTER DATABASE, CREATE TABLE, ALTER TABLE, and CREATE INDEX.

systasks contained one row for every scheduled task.

Removed; replaced by sysjobs, sysjobsteps, and sysjobservers.

Remove all references to systasks and replace with references to sysjobs, sysjobsteps, and sysjobservers as appropriate.

sysusages contained one row for each disk-allocation piece assigned to a database.

Removed; no replacement. SQL Server 7.0 relies on sysdevices for database file information. Filegroups are supported, and the sysfiles and sysfilegroups system tables are added. These system tables reside in every database and describe database files and filegroups.

Remove or comment out all references to sysusages.

master.dbo.spt_datatype_info

Removed; no replacement.

Remove all references to master.dbo.spt_datatype_info.

For more information, see "System Tables" in Microsoft SQL Server Building Applications.

Transactions (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

A data type conversion error not inside a transaction returned an error to SQL Server. If the data type conversion was inside a transaction, the transaction continued. For example:
USE pubs
CREATE TABLE test (c1 int)
GO
BEGIN TRANSACTION
GO
INSERT INTO test VALUES (1)
GO
INSERT INTO test VALUES ('aaa')
GO
COMMIT TRANSACTION
GO
SELECT *
FROM test

SQL Server returns an error when an attempted data type conversion fails. If the data type conversion error occurs inside a transaction, the transaction is terminated.

Expect differences in behavior as compared to earlier versions of SQL Server if a data type conversion fails inside a transaction.

Utilities (Level 1) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The probe login, which required no password, was used by DB-Library and Windows NT Performance Monitor. The DB-Library two-phase commit library used the probe login to check on the status of distributed transactions. It was also used by Windows NT Performance Monitor to get statistics from SQL Server.

The probe login has been eliminated. Windows NT Performance Monitor will always use Windows NT Authentication, known earlier as integrated security, to connect to SQL Server.

Ensure that your Windows NT username and password have the appropriate privileges to use Windows NT Performance Monitor.

Backward Compatibility Details (Level 2)

Backward Compatibility Details (Level 2) consists of important changes in Microsoft SQL Server version 7.0 that produce different behavior from earlier versions of SQL Server. For example, items in this category are those that have changed behavior in data type conversion or usage of selected functions, changed behavior of clauses in selected Transact-SQL statements and stored procedures, changed column names in selected system tables, and changed behavior due to the database compatibility setting. This topic covers backward compatibility details for these items.

This subheading

Relates to

Backup and Restore

BACKUP
CREATE DATABASE
ALTER DATABASE
RESTORE
sp_dboption

Bulk Copy

bcp Utility

Configuration Options

Setting Configuration Options
sp_configure (open objects and user connections options)

Database Pages and Extents

Pages and Extents

Data Types

CAST and CONVERT
Data Types

DB-Library

dbcursorfetchex
Trace Flags

Empty Strings

sp_dbcmptlevel 
CHARINDEX
DATALENGTH
LEFT
LTRIM
PATINDEX
REPLICATE
RIGHT
RTRIM
SPACE
SUBSTRING
UPDATETEXT

Indexes

CREATE INDEX

INSERT

sp_dbcmptlevel 
INSERT

Keyset Cursors

Keyset cursors

LTRIM and RTRIM

SET CURSOR_CLOSE_ON_COMMIT
ROLLBACK
DECLARE CURSOR

OBJECT_ID

OBJECT_ID

ODBC

SQLGetDiagRec
SQLMoreResults

RIGHT

Using Identifiers
Reserved Keywords

Security

GRANT
REVOKE
DENY
sp_addlinkedsrvlogin

SELECT

SELECT

SET SHOWPLAN

SET SHOWPLAN_ALL
SET SHOWPLAN_TEXT

System Tables

Information Schema Views
System Stored Procedures (Catalog Procedures)
sysdatabases (logptr and dumptrdate columns)
sysmessages (langid column)
syslogins (language column)
Computed Columns

Table Hints

DELETE
FROM
INSERT
SELECT
UPDATE

Transactions

SET TRANSACTION ISOLATION LEVEL

Triggers and System Stored Procedures

sp_dbcmptlevel
sp_dboption
sp_create_removable
CREATE TRIGGER
SET QUOTED_IDENTIFIER
SET ANSI_NULLS
SET ANSI_DEFAULTS

UPDATE

@@ERROR
UPDATE
INSERT

UPDATETEXT

UPDATETEXT
WRITETEXT

Views

DELETE
INSERT
UPDATE

Backup and Restore (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Using the SKIP and INIT clauses of the DUMP statement together overwrote the contents of the backup device unconditionally.

The SKIP and INIT clauses of the BACKUP statement preserve the Microsoft Tape Format media header. In some situations, this prevents overwriting the backup contents. The FORMAT clause overwrites the media unconditionally, generating a new header, and is required for media used for the first time or when necessary to overwrite the media header.

Expect different results as compared to earlier versions of SQL Server. If the media is empty, SKIP and INIT act the same as the FORMAT clause of the BACKUP or DUMP statements and write a new media header. If the media is not empty, SKIP and INIT do not write a new media header.

The LOAD statement did not create the database automatically when restoring the database backup.

It is no longer necessary to create the database before restoring it. The RESTORE statement re-creates the database automatically, including all files. However, database devices are not re-created in sysdevices. These devices are supported only for backward compatibility. After restoration, databases originally created using devices (DISK INIT) appear as if they had been created using SQL Server 7.0 file syntax.

Expect different results as compared to earlier versions of SQL Server. Consider using the new syntax in CREATE DATABASE and ALTER DATABASE for specifying files.

The NO_LOG clause of DUMP was used only when you ran out of space in the database and could not use DUMP TRANSACTION WITH TRUNCATE_ONLY to purge the log. The NO_LOG clause removes the inactive part of the log without making a backup copy of it, and saves space by not logging the operation. The TRUNCATE_ONLY clause of the DUMP statement removed the inactive part of the log without making a backup copy of it.

The NO_LOG and TRUNCATE_ONLY clauses of RESTORE are synonyms. Both clauses of BACKUP now remove the inactive part of the log without making a backup copy of it and truncates the log.

Expect different results as compared to earlier versions of SQL Server. Expect the NO_LOG and TRUNCATE_ONLY clauses of the BACKUP or DUMP statements to behave identically.

Recovery of multiple transaction logs could be performed without special keywords in the LOAD statement.

It is no longer possible to restore multiple transaction logs without using the WITH clauses of the RESTORE statement.

Expect different results as compared to earlier versions of SQL Server. Use the appropriate RESTORE syntax for restoring a database with multiple transaction logs as shown in the following examples. All but the last RESTORE statement should specify the NORECOVERY clause.

When loading a database, all database options of sp_dboption were unaffected and had to be set manually.

Changes to all sp_dboption database settings (except the offline, merge publish, published, and subscribed settings) are logged, like any other change. When a database is restored and recovered, all database options of sp_dboption are rolled forward. Every database option will be in its expected state at the time when recovery finished, consistent with the remainder of the database.

Expect different results as compared to earlier versions of SQL Server. It is no longer necessary to reset the database options after a RESTORE operation.

Examples 

A. Restore a database by applying a full database backup and multiple transaction logs

This example restores a database with multiple transaction log backups.

RESTORE DATABASE mydb
FROM mydb
WITH NORECOVERY

RESTORE LOG mydb
FROM mydb_log1
WITH NORECOVERY

RESTORE LOG mydb
FROM mydb_log2
WITH RECOVERY

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

A warm standby server could be brought up in read-only mode between recovery of each transaction log, provided that the no chkpt. on recovery option of sp_dboption was enabled.

A warm standby server can be brought up in read-only mode between transaction log restore operations if an undo file is used.

Expect different results as compared to earlier versions of SQL Server. Use an undo file for a warm standby server using the STANDBY clause of RESTORE, as shown in the following example.

B. Restore a database using the STANDBY clause and an undo file 

This example brings the server up to allow write operations on the database(s) by using a final, necessary RESTORE statement.

RESTORE DATABASE mydatabase 
FROM mydb_backup
WITH NORECOVERY

RESTORE LOG mydb
FROM mydb_log1
WITH RECOVERY STANDBY (FILENAME = 'c:\mssql7\data\mydbundo.dat')

RESTORE LOG mydb
FROM mydb_log2
WITH RECOVERY STANDBY (FILENAME = 'c:\mssql7\data\mydbundo.dat')

RESTORE DATABASE mydb
WITH RECOVERY

Bulk Copy (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The bcp utility (using DB-Library) could import datetime or smalldatetime values in character-mode data files using:
· The default format used by DB-Library (mmm dd yyy hh:mmXX where XX is either A.M. or P.M.).
· Any format supported by dbconvert except the ODBC format.
bcp exported character-mode data files with datetime and smalldatetime values by using the default DB-Library format.

The bcp utility (which uses ODBC) can import datetime and smalldatetime values in character-mode data files using:
· The default format used by DB-Library.
· The format used by ODBC (yyyy-mm-dd hh:mm:ss[.f…]).
However, bcp does not use other formats supported by dbconvert.
bcp exports datetime and smalldatetime values using the ODBC default format.

Expect different results as compared to earlier versions of SQL Server. To bulk copy data in character mode between SQL Server 6.x and SQL Server 7.0 servers, use the same bcp version (either SQL Server 6.x or SQL Server 7.0) for both importing and exporting data.
To export data from a SQL Server 7.0 server into a character-mode data file and later import that data using a DB-Library bulk copy application, use the SQL Server 6.x version of bcp.
For existing datetime or smalldatetime values in a character-mode data file in a format other than the DB-Library default:
· Change the values to the DB-Library default format for continued use with SQL Server 6.x and SQL Server 7.0 bcp.
· Change the values to the ODBC format for exclusive use with SQL Server 7.0 bcp.

bcp exported money values in character mode data files using digit grouping symbols (for example, the comma in the United States when using the U.S. version of SQL Server, the US version of Microsoft Windows NT, and US settings) and two digits after the decimal point.

bcp exports money values in character mode data files without digit grouping symbols, but with four digits after the decimal point.

Expect different results as compared to earlier versions of SQL Server.
To read character files created by version 6.x DB-Library bcp in SQL Server 7.0, use the -6 switch. For more information, see "bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference.

Configuration Options (Level 2) 

Administrative scripts may have used these configuration options. For more information about configuration options, see "sp_configure" in Microsoft SQL Server Building Applications and "Setting Configuration Options" in Microsoft SQL Server Administrator's Companion. 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

open objects set the maximum number of database objects that can be open at one time on SQL Server.

Now an advanced option. Default value of 0 indicates automatic growth.

Expect different results as compared to earlier versions of SQL Server. Remove or comment out all references to open objects. For more information, see "open objects Option" in Microsoft SQL Server Administrator's Companion.

user connections set the maximum allowed number of simultaneous connections to SQL Server.

Now an advanced option. Default value of 0 indicates automatic growth.

Expect different results as compared to earlier versions of SQL Server. Remove or comment out all references to user connections Option.

Database Pages and Extents (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

A database extent consisted of eight 2 KB pages.

A database extent consists of eight 8 KB pages. Different objects can now share an extent or an object can have its own extent(s). A table and index both have a minimum of two pages.

Expect different results as compared to earlier versions of SQL Server. Adjust disk space requirements for adequate database storage. For more information, see "Pages and Extents" in this volume.

Data Types (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Conversion of binary or varbinary to decimal or numeric was explicit.

This conversion is implicit.

Expect different results as compared to earlier versions of SQL Server. Expect conversions of binary or varbinary to decimal or numeric to be implicit. For more information about data type conversions, see "CAST AND CONVERT" in Microsoft SQL Server Transact-SQL and Utilities Reference. For more information about system-supplied data types, see "Data Types" in Microsoft SQL Server Administrator's Companion.

Conversion of binary or varbinary to smallmoney was not allowed.

This conversion is allowed.

Expect different results as compared to earlier versions of SQL Server. Use as appropriate.

Conversion of datetime or smalldatetime to decimal, numeric, float, real, int, smallint, tinyint, money, smallmoney, or bit was not allowed.

This conversion is allowed.

Expect different results as compared to earlier versions of SQL Server. Use as appropriate.

Conversion of float or real to binary or varbinary was not allowed.

This conversion is allowed.

Expect different results as compared to earlier versions of SQL Server. Use as appropriate.

Conversion of money or smallmoney to char or varchar was implicit.

This conversion is explicit.

Expect different results as compared to earlier versions of SQL Server. Expect conversions of money or smallmoney to char or varchar to be explicit.

Conversion of bit to money or smallmoney was not allowed.

This conversion is allowed.

Expect different results as compared to earlier versions of SQL Server. Use as appropriate.

Conversion of text to image was not allowed.

This conversion is allowed.

Expect different results as compared to earlier versions of SQL Server. Use as appropriate.

sysname data type was varchar(30).

sysname data type is nvarchar(128), which allows for 128 Unicode characters.

Expect different results as compared to earlier versions of SQL Server. Expect any columns or local variables defined as sysname to allow Unicode data. For more information about sysname see "Data Types" in Microsoft SQL Server Building Applications. 

No direct support for the nchar, nvarchar, and ntext Unicode data types because maximum storage was 255 bytes for char, binary, varchar, and varbinary data types.

The maximum number of bytes that can be stored in char, binary, varchar, and varbinary data types is increased to 8,000. SQL Server 7.0 clients fully support the nchar, nvarchar, and ntext data types. SQL Server 6.x clients accessing a version 7.0 with these Unicode data types will experience these results:
nvarchar data is returned as varchar and nchar data is returned as char. nvarchar and nchar values longer than 255 double-byte characters are truncated to 255 single-byte characters.
Attempting to access ntext data causes SQL Server to issue a 4004 error. ntext data cannot be sent to version 6.x clients.

Expect different results as compared to earlier versions of SQL Server. Expect changes in data when accessing version 7.0 data from version 6.x clients. To eliminate these differences, upgrade the clients to version 7.0 client software.

No direct support for the nchar, nvarchar, and ntext Unicode data types because maximum storage was 255 bytes for char, binary, varchar, and varbinary data types.

char, varchar, binary, and varbinary values longer than 255 bytes are truncated to 255 bytes.

 

Using CONVERT to convert an empty string to int (CONVERT(int, "") or float (CONVERT(int, "") returned a zero.

Using CAST or CONVERT to convert an empty string to int (CAST("" AS int) or float (CAST("" AS int) returns an error message.

Expect different results as compared to earlier versions of SQL Server. Expect different results when converting an empty string to int or float, compared to pre-SQL Server 7.0.

DB-Library (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

When connected to a version 6.x SQL Server, a call to dbcursorfetchex resulting in a cursor position after the end of the cursor result set returned:
· FAIL, with either a keyset or an insensitive cursor.
· SUCCEED, with all row status indicators set to 0, with either a dynamic or forward cursor.

A call to dbcursorfetchex resulting in a cursor position after the end of the cursor result set returns SUCCEED. All row status indicators are set to 0. This behavior applies to all types of cursors.

Expect different results as compared to earlier versions of SQL Server. Expect different results, compared to SQL Server 6.x, when a call to dbcursorfetchex results in a cursor position after the end of the cursor result set. To achieve SQL Server 6.x behavior, use trace flag 7505. For more information, see "Trace Flags" in Microsoft SQL Server Transact-SQL and Utilities Reference.

Empty Strings (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

An empty string could be interpreted as either a NULL or a single blank character.

Interpretation of an empty string is controlled by the setting of sp_dbcmptlevel. If the setting of sp_dbcmptlevel is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the setting of sp_dbcmptlevel is equal to 70, SQL Server interprets empty strings as empty strings. For more information, see "sp_dbcmptlevel" in Microsoft SQL Server Building Applications. 

Expect different results as compared to earlier versions of SQL Server. Expect differences in interpretation of empty strings compared to earlier versions of SQL Server. Transact-SQL functions and statements affected by the setting of sp_dbcmptlevel include CHARINDEX, DATALENGTH, LEFT, LTRIM, PATINDEX, REPLICATE, RIGHT, RTRIM, SPACE, SUBSTRING, and UPDATETEXT.

Indexes (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The DROP INDEX statement dropped the pages holding the clustered index B-tree when used on a clustered index.

On a clustered index, the DROP INDEX statement must rebuild all nonclustered indexes. SQL Server must also replace the clustered index keys in the nonclustered leaf rows with row pointers.

Expect different results as compared to earlier versions of SQL Server. Use the DROP_EXISTING clause of the CREATE INDEX statement if, for example, dropping or re-creating clustered indexes to set a new fill factor value.

INSERT (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

An INSERT x  SELECT INTO Y statement ignored table Y and inserted the SELECT results into table X, as shown.
INSERT X
SELECT select_list INTO Y

The INSERT…SELECT INTO syntax is retained only when the compatibility setting is equal to 60 or 65. If the compatibility setting is 70 and a similar query is executed, SQL Server returns a syntax error.

Expect different results as compared to earlier versions of SQL Server. Set the compatibility setting to 60 or 65 by executing sp_dbcmptlevel to retain version 6.x functionality for queries using INSERT …SELECT INTO syntax. Otherwise, use a compatibility setting of 70.

In an INSERT statement, a SELECT statement returning a scalar value was allowed in the VALUES clause.

The INSERT statement cannot have a SELECT statement in the VALUES clause as one of the values to be inserted. The version 6.x supportability is available only when the compatibility setting is equal to 60 or 65.

Expect different results as compared to earlier versions of SQL Server. Set the compatibility setting to 60 or 65 by executing sp_dbcmptlevel to retain version 6.x functionality for using a SELECT statement in the VALUES clause of an INSERT statement. Otherwise, use a compatibility setting of 70.

A ROLLBACK statement in a stored procedure referenced in an INSERT table EXEC procedure statement caused the INSERT to be rolled back, but the batch continued.

A ROLLBACK statement in the stored procedure referenced by an INSERT…EXEC statement causes the entire transaction to be rolled back and the batch stops executing. The version 6.x supportability is available only when the compatibility setting is equal to 60 or 65.

Expect different results as compared to earlier versions of SQL Server. Set the compatibility setting to 60 or 65 by executing sp_dbcmptlevel to retain version 6.x functionality for ROLLBACK statement behavior inside an INSERT…EXEC statement. Otherwise, use a compatibility setting of 70.

Keyset Cursors (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

When using a keyset cursor, a row deletion followed by a row insertion using the same key as the deleted row caused the inserted row to occupy the slot of the original row.

When using a keyset cursor, a row deletion followed by a row insertion with the same key as the deleted row allows the original row to remain empty and the newly inserted row to be inserted at the end.

Expect different results as compared to earlier versions of SQL Server. Expect a change in behavior when inserting and deleting rows with the same key values when using keyset cursors.

LTRIM and RTRIM Trimming Functions (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The LTRIM and RTRIM functions returned NULL in queries using zero-length strings:
SELECT RTRIM("")

SELECT DATALENGTH(RTRIM(""))

Zero-length strings are supported. The queries shown return non-null values; the first returns "" and the second returns 0.

Expect different results as compared to earlier versions of SQL Server. LTRIM and RTRIM provide different output from earlier versions of SQL Server.

OBJECT_ID (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

A temporary table name in the OBJECT_ID function could be specified without the database name preceding the temporary table name. For example:
CREATE TABLE #mytemptable
( c1 int, c2 char(2))

USE tempdb
SELECT OBJECT_ID('#mytemp
table')

The database name must be specified before the temporary table name in the OBJECT_ID function. For example:
SELECT OBJECT_ID('tempdb..#mytemptable')

 

Expect different results as compared to earlier versions of SQL Server. Specify the database name before the temporary table name in the OBJECT_ID function.

ODBC (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

SQL_ERROR was returned by SQLExecute, SQLExecDirect, or SQLParamData when extended stored procedures or batches met the following criteria:
· The first data-returning statement caused an error (either by a run-time error or a RAISERROR statement with severity greater than or equal to 11).
· There was data from any other statement, even a simple RETURN statement, after the error-causing statement.
Due to the SQL_ERROR return code, the statement handle was available for use immediately.

SQL_SUCCESS_WITH_INFO is returned when an ODBC 3.x application uses the ODBC SQL Server 3.5 driver included with this release (using SQLExecute, SQLExecDirect, or SQLParamData).
Due to the SQL_SUCCESS_WITH_INFO return code, process the results for that statement handle before it is available for use.

Expect different results as compared to earlier versions of SQL Server. Handle SQL_SUCCESS_WITH_INFO using SQLGetDiagRec, and then call SQLMoreResults to process the remaining results, as appropriate.

RIGHT (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

RIGHT was a reserved word.

RIGHT is a reserved keyword and should not be used for database object names (unless using identifiers). For more information about SQL Server 7.0 reserved keywords, see "Reserved Keywords" in Microsoft SQL Server Database Developer's Companion.

Expect different results as compared to earlier versions of SQL Server because RIGHT is now a reserved keyword. For more information about using RIGHT with identifiers, see "Using Identifiers" in Microsoft SQL Server Database Developer's Companion.

Security (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The GRANT and REVOKE statements granted and revoked permissions, respectively. The REVOKE statement denied a permission to a single user that was granted to the user's group.

The security model uses DENY in addition to GRANT and REVOKE. REVOKE has changed to remove a previously granted or denied permission. DENY creates an entry in the security system that denies a permission from a security account and prevents the user, group, or role from inheriting the permission through its group and role memberships. The REVOKE statement can no longer be used to deny permission to a user whose group has permission. Use the DENY statement to deny permissions explicitly to a specific user or group.

Expect different results as compared to earlier versions of SQL Server. Recognize that scripts using the pre-SQL Server 7.0 security model using GRANT and REVOKE behave differently than scripts using the current model of GRANT, REVOKE, and DENY if REVOKE was used to deny permissions to selected members of a group.

When executing an RPC, logins using integrated security mode referred to an internal login name with the backslashes (\) translated to underscores (_). For example, \Domain\Joe was translated to Domain_Joe.

Those servers upgraded to SQL Server that execute RPC calls no longer translate backslashes to underscores when using Windows NT Authentication. To use the SQL Server version 6.x naming convention for login names, use sp_addlinkedsrvlogin to map the backslash version of the username to an underscore version.

Expect different results as compared to earlier versions of SQL Server. Add references for sp_addlinkedsrvlogin to translate backslash version login names to underscore versions to maintain version 6.x login translations when the sending server of an RPC uses SQL Server version 7.0.

Examples 

A. Map specific backslash login to underscore login

This example maps the \LONDON1\nancyd login name to LONDON1_nancyd:

sp_addlinkedsrvlogin 'receiving_server_name', 
false, 
'LONDON1\nancyd', 
'LONDON1_nancyd', NULL

B. Map specific backslash login to sa login 

This example maps Nancy's LONDON1 login to the sa login, because Nancy's domain login is part of the built-in administrators group:

sp_addlinkedsrvlogin 'receiving_server_name', 
false, 
'LONDON1\nancyd', 
'sa', NULL

SELECT (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

A SELECT statement without an ORDER BY clause returned the rows in an apparent ordered set.

An explicit ORDER BY clause for a SELECT statement is required to ensure any useful ordering of data.

Expect different results as compared to earlier versions of SQL Server. Add an explicit ORDER BY clause to all SELECT statements needing to produce ordered rows.

SET SHOWPLAN (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

When SET SHOWPLAN was set ON, SQL Server executed Transact-SQL statements.

When set ON, the SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT statements, which replace SET SHOWPLAN, do not execute Transact-SQL statements.

Expect different results as compared to earlier versions of SQL Server. Expect a difference in behavior when SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT are set to ON.

System Tables (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

System tables were used internally by SQL Server for a wide range of uses.

Some system tables have had minor changes, while others have been replaced by Information Schema Views that provide the same information.

Expect different results as compared to earlier versions of SQL Server. Use the provided Information Schema Views or ODBC catalog stored procedures to obtain system catalog information.

The logptr column of sysdatabases was a pointer to the transaction log.

The logptr column has been renamed to status2.

Expect different results as compared to earlier versions of SQL Server. Remove all references of the logptr column of sysdatabases and replace with references to the status2 column.

The dumptrdate column of sysdatabases was the date of the last DUMP TRANSACTION.

This column is now Reserved.

Expect different results as compared to earlier versions of SQL Server. Remove or comment out all references to the dumptrdate column of sysdatabases 

The langid column of sysmessages contained the SQL Server message group ID.

The langid column has been renamed to msglangid.

Expect different results as compared to earlier versions of SQL Server. Remove all references of the langid column of sysmessages and replace with references to the msglangid column.

A NULL value for the language column of the syslogins table was equivalent to specifying us_english.

A NULL value for the language column is no longer equivalent to us_english.

Expect different results as compared to earlier versions of SQL Server. Remove all NULL values for the language column of syslogins and replace with the name of the language to be used.

System tables obtained their column values by insertion of a specific value (SQL Server version 7.0 uses computed columns in many system and user-defined tables).

System tables (and user-defined tables) can now use computed columns.

Expect different results as compared to earlier versions of SQL Server. SQL Server version 6.5 queries involving table hints and system tables may still produce the same result set, but may behave differently in SQL Server version 7.0. For example, the query may still wait for some locks even if the NOLOCK table hint has been specified in the query's FROM clause.

Table Hints (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

These table hints (previously called optimizer hints) could be specified as just the keyword following the FROM clause:
FASTFIRSTROW, HOLDLOCK,
INDEX,
NOLOCK,
PAGLOCK,
TABLOCK,
TABLOCKX, and UPDLOCK.

Table hints must be specified following the FROM clause using a WITH clause. Only the HOLDLOCK table hint can be specified with or without parentheses. All other table hints require parentheses around them.

Expect different results as compared to earlier versions of SQL Server. Put parentheses around all table hints. For more information, see "DELETE", "FROM", "INSERT", "SELECT", and "UPDATE" in Microsoft SQL Server Transact-SQL and Utilities Reference.

Transactions (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

When CURSOR_CLOSE_ON_COMMIT was set OFF, a ROLLBACK statement did not close a Transact-SQL cursor defined with the DECLARE CURSOR statement. Server cursors opened through database API functions were also left open after a ROLLBACK statement.

When CURSOR_CLOSE_ON_COMMIT is set OFF, a ROLLBACK statement closes any Transact-SQL cursor defined with the SQL-92 form of the DECLARE CURSOR statement, unless the DECLARE CURSOR statement contains either the INSENSITIVE or STATIC keywords. All API server cursors are also closed unless they have been defined as STATIC cursors (such as using the ODBC SQL_CURSOR_STATIC attribute).

Expect different results as compared to earlier versions of SQL Server. Reopen all cursors after issuing a ROLLBACK statement.

The REPEATABLE READ clause of the SET TRANSACTION ISOLATION LEVEL statement behaved identically to the SERIALIZABLE clause. There was no way to ensure repeatable reads without also protecting against phantoms (after a rollback, the value read logically never existed.) Transactions that required REPEATABLE READ semantics had to pay the additional concurrency penalty of serializability.

The REPEATABLE READ clause now does not necessarily protect against phantoms. Serializable transactions, set using the SERIALIZABLE clause of SET TRANSACTION ISOLATION LEVEL, allow less concurrency than the REPEATABLE READ clause because they protect against phantoms.

Expect different results as compared to earlier versions of SQL Server. Many applications only need REPEATABLE READ semantics for correct operation. Use the REPEATABLE READ clause of SET TRANSACTION ISOLATION LEVEL for applications requiring REPEATABLE READ semantics but that do not need phantom protection. If phantom protection is required, use the SERIALIZABLE clause.

Here is a summary of phantom protection for both SQL Server versions 6.5 and 7.0 using SET TRANSACTION ISLATION LEVEL.

Phantom protection

SQL Server 6.5

SQL Server 7.0

REPEATABLE READ

Yes

No

SERIALIZABLE

Yes

Yes

Triggers and System Stored Procedures (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

sp_helpsql provided syntax for Transact-SQL statements, system stored procedures, and other special topics.

sp_helpsql is included, but no longer returns syntax information for Transact-SQL statements or system stored procedures. Executing sp_helpsql produces a message that recommends obtaining syntax information from SQL Server Books Online.

Expect different results as compared to earlier versions of SQL Server. Use SQL Server Books Online for the syntax of Transact-SQL statements and system stored procedures.

Only one trigger for each data modification event (INSERT, UPDATE, DELETE) was allowed for each table. If a new trigger was created for a specific data modification event, it replaced the previous trigger.

Microsoft SQL Server allows multiple triggers to be created for each data modification event (DELETE, INSERT, or UPDATE). For example, if CREATE TRIGGER FOR UPDATE is executed for a table that already has an UPDATE trigger, an additional update trigger is created.

Expect different results as compared to earlier versions of SQL Server. Enable multiple triggers by setting the compatibility level to 70 for sp_dbcmptlevel. Retain pre-SQL Server 7.0 behavior by setting the compatibility level to 60 or 65. For more information, see "sp_dbcmptlevel"and "CREATE TRIGGER" in Microsoft SQL Server Transact-SQL and Utilities Reference.

If a trigger modified the table on which it was defined, the triggers were not invoked recursively for that modification.

SQL Server allows recursive invocation of triggers.

Expect different results as compared to earlier versions of SQL Server. Enable recursive triggers by setting the recursive triggers setting of sp_dboption.

Several parameters of sp_create_removable referred to devices.

Devices have been replaced with files and filegroups.

Expect different results as compared to earlier versions of SQL Server. Replace all device references in sp_create_removable with references to filegroups sp_create_removable.

References to text or image columns in either the inserted or deleted tables appeared as NULL.

References to text or image columns in both the inserted and deleted tables are no longer allowed unless the compatibility level setting of sp_dbcmptlevel is 60 or 65.

Expect different results as compared to earlier versions of SQL Server. Expect a difference in behavior when referring to text or image columns in inserted and deleted tables when using CREATE TRIGGER, depending on the setting of sp_dbcmptlevel.

SQL Server searched the current database followed by a search in master for a stored procedure using the sp_ prefix.

Stored procedures with the prefix sp_ are first looked up in master. If a user-defined stored procedure has the same name as a system-supplied stored procedure residing in master, SQL Server always finds the system-supplied stored procedure.

Expect different results as compared to earlier versions of SQL Server. Expect a difference in behavior when calling user-defined stored procedures with the sp_ prefix. Either explicitly qualify the name of the user-defined stored procedure, or rename the user-defined stored procedure.

The settings of SET ANSI_NULLS and SET QUOTED_IDENTIFIER statements were active only during the session that changed either option.

The settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS are saved when a stored procedure is created or altered. These original settings are enabled when the stored procedure is executed, and any client session settings are restored afterward. Within the stored procedure, any changes to SET ANSI_NULLS do not take effect until after the stored procedure executes.

Expect different results as compared to earlier versions of SQL Server. Develop databases or applications with one setting for SET QUOTED_IDENTIFIER, SET ANSI_NULLS, and all other pertinent SET options. If a client session changes SET options, do so outside of stored procedures.

When executing remote stored procedures, these procedures may have assumed non-standard behavior for the options set by SET ANSI_DEFAULTS. In addition, remote stored procedures may not have explicitly set these options (set by SET ANSI_DEFAULTS) to OFF.

When executing remote stored procedures, these procedures are executed with SET ANSI_DEFAULTS set to ON.

Expect different results as compared to earlier versions of SQL Server. Expect a difference in behavior when executing remote stored procedures if non-standard settings were used with SET ANSI_DEFAULTS, or if options were not explicitly set OFF.

UPDATE (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

An UPDATE statement encountering an arithmetic overflow condition would set the updated value to NULL, or skip the update if the value belonged to a non-null column.

SQL Server terminates the UPDATE or INSERT statement, even for one row, if arithmetic overflow occurs. No rows are updated.

Expect different results as compared to earlier versions of SQL Server. Use the @@ERROR function to test for errors after UPDATE or INSERT statements.

UPDATETEXT (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

UPDATETEXT initialized text columns to NULL, allocating a full 2K page.

If the compatibility level setting of sp_dbcmptlevel is 65, UPDATETEXT initializes text columns to NULL. However, if the compatibility level setting of sp_dbcmptlevel is 70, WRITETEXT initializes text columns to NULL; UPDATETEXT initializes text columns to an empty string.

Expect different results as compared to earlier versions of SQL Server. Expect differences in behavior when initializing text values to NULL (using UPDATETEXT or WRITETEXT) depending on the compatibility level setting of sp_dbcmptlevel.

Views (Level 2) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Updatable views were restricted to modifications that affected only one table.
 

Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition.

Expect different results as compared to earlier versions of SQL Server. Expect differences in behavior when working with updatable views with more than one table involved in the DELETE, INSERT, or UPDATE statements.

Backward Compatibility Details (Level 3)

Backward Compatibility Details (Level 3) consists of items supported for backward compatibility only. Any item included in this category is fully supported, but may be removed or unsupported in a future release. It is recommended that, as time allows, the backward compatible item be replaced with the recommended item. Microsoft SQL Server version 7.0 provides features that accomplish these tasks more efficiently and have ongoing support.

This topic covers backward compatibility details for these items.

This subheading

Relates to

Backup and Restore

BACKUP
RESTORE
CREATE DATABASE

Database Options

sp_dboption (publish option)
sp_replicationdboption

DBCC

DBCC NEWALLOC
DBCC CHECKALLOC
DBCC ROWLOCK
Architecture Enhancements
DBCC TEXTALL
DBCC CHECKDB
DBCC TEXTALLOC
DBCC CHECKTABLE
DBCC DBREPAIR
DROP DATABASE

Devices

Overview of SQL Server Architecture
DISK INIT
CREATE DATABASE
ALTER DATABASE
DISK REINIT
sp_logdevice
sp_dropdevice

Open Data Services

Opends60.lib
srv_config
srv_config_alloc
SRV_GETCONFIG
srv_init
srv_run
SRV_TDSVERSION
srv_getuserdata
srv_setuserdata
srv_errhandle
SRV_IODEAD
srv_log
srv_sfield
srv_event
SRV_EVENTDATA
SRV_GETSERVER
SRV_GOT_ATTENTION
srv_handle
srv_pre_handle
srv_post_handle
srv_setevent
srv_terminatethread
SRV_ATTENTION
SRV_CONNECT
SRV_DISCONNECT
SRV_LANGUAGE
SRV_RPC
SRV_EXIT
SRV_START
SRV_SLEEP
SRV_RESTART
SRV_STOP
srv_langcpy
srv_langlen
srv_langptr
srv_paramdata
srv_paramlen
srv_parammaxlen
srv_paramname
srv_paramnnumber
srv_paramset
srv_paramstatus
srv_paramtype
srv_returnval
srv_rpcdb
srv_rpcnumber
srv_rpcoptions
srv_clearstatistics
srv_sendstatistics
srv_alloc
srv_bmove
srv_bzero
srv_free
srv.h
srv_describe
srv_setcollen
srv_setcoldata
srv_paramsetoutput
srv_paraminfo
srv_sendstatus

Query Performance

SUSER_ID
SUSER_SID
SUSER_NAME
SUSER_SNAME
syslogins
sysdatabases
sysremotelogins
sysusers
sysalternates

Security

GRANT
Authentication
SETUSER

SELECT

FASTFIRSTROW
SELECT
INDEX = (index hint)

SET SHOWPLAN

SET SHOWPLAN_TEXT
SET SHOWPLAN_ALL
SQLGetDiagRec

System Stored Procedures (Extended)

xp_grantlogin
xp_revokelogin
sp_grantlogin
sp_revokelogin

System Stored Procedures (Replication)

sp_replstatus

System Stored Procedures (System)

sp_add_job
sp_add_jobschedule
sp_add_jobstep
sp_addtask
sp_delete_job
sp_delete_jobschedule
sp_delete_jobstep
sp_droptask
sp_help_jobhistory
sp_help_jobschedule
sp_help_jobstep
sp_helptask
sp_purge_jobhistory
sp_reassigntask
sp_start_job
sp_stop_job
sp_update_job
sp_update_jobschedule
sp_update_jobstep
sp_updatetask

Backup and Restore (Level 3) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The DUMP statement created database or transaction log backups (dumps).

The DUMP DATABASE and DUMP TRANSACTION statements are synonymous with BACKUP DATABASE and BACKUP LOG statements. Support for the DUMP DATABASE and DUMP TRANSACTION statements may be removed in a future release.

Consider removing all references of DUMP DATABASE and replacing with references to BACKUP DATABASE. Consider removing all references of DUMP TRANSACTION and replacing with references to BACKUP LOG.

The LOAD statement restored or loaded database or transaction log backups (dumps).

The LOAD DATABASE and LOAD TRANSACTION statements are synonymous with the RESTORE DATABASE and RESTORE LOG statements. Support for the LOAD DATABASE and LOAD TRANSACTION statements may be removed in a future release.

Consider removing all references of LOAD DATABASE and replacing with references to RESTORE DATABASE. Consider removing all references of LOAD TRANSACTION and replacing with references to RESTORE LOG. For more information about RESTORE DATABASE see "RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference. 

The CREATE DATABASE …FOR LOAD statement syntax created a destination database before its restoration from a database backup and prevented anyone from using the database between the CREATE DATABASE, ALTER DATABASE, and LOAD statements.

The CREATE DATABASE …FOR RESTORE syntax is supported for backward compatibility only. However, because SQL Server 7.0 now creates the destination database within a restore operation, it is recommended that the destination database not be created before executing the restore operation.

Do not create the database prior to restoring it.

Database Options (Level 3) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The publish option of sp_dboption enabled or disabled publishing in a database.

publish can still be used to enable or disable publishing in a database. However, this option is supported for backward compatibility only. It is recommended that sp_replicationdboption be used for enabling or disabling publishing in a database.

Consider removing all references of sp_dboption publish and replacing with references to sp_replicationdboption.

DBCC (Level 3) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

DBCC NEWALLOC checked data and index pages against corresponding extent structures.

DBCC NEWALLOC is supported for backward compatibility only and is identical to DBCC CHECKALLOC.

Consider removing all references of DBCC NEWALLOC and replacing with references to DBCC CHECKALLOC.

DBCC ROWLOCK dynamically enabled Insert Row Locking (IRL) operation on tables.

Row-level locking is automatic. DBCC ROWLOCK available for backward compatibility only.

Consider removing all references of DBCC ROWLOCK. For more information, see "Architecture Enhancements" in this volume.

DBCC TEXTALL selected tables in the database that had text or image columns and ran DBCC TEXTALLOC on them.

DBCC CHECKDB checks the consistency of text, ntext, and image columns in a database. DBCC TEXTALL is available for backward compatibility only.

Consider removing all references of DBCC TEXTALL and replacing with references to DBCC CHECKDB.

DBCC TEXTALLOC checked the allocation of text or image columns for a table.

DBCC CHECKTABLE checks the integrity of the data, index, text, ntext, and image pages for the specified table. DBCC TEXTALLOC is available for backward compatibility only.

Consider removing all references of DBCC TEXTALLOC and replacing with references to DBCC CHECKTABLE.

DBCC DBREPAIR dropped the specified, and usually damaged, database.

Use DROP DATABASE to drop or remove a SQL Server database. DBCC DBREPAIR is available for backward compatibility only.

Consider removing all references of DBCC DBREPAIR and replacing with references to DROP DATABASE.

Devices (Level 3) 

In Microsoft SQL Server 7.0, the following changes have been made to database architecture:

  • Operating system files replace database devices. 

  • Data files and transaction logs cannot co-exist on the same operating system file. 

  • A single operating system file cannot be shared by multiple databases. 

For more information about database architecture, see "Overview of SQL Server Architecture" in this volume.

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

DISK INIT created database or transaction log devices. When DISK INIT followed either a CREATE DATABASE or ALTER DATABASE statement, SQL Server used the specified device(s) for storing the specified database or transaction log.

The CREATE DATABASE statement syntax and ALTER DATABASE statement syntax both allow the creation of separate data and log files. Both CREATE DATABASE and ALTER DATABASE create operating system files and databases in a single step (generating a log file automatically, if none is specified with the LOG ON clause).

Consider removing all references to DISK INIT and replacing with references to either CREATE DATABASE or ALTER DATABASE.
DISK INIT has limited support in SQL Server 7.0; existing scripts will run as long as they do not have data and log sharing the same data file(s).

DISK REINIT restored device entries to appropriate system tables when the device entry was missing from sysdevices.

Removed; no replacement.

Consider removing or commenting out all references to DISK REINIT.

sp_logdevice put syslogs (contains the transaction log) on a separate database device. To add another log segment to a database with an existing log segment, it was necessary to execute DISK INIT followed by sp_logdevice.

Removed. The CREATE DATABASE statement creates a log file on a new operating system file.

Consider removing all references to sp_logdevice and replacing with references to CREATE DATABASE. Pre-SQL Server 7.0 scripts using the LOG ON clause of CREATE DATABASE will work as expected. Scripts without the LOG ON clause of CREATE DATABASE will have a log file generated automatically.

Devices created using DISK INIT and CREATE DATABASE could be dropped only by using sp_dropdevice.

Databases created without DISK INIT before CREATE DATABASE can be dropped with DROP DATABASE; otherwise, use sp_dropdevice.

Use sp_dropdevice when using DISK INIT, followed by CREATE DATABASE.

Examples 

A. Use both DISK INIT and CREATE DATABASE syntax 

This example uses DISK INIT and CREATE DATABASE and works in SQL Server versions 6.5 and 7.0:

DISK INIT name = 'testdb_data', 
physname = 'c:\testdb_data.dat', 
vdevno = 9, 
size = 10240
DISK INIT name = 'testdb_log', 
physname = 'c:\testdb_log.dat', 
vdevno = 8, 
size = 10240
CREATE DATABASE testdb 
ON testdb_data = 10 
LOG ON testdb_log = 10
GO

B. Use sp_logdevice and CREATE DATABASE in 7.0 fails 

In pre-SQL Server 7.0, this script created a 20 MB database consisting of the two files named testdb_data and testdb_log. This script also moved the transaction log to the testdb_log device by using sp_logdevice.

Note Scripts like this one were usually generated by the SQL Server 6.5 sp_help_revdatabase system stored procedure, which used sp_logdevice to ensure the proper device layout for database restores. Because SQL Server 7.0 creates the database when it is restored, scripts such as these are no longer necessary.

-- Pre-SQL Server 7.0 example.
DISK INIT name = 'testdb_data', 
physname = 'c:\testdb_data.dat', 
vdevno = 9, 
size = 10240
DISK INIT name = 'testdb_log', 
physname = 'c:\testdb_log.dat', 
vdevno = 8, 
size = 10240
CREATE DATABASE testdb on testdb_data = 10, testdb_log = 10
-- Use sp_logdevice to move the log to the testdb_log device.
sp_logdevice testdb, testdb_log 

In SQL Server 7.0, the above script does not work the same as in pre-SQL Server 7.0 because sp_logdevice no longer exists.

In SQL Server 7.0, this script creates a 20 MB database consisting of the two files named testdb_data and testdb_log. In addition, SQL Server generates a log file automatically, which is 25 percent of the database size. In the following script (using the devices created earlier), a 10 MB log file is generated automatically:

CREATE DATABASE testdb on testdb_data = 10, testdb_log = 10

C. Use CREATE DATABASE syntax only

Using the SQL Server 7.0 CREATE DATABASE syntax, the database from the earlier example could be created as follows:

CREATE DATABASE testdb ON (name = 'testdb_data',
filename = 'd:\testdb_data.dat', size = 10)
LOG ON (name = 'testdb_log', filename = 'd:\testdb_log.dat', 
size = 10)

Open Data Services (Level 3) 

Important When upgrading an existing Open Data Services application to SQL Server 7.0, it is recommended that you recompile all existing Open Data Services applications with the SQL Server 7.0 Open Data Services header file (Srv.h) and library file (Opends60.lib). For more information about Open Data Services, see "Backward Compatibility Details (Level 1)" in this volume.

The Open Data Services gateway functions, macros, and events listed in the table are supported for backward compatibility only.

Level

Function/macro name

Server Initialization and Configuration

srv_config
srv_config_alloc
SRV_GETCONFIG
srv_init
srv_run
SRV_TDSVERSION

Data Retrieval

srv_getuserdata
srv_setuserdata

Error and Message Handling

srv_errhandle
SRV_IODEAD
srv_log
srv_sendstatus
srv_sfield

Event Management

srv_event
SRV_EVENTDATA
SRV_GETSERVER
SRV_GOT_ATTENTION
srv_handle
srv_pre_handle
srv_post_handle
srv_setevent
srv_terminatethread

Event Types

SRV_ATTENTION
SRV_CONNECT
SRV_DISCONNECT
SRV_LANGUAGE
SRV_RPC
SRV_EXIT
SRV_START
SRV_SLEEP
SRV_RESTART
SRV_STOP

Language Event Processing

srv_langcpy
srv_langlen
srv_langptr

Remote Stored Procedure Event Processing

srv_paramdata
srv_paramlen
srv_parammaxlen
srv_paramname
srv_paramnnumber
srv_paramset
srv_paramstatus
srv_paramtype
srv_returnval
srv_rpcdb
srv_rpcnumber
srv_rpcoptions

Application Management

srv_clearstatistics
srv_sendstatistics

Memory Management

srv_alloc
srv_bmove
srv_bzero
srv_free

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Multiple header files were used in Open Data Services, including:
· Srvapi.h
· Srvconst.h
· Srvdbtyp.h
· Srvmisc.h
· Srvstruc.h
· Srvtok.h
· Srvtypes.h

The contents of these header files have been consolidated into srv.h. When recompiling a gateway applications, only srv.h must be included.

Consider removing all references to SQL Server 6.x header files and replacing with references to the srv.h header file.

srv_describe
srv_setcollen
srv_setcoldata

These Open Data Services extended stored procedure functions have been enhanced to support new data types.

Use these enhanced extended stored procedure functions (srv_describe, srv_setcollen, and srv_setcoldata), as appropriate.

srv_describe

srv_describe does not support data conversion to or from the new SQL Server 7.0 data types.

Consider removing or commenting out all references to srv_describe when using data types new to SQL Server 7.0.

srv_paramset *

srv_parameset has been superseded by srv_paramsetoutput.

Consider removing all references of srv_paramset and replacing with references to srv_paramsetoutput to obtain access to new SQL Server 7.0 data types in extended stored procedures.

srv_paramdata*
srv_paramlen*
srv_parammaxlen*
srv_paramtype*

These extended stored procedure functions have been superseded by srv_paraminfo.

Consider removing all references of srv_paramdata, srv_paramlen, srv_parammaxlen, or srv_paramtype and replacing with references to srv_paraminfo.

* These extended stored procedure functions will support some of the new SQL Server 7.0 data types and return data compatible with version 6.x format whenever possible. For example, if a parameter is nchar(25) and srv_paramdata accesses the data, srv_paramdata returns the data as char(25). For obtaining new SQL Server 7.0 data types, use srv_paraminfo and srv_paramsetoutput.

Query Performance (Level 3) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Queries could include a server user ID (SUID) without performance implications, as shown in the following table.

Queries using SUIDs continue to run and produce the same results as in earlier versions of SQL Server. However, there is a severe performance penalty because SUIDs are no longer native to the new security design.

Consider removing all references to SUIDs and replacing with references to SIDs (as shown in the following table) to avoid degradation in query performance.

SQL Server 6.x SUID

Replace with SQL Server 7.0 SID

SUSER_ID

SUSER_SID, which returns a SID

SUSER_NAME

SUSER_SNAME, which accepts a SID as input

syslogins.suid

syslogins.sid

sysdatabases.suid

sysdatabases.sid

sysremotelogins.suid

sysremotelogins.sid

sysusers.suid

sysusers.sid

sysalternates.suid

sysusers.isaliased

sysalternates.altsuid

sysusers.isaliased

Examples 

A. Use SIDs and SUIDs to display login names of users in sysusers 

This example shows a SQL Server 6.x query that displayed the login names of all users in sysusers:

SELECT L.name 
FROM master.dbo.syslogins L, sysusers U 
WHERE L.suid = U.suid
-- Or
SELECT suser_name(suid) AS name
FROM sysusers

Here is the earlier query rewritten to use SIDs rather than SUIDs:

SELECT L.loginname 
FROM master.dbo.syslogins L, sysusers U 
WHERE L.sid = U.sid
-- Or
SELECT suser_sname(sid) AS name
FROM sysusers

B. Use SIDs and SUIDs to display aliased usernames 

This example shows a SQL Server 6.x query that displayed the login names of all logins aliased to some other login:

SELECT suser_name(suid) AS name
FROM sysalternates

Here is the earlier query rewritten to use SIDs rather than SUIDs:

SELECT suser_sname(sid) AS name
FROM sysusers 
WHERE isaliased = 1

Security (Level 3) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The ON {table | view} ( column [,n]) syntax for the GRANT statement assigned the specified permissions to the column(s) given for the specified table or view.

The ON {table | view} ( column [,n]) syntax for the GRANT statement is supported for backward compatibility only.

Consider using the SQL-92 standard GRANT syntax for object permissions, and placing the column list before the ON clause.

The term integrated security allowed a SQL Server to use Windows NT Authentication mechanisms to validate logins for all connections. Standard security used SQL Server's own login validation process for all connections. Mixed security allowed login requests to be validated using either integrated or standard security.

The terms Windows NT Authentication and Mixed Mode, respectively, replace integrated security and mixed security. Standard security no longer exists.

Consider using the terms Windows NT Authentication and Mixed Mode rather than integrated security and mixed security. Do not refer to standard security. For more information about security modes, see "Authentication" in Microsoft SQL Server Administrator's Companion. 

The SETUSER statement allowed a database owner to impersonate another user.

SETUSER is included in Microsoft SQL Server version 7.0 for backward compatibility only, and is not recommended to be used. This statement may no longer be supported in a future release of SQL Server.

Consider removing or commenting out all references to SETUSER.

SELECT (Level 3) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The FASTFIRSTROW optimizer hint caused the optimizer to use the nonclustered index if one matches the ORDER BY clause.

The OPTION (FAST n) query hint replaces FASTFIRSTROW. However, FASTFIRSTROW is maintained for backward compatibility only.

Consider removing all references to FASTFIRSTROW in SELECT statements and replacing with references to OPTION (FAST n).

The INDEX = syntax specified one or more indexes to use for a table hint.

Supported for backward compatibility only.

Consider removing all references to INDEX = and replacing (when using multiple index hints) with references to INDEX( index , index) as shown in SELECT.

SET SHOWPLAN (Level 3) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The SET SHOWPLAN statement returned output as informational messages through SQLGetDiagRec in ODBC, or through the message handler in DB-Library applications.

The SET SHOWPLAN statement is no longer supported. It has been replaced by SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. The output of SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL is returned not as informational messages, but as a result set.

Consider removing all references of SET SHOWPLAN and replacing with references to either SET SHOWPLAN_TEXT (to display readable text) or SET SHOWPLAN_ALL (to display output that can be parsed more easily by an application building a report of showplan output). The application needs to process the output as part of the result set, not as messages returned through the ODBC SQLGetDiagRec function or the DB-Library message handler.

System Stored Procedures (Extended) (Level 3) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

xp_grantlogin and xp_revokelogin granted or revoked SQL Server access to a Windows NT-based group or user.

Use sp_grantlogin and sp_revokelogin even though xp_grantlogin and xp_revokelogin are supported for backward compatibility only.

Consider removing all references of xp_grantlogin and xp_revokelogin and replacing with references to sp_grantlogin and sp_revokelogin, respectively.

System Stored Procedures (Replication) (Level 3) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

sp_replstatus updated the internal table structure for replication.

Even though sp_replstatus is still available, support for this stored procedure may not be available in a later release.

Consider using the minimum number of references to sp_replstatus.

System Stored Procedures (System) (Level 3) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Job management was called task management, and several system stored procedures allowed system administrators to create and manage tasks.

The task management system stored procedures are no longer documented and are included for backward compatibility only.

Even though SQL Server 7.0 supports the task management system stored procedures (sp_addtask, sp_droptask, sp_helptask, sp_reassigntask, and sp_updatetask) for scheduling and managing SQL Server jobs, consider using either SQL Server Enterprise Manager or the job-related system stored procedures listed in the following table be used for managing jobs.

The task-related stored procedures listed in the Pre-SQL Server 7.0 column below have been replaced by the corresponding job-related stored procedures shown in the SQL Server 7.0 column.

Pre-SQL Server 7.0

SQL Server 7.0

sp_addtask

sp_add_job
sp_add_jobstep
sp_add_jobschedule
sp_start_job

sp_droptask

sp_delete_job
sp_delete_jobstep
sp_delete_jobschedule  

sp_helptask

sp_help_jobhistory
sp_help_jobschedule
sp_help_jobstep

sp_reassigntask

sp_purge_jobhistory
sp_stop_job

sp_updatetask

sp_update_job
sp_update_jobstep
sp_update_jobschedule  

Backward Compatibility Details (Level 4)

Backward Compatibility Details (Level 4) consists of minor changes in Microsoft SQL Server version 7.0 that produce different behavior from earlier versions of SQL Server. For example, items in this level are either ignored or have one or more ignored parameters, changes to byte lengths, added parameters or columns, or changed data type columns.

This topic covers backward compatibility details for these items.

This subheading

Relates to these items

Aliases

Roles
Managing Permissions

Backup and Restore

RESTORE HEADERONLY
LOAD HEADERONLY

Configuration

sp_configure (media retention option)
Setting Configuration Options

CREATE PROCEDURE

CREATE TABLE
SELECT INTO

Data Types

decimal and numeric
Using Mathematical Functions
+ (Add)
- (Subtract)
* (Multiply)
/ (Divide)
ATN2
AVG
CAST and CONVERT
EXP
POWER
RADIANS
ROUND
SUM

DATEPART and SET DATEFIRST

SET DATEFIRST
DATEPART

DBCC

DBCC

DBCS Space Comparisons

Unicode space characters

DELETE and SELECT

FROM

Devices

ALTER DATABASE

Functions

@@DBTS

Global Variables

Functions

ODBC

SQL_COPT_SS_PERF_QUERY_INTERVAL
SQLMoreResults
SQL_NO_DATA

Rebuilding the master Database

Rebuild Master Utility

Rebuilding the Registry

setup /t
regrebld Utility

Replication

Replication Between Different Versions of
SQL Server
Restricted Publications
Subscribing to One or More Articles of a
Publication

Security

SYSTEM_USER

SELECT

SELECT
FROM

Triggers and System Stored Procedures (System)

CREATE TRIGGER
sp_dboption (recursive triggers option) 
sp_tableoption
xp_readmail
xp_sendamil

UPDATE

 

Utilities

SQL Server Query Analyzer
isql utility

Aliases (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

An alias allowed a user to temporarily assume the identity of another user within a database and perform actions as the aliased user. For example, the database owner could be aliased to a user so they could act as that user, if the user were on vacation.

Roles have replaced aliases. Because a user can belong to more than one role at a time, it is no longer necessary to assume the identity of another. Users belonging to the same roles have the same permissions automatically, assuming permissions are only applied at the role level, not the user level.

Expect different results as compared to earlier versions of SQL Server. Use roles instead of aliases. For more information about database roles, see "Managing Permissions" in Microsoft SQL Server Administrator's Companion. 

Backup and Restore (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The LOAD HEADERONLY statement retrieved a result set detailing the header information from a database dump.

The result set returned for the LOAD HEADERONLY statement has changed.

Expect different results as compared to earlier versions of SQL Server. Expect a different result set from LOAD HEADERONLY, compared to pre-SQL Server 7.0 versions of LOAD HEADERONLY. For more information about LOAD HEADERONLY, see "LOAD" in Microsoft SQL Server Building Applications. 

Configuration (Level 4) 

Administration scripts may have used these configuration options. For more information about configuration options, see "sp_configure" in Microsoft SQL Server Building Applications and "Setting Configuration Options" in Microsoft SQL Server Administrator's Companion. 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Administration scripts may have used the nonadvanced media retention option to set the number of days to retain each backup medium after it has been used for a database or transaction log dump.

media retention is now an advanced option.

Expect different results as compared to earlier versions of SQL Server. Expect the media retention configuration option to appear only if you have enabled the advanced configuration options of sp_configure.

CREATE PROCEDURE (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

CREATE PROCEDURE statements failed if they contained a CREATE TABLE or SELECT INTO statement creating a temporary table with the same name as a temporary table that existed at the time the CREATE PROCEDURE statement was executed.

The CREATE PROCEDURE statement succeeds.

Recode any logic that depended on the earlier behavior.

Data Types (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The decimal and numeric data types could use anywhere from 2 through 17 bytes to store a value, depending on the precision of the stored value.

numeric and decimal now use 5, 9, 13, or 17 bytes of storage.

Expect different results as compared to earlier versions of SQL Server. Make sure that databases using numeric or decimal data types have sufficient storage for the change in storage bytes.

Results that were too small to display, called floating point underflow, returned inconsistent results for some mathematical operators and functions.

SQL Server now returns 0.0 and no error message for all instances of floating point underflow.
Because of the fixed size of floating point numbers like the float and real data types, approximate numeric data have intrinsic precision and ranges of values. In cases of floating point underflow, a result of 0.0 will be returned and no error message will be displayed. For example, the mathematical calculation of 2 to the -100.0 power would have a result 0.0.

Expect different results as compared to earlier versions of SQL Server. Expect different results with floating point underflow with any of the Using Mathematical Functions or operators
+ (Add)
-(Subtract)
* (Multiply)
/ (Divide)
ATN2
AVG•CONVERT
EXP
POWERRADIANS
SUM

A negative second parameter equal to the number of digits in the ROUND expression returned a value of 0 for integer, float, and money data types. When the second parameter was negative and less than the number of digits in the ROUND expression, ROUND returned a value that rounded the right-most digit down to 0.

When the second parameter in the ROUND function is negative for all data types, SQL Server returns a value rounded up to the next highest value, respecting decimal places, where appropriate.

Expect different results as compared to earlier versions of SQL Server. Expect different results with the ROUND function, compared to earlier versions of SQL Server, when the second parameter is negative.

The DATEADD and DATEDIFF functions returned a date value when adding or subtracting date values.

Direct date value addition and subtraction operations are supported for and datetime and smalldatetime using the +(Add) and -(Subtract) operators.

For simple date arithmetic, you can also use addition ( + (Add) ) or subtraction ( - (Subtract)) instead of DATEADD and DATEDIFF.

DATEPART and SET DATEFIRST (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The SET DATEFIRST setting of the DATEPART function had no effect on the week datepart.

The week datepart may give values different from earlier versions of SQL Server. However, any difference will appear only if the SET DATEFIRST setting is not the default (the U.S. English default is 7, or Saturday).
If the year provided in the DATEPART function has 366 days, a week value of 54 can be returned if the first week of the year starts on a Saturday, and the year ends on the same day of the week with the first day of the week counted from Sunday.
When using the ISO 8601 standard, week values are always from 1 through 53, as the first week of the year is guaranteed to have a minimum of 4 days.

Expect different results as compared to earlier versions of SQL Server. Use the default value for SET DATEFIRST so that DATEPART returns the expected results for the week datepart. Otherwise, DATEPART values will be one less than expected.

DBCC (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Each DBCC statement had a certain output format.

The output formats of many DBCC statements have changed.

Expect different results as compared to earlier versions of SQL Server. Expect changes in output from DBCC statements as compared to pre-SQL Server version 7.0 DBCC statements.

The DBCC PERFMON and DBCC SQLPERF statements documented SQL Server performance statistics used for studying SQL Server performance.

No longer documented. These statements may change in a future release of SQL Server.

Use the SQL Server Performance Monitor to monitor the performance counters for SQL Server. For more information, see "Monitoring with Windows NT Performance Monitor" in Microsoft SQL Server Administrator's Companion. 

DBCS String Comparisons (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

When comparing DBCS space characters, the Unicode A140 space character (U-A140) was not equal to the Unicode 0020 (U-0020) space character.

Comparisons involving the Unicode A140 space character (U-A140) are now equivalent to the Unicode 0020 (U-0020) space character.

Expect different results as compared to earlier versions of SQL Server when comparing DBCS space characters.

DELETE and SELECT (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Duplicate table names in the FROM clause of a SQL Server version 6.0 DELETE or SELECT statement caused SQL Server to treat both table references as the same table. SQL Server discarded the reference to the second authors table in this SELECT example:
USE pubs
GO
SELECT *
FROM authors, authors
GO
 

Duplicate table names in the FROM clause of a DELETE or SELECT statement generated errors in SQL Server. Rewrite statements using aliases. Here is a SELECT example:
SELECT *
FROM pubs..authors AS
a1, pubs.dbo.authors
AS a2

USE pubs
SELECT *
FROM authors AS au1, authors AS au2

Expect different results as compared to SQL Server version 6.0. Rewrite DELETE and SELECT statements to use aliases in the FROM clause when referring to more than one instance of the same table.

However, if the table names specified in the FROM clause of the DELETE or SELECT were not identical, SQL Server version 6.0 treated the two table references as two different tables as in this SELECT example:
USE pubs
GO
SELECT *
FROM pubs..authors,
pubs.dbo.authors
GO

 

 

Devices (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The DISK RESIZE statement altered the size of a database device.

The DISK RESIZE statement is supported, but may not be supported in future releases. In addition, the DISK RESIZE statement does not alter the size of the database. Instead, use ALTER DATABASE.

Expect different results as compared to earlier versions of SQL Server. Use the MODIFY file_decl clause of the ALTER DATABASE MODIFY statement to alter the size of a database.

Functions (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The @@DBTS global variable was incremented any time any page in the database was modified in any way.

The value returned by @@DBTS changes only if a row containing a timestamp column is modified.

Expect different results as compared to earlier versions of SQL Server when using @@DBTS.

Global Variables (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Global variables were those system-supplied, predeclared variables that were distinguished from local variables by having two at symbols (@@) preceding their names.

Transact-SQL global variables are a form of function and are now referred to as functions.

Expect different results as compared to earlier versions of SQL Server. For more information, see "Functions" in Microsoft SQL Server Building Applications.

ODBC (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

In the version 2.65 ODBC driver, the long-running query interval, specified by calling SQLSetConnectOption with the driver-specific connection option SQL_COPT_SS_PERF_QUERY_INTERVAL, was specified in seconds.

The SQL_COPT_SS_PERF
_QUERY_INTERVAL value is specified in milliseconds.

Expect different results as compared to earlier versions of SQL Server. Multiply the value of SQL_COPT_SS_PERF_QUERY_INTERVAL by 1,000 to convert the number of seconds to milliseconds. For more information about SQL_COPT_SS_PERF_QUERY_INTERVAL, see "SQLSetConnectAttr" in Microsoft SQL Server Building Applications. 

For earlier versions of the ODBC SQL Server driver, messages from consecutive PRINT, RAISERROR, DBCC or similar statements (in a batch or stored procedure) were combined into a single result set.

For the ODBC SQL Server 3.5 driver (included with SQL Server 7.0), messages from consecutive PRINT, RAISERROR, DBCC or similar statements (in a batch or stored procedure) are returned in a separate result set for each statement.

Expect different results as compared to earlier versions of SQL Server. Call SQLMoreResults to process the result set from each statement.

Earlier versions of the ODBC SQL Server driver returned SQL_SUCCESS when executing a searched UPDATE or DELETE statement that affects no rows (using SQLExecute, SQLExecDirect, or SQLParamData). SQLRowCount returned zero.

When an ODBC version 3.x application uses the ODBC SQL Server 3.5 driver included with this release, it returns SQL_NO_DATA when executing a searched UPDATE or DELETE statement that affects no rows (using SQLExecute, SQLExecDirect, or SQLParamData). SQLRowCount still returns zero.

Expect different results as compared to earlier versions of SQL Server. Handle SQL_NO_DATA appropriately.

Rebuilding the master Database (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Executing SQL Server Setup rebuilt the master database.

No longer supported. SQL Server includes the Rebuild Master (rebuildm) utility.

Use the Rebuild Master (rebuildm) Utility located in the \Mssql7\Binn folder to rebuild the master database.

Rebuilding the Registry (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Executing this SQL Server Setup rebuilt the registry:
setup/t
RegistryRebuild = On.

No longer supported. SQL Server includes the regrebld utility for backing up and restoring the registry.

Use the regrebld utility located in the \Mssql7\Binn folder to rebuild the registry.

Replication (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Restricted publications could be created through SQL Server Enterprise Manager and used in replicating data.

No longer supported.

Expect different results as compared to earlier versions of SQL Server. Remove all references to restricted publications. A replacement for restricted publications will be available in a later release.

Subscriptions to one or more articles in a publication were created either through SQL Server Enterprise Manager or through the appropriate system stored procedures.

SQL Server Enterprise Manager no longer allows subscription to one or more articles. Subscribing to one or more articles of a publication can be done only by using the appropriate replication system stored procedures.

Expect different results as compared to earlier versions of SQL Server. Use the replication system stored procedures to subscribe to one or more articles of a publication. When using SQL Server Enterprise Manager, it is necessary to subscribe to the entire publication.

Replication functions differently between Microsoft SQL Server 7.0 and SQL Server 6.5 servers. In addition, SQL Server 7.0 offers enhanced scripting ability after your replication topology is created in the user interface. This enhanced scripting allows mass implementation of replication topology with a minimum of time and effort. For more information, see "Replicating Between Different Versions of SQL Server" in Microsoft SQL Server Distributed Data Operations and Replication. 

Security (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The SYSTEM_USER niladic function returned nulls for any Microsoft Windows NT login.

The appropriate Windows NT domain and login names are returned if Windows NT Authentication is used with the SYSTEM_USER function.

Expect different results as compared to earlier versions of SQL Server. Expect a different result, as compared to earlier versions, when using SYSTEM_USER with Windows NT Authentication.

SELECT (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The *= and =* operators (left outer join and right outer join, respectively) were used in SELECT statements to produce left and right outer joins, respectively.

The SQL-92-standard syntax of LEFT OUTER JOIN and RIGHT OUTER JOIN is preferred. However, pre-SQL Server 7.0 join operators are supported.

Expect different results as compared to earlier versions of SQL Server. Remove all references of the *= (left outer join) and =* (right outer join) operators in all SELECT statement FROM clauses and replace with references to the SQL-92-standard syntax RIGHT OUTER JOIN and LEFT OUTER JOIN. Future versions of SQL Server will support only the SQL-92-standard syntax.

Triggers and System Stored Procedures (System) (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

Returned values were not always correct for text or image columns in either the inserted or deleted tables when either table was used in a CREATE TRIGGER statement.

NULL values are returned for text or image column references in the inserted or deleted tables in CREATE TRIGGER.

Expect different results as compared to earlier versions of SQL Server. Remove or comment out all references to either the text or image columns of the inserted or deleted tables when used in CREATE TRIGGER statements.

Direct recursion of triggers (the ability of a trigger to call itself) was not supported, but indirect recursion was allowed.

Direct trigger recursion is supported with the recursive triggers setting of sp_dboption.

Expect different results as compared to earlier versions of SQL Server. Enable the recursive triggers setting of sp_dboption to use direct trigger recursion. For more information about the recursive triggers setting see "Setting Database Options" in Microsoft SQL Server Database Developer's Companion. 

Server-to-server communication existed between version 4.x and version 6.x servers when initialized by either side.

Version 4.x or 6.x servers can communicate with SQL Server 7.0 servers. However, server-to-server communication is not supported from SQL Server 7.0 servers to 4.x servers.

Expect different results as compared to earlier versions of SQL Server. Upgrade the 4.x server to either SQL Server 6.x or SQL Server 7.0.

sp_tableoption set option values for user-defined tables, including the use of Insert Row-level locking (IRL).

The Insert Row-level locking (IRL) parameters in sp_tableoption are not supported but have been replaced with complete row-level locking.

Expect different results as compared to earlier versions of SQL Server. Remove all references to IRL actions implemented using sp_tableoption and use the built-in row-level locking of SQL Server 7.0 instead. Applications calling sp_tableoption should continue to work properly; the IRL parameters will be ignored.

The @message parameter of xp_readmail was varchar(255). The @message and @query parameters of xp_sendmail were varchar(255).

The @message and @query parameters are now varchar(8000).

Expect differences in behavior as compared to earlier versions of SQL Server when using the @message parameter of xp_readmailand the @message and @query parameters of xp_sendmail.

UPDATE (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

In SQL Server version 6.0, the following UPDATE statement, using two different table aliases for the same base table, was allowed:
CREATE TABLE t1 (c1 int)
GO
INSERT t1 VALUES (1)
INSERT t1 VALUES (2)
GO
UPDATE t1
SET c1 = 50
FROM t1 a1, t1 a2
WHERE a1.c1 = 1 AND
a2.c1 = 2
GO

No longer supported. Use the table alias following the UPDATE keyword of the UPDATE statement rather than the table name. The UPDATE statement would be rewritten to:
UPDATE a1
SET c1 = 50
FROM t1 a1, t1 a2
WHERE a1.c1 = 1 AND
a2.c1 = 2

Expect differences in behavior as compared to SQL Server version 6.0 when referring to a table by its table name rather than a table alias following the UPDATE keyword of the UPDATE statement.

This UPDATE statement with table and alias references worked.
USE pubs
GO
UPDATE titles
SET t.ytd_sales =
t.ytd_sales + s.qty
FROM titles t,
sales s
WHERE
t.title_id =
s.title_id
AND
s.ord_date = (SELECT
MAX(sales.ord_date)
FROM sales)
GO
 

The alias specified after the UPDATE keyword must match the alias specified following the SET keyword. Without this change, the compatibility level setting must be changed to 65 for this UPDATE statement to function as it did in version 6.x.
Here is the same UPDATE statement rewritten:
USE pubs
GO
UPDATE t
SET t.ytd_sales =
t.ytd_sales + s.qty
FROM titles t,
sales s
WHERE
t.title_id = s.title_id

AND
s.ord_date = (SELECT
MAX(sales.ord_date)
FROM sales)
GO

Expect differences in behavior as compared to SQL Server version 6.x when using different table references following the UPDATE keyword of the UPDATE statement and the SET keyword of the UPDATE statement.

Utilities (Level 4) 

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

isql/w used DB-Library. The SQL-92 settings, like ANSI_WARNINGS, were set off, by default.

SQL Server Query Analyzer uses the SQL Server ODBC driver, which, by default, sets these SQL-92 options on: SET ANSI_WARNINGS, SET ANSI_PADDING, and SET ANSI_NULLS. Any errors returned are formatted as ODBC errors rather than DB-Library errors.

Expect different results as compared to earlier versions of SQL Server. Expect different results with SQL Server Query Analyzer, compared to the isql utility or the SQL Server 6.5 version of isql/w. These SQL-92 options can also be turned off on the Query Current Connections Options menu.

isql/w used the ANSI-ISO code pages. When connected to a server using OEM code page 850 or 437, ANSI to OEM character translation had to be explicitly enabled. Otherwise, data with extended characters appeared garbled.

The SQL Server 7.0 ODBC driver automatically detects the need for and sets up automatic ANSI to OEM conversion. In addition, when SQL Server Query Analyzer connects, automatic detection is enabled.

Expect different results as compared to earlier versions of SQL Server. Expect a change in behavior when using SQL Server Query Analyzer with international or extended characters.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft