Post reply

Updating ANSI NULLS setting on a stored procedure.

  • April 7, 2011 at 6:39 am

    #389227

    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.

  • April 7, 2011 at 6:53 am

    #1308480

    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)

  • April 7, 2011 at 7:30 am

    #1308510

    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.

  • April 7, 2011 at 8:39 am

    #1308581

    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)

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

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