Updating ANSI NULLS setting on a stored procedure.

  • Hi,

    When a stored procedure is created the settings for ANSI NULLS and QUOTED IDENTIFER which were set at creation time are 'saved with the stored procedure' and can be seen in sys.sql_modules.

    My question is the following: Is it possible to update these settings without rescripting the objects?

    I suspect not, but hopefully I'm wrong.

    Thanks,

    David.

  • David McKinney (4/7/2011)


    Hi,

    When a stored procedure is created the settings for ANSI NULLS and QUOTED IDENTIFER which were set at creation time are 'saved with the stored procedure' and can be seen in sys.sql_modules.

    My question is the following: Is it possible to update these settings without rescripting the objects?

    I suspect not, but hopefully I'm wrong.

    Thanks,

    David.

    Unless the SET ANSI NULLS was for OFF, I wouldn't change them on a bet.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Hi Jeff.

    I'm aware of the consequences of changing these settings.

    Actually I'm not even changing them - the question relates more to scripting the existing setup. I know I'm being a little cryptic, but you wouldn't thank me for giving you all the details.

    Let me put the question differently....if I have an sp with the settings incorrect, is there an other option than to ALTER or DROP / CREATE the object?

    Thanks again.

  • David McKinney (4/7/2011)


    Hi Jeff.

    I'm aware of the consequences of changing these settings.

    Actually I'm not even changing them - the question relates more to scripting the existing setup. I know I'm being a little cryptic, but you wouldn't thank me for giving you all the details.

    Let me put the question differently....if I have an sp with the settings incorrect, is there an other option than to ALTER or DROP / CREATE the object?

    Thanks again.

    I don't know 100% for sure but, IIRC, I don't believe there is.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply