How to SET options for a scope before executing in that scope?

How to SET options for a scope before executing in that scope?

Post by Peter N?rregaar » Tue, 03 Dec 2002 20:36:35



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

 
 
 

How to SET options for a scope before executing in that scope?

Post by Tibor Karasz » Tue, 03 Dec 2002 20:56:35


Why not quite simply create the proc having ANSI_NULLS on, and the proc inheriting that setting?

--
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


 
 
 

How to SET options for a scope before executing in that scope?

Post by Bill Hollinshead [MS » Wed, 04 Dec 2002 01:56:49


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.

Quote:}

(** 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.

Quote:}

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.

 
 
 

How to SET options for a scope before executing in that scope?

Post by Peter N?rregaar » Wed, 04 Dec 2002 20:37:02


Thanks to both of you!

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.

 
 
 

How to SET options for a scope before executing in that scope?

Post by Bill Hollinshead [MS » Thu, 05 Dec 2002 02:13:35


Hi Peter,

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.

 
 
 

1. Duration/scope of SET ISOLATION and SET LOCK MODE TO WAIT

I'm trying to solve a problem in which I'm getting error -107 (failed
to perform physical-order read) when updating a single row in a table.
I have several applications updating the same table, although each
application only updates its own specific rows, so no more than one
application can update a particular row.

What I need to know is the scope or duration of the following SQL
statements:

SET ISOLATION TO DIRTY READ

and

SET LOCK MODE TO WAIT n

Do these statements apply for the duration of an open connection,
within a single transaction, or some other scope of which I am unaware?

thanks,

- chris


Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

2. Help on FOXPRO

3. Execute string & variable scope

4. strange error when concatenate two varchars

5. Another question on EXECUTE scope

6. trigger problem

7. SET LANGUAGE - scope

8. Generating SQL Scripts

9. Scope of SET ROWCOUNT in pass through query?

10. Scope of *Inserted* and *Deleted* tables in triggers ?????????

11. Cursors scope and speed question

12. Question on Scope of Case-Sensitivity on a Case-Sensitive SQL Server 7.0/2000

13. Scopes: @@error, @@trancount, @@identity?