none
ANSI_NULLS ON but Sql_Modules table shows 0

    Question

  • I am running the script below and getting the list of stored procedures.

    select * from sys.sql_modules sm where sm.uses_ansi_nulls  = 0

    I opened each Proc but they have SET ANSI_NULLS ON.

    Any idea why i am getting those objects?

    Wednesday, August 20, 2014 11:36 AM

Answers

  • I am looking at the same database. any idea why the code returned by sql_modules is different?

    As I said, one of:

    1) You have two procedures in differnt schemas.
    2) The procedure you are looking at in sys.sql_modules is called something else these days. That is, it was called _DisplayText originally but has been renamed.

    Did you run the query against sys.objectcs that I suggested you.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 21, 2014 7:19 AM

All replies

  • What's the version of SQL Engine (Select @@version) and Client Tools (Help>About)

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, August 20, 2014 11:39 AM
    Moderator
  • Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
    Feb 20 2014 20:04:26 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
    Wednesday, August 20, 2014 11:42 AM
  • and SSMS?

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, August 20, 2014 11:42 AM
    Moderator
  • 2012
    Wednesday, August 20, 2014 11:48 AM
  • I am unable to reproduce. (SQL 2014 and SSMS 2012)


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, August 20, 2014 11:54 AM
    Moderator
  • Not sure why its happening

    

    Wednesday, August 20, 2014 12:06 PM
  • You seem to have more than once procedure called _DisplayText. The parameter list for the scripted SP, is not the same for the procedure selected from sys.sql_modules.

    Run

       SELECT * FROM sys.objects WHERE object_id = 12019274

    I expect that either the schema_id will be <> 1 (that is, a _DisplayText in a different schema), or the name is different. (The original procedure has been renamed.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, August 20, 2014 12:38 PM
  • Hi,

    Is it not a different stored procedure? The parameters look different to the one defined above.

    Regards,

    Brindha.

    Wednesday, August 20, 2014 12:43 PM
  • I would agree with comments made by Erland and Brindha.

    The script is not matching with the output shown. Either you are in different database context while running query and scripting OR it might be a schema issue.


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, August 20, 2014 1:00 PM
    Moderator
  • I am looking at the same database. any idea why the code returned by sql_modules is different?
    Thursday, August 21, 2014 6:16 AM
  • I am looking at the same database. any idea why the code returned by sql_modules is different?

    select * from sys.sql_modules where definition like '%DisplayText%' 

    Contact me on Facebook, if you want.

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Thursday, August 21, 2014 6:58 AM
    Moderator
  • I am looking at the same database. any idea why the code returned by sql_modules is different?

    As I said, one of:

    1) You have two procedures in differnt schemas.
    2) The procedure you are looking at in sys.sql_modules is called something else these days. That is, it was called _DisplayText originally but has been renamed.

    Did you run the query against sys.objectcs that I suggested you.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 21, 2014 7:19 AM