QUOTED_IDENTIFIER

Hi.
In Query Analyser I ran:
SET QUOTED_IDENTIFIER OFF
GO
EXECUTE sp1
GO
where SP1 included:
SET QUOTED_IDENTIFIER OFF
...
IF (...) BEGIN SET QUOTED_IDENTIFIER OFF
EXECUTE ('ALTER VIEW view1...') END
IF (...) BEGIN SET QUOTED_IDENTIFIER OFF
EXECUTE ('ALTER VIEW view2...') END
Both IF conditions were TRUE.
Both views ended up with QUOTED_IDENTIFIER ON.
How can I ensure that both views end up with QUOTED_IDENTIFIER OFF?
Thanks in advance.
--
Peter HyssettThis is a "sticky option", so you need to set it correctly when you *create*
the procedure. Setting
it at runtime accomplishes nothing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Hyssett" <PeterHyssett@.discussions.microsoft.com> wrote in message
news:8E515BA3-E727-4AD6-8B30-0386B5BB0220@.microsoft.com...
> Hi.
> In Query Analyser I ran:
> SET QUOTED_IDENTIFIER OFF
> GO
> EXECUTE sp1
> GO
> where SP1 included:
> SET QUOTED_IDENTIFIER OFF
> ...
> IF (...) BEGIN SET QUOTED_IDENTIFIER OFF
> EXECUTE ('ALTER VIEW view1...') END
> IF (...) BEGIN SET QUOTED_IDENTIFIER OFF
> EXECUTE ('ALTER VIEW view2...') END
> Both IF conditions were TRUE.
> Both views ended up with QUOTED_IDENTIFIER ON.
> How can I ensure that both views end up with QUOTED_IDENTIFIER OFF?
> Thanks in advance.
> --
> Peter Hyssett|||By "sticky option", that means that the the settings you had for
quoted_ident, and ansi_nulls when you created the stored procedure, stick
and are saved within the sp. The SP then runs with those option settings
regardless of how the option is set at run-time..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter Hyssett" <PeterHyssett@.discussions.microsoft.com> wrote in message
news:8E515BA3-E727-4AD6-8B30-0386B5BB0220@.microsoft.com...
> Hi.
> In Query Analyser I ran:
> SET QUOTED_IDENTIFIER OFF
> GO
> EXECUTE sp1
> GO
> where SP1 included:
> SET QUOTED_IDENTIFIER OFF
> ...
> IF (...) BEGIN SET QUOTED_IDENTIFIER OFF
> EXECUTE ('ALTER VIEW view1...') END
> IF (...) BEGIN SET QUOTED_IDENTIFIER OFF
> EXECUTE ('ALTER VIEW view2...') END
> Both IF conditions were TRUE.
> Both views ended up with QUOTED_IDENTIFIER ON.
> How can I ensure that both views end up with QUOTED_IDENTIFIER OFF?
> Thanks in advance.
> --
> Peter Hyssett