Very Computer

I cannot use

exec 'SET ANSI_NULLS ON

    SELECT Something FROM View'

as the ANSI_NULLS apparently is off when the exec-scope is created, causing
the creation of the queryplan to fail.

I cannot use

SET ANSI_NULLS ON

exec 'SELECT Something FROM View'

as the exec-scope does not inherit the settings from the creating scope.

Can anybody help me out here, please?

Regards,

Peter

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

Quote:> I have a stored procedure where I need to execute a SQL-statement that
> references an indexed view. In order for a queryplan to be made
> successfully, some options must be set, e.g. ANSI_NULLS

> I cannot use

> exec 'SET ANSI_NULLS ON

>     SELECT Something FROM View'

> as the ANSI_NULLS apparently is off when the exec-scope is created, causing
> the creation of the queryplan to fail.

> I cannot use

> SET ANSI_NULLS ON

> exec 'SELECT Something FROM View'

> as the exec-scope does not inherit the settings from the creating scope.

> Can anybody help me out here, please?

> Regards,

> Peter

What Tibor correctly suggests is documented in BOL under the "Duration of
SET Options" section of
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_03_35df.asp.

Keep in mind that the indexed view itself must *already* have been created
using those settings (from
http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_64l4.asp):
{
When **creating** indexed views or manipulating rows in tables
participating in an indexed view, seven SET options must be assigned
specific values. The SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL,
QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNING must be ON.
The SET option NUMERIC_ROUNDABORT must be OFF.

Quote:}

Finally, from the same URL:
{
In addition, for a SELECT statement that involves an indexed view, if the
values of any of the SET options are not the required values, SQL Server
processes the SELECT without considering the indexed view substitution.

Quote:}

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

It is a bit sad, however, not to be able to modify the stored procedure via
Enterprise Manager as the server settings are different from the required
settings, thus causing execution errors after editing it with that tool.

Could microsoft maybe consider to enable setting enviroment-settings for an
exec-statement?

/Peter

Quote:> Hi Peter,

> What Tibor correctly suggests is documented in BOL under the "Duration of
> SET Options" section of
> http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_03_35df.asp.

> Keep in mind that the indexed view itself must *already* have been created
> using those settings (from
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_64l4.asp):
> {
> When **creating** indexed views or manipulating rows in tables
> participating in an indexed view, seven SET options must be assigned
> specific values. The SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL,
> QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNING must be ON.
> The SET option NUMERIC_ROUNDABORT must be OFF.
> }
> (** is my emphasis).

> Finally, from the same URL:
> {
> In addition, for a SELECT statement that involves an indexed view, if the
> values of any of the SET options are not the required values, SQL Server
> processes the SELECT without considering the indexed view substitution.
> }
> is what I suspect you wish that code to accommodate. You can verify
whether
> that is accomodated (whether an indexed view is being used) via the use of
> SHOWPLAN.

> Thanks,

> Bill Hollinshead
> Microsoft, SQL Server

> This posting is provided "AS IS" with no warranties, and confers no
> rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Perhaps your 'wish' has already been 'granted' <g>. If you are not seeing
SET QUOTED_IDENTIFIER ON (as can be confirmed by running SQL Profiler and
inspecting that trace's EM connection) then you can fix the *client* tools
(i.e., EM) by applying SP2 upon that client box. See
http://support.microsoft.com/default.aspx?scid=KB;EN-US;306334 and the SP2
readme (for client tool SP2 setup instructions).

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.