Checking Settings?

26 Jan 2006 6:01 PM

Brian

I have an existing view, I didn't create. It runs, but I was running it
through a 3rd party tool Toad to do some tuning and it gve me an error
saying, "SELECT failed because the following SET options have incorrect
settings: 'ANSI_NULLS., CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS,
ANSI_PADDING'.] ERROR **"

Looking around I see that the setting below should be set when created a
view in some cases. I was wondering if I can extract what these were set to
when the view was created?

ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YEILDS_NULL
QUOTED_IDENTIFIERS
Session options that must be off
NUMERIC_ROUNDABORT
Thanks!

26 Jan 2006 6:21 PM

Tibor Karaszi

The only two options that are "sticky" options are ANSI_NULLS and QUOTED_IDENTIFIERS. You can check
what value the options had when the view was created using the OBJECTPROPERTY function.

My guess is that it is an indexed view and that Toad doesn't have appropriate settings in the
connection that it is using. Hard to be more specific without having used that tool myself.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Brian" <brian@nospam.com> wrote in message news:Oii1UKqIGHA.424@TK2MSFTNGP12.phx.gbl...
> I have an existing view, I didn't create. It runs, but I was running it through a 3rd party tool
> Toad to do some tuning and it gve me an error saying, "SELECT failed because the following SET
> options have incorrect settings: 'ANSI_NULLS., CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS,
> ANSI_PADDING'.] ERROR **"
>
> Looking around I see that the setting below should be set when created a view in some cases. I
> was wondering if I can extract what these were set to when the view was created?
>
> ANSI_NULLS
> ANSI_PADDING
> ANSI_WARNINGS
> ARITHABORT
> CONCAT_NULL_YEILDS_NULL
> QUOTED_IDENTIFIERS
> Session options that must be off
> NUMERIC_ROUNDABORT
> Thanks!
>

26 Jan 2006 6:46 PM

Mike Labosh

Show quote

>   I have an existing view, I didn't create. It runs, but I was running it
> through a 3rd party tool Toad to do some tuning and it gve me an error
> saying, "SELECT failed because the following SET options have incorrect
> settings: 'ANSI_NULLS., CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS,
> ANSI_PADDING'.] ERROR **"
>
>   Looking around I see that the setting below should be set when created a
> view in some cases. I was wondering if I can extract what these were set
to
> when the view was created?
>
>   ANSI_NULLS
>   ANSI_PADDING
>   ANSI_WARNINGS
>   ARITHABORT
>   CONCAT_NULL_YEILDS_NULL
>   QUOTED_IDENTIFIERS
> Session options that must be off
>   NUMERIC_ROUNDABORT

There are WAY smarter people here than I, who can answer your actual
question, but it seems to me that perhaps you can just recompile the view
and be done with it.

Or am I missing something?

--
Peace & happy computing,

Mike Labosh, MCSD MCT
"Escriba coda ergo sum." -- vbSensei