Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

Replacement for system stored procedure sp_dboption in SQL Server 2012 (Denali)

Jun 30 2012 1:22AM by Jacob Sebastian   

This post is inspired by a question raised by a reader in the What is New in SQL Server 2012 section where I posted a note that the system stored procedure sp_dboption is not available in SQL Server 2012 anymore. Books online says the recommended alternative is to use the ALTER DATABASE command.

Naomi asked me whether the ALTER DATABASE command provides enough options to support all the options available with sp_dboption procedure. Though I had seen most of the options that I frequently use available, I had never done a full comparison to verify that. Though I was pretty sure that there will be enough options available to replace all the previous options, I thought of doing a quick review and post back my findings.

'auto create statistics'

EXEC sp_dboption 'BR', 'auto create statistics', 'TRUE';
EXEC sp_dboption 'BR', 'auto create statistics', 'FALSE';

-- Replacement
ALTER DATABASE BR SET AUTO_CREATE_STATISTICS OFF
ALTER DATABASE BR SET AUTO_CREATE_STATISTICS ON 

'auto update statistics'

EXEC sp_dboption 'BR', 'auto update statistics', 'TRUE';
EXEC sp_dboption 'BR', 'auto update statistics', 'FALSE';

-- Replacement
ALTER DATABASE BR SET AUTO_UPDATE_STATISTICS ON 
ALTER DATABASE BR SET AUTO_UPDATE_STATISTICS OFF

'autoclose'

EXEC sp_dboption 'BR', 'autoclose', 'TRUE';
EXEC sp_dboption 'BR', 'autoclose', 'FALSE';

-- Replacement
ALTER DATABASE BR SET AUTO_CLOSE ON 
ALTER DATABASE BR SET AUTO_CLOSE OFF

'autoshrink'

EXEC sp_dboption 'BR', 'autoshrink', 'TRUE';
EXEC sp_dboption 'BR', 'autoshrink', 'FALSE';

-- Replacement
ALTER DATABASE BR SET AUTO_SHRINK ON 
ALTER DATABASE BR SET AUTO_SHRINK OFF

'ANSI null default'

EXEC sp_dboption 'BR', 'ANSI null default', 'TRUE';
EXEC sp_dboption 'BR', 'ANSI null default', 'FALSE';

-- Replacement
ALTER DATABASE BR SET ANSI_NULL_DEFAULT ON 
ALTER DATABASE BR SET ANSI_NULL_DEFAULT OFF

'ANSI nulls'

EXEC sp_dboption 'BR', 'ANSI nulls', 'TRUE';
EXEC sp_dboption 'BR', 'ANSI nulls', 'FALSE';

-- Replacement
ALTER DATABASE BR SET ANSI_NULLS ON 
ALTER DATABASE BR SET ANSI_NULLS OFF

'ANSI warnings'

EXEC sp_dboption 'BR', 'ANSI warnings', 'TRUE';
EXEC sp_dboption 'BR', 'ANSI warnings', 'FALSE';

-- Replacement
ALTER DATABASE BR SET ANSI_WARNINGS ON 
ALTER DATABASE BR SET ANSI_WARNINGS OFF

'arithabort'

EXEC sp_dboption 'BR', 'arithabort', 'TRUE';
EXEC sp_dboption 'BR', 'arithabort', 'FALSE';

-- Replacement
ALTER DATABASE BR SET ARITHABORT ON 
ALTER DATABASE BR SET ARITHABORT OFF

'concat null yields null'

EXEC sp_dboption 'BR', 'concat null yields null', 'TRUE';
EXEC sp_dboption 'BR', 'concat null yields null', 'FALSE';

-- Replacement
ALTER DATABASE BR SET CONCAT_NULL_YIELDS_NULL ON 
ALTER DATABASE BR SET CONCAT_NULL_YIELDS_NULL OFF

'cursor close on commit'

EXEC sp_dboption 'BR', 'cursor close on commit', 'TRUE';
EXEC sp_dboption 'BR', 'cursor close on commit', 'FALSE';

-- Replacement
ALTER DATABASE BR SET CURSOR_CLOSE_ON_COMMIT ON 
ALTER DATABASE BR SET CURSOR_CLOSE_ON_COMMIT OFF

'default to local cursor'

EXEC sp_dboption 'BR', 'default to local cursor', 'TRUE';
EXEC sp_dboption 'BR', 'default to local cursor', 'FALSE';

-- Replacement
ALTER DATABASE BR SET CURSOR_DEFAULT LOCAL 
ALTER DATABASE BR SET CURSOR_DEFAULT GLOBAL

'numeric roundabort'

EXEC sp_dboption 'BR', 'numeric roundabort', 'TRUE';
EXEC sp_dboption 'BR', 'numeric roundabort', 'FALSE';

-- Replacement
ALTER DATABASE BR SET NUMERIC_ROUNDABORT ON 
ALTER DATABASE BR SET NUMERIC_ROUNDABORT OFF

'quoted identifier'

EXEC sp_dboption 'BR', 'quoted identifier', 'TRUE';
EXEC sp_dboption 'BR', 'quoted identifier', 'FALSE';

-- Replacement
ALTER DATABASE BR SET QUOTED_IDENTIFIER ON 
ALTER DATABASE BR SET QUOTED_IDENTIFIER OFF

'read only'

EXEC sp_dboption 'BR', 'read only', 'TRUE';
EXEC sp_dboption 'BR', 'read only', 'FALSE';

-- Replacement
ALTER DATABASE BR SET READ_ONLY 
ALTER DATABASE BR SET READ_WRITE

'offline'

EXEC sp_dboption 'BR', 'offline', 'TRUE';
EXEC sp_dboption 'BR', 'offline', 'FALSE';

-- Replacement
ALTER DATABASE BR SET OFFLINE
ALTER DATABASE BR SET ONLINE

'recursive triggers'

EXEC sp_dboption 'BR', 'recursive triggers', 'TRUE';
EXEC sp_dboption 'BR', 'recursive triggers', 'FALSE';

-- Replacement
ALTER DATABASE BR SET RECURSIVE_TRIGGERS ON 
ALTER DATABASE BR SET RECURSIVE_TRIGGERS OFF

'single user'

EXEC sp_dboption 'BR', 'single user', 'TRUE';
EXEC sp_dboption 'BR', 'single user', 'FALSE';

-- Replacement
ALTER DATABASE BR SET SINGLE_USER
ALTER DATABASE BR SET MULTI_USER

'torn page detection'

EXEC sp_dboption 'BR', 'torn page detection', 'TRUE';
EXEC sp_dboption 'BR', 'torn page detection', 'FALSE';

-- Replacement
ALTER DATABASE BR SET TORN_PAGE_DETECTION ON 
ALTER DATABASE BR SET TORN_PAGE_DETECTION OFF

'select into/bulkcopy'

EXEC sp_dboption 'BR', 'select into/bulkcopy', 'TRUE';
EXEC sp_dboption 'BR', 'select into/bulkcopy', 'FALSE';

-- Replacement
ALTER DATABASE BR SET RECOVERY BULK_LOGGED 
ALTER DATABASE BR SET RECOVERY FULL 
-- or
ALTER DATABASE BR SET RECOVERY SIMPLE

'trunc. log on chkpt.'

EXEC sp_dboption 'BR', 'trunc. log on chkpt.', 'TRUE';
EXEC sp_dboption 'BR', 'trunc. log on chkpt.', 'FALSE';

-- Replacement
ALTER DATABASE BR SET RECOVERY SIMPLE 
ALTER DATABASE BR SET RECOVERY FULL
-- or
ALTER DATABASE BR SET RECOVERY BULK_LOGGED

'dbo use only'

EXEC sp_dboption 'BR', 'dbo use only', 'TRUE';
EXEC sp_dboption 'BR', 'dbo use only', 'FALSE';

-- Replacement
ALTER DATABASE BR SET RESTRICTED_USER
ALTER DATABASE BR SET MULTI_USER 
-- or
ALTER DATABASE BR SET SINGLE_USER

'merge publish', 'published' and 'subscribed'

I have never used these options and I could not find any documentation about the replacement for these options.

See more information about the new features and changes in SQL Server 2012 at What is new in SQL Server 2012

Tags: 


Jacob Sebastian
1 · 100% · 32235
8
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

12  Comments  

  • Very good reference. Thanks Jacob.

    commented on Jun 30 2012 1:54AM
    Edde Yang
    647 · 0% · 53
  • Thanks for sharing....

    commented on Jul 6 2012 4:49AM
    Chirag Prajapati
    197 · 1% · 238
  • The DB Subscription options are now controlled via spreplicationdboption. http://msdn.microsoft.com/en-us/library/ms188769.aspx

    commented on Jul 10 2012 7:45AM
    jgurtz
    2216 · 0% · 6
  • Nice Post!

    commented on Jul 23 2012 8:00AM
    dpechetti
    849 · 0% · 34
  • Thanks for sharing this saved me loads of time when upgrading a create table script to SQL 2012, if you're ever in Australia I'll buy you a beer.

    Gerard Beckerleg www.ssw.com.au

    commented on Nov 8 2012 6:48PM
    gerardbeckerleg
    3063 · 0% · 2
  • ALTER DATABASE MYDB SET RECURSIVE_TRIGGERS OFF

    This gives the following error

    Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'OFF'.

    commented on Jan 10 2013 11:53AM
    MrSmoofy
    2376 · 0% · 5
  • It looks like you are running the query in a previous versions of SQL Server. I just checked it again on a SQL Server 2012 instance and it worked.

    commented on Jan 11 2013 12:30PM
    Jacob Sebastian
    1 · 100% · 32235
  • Very weird then. Definitely running it on SQL2012 because the old way doesn't work either.

    commented on Jan 11 2013 1:02PM
    MrSmoofy
    2376 · 0% · 5
  • Try running select @@version and see what you get

    commented on Jan 12 2013 9:47AM
    Jacob Sebastian
    1 · 100% · 32235
  • Narrowed it down to that I'm trying to use a variable for the database name. Which I was able to do with the old function prior to 2012. How do I do it now?

    select @@version

    Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: )

    -- DOESN'T WORK DECLARE @dbname varchar(128) SET @dbname = DBNAME() -- 642Reference ALTER DATABASE @dbname SET RECURSIVE_TRIGGERS OFF

    -- WORKS ALTER DATABASE [642Reference] SET RECURSIVE_TRIGGERS OFF

    commented on Jan 21 2013 9:10AM
    MrSmoofy
    2376 · 0% · 5
  • Actually figured it out. Thanks for the help as it lead me to the solution I needed

    DECLARE @dbname varchar(128) DECLARE @sql varchar(256) SET @dbname = DBNAME() -- 642Reference SET @sql = 'ALTER DATABASE [' + @dbname + '] SET RECURSIVE_TRIGGERS OFF' EXEC(@sql)

    commented on Jan 21 2013 9:14AM
    MrSmoofy
    2376 · 0% · 5
  • Hi jacob,

    Really fantastic one. i just tried one of your query

    declare @FirstName varchar(50)

    declare @LastName varchar(50)

    set @FirstName='balakrishna'

    --here we get a null, because lastname is null

    select @FirstName + ' ' + @Lastname

    --or, we could handle it like this

    select @FirstName + ' ' + isnull(@Lastname, '')

    --or (As for your query)

    set concatnullyields_null off

    select @FirstName + ' ' + @Lastname

    Thanks for sharing.

    commented on Jan 21 2013 10:25AM
    Bala Krishna
    83 · 2% · 676

Your Comment


Sign Up or Login to post a comment.

"Replacement for system stored procedure sp_dboption in SQL Server 2012 (Denali)" rated 5 out of 5 by 8 readers
Replacement for system stored procedure sp_dboption in SQL Server 2012 (Denali) , 5.0 out of 5 based on 8 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]