Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: Setting Database Properties don't stick

    We will develop a new application on SQL Server 2008 R2. I have created a test database and wanted to change some database properties.
    Code:
    USE tst
    GO
    
    SET ANSI_NULL_DFLT_ON ON;	-- (was off)
    SET ANSI_NULLS ON;		-- (was off)
    SET ANSI_PADDING ON;		-- (was off)
    SET ANSI_WARNINGS ON;		-- (was off)
    SET ARITHABORT ON;		-- (was off)
    ....
    When I check the database property values before and after running the previous script, nothing has changed. What am I missing?

    I managed to do it with the GUI, but I would very much prefer to be able to do it with a script.
    Code:
    SELECT  name AS 'Database_Name' ,
            snapshot_isolation_state AS 'Allow Snapshot Isolation' ,
            is_ansi_null_default_on AS 'ANSI NULL Default' ,	
            is_ansi_nulls_on AS 'ANSI NULLS Enabled' ,
            is_ansi_padding_on AS 'ANSI Paddings Enabled' ,
            is_ansi_warnings_on AS 'ANSI Warnings Enabled' ,
            is_arithabort_on AS 'Arithmetic Abort Enabled' ,
            is_auto_close_on AS 'Auto CLOSE' ,
            is_auto_create_stats_on AS 'Auto Create Statistics' ,
            is_auto_shrink_on AS 'Auto Shrink' ,
            is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' ,
            is_auto_update_stats_on AS 'Auto Update Statistics' ,
            is_cursor_close_on_commit_on AS 'Close Cursor on Commit Enabled' ,
            is_concat_null_yields_null_on AS 'Concatenate Null Yields Null' ,
            is_db_chaining_on AS 'Cross-Database Ownership Chaining Enabled' ,
            is_date_correlation_on AS 'Data Correlation Optimization Enabled' ,
            is_read_only AS 'Database Read-Only' ,
            is_local_cursor_default AS 'Default Cursor' ,
            is_encrypted AS 'Encryption Enabled' ,
            is_arithabort_on AS 'Numeric Round-Abort' ,
            page_verify_option_desc AS 'Page Verify' ,
            is_parameterization_forced AS 'Parameterization' ,
            is_quoted_identifier_on AS 'Quoted Identifiers Enabled' ,
            is_read_committed_snapshot_on AS 'Read Committed Snapshot' ,
            is_recursive_triggers_on AS 'Recursive Triggers Enabled' ,
            user_access_desc AS 'Restrict Access' ,
            is_broker_enabled AS 'Service Broker Enabled' ,
            is_trustworthy_on AS 'Trustworthy'
    FROM    sys.databases ;
    GO
    Last edited by Wim; 10-22-11 at 05:33.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Sep 2011
    Posts
    71
    Hello wim ,
    your code is correct except one thing ,As i thought :you should use Alter statement first
    As below code
    USE master;
    GO
    ALTER DATABASE AdventureWorks2008R2
    Modify Name = Northwind ;
    GO

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Paul,

    Thank you for your reply,

    I tried
    Code:
    ALTER DATABASE tst 
    SET ANSI_NULL_DFLT_ON ON;	-- (was off)
    It produced this error message:
    Msg 102, Level 15, State 6, Line 2
    Incorrect syntax near 'ANSI_NULL_DFLT_ON'.


    Code:
    ALTER DATABASE tst 
    SET ANSI_NULL_DFLT_ON = ON;	-- (was off)
    gives:
    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword 'ON'.


    When I run
    Code:
    USE tst
    GO
    SET ANSI_NULL_DFLT_ON ON;	-- (was off)
    I don't get any error or warning message, but nothing changes in the database.

    Select @@version

    gives

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2772.0 (X64) Aug 9 2011 15:12:53 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
    Last edited by Wim; 10-26-11 at 08:27. Reason: added SQL Server version
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The SET statements on their own just affect the current session. You should see those changes in sys.dm_exec_sessions. To change a database's default settings, you should have those settings set on the connection that creates the database. I am not sure if you can change them after the fact, since that could have various implications for objects like indexes and indexed views.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •