The Microsoft and Sybase implementations of the system stored procedures sp_addmessage, sp_dboption, and sp_configure are not the same.
In Sybase systems, the range for user-defined message numbers starts at 20,000. In SQL Server, the range starts at 50,000 and also requires a severity to be specified to support alerts (severity ranges from 1 through 25).
Microsoft SQL Server stores user messages in master.dbo.sysmessages, and Sybase stores them in master.dbo.sysusermessages.
The table lists the parameters for sp_dboption that are different for Sybase and Microsoft implementations.
| Sybase parameters | Microsoft parameters |
|---|---|
| ABORT TRAN ON LOG FULL | No equivalent. |
| ALLOW NULLS BY DEFAULT | ANSI NULL DEFAULT |
| AUTO IDENTITY | No equivalent. |
| DDL IN TRAN | SQL Server version 7.0 allows DDL in transactions. |
| IDENTITY IN NONUNIQUE INDEX | No equivalent. |
Sybase requires a checkpoint in the affected database after sp_dboption completes, and Microsoft SQL Server automatically checkpoints the affected database. Also, Microsoft SQL Server allows DDL in transactions without requiring the system administrator to set any server or database options.
You can manage and optimize Microsoft SQL Server resources through configuration options by using SQL Server Enterprise Manager or the sp_configure system stored procedure. The most commonly used server configuration options are available through SQL Server Enterprise Manager; all configuration options are accessible through sp_configure.
Compared to earlier versions, SQL Server version 7.0 has more internal features for self-tuning and reconfiguring. These features reduce the need to set server configuration options manually. You should consider the effects on your system carefully before setting these options.
The options for the Sybase and Microsoft versions of sp_configure are quite different. Detailing all of the differences is beyond the scope of this chapter. Sybase DBAs should review the SQL Server documentation for sp_configure options.
sp_configure allows members of the sysadmin fixed server role to set defaults for user options, such as ANSI options, although individual connections can later change the settings. The current state of a connection’s settings are made visible to it through the @@OPTIONS function. @@OPTIONS returns a numeric value that records the current option settings. For more information about a stored procedure that returns a character list of the options recorded by @@OPTIONS, see the Microsoft Knowledge Base article Q156498.
The DUMP statement is included in Microsoft SQL Server version 7.0 for backward compatibility. It is recommended that the BACKUP statement be used instead of the DUMP statement. In future versions of SQL Server, DUMP will not be supported. For more information about database back up and restore operations, see SQL Server Books Online.
Use DTS to perform imports and exports on a regular basis with Microsoft SQL Server.
DUMP/LOAD statements |
Sybase Adaptive Server Enterprise | Microsoft SQL Server 7.0 |
|---|---|---|
| dump devices | FILE | DISK |
| listing | LISTONLY | Not supported in the same way; closest statement is HEADERONLY. |
| HEADERONLY only lists the first dump. | HEADERONLY lists information about all dumps in a device. | |
| Striping | STRIPE=n | Remove the STRIPE=n parameter from the Sybase DUMP and LOAD statements and set the sp_configure backup threads parameter to n. |
Replace all logic that uses the Sybase syslogshold table to determine the oldest outstanding transaction with logic that uses the Microsoft DBCC OPENTRAN statement.