Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Angry Unanswered: Database setting

    . The BPA recommend that the model database setting for the items below be set to on. I can accomplish this task through the query analyzer and run the set command. (Set ANSI_NULLS on). The response is positive but when I re-run the report the setting are back off.

    Why???

    • QUOTED_IDENTIFIER
    • ANSI_NULLS
    • ANSI_WARNINGS
    • ANSI_PADDING
    • ANSI_NULL_DFLT_ON
    • CONCAT_NULL_YIELDS_NULL

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Don't the setting only last for the scope of the session?

    That's why they have to be coded inside the sporcs?

    I'll have to look a more defenitive answer...but I'll just be fgetting from BOL
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Red face Response from Microsoft

    Garry,

    Those settings are only taking effect for that single session i.e. within Query Analyzer.

    To make any permanent changes to the model database, you need to right click it in Query Analyzer and check the options under properties. You should save a copy of the database first in case you should find that you need to return to the default settings.

    Please note carefully this article in case you need to reattach your model database:

    http://support.microsoft.com/?id=224071

    We would advise leaving the model database at default settings.

    Use the following from Query Analyzer to check the settings:
    Sp_helpdb
    And also check for databaseproperty in Books Online
    Syntax
    DATABASEPROPERTY( database , property )

    USE master

    SELECT DATABASEPROPERTY('model', 'IsANSINullDEFAULT')

  4. #4
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Arrow additional Microsoft info:

    Well it’s up to you, but most people would leave the model database alone. It depends on your particular needs more than anything else. Remember the model database is only a template used to create new databases, so if you are bringing databases to this machine from another server, then the model database settings will have no effect.

    Any conflict here is due to ANSI compatibility levels. SQL Server does not always default to the ANSI compatible levels.

    Please review this article for a note on the various database settings and their effects:-

    http://msdn.microsoft.com/library/de...es_03_6ohf.asp

    These two commands give you information on your current connection details, and on the database settings that may be configured respectively:-

    sp_dboption

    dbcc useroptions

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    why did you post the question if you already had the answer?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Perhaps it was a rhetorical question?

    Read the top of his posts. He was just copying information from MicroCoughed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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