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 syslogs sysprocesses (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 |
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.
| 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. |
Administrative scripts may have used these configuration options. For more information about configuration options, see sp_configure and Setting Configuration Options.
| 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. | 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. | 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. |
| 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. |
| 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. |
| 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. |
Administrative scripts may have used these database options. For more information about configuration options, see sp_dboption and Setting Database Options.
| 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. |
| 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. |
| 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. | |
| 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). |
| 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. |
| 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. |
| 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. |
| 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. |
| 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. |
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 |
| 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. |
| 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 Publication Access Lists. |
| 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 identifiers. |
| 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. |
| 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 SQLServerAgent Service. |
| 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. |
| 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. |
| 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. |
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. |
| 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. |
| 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_distcounters was used to query for delivered or undelivered commands as used by Performance Monitor, which no longer uses this procedure. | No longer supported. | The new view MSdistribution_status presents much of the same information. |
| sp_helpreplicationdb was used to return information about a specified database or a list of all publication databases on the server. | Removed; no longer supported. | Remove all references to sp_helpreplicationdb and replace with references to sp_helpreplicationdboption. |
| 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 Service. | Remove or comment out all references to these fallback system stored procedures: sp_fallback_activate _svr_db, sp_fallback_deactivate sp_fallback_enroll_svr sp_fallback_help, sp_fallback_permanent sp_fallback_upd_dev 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. |
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.
| 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 system 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.
| 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:
|
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. |
| 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. |