BUG: Not All Database Options Transfer from Model to New DB

• Microsoft SQL Server, versions 6.0 and 6.5

SYMPTOMS

When creating a new database, only the following database options will be transferred from the model database to the new database:

   Select Into/Bulk Copy
   Truncate Log on Checkpoint

   Columns Null by Default
   No Checkpoint on Recovery
   Single User
   DBO Use Only
   Read Only

WORKAROUND

To work around this problem and set the desired database options, do either of the following:

• Double-click the database in SQL Enterprise Manager, select the Edit Database Options dialog tab, and select the appropriate option check boxes.

  -or-

• Run the sp_dboption stored procedure.

  For example, to set the "Columns Null by Default" option in a database, execute the following statements from a query window (where <dbname> is the name of the database where the change will occur):

        use master
        go
        sp_dboption <db_name>, "ANSI null default", true
        go
  

STATUS

Microsoft has confirmed this to be a problem in SQL Server versions 6.0 and 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.