SQL Server 2012 Compatibility

Question

I'm converting some stuff over to be compatible with SQL Server 2012 and had a couple of questions. I'm taking out all of the sp_dboption commands and making them ALTER commands instead, but there were a few options I haven't been able to tell the equivalent of. I was wondering if anyone here knew.

Here are the sp_dboptions I couldn't find:

• bulkcopy
• trunc. log
• dbo use
• single
• ansi null default
• db chaining

Here is the website I was using if it helps:

http://technet.microsoft.com/en-us/library/bb522682.aspx

Answer 1

They're all listed in Setting Database Options:

• bulkcopy: ALTER DATABASE .. SET RECOVERY BULK_LOGGED;
• trunc. log: ALTER DATABASE .. SET RECOVERY SIMPLE;
• dbo use: ALTER DATABASE ... SET RESTRICTED_USER;
• single: ALTER DATABASE ... SET SINGLE_USER;
• ansi null default: ALTER DATABASE ... SET ANSI_NULL_DEFAULT ON;
• db chaining: ALTER DATABASE ... SET DB_CHAINING ON;

Answer 2

I'll complement Remus' answer by posting an excerpt from the implementation of sp_dboption. This thing is not a black box (EXEC sp_helptext 'sp_dboption').

select @alt_optname = (case @fulloptname
        when 'auto create statistics' then 'AUTO_CREATE_STATISTICS'
        when 'auto update statistics' then 'AUTO_UPDATE_STATISTICS'
        when 'autoclose' then 'AUTO_CLOSE'
        when 'autoshrink' then 'AUTO_SHRINK'
        when 'ansi padding' then 'ANSI_PADDING'
        when 'arithabort' then 'ARITHABORT'
        when 'numeric roundabort' then 'NUMERIC_ROUNDABORT'
        when 'ansi null default' then 'ANSI_NULL_DEFAULT'
        when 'ansi nulls' then 'ANSI_NULLS'
        when 'ansi warnings' then 'ANSI_WARNINGS'
        when 'concat null yields null' then 'CONCAT_NULL_YIELDS_NULL'
        when 'cursor close on commit' then 'CURSOR_CLOSE_ON_COMMIT'
        when 'torn page detection' then 'TORN_PAGE_DETECTION'
        when 'quoted identifier' then 'QUOTED_IDENTIFIER'
        when 'recursive triggers' then 'RECURSIVE_TRIGGERS'
        when 'default to local cursor' then 'CURSOR_DEFAULT'
        when 'offline' then (case @alt_optvalue when 'ON' then 'OFFLINE' else 'ONLINE' end)
        when 'read only' then (case @alt_optvalue when 'ON' then 'READ_ONLY' else 'READ_WRITE' end)
        when 'dbo use only' then (case @alt_optvalue when 'ON' then 'RESTRICTED_USER' else 'MULTI_USER' end)
        when 'single user' then (case @alt_optvalue when 'ON' then 'SINGLE_USER' else 'MULTI_USER' end)
        when 'select into/bulkcopy' then 'RECOVERY'
        when 'trunc. log on chkpt.' then 'RECOVERY'
        when 'db chaining' then 'DB_CHAINING'
        else @alt_optname