incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'.

[Matt Rink]

Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFI ER'."
error after creating a view.

We wanted a composite unique constraint that ignored nulls, so we set
up a view using the following script:

/* --- start --- */
BEGIN TRANSACTION
SET QUOTED_IDENTIFI ER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDAB ORT OFF
SET CONCAT_NULL_YIE LDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT

GO

CREATE VIEW vw_MyView
WITH SCHEMABINDING
AS
SELECT Col1, Col2 FROM dbo.MyTable WHERECol2 IS NOT NULL

GO
/* --- end --- */

and then added the constraint to the new view

/* --- start --- */
CREATE UNIQUE CLUSTERED INDEX AK_MyTable_Cons traint1 ON
vw_MyView(Col1, Col2)

GO
/* --- end --- */

I thought we were doing fine, 'til we started running some DELETE
stored procedures and got the above error. The error also cited
ARITHABORT as an incorrect setting until we ran this script:

/* --- start --- */
USE master
DECLARE @value int
SELECT @value = value FROM syscurconfigs
WHERE config = 1534
SET @value = @value | 64

EXEC sp_configure 'user options', @value
RECONFIGURE
/* --- end --- */

TIA to anyone kind enough to shed some light on this for me. Is there
something we should have done differently in creating the view and
index? If not, what's the procedure for working through these
settings errors?

I've read through some other threads on this subject, but didn't
really find what I was looking for. Thanks again for any help. Would
be appreciated.

-matt

[Simon Hayes]

Re: incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFI ER'.

matty2112@hotma il.com (Matt Rink) wrote in message news:<1b11065c. 0310262201.4f2b a70a@posting.go ogle.com>...[color=blue]
> Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFI ER'."
> error after creating a view.
>
> We wanted a composite unique constraint that ignored nulls, so we set
> up a view using the following script:
>
> /* --- start --- */
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFI ER ON
> SET ARITHABORT ON
> SET NUMERIC_ROUNDAB ORT OFF
> SET CONCAT_NULL_YIE LDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
>
> GO
>
> CREATE VIEW vw_MyView
> WITH SCHEMABINDING
> AS
> SELECT Col1, Col2 FROM dbo.MyTable WHERECol2 IS NOT NULL
>
> GO
> /* --- end --- */
>
> and then added the constraint to the new view
>
> /* --- start --- */
> CREATE UNIQUE CLUSTERED INDEX AK_MyTable_Cons traint1 ON
> vw_MyView(Col1, Col2)
>
> GO
> /* --- end --- */
>
> I thought we were doing fine, 'til we started running some DELETE
> stored procedures and got the above error. The error also cited
> ARITHABORT as an incorrect setting until we ran this script:
>
> /* --- start --- */
> USE master
> DECLARE @value int
> SELECT @value = value FROM syscurconfigs
> WHERE config = 1534
> SET @value = @value | 64
>
> EXEC sp_configure 'user options', @value
> RECONFIGURE
> /* --- end --- */
>
> TIA to anyone kind enough to shed some light on this for me. Is there
> something we should have done differently in creating the view and
> index? If not, what's the procedure for working through these
> settings errors?
>
> I've read through some other threads on this subject, but didn't
> really find what I was looking for. Thanks again for any help. Would
> be appreciated.
>
> -matt[/color]

You need to have those SET options in force not only when you create
the view and indexes, but also when you query it. So your client
application has to use the same settings in its code - OLE DB/ODBC
does this automatically, with the exception of ARITHABORT. In BOL,
Microsoft recommend to set this on at the server level, as you've done
already.

If you still have errors when using the indexed view, it is most
likely that you have some stored procedures which have been created
with ANSI_NULLS and QUOTED_IDENTIFI ER off, not on - those settings are
fixed when the procedure is created. You can recreate the procedures
with the correct SET options, and it should work fine, although of
course that change could affect other code, so you need to test it.

Simon

[Kalen Delaney]

Re: incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFI ER'.

Hi Matt

As Doug and Simon have said, stored procedures created with certain SET
options enabled will always run with those options, even if you SET them
differently in the batch that calls the procedure. The only two that are
stored this way are "ANSI_NULLS " and "QUOTED_IDENTIF IER". I call these
'sticky' options, because their values 'stick' to the stored procedure.
Since these are the two you are getting messages about, it seems likely that
your procedure was created with the wrong values for these options,.

You can verify whether these options are set with the procedure by using the
OBJECTPROPERTY FUNCTION:

SELECT OBJECTPROPERTY( object_id('proc name'), 'ExecIsAnsiNull sOn' )

SELECT OBJECTPROPERTY( object_id('proc name'), 'ExecIsQuotedId entOn')

If the functions return 1, the property was set, if they return 0, it was
NOT set for the procedure, and you MUST recreate the procedure to use it
with an indexed view.

(If the function returns NULL, it means you typed something wrong. :-) )
--
HTH
----------------
Kalen Delaney
SQL Server MVP

https://www.SolidQualityLearning.com

"Matt Rink" <matty2112@hotm ail.com> wrote in message
news:1b11065c.0 310262201.4f2ba 70a@posting.goo gle.com...[color=blue]
> Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFI ER'."
> error after creating a view.
>
> We wanted a composite unique constraint that ignored nulls, so we set
> up a view using the following script:
>
> /* --- start --- */
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFI ER ON
> SET ARITHABORT ON
> SET NUMERIC_ROUNDAB ORT OFF
> SET CONCAT_NULL_YIE LDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
>
> GO
>
> CREATE VIEW vw_MyView
> WITH SCHEMABINDING
> AS
> SELECT Col1, Col2 FROM dbo.MyTable WHERECol2 IS NOT NULL
>
> GO
> /* --- end --- */
>
> and then added the constraint to the new view
>
> /* --- start --- */
> CREATE UNIQUE CLUSTERED INDEX AK_MyTable_Cons traint1 ON
> vw_MyView(Col1, Col2)
>
> GO
> /* --- end --- */
>
> I thought we were doing fine, 'til we started running some DELETE
> stored procedures and got the above error. The error also cited
> ARITHABORT as an incorrect setting until we ran this script:
>
> /* --- start --- */
> USE master
> DECLARE @value int
> SELECT @value = value FROM syscurconfigs
> WHERE config = 1534
> SET @value = @value | 64
>
> EXEC sp_configure 'user options', @value
> RECONFIGURE
> /* --- end --- */
>
> TIA to anyone kind enough to shed some light on this for me. Is there
> something we should have done differently in creating the view and
> index? If not, what's the procedure for working through these
> settings errors?
>
> I've read through some other threads on this subject, but didn't
> really find what I was looking for. Thanks again for any help. Would
> be appreciated.
>
> -matt[/color]

[Matt Rink]

Re: incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFI ER'.

Thank you all for your responses. I was able to get past the error by
adding

SET QUOTED_IDENTIFI ER ON
SET ANSI_NULLS ON
GO

to the top of my trouble Stored Procedures in EM. What a maintainance
nightmare! We make all our changes to DB table structure using change
scripts, so that we can execute the batch of scripts on any of our
development/test/production databases. These manual changes needed to
make a new view work definitely monkeys things up. I suppose I'm going
to have to give this some more thought.

I'm surprised this is not a larger issue. Leads me to wonder what I'm
doing wrong...

thanks again,
-matt


"Kalen Delaney" <replies@public _newsgroups.com > wrote in message news:<eIuw$yJnD HA.1072@TK2MSFT NGP09.phx.gbl>. ..[color=blue]
> Hi Matt
>
> As Doug and Simon have said, stored procedures created with certain SET
> options enabled will always run with those options, even if you SET them
> differently in the batch that calls the procedure. The only two that are
> stored this way are "ANSI_NULLS " and "QUOTED_IDENTIF IER". I call these
> 'sticky' options, because their values 'stick' to the stored procedure.
> Since these are the two you are getting messages about, it seems likely that
> your procedure was created with the wrong values for these options,.
>
> You can verify whether these options are set with the procedure by using the
> OBJECTPROPERTY FUNCTION:
>
> SELECT OBJECTPROPERTY( object_id('proc name'), 'ExecIsAnsiNull sOn' )
>
> SELECT OBJECTPROPERTY( object_id('proc name'), 'ExecIsQuotedId entOn')
>
> If the functions return 1, the property was set, if they return 0, it was
> NOT set for the procedure, and you MUST recreate the procedure to use it
> with an indexed view.
>
> (If the function returns NULL, it means you typed something wrong. :-) )
> --
> HTH
> ----------------
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "Matt Rink" <matty2112@hotm ail.com> wrote in message
> news:1b11065c.0 310262201.4f2ba 70a@posting.goo gle.com...[color=green]
> > Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFI ER'."
> > error after creating a view.
> >
> > We wanted a composite unique constraint that ignored nulls, so we set
> > up a view using the following script:
> >
> > /* --- start --- */
> > BEGIN TRANSACTION
> > SET QUOTED_IDENTIFI ER ON
> > SET ARITHABORT ON
> > SET NUMERIC_ROUNDAB ORT OFF
> > SET CONCAT_NULL_YIE LDS_NULL ON
> > SET ANSI_NULLS ON
> > SET ANSI_PADDING ON
> > SET ANSI_WARNINGS ON
> > COMMIT
> >
> > GO
> >
> > CREATE VIEW vw_MyView
> > WITH SCHEMABINDING
> > AS
> > SELECT Col1, Col2 FROM dbo.MyTable WHERECol2 IS NOT NULL
> >
> > GO
> > /* --- end --- */
> >
> > and then added the constraint to the new view
> >
> > /* --- start --- */
> > CREATE UNIQUE CLUSTERED INDEX AK_MyTable_Cons traint1 ON
> > vw_MyView(Col1, Col2)
> >
> > GO
> > /* --- end --- */
> >
> > I thought we were doing fine, 'til we started running some DELETE
> > stored procedures and got the above error. The error also cited
> > ARITHABORT as an incorrect setting until we ran this script:
> >
> > /* --- start --- */
> > USE master
> > DECLARE @value int
> > SELECT @value = value FROM syscurconfigs
> > WHERE config = 1534
> > SET @value = @value | 64
> >
> > EXEC sp_configure 'user options', @value
> > RECONFIGURE
> > /* --- end --- */
> >
> > TIA to anyone kind enough to shed some light on this for me. Is there
> > something we should have done differently in creating the view and
> > index? If not, what's the procedure for working through these
> > settings errors?
> >
> > I've read through some other threads on this subject, but didn't
> > really find what I was looking for. Thanks again for any help. Would
> > be appreciated.
> >
> > -matt[/color][/color]