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 3 10725 ma*******@hotmail.com (Matt Rink) wrote in message news:<1b**************************@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" <ma*******@hotmail.com> wrote in message
news:1b**************************@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:<eI**************@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" <ma*******@hotmail.com> wrote in message news:1b**************************@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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Steve Foster |
last post by:
I have tried many variations (after reviewing other posts) and can not
resolve the following issue:
RUNNING SQL MAINTENANCE
----------------------------
SET ARITHABORT ON
SET...
|
by: Iqbal |
last post by:
Hi,
I am getting the following error when I run a stored procedure in
which I am inserting/deleting data from a view that selects from a
remote table.
INSERT failed because the following SET...
|
by: teddysnips |
last post by:
I am trying to insert a row into a table using a stored procedure and I
get the following error if I try this from QA:
INSERT failed because the following SET options have incorrect
settings:...
|
by: teddysnips |
last post by:
In the script below is the DDL to create some tables and a UDF.
What I'm interested in is the UDF at the end. Specifically, these few
lines:
--CLOSE OTRate
--DEALLOCATE OTRate
ELSE --...
|
by: Calvin Slater |
last post by:
It is recommended that the following settings be set for all
connections so that the optimizer will perform well on indexed views
and stuff.
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET...
|
by: Sandesh |
last post by:
Hello All,
Me saying " has any body come across such error would be
underestimating".
Well I am getting a very peculiar and unique error "Line 1: Incorrect
syntax near 'Actions'."
...
|
by: ilo |
last post by:
When I want to delete a data from a table that this tabl has a trigger
and this trigger reached another tables to delete the data in cursor I
have this messeage:
DELETE failed because the...
|
by: griffel |
last post by:
Hello,
I am getting a syntax error on a Stored procedure. Server 2003/SQL 2000 SP4
I cant seem to figure what is wrong. Any help would be appreciated.
Server: Msg 170, Level 15, State 1,...
|
by: =?Utf-8?B?QmlsIENsaWNr?= |
last post by:
I wrote a stored procedure that runs fine when I execute from SQL 2005
Management Studio, and runs ok when I step into it from VS2005. Both return a
value of 0, drop, create & populate the table.
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| | |