Thank you all for your responses. I was able to get past the error by
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...
> 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
> > 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
> > WHERE config = 1534
> > 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
by