SQL Server Performance Forum – Threads Archive

Strange Error with an Indexed View

OK, I got my indexed view working properly and it was making queries against a specific subset of my Million Row table without any issues. Then my testers came back and tell me that INSERTS to the base table were now failing complaining that QUOTED_IDENTIFIER and ARITHABORT were not properly set. OK. One Googling of the verbatim message later, I find that Microsoft says you have to either set the Server/Database defaults for these two options on, or explicitly send the commands SET QUOTED_IDENTIFIER ON and SET ARITHABORT ON through the Connection.Execute method to ensure that OLE DB or ODBC connections were operating with them. To be safe, I did both.
ALTER DATABASE MyDatabaseName SET QUOTED_IDENTIFIER ON SET ARITHABORT ON GO AND
Connection.Execute "SET QUOTED_IDENTIFIER ON"
Connection.Execute "SET ARITHABORT ON" This did a lovely job on my test server.
My DBA wasn’t thrilled with the idea of altering DB settings, but I got him to go along for testing purposes. Since we could just do the explicit Connection.Execute statements for ADO calls and DTS executions would presumably be a different story. So we moved the monster up to the client’s testing system. Everything worked wonderfully until we got to the Insert SP that the above VBScript was supposed to handle. It screamed that QUOTED_IDENTIFIER was off. This despite the fact that the above Connection.Execute statement as not 10 lines above the SP call against the connection, and that the DB defaults had been changed. Now, when I pulled the DB Defaults back to off, and commented out the ARITHABORT Execute statement, the Connection screamed that it needed both QUOTED_IDENTIFIER and ARITHABORT set to on. So I’m at a loss to explain why the thing is screwing up on one server and working fine on another… Anyone have any ideas?

You have to set these options when creating the index on the view. AFAIK, the problem is mainly with the wizard in Enterprise Manager, which seems to set the wrong values for the options, instead of relying on the defaults which would not have produced the erratic behaviour you’ve run into. Easiest solution would be to script the index on the view from Query Analyzer – this will show you all those incorrect option values (and it’s probably a messy script). Change the option values to the recommended ones, add a DROP INDEX statement before it, and execute the script. If you do a CREATE INDEX from QA, instead of using Enterprise Manager’s wizard, I would not be surprised if 90% of the time you’ll have the right option values by default (by not setting any of them).

OK, BUT… I did create the Index in Query Analyzer using a CREATE INDEX Statement. I make it a point to always prepare my SQL scripts by hand so that I can quickly deploy any chages between my staging and production environment without any fuss. (And not forget anything in most cases.) So I think that what you’re saying is that the Script needs to have the set options in the same Batch. OK, I’ll give that a try and get back to you. If that were to eliminate the need for the Connection Execute statements, then that would be nice.

Yes, include the settings in the same batch. You could also create the index through Enterprise Manager, which adds the incorrect settings ‘under the hood’. Then script out the index in Query Analyzer, which will include those settings, and make the necessary changes there. Add a DROP INDEX statement at the start, and execute the script.

OK, maybe I’m just not understanding, but your advice simply doesn’t work.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[QuickIndexedClinicians]’) and OBJECTPROPERTY(id, N’IsView’) = 1)
BEGIN
DROP VIEW [dbo].[QuickIndexedClinicians]
END
SET QUOTED_IDENTIFIER ON SET ARITHABORT ON
GO CREATE VIEW QuickIndexedClinicians
WITH SCHEMABINDING
AS
SELECT ClinicianID, LastName, FirstName, StandardizedAddress FROM dbo.tblClinician GO SET QUOTED_IDENTIFIER OFF SET ARITHABORT OFF
GO
GRANT SELECT ON QuickIndexedClinicians TO Reporderz
GO CREATE UNIQUE CLUSTERED INDEX QuickIndexedCliniciansPrimary ON QuickIndexedClinicians (
ClinicianID
) GO
CREATE INDEX QuickIndexedCliniciansIndex ON QuickIndexedClinicians (
LastName, FirstName, StandardizedAddress
) GO This fails because:
Server: Msg 1934, Level 16, State 1, Line 2
CREATE INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER, ARITHABORT’. Alternately:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[QuickIndexedClinicians]’) and OBJECTPROPERTY(id, N’IsView’) = 1)
BEGIN
DROP VIEW [dbo].[QuickIndexedClinicians]
END
SET QUOTED_IDENTIFIER ON SET ARITHABORT ON CREATE VIEW QuickIndexedClinicians
WITH SCHEMABINDING
AS
SELECT ClinicianID, LastName, FirstName, StandardizedAddress FROM dbo.tblClinician GO SET QUOTED_IDENTIFIER OFF SET ARITHABORT OFF
GO
GRANT SELECT ON QuickIndexedClinicians TO Reporderz
GO CREATE UNIQUE CLUSTERED INDEX QuickIndexedCliniciansPrimary ON QuickIndexedClinicians (
ClinicianID
) GO
CREATE INDEX QuickIndexedCliniciansIndex ON QuickIndexedClinicians (
LastName, FirstName, StandardizedAddress
) GO
This fails because:
Server: Msg 111, Level 15, State 1, Line 11
‘CREATE VIEW’ must be the first statement in a query batch. Still another:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[QuickIndexedClinicians]’) and OBJECTPROPERTY(id, N’IsView’) = 1)
BEGIN
DROP VIEW [dbo].[QuickIndexedClinicians]
END
GO CREATE VIEW QuickIndexedClinicians
WITH SCHEMABINDING
AS
SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SELECT ClinicianID, LastName, FirstName, StandardizedAddress FROM dbo.tblClinician SET QUOTED_IDENTIFIER OFF SET ARITHABORT OFF
GO
GRANT SELECT ON QuickIndexedClinicians TO Reporderz
GO CREATE UNIQUE CLUSTERED INDEX QuickIndexedCliniciansPrimary ON QuickIndexedClinicians (
ClinicianID
) GO
CREATE INDEX QuickIndexedCliniciansIndex ON QuickIndexedClinicians (
LastName, FirstName, StandardizedAddress
) GO
This fails because:
Server: Msg 156, Level 15, State 1, Procedure QuickIndexedClinicians, Line 9
Incorrect syntax near the keyword ‘SET’. So, I’m sorry but I don’t get it. It actually worked the first time I tried it with the SET ON statements in a separate batch, so I’ll be damned if I know what the fickle thing wants.

My advice was to drop the index, not the view. Something along the lines of this … DROP INDEX …………
GO SET blablabla
SET blablabla
SET blablabla
SET blablabla
SET blablabla
SET blablabla CREATE INDEX …….. SET blablabla
SET blablabla
SET blablabla
SET blablabla
SET blablabla
SET blablabla GO

OK. I’m sorry if I’m coming off as being rude, I’m just really, really frustrated. Here’s what I’m getting for this statement:
SET QUOTED_IDENTIFIER ON SET ARITHABORT ON
CREATE INDEX QuickIndexedCliniciansIndex ON QuickIndexedClinicians (
LastName, FirstName, StandardizedAddress
) SET QUOTED_IDENTIFIER OFF SET ARITHABORT OFF GO Server: Msg 1934, Level 16, State 1, Line 7
CREATE INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. I’m really just not getting something here. I don’t know how much more explicit I can be that the QUOTED_IDENTIFIER hould be ON, and the parser just doesn’t seem to care.

Are there any stored procs involved here – i.e. do you query this view using a stored procedure? I ask because there are some special (confusing) behaviors of stored procs related to SET options. From BOL: "Stored procedures execute with the SET settings specified at execute time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER. Stored procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the setting specified at stored procedure creation time. If used inside a stored procedure, any SET setting is ignored." This is one way to explain why a stored proc / indexed view combination might work on one system and not on another. Shot in the dark…

Hi merrill, based on what you just said, I think that was the cause of my original problem. Of course now I’m unable to recreate the Index on my original test system, so the question is a bit moot.[<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />]

Puzzling. There must be something simple amiss – I do see the reasons for the errors in your previous post (the long one <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />. So there’s likely something subtly wrong. Here’s the correct sequence, as far as I know: set ANSI_NULLS, QUOTED_IDENTIFIER on GO — This GO is required, because Create View must be the first statement in a batch. The settings do "stick." create view yourView with schemabinding as select cols from table where conditions GO create unique clustered index clustindex on yourView( primaryKey ) GO create nonclustered index anotherIndex on yourView( col ) GO Is there a way you can try this sequence "from scratch?"

It seemed to like that very much. OK, now to see if it translates nicely to the other server…

Great! I think the other trick, then, is to be sure to set the right connection options before the creation script for any stored procs that touch the indexed view (and make sure the setting is there when depolying on the other box): set ANSI_NULLS, QUOTED_IDENTIFIER on
GO create procedure insertStuff
@variables
as … I don’t know why MS created that strange stored proc behavior, "remembering" two connection settings that were in play when a procedure was created. Must be for some reason … but I have found that the connection settings in use in Enterprise Manager and QA can default to different things, and script out to different things, which makes this while situation seem unpredictable — just because the rules are subtle, and a little wierd.

Also, though it’s painful to digest, I would read in great detail the page "Creating an Indexed View," in BOL. There are actually seven (!) connection settings that need to be set properly both for creating indexes on views and for any modifications to the underlying data. They are probably the defaults in QA, so it doesn’t complain, but they might or might not be defaults on the connection your app is using, so it’s useful to know ALL about them <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />.

That’s why I suggested creating the index through Enterprise Manager. This will add a bunch of incorrect settings. When you script out this index from Query Analyzer, all the effective settings from the EM wizard will be returned. So now it is easy to correct the bothersome setting in the script, drop the index and recreate. IIRC, the errors come up one at a time, so when the first one is solved, the next one might come up. So test again if data entry on the underlying table(s) produces any errors, and make the necessary corrections in the script, and repeat until there is no more error.

]]>