Professional Web Applications Themes

SET OPTIONS - Microsoft SQL / MS SQL Server

Can someone tell me how I can tell what set options a procedure has, I am looking for any procedure or view that has SET QUOTED_IDENTIFIER OFF OR SET ANSI_NULLS OFF. Thanks Devron...

  1. #1

    Default SET OPTIONS

    Can someone tell me how I can tell what set options a procedure has, I am
    looking for any procedure or view that has SET QUOTED_IDENTIFIER OFF OR SET
    ANSI_NULLS OFF.


    Thanks
    Devron


    Devron Blatchford Guest

  2. #2

    Default Re: SET OPTIONS

    Devron,

    You can use something like this:

    SELECT DISTINCT sysobjects.[name]
    FROM
    sysobjects JOIN syscomments ON sysobjects.[id] = syscomments.[id]
    WHERE
    syscomments.[text] LIKE '%SET QUOTED_IDENTIFIER OFF%' or
    syscomments.[text] LIKE '%SET ANSI_NULLS OFF%'

    ---
    Thanks,
    Tatiana

    "Devron Blatchford" <devronauspine.com.au> wrote in message
    news:e0wsE$RRDHA.3880tk2msftngp13.phx.gbl...
    > Can someone tell me how I can tell what set options a procedure has, I am
    > looking for any procedure or view that has SET QUOTED_IDENTIFIER OFF OR
    SET
    > ANSI_NULLS OFF.
    >
    >
    > Thanks
    > Devron
    >
    >

    Tatiana S Baranova Guest

  3. #3

    Default Re: SET OPTIONS

    That will not help as these options are "sticky" options. The behavior is determined from when
    you create the objects and trying to set the option inside the object has no effect.

    To check how these options are set, use the OBJECTPROPERTY() function.

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]


    "Tatiana S Baranova" <Tatiana.Baranovaarcadia.spb.ru> wrote in message
    news:u8M2lOSRDHA.3144tk2msftngp13.phx.gbl...
    > Devron,
    >
    > You can use something like this:
    >
    > SELECT DISTINCT sysobjects.[name]
    > FROM
    > sysobjects JOIN syscomments ON sysobjects.[id] = syscomments.[id]
    > WHERE
    > syscomments.[text] LIKE '%SET QUOTED_IDENTIFIER OFF%' or
    > syscomments.[text] LIKE '%SET ANSI_NULLS OFF%'
    >
    > ---
    > Thanks,
    > Tatiana
    >
    > "Devron Blatchford" <devronauspine.com.au> wrote in message
    > news:e0wsE$RRDHA.3880tk2msftngp13.phx.gbl...
    > > Can someone tell me how I can tell what set options a procedure has, I am
    > > looking for any procedure or view that has SET QUOTED_IDENTIFIER OFF OR
    > SET
    > > ANSI_NULLS OFF.
    > >
    > >
    > > Thanks
    > > Devron
    > >
    > >
    >
    >

    Tibor Karaszi Guest

  4. #4

    Default Re: SET OPTIONS

    Devron Blatchford (devronauspine.com.au) writes:
    > Can someone tell me how I can tell what set options a procedure has, I
    > am looking for any procedure or view that has SET QUOTED_IDENTIFIER OFF
    > OR SET ANSI_NULLS OFF.
    SELECT name
    FROM sysobjects
    WHERE xtype IN ('P', 'V', 'TR', 'IF', 'FN', 'TF')
    AND (objectproperty(id, 'IsAnsiNullsOn') = 0 OR
    objectproperty(id, 'IsQuotedIdentOn') = 0)

    I also included triggers and functions as they too can pose a problem
    if they have incorrect settings.

    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

  5. #5

    Default Re: SET OPTIONS

    SELECT 'Stored procedure', ROUTINE_NAME,
    CASE WHEN OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'ExecIsAnsiNullsOn')
    =1 THEN 'ON' ELSE 'OFF' END AS ANSI_NULLS_STATUS ,
    CASE WHEN OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'ExecIsQuotedIdentOn')
    =1 THEN 'ON' ELSE 'OFF' END AS QUOT_IDENT_STATUS
    FROM INFORMATION_SCHEMA.ROUTINES
    UNION ALL
    SELECT 'View', TABLE_NAME,
    CASE WHEN OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'ExecIsAnsiNullsOn') =1
    THEN 'ON' ELSE 'OFF' END AS ANSI_NULLS_STATUS,
    CASE WHEN OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'ExecIsQuotedIdentOn')
    =1 THEN 'ON' ELSE 'OFF' END AS QUOT_IDENT_STATUS
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'View'
    (OBJECT_ID('<procedure name>'), 'ExecIsQuotedIdentOn')


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Devron Blatchford" <devronauspine.com.au> wrote in message
    news:e0wsE$RRDHA.3880tk2msftngp13.phx.gbl...
    > Can someone tell me how I can tell what set options a procedure has, I am
    > looking for any procedure or view that has SET QUOTED_IDENTIFIER OFF OR
    SET
    > ANSI_NULLS OFF.
    >
    >
    > Thanks
    > Devron
    >
    >

    Jacco Schalkwijk Guest

  6. #6

    Default Re: SET OPTIONS

    Ok, thanks for that. Now that I have identified the ones that are wrong what
    is the best/fastest way to set them?

    Thanks again
    Devron.

    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    news:ehyr8bSRDHA.2188TK2MSFTNGP10.phx.gbl...
    > SELECT 'Stored procedure', ROUTINE_NAME,
    > CASE WHEN OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'ExecIsAnsiNullsOn')
    > =1 THEN 'ON' ELSE 'OFF' END AS ANSI_NULLS_STATUS ,
    > CASE WHEN OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),
    'ExecIsQuotedIdentOn')
    > =1 THEN 'ON' ELSE 'OFF' END AS QUOT_IDENT_STATUS
    > FROM INFORMATION_SCHEMA.ROUTINES
    > UNION ALL
    > SELECT 'View', TABLE_NAME,
    > CASE WHEN OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'ExecIsAnsiNullsOn')
    =1
    > THEN 'ON' ELSE 'OFF' END AS ANSI_NULLS_STATUS,
    > CASE WHEN OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'ExecIsQuotedIdentOn')
    > =1 THEN 'ON' ELSE 'OFF' END AS QUOT_IDENT_STATUS
    > FROM INFORMATION_SCHEMA.TABLES
    > WHERE TABLE_TYPE = 'View'
    > (OBJECT_ID('<procedure name>'), 'ExecIsQuotedIdentOn')
    >
    >
    > --
    > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > Database Administrator
    > Eurostop Ltd.
    >
    >
    > "Devron Blatchford" <devronauspine.com.au> wrote in message
    > news:e0wsE$RRDHA.3880tk2msftngp13.phx.gbl...
    > > Can someone tell me how I can tell what set options a procedure has, I
    am
    > > looking for any procedure or view that has SET QUOTED_IDENTIFIER OFF OR
    > SET
    > > ANSI_NULLS OFF.
    > >
    > >
    > > Thanks
    > > Devron
    > >
    > >
    >
    >

    Devron Blatchford Guest

  7. #7

    Default Re: SET OPTIONS

    Recreate them with the correct set options. You can get the source from sp_helptext, for
    instance (just watch for the line wraps) or EM (copy and paste into QA).

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]


    "Devron Blatchford" <devronauspine.com.au> wrote in message
    news:%23o4IAVaRDHA.2424tk2msftngp13.phx.gbl...
    > Ok, thanks for that. Now that I have identified the ones that are wrong what
    > is the best/fastest way to set them?
    >
    > Thanks again
    > Devron.
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > news:ehyr8bSRDHA.2188TK2MSFTNGP10.phx.gbl...
    > > SELECT 'Stored procedure', ROUTINE_NAME,
    > > CASE WHEN OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'ExecIsAnsiNullsOn')
    > > =1 THEN 'ON' ELSE 'OFF' END AS ANSI_NULLS_STATUS ,
    > > CASE WHEN OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),
    > 'ExecIsQuotedIdentOn')
    > > =1 THEN 'ON' ELSE 'OFF' END AS QUOT_IDENT_STATUS
    > > FROM INFORMATION_SCHEMA.ROUTINES
    > > UNION ALL
    > > SELECT 'View', TABLE_NAME,
    > > CASE WHEN OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'ExecIsAnsiNullsOn')
    > =1
    > > THEN 'ON' ELSE 'OFF' END AS ANSI_NULLS_STATUS,
    > > CASE WHEN OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'ExecIsQuotedIdentOn')
    > > =1 THEN 'ON' ELSE 'OFF' END AS QUOT_IDENT_STATUS
    > > FROM INFORMATION_SCHEMA.TABLES
    > > WHERE TABLE_TYPE = 'View'
    > > (OBJECT_ID('<procedure name>'), 'ExecIsQuotedIdentOn')
    > >
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Devron Blatchford" <devronauspine.com.au> wrote in message
    > > news:e0wsE$RRDHA.3880tk2msftngp13.phx.gbl...
    > > > Can someone tell me how I can tell what set options a procedure has, I
    > am
    > > > looking for any procedure or view that has SET QUOTED_IDENTIFIER OFF OR
    > > SET
    > > > ANSI_NULLS OFF.
    > > >
    > > >
    > > > Thanks
    > > > Devron
    > > >
    > > >
    > >
    > >
    >
    >

    Tibor Karaszi Guest

Similar Threads

  1. DNS Options?
    By Pete-L in forum Windows Server
    Replies: 2
    Last Post: June 11th, 11:51 AM
  2. Help with options
    By tbrowner@digidyne.com in forum PERL Beginners
    Replies: 3
    Last Post: January 28th, 08:04 PM
  3. What is an options key?
    By Ginny Baily in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 6
    Last Post: August 6th, 01:57 PM
  4. OS options for Sun PC
    By Jan Olof Flink in forum Sun Solaris
    Replies: 1
    Last Post: July 28th, 02:50 PM
  5. Options and creating a macro for options
    By Dustin Harrison in forum Microsoft Access
    Replies: 0
    Last Post: July 2nd, 07:18 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139