Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'."
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_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_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_Constraint1 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.
-mattmatty2112@.hotmail.com (Matt Rink) wrote in message news:<1b11065c.0310262201.4f2ba70a@.posting.google.com>...
> Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'."
> 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_IDENTIFIER ON
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_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_Constraint1 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
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_IDENTIFIER 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|||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_IDENTIFIER". 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'), 'ExecIsAnsiNullsOn' )
SELECT OBJECTPROPERTY(object_id('proc name'), 'ExecIsQuotedIdentOn')
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@.hotmail.com> wrote in message
news:1b11065c.0310262201.4f2ba70a@.posting.google.c om...
> Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'."
> 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_IDENTIFIER ON
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_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_Constraint1 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|||Thank you all for your responses. I was able to get past the error by
adding
SET QUOTED_IDENTIFIER 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$yJnDHA.1072@.TK2MSFTNGP09.phx.gbl>...
> 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_IDENTIFIER". 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'), 'ExecIsAnsiNullsOn' )
> SELECT OBJECTPROPERTY(object_id('proc name'), 'ExecIsQuotedIdentOn')
> 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@.hotmail.com> wrote in message
> news:1b11065c.0310262201.4f2ba70a@.posting.google.c om...
> > Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'."
> > 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_IDENTIFIER ON
> > SET ARITHABORT ON
> > SET NUMERIC_ROUNDABORT OFF
> > SET CONCAT_NULL_YIELDS_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_Constraint1 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
