Post reply

SET ANSI_NULLS OFF questions

  • August 4, 2005 at 4:39 am

    #168253

    When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent executions of that stored procedure.

    Does anyone know where SQL Server stores what QUOTED_IDENTITFIER and ANSI_NULLS settings were used when the procedure was created?

    I can't see it in syscomments or sysobejcts (unless it's one of the undocumented columns in sysobejcts) but it must be stored somewhere because when you script and object it will also script the QUOTED_IDENTIFIER and ANSI_NULLS settings that were used when the proc was created.

    Thanks

    Karl
    source control for SQL Server

  • August 4, 2005 at 5:44 am

    #579508

    Run

    select * from sysobjects where type='p' and status & 536870912 > 0

    to find out which procedures are compiled with ansi_nulls on. I don't know about quoted_identifier, but that's probably in the same column... Test it and let us know what you find out

  • August 4, 2005 at 6:21 am

    #579519

    Select objectproperty(object_id('ObjectName'), 'IsQuotedIdentOn') as QuotedIdentifier, objectproperty(object_id('ObjectName'), 'IsAnsiNullsOn') as AnsiNulls

  • August 4, 2005 at 7:19 am

    #579549

    That's perfect Remi. 

    Exactly what I wanted.   I use the objectproperty function quite a bit and I didn't even think to look there

    Thanks for the suggestion Jesper.  It makes sense that it's in the status column of sysobjects.  The objectproperty function does the job though - and there's no telling whether they will change the status column in future releases.

     

    Karl
    source control for SQL Server

  • August 4, 2005 at 8:26 am

    #579593

    I agree with you (Remi is normally right ), I just didn't know you could use objectproperty.

  • August 4, 2005 at 8:32 am

    #579597

    Glab to be right for once .

Viewing 6 posts - 1 through 5 (of 5 total)

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