SQL Server Performance

How to Alter Database Settings

Discussion in 'Getting Started' started by lcerni, Oct 15, 2007.

  1. lcerni New Member

    I have tested with this:
    --========================================================
    SET ANSI_NULLS ON
    --740 rows
    select * from client where client_addr_2 IS NULL
    --0 rows
    select * from client where client_addr_2=NULL
    --0 rows
    select * from client where client_addr_2 <> NULL
    --========================================================
    SET ANSI_NULLS OFF
    --740 rows
    select * from client where client_addr_2 IS NULL
    --740 rows
    select * from client where client_addr_2=NULL
    --3824 rows
    select * from client where client_addr_2 <> NULL
    --========================================================
    SET means that it is for the current connection, yes?
    When I change the ANSI_NULLS, I can see it working.
    However, when I try to play with the database settings,
    I cannot seem to get it to work. Am I looking at the right settings.
    I was tinkering with "SELECT is_ansi_null_default_on FROM SYS.DATABASES".
    Is this the wrong option?
    What command or option should I be using to alter the database settings?
    I have tried the ALTER DATABASE command and the sp_dboption but I am
    getting errors.
    For example,USE
    masterGOEXEC sp_dboption 'DatabaseName', 'ANSI NULL DEFAULT', 'FALSE';
    Msg 226, Level 16, State 6, Line 1
    ALTER DATABASE statement not allowed within multi-statement transaction.
    sp_dboption command failed.
    What am I doing wrong?
  2. Greg Larsen New Member

    What other statements are you running within the same batch (between the go's) as the "EXEC sp_dboption 'test', 'ANSI NULL DEFAULT', 'FALSE';" statement? The message would indicate you have multiple statements in your batch.
    I ran this command on my SQL 2005 SP2 database with no issues.
  3. satya Moderator

    Have you tried with
    SET ANSI_NULL_DFLT_ON {ON | OFF}
  4. lcerni New Member

    Yes. I have tried SET ANSI_NULL_DFLT_ON ON|OFF. I am able at the session/connection level to get it to work. However, it appears that at the database level I cannot get it to work. I am wondering if I need to run something before hand but I am not sure. I was looking into sp_configure but I don't think that this is it, but I could be wrong?
  5. thomas New Member

    There is a whole precedence when it comes to database and server defaults, and sessions settings made with the SET statement. Have you checked the 'Considerations When You Use the SET Statements' section at the bottom of the SET topic in Books Online?

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/f7e107f8-0fcf-408b-b30f-da2323eeb714.htm
  6. thomas New Member

  7. Adriaan New Member

    Also, according to BOL 2000,
    sp_dboption should not be used on either the master or tempdb databases.
    ... while your script says use master.
  8. lcerni New Member

    Thank you all for all your answers. Now I understand what I was doing wrong. And now I believe I understand these settings better.
  9. lcerni New Member

    That is why I am puzzled. I am only running
    USE master
    GO
    EXEC sp_dboption 'DatabaseName', 'ANSI NULL DEFAULT', 'FALSE';

Share This Page