SET OPTIONS

[Devron Blatchford]

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]

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" <devron@auspine.com.au> wrote in message
news:e0wsE$RRDHA.3880@tk2msftngp13.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]

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.Baranova@arcadia.spb.ru> wrote in message
news:u8M2lOSRDHA.3144@tk2msftngp13.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" <devron@auspine.com.au> wrote in message
> news:e0wsE$RRDHA.3880@tk2msftngp13.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
> >
> >

>
>

[Erland Sommarskog]

Devron Blatchford (devron@auspine.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]sommar@algonet.se[/email]

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

[Jacco Schalkwijk]

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" <devron@auspine.com.au> wrote in message
news:e0wsE$RRDHA.3880@tk2msftngp13.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]

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" <NOSPAMjaccos@eurostop.co.uk> wrote in message
news:ehyr8bSRDHA.2188@TK2MSFTNGP10.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" <devron@auspine.com.au> wrote in message
> news:e0wsE$RRDHA.3880@tk2msftngp13.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]

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" <devron@auspine.com.au> wrote in message
news:%23o4IAVaRDHA.2424@tk2msftngp13.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" <NOSPAMjaccos@eurostop.co.uk> wrote in message
> news:ehyr8bSRDHA.2188@TK2MSFTNGP10.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" <devron@auspine.com.au> wrote in message
> > news:e0wsE$RRDHA.3880@tk2msftngp13.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
> > >
> > >

> >
> >

>
>