View, clustered index, and QUOTED_IDENTIFIER error
-
I am having a problem with a view with a clustered index. The view joins two tables, CaseCounty and OCSEIntercepts. OCSEIntercepts has a FK to CaseCounty, CCLink. There can be from one to seven rows JOINed from OCSEIntercepts to a CaseCounty row, one for each ProcessYear.
I create the tables and the view like so:
CREATE TABLE dbo.CaseCounty (
SSN SSN NOT NULL ,
CountyCode CountyID NOT NULL ,
LocalCode smallint NOT NULL ,
FipsStateCode char (2) NULL ,
FipsCountyCode CountyID NULL ,
DCSSAddDate smalldatetime NULL ,
DCSSDeleteDate smalldatetime NULL ,
InvestigatorID varchar (5) NULL ,
CaseID varchar (15) NULL ,
CaseType char (1) NULL ,
LastActivityDate smalldatetime NULL ,
EDDActiveCase char (1) NULL ,
CurrentTANFFTBDebt decimal(9, 2) NULL ,
CurrentNonTANFFTBDebt decimal(9, 2) NULL ,
CurrentTANFOCSEDebt decimal(9, 2) NULL ,
CurrentNonTANFOCSEDebt decimal(9, 2) NULL ,
CurrentOffsetPercent smallint NULL ,
CurrentEddDebt decimal(9, 2) NULL ,
CurrentSupportObligation decimal(9, 2) NULL ,
BirthDate smalldatetime NULL ,
CofName varchar (30) NULL ,
Address varchar (45) NULL ,
City varchar (45) NULL ,
StateCountry varchar (45) NULL ,
ZipCode varchar (9) NULL ,
MailCode char (1) NULL ,
CountyTransDate smalldatetime NULL ,
ResultCode char (3) NULL ,
StatusDate smalldatetime NULL ,
RowIdent int IDENTITY (100, 1) NOT NULL -- the primary key
)
GO
CREATE TABLE dbo.OCSEIntercepts (
CCLink int NOT NULL , -- points to CaseCounty.RowIdent
ProcessYear smallint NOT NULL ,
NonTANFCertDebt decimal(9, 2) NULL ,
NonTANFOffsetYTD decimal(9, 2) NULL ,
NonTANFNewCertDebt decimal(9, 2) NULL ,
NonTANFAdjAmtYTD decimal(9, 2) NULL ,
NonTANFRefundAmtYTD decimal(9, 2) NULL ,
NonTANFAddDate smalldatetime NULL ,
NonTANFDeleteDate smalldatetime NULL ,
NonTANFInactiveReason smallint NULL ,
TANFCertDebt decimal(9, 2) NULL ,
TANFOffsetYTD decimal(9, 2) NULL ,
TANFNewCertDebt decimal(9, 2) NULL ,
TANFAdjAmtYTD decimal(9, 2) NULL ,
TANFRefundAmtYTD decimal(9, 2) NULL ,
SupportObligation decimal(9, 2) NULL CONSTRAINT DF_OCSEIntercepts_SupportObligation DEFAULT (0),
TanfAddDate smalldatetime NULL ,
TanfDeleteDate smalldatetime NULL ,
TanfInactiveReason smallint NULL ,
RowIdent int IDENTITY (100, 1) NOT NULL ,
CONSTRAINT PK_OCSEIntercepts PRIMARY KEY CLUSTERED
(
CCLink,
ProcessYear)
)
GO
And here is the view:
---------------------------------------------------------------------------
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = N'vw_CountyOCSEIntercepts')
DROP VIEW dbo.vw_CountyOCSEIntercepts
GO
CREATE VIEW dbo.vw_CountyOCSEIntercepts
WITH SCHEMABINDING
AS
SELECT cc.SSN
, cc.CountyCode
, cc.LocalCode
, cc.CaseID
, cc.InvestigatorID
, cc.DCSSDeleteDate
, cc.LastActivityDate
, i.ProcessYear
, i.NonTANFNewCertDebt
, i.NonTANFOffsetYTD
, i.NonTANFAdjAmtYTD
, i.NonTANFRefundAmtYTD
, i.NonTANFDeleteDate
, i.NonTANFInactiveReason
, i.TANFNewCertDebt
, i.TANFOffsetYTD
, i.TANFAdjAmtYTD
, i.TANFRefundAmtYTD
, i.TanfDeleteDate
, i.TanfInactiveReason
FROM dbo.CaseCounty AS cc
JOIN dbo.OCSEIntercepts AS i ON cc.RowIdent = i.CCLink
GO
IF EXISTS (select * from dbo.sysindexes where name = 'ix_vw_CountyOCSEIntercepts' and id = object_id('dbo.vw_OCSEPersonCountyIntercepts'))
DROP INDEX dbo.vw_CountyOCSEIntercepts.ix_CountyOCSEIntercepts
GO
CREATE UNIQUE CLUSTERED INDEX ix_CountyOCSEIntercepts
ON dbo.vw_CountyOCSEIntercepts
(SSN, CountyCode, LocalCode, ProcessYear)
GO
---------------------------------------------------------------------
Then, I create a stored procedure like so:
---------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
GO
if exists (select * from sysobjects where id = object_id('dbo.usp_AllocateOCSEFundsToCases') and sysstat & 0xf = 4)
drop procedure dbo.usp_AllocateOCSEFundsToCases
GO
CREATE PROCEDURE dbo.usp_AllocateOCSEFundsToCases
... useful work happens here, selecting from and updating the view.
COMMIT TRANSACTION
RETURN @Result
GO
SET QUOTED_IDENTIFIER OFF
GRANT EXECUTE ON dbo.usp_AllocateOCSEFundsToCases TO IDBRAdmin
GO
--------------------------------------------------------------------
I thought I had coded this all according to Hoyle, i.e., BOL. The problem is that when you create the view, other, unrelated procedures throw SS errors that tell you that the setting of either ARITHABORT or QUOTED_IDENTIFIER is incorrect. At first, we had EXEC sp_dboption 'IDBR', 'QUOTED IDENTIFIER', 'FALSE'. So we set it to TRUE, but don't see a difference. A couple things have me stumped. First, set quoted identifer ON should go out of scope once the stored proc is run. It shouldn't affect other procs run after it. Second, it's not a global setting, so again it shouldn't affect other stored procs. BOL says that when you create the stored proc, it will retain the settings of QUOTED_IDENTIFER, so you don't have to have it on. I know this is a lot of code to wade through, but if someone could point out what I'm doing wrong, I would really appreciate it. Friday's the last day of my contract here, and I would love to leave on a high note.
Thanks, Dave
There is no "i" in team, but idiot has two. -
Are the 'unrelated' procs that throws these errors compiled with the correct settings for ARITHABORT and QUOTED_IDENTIFIER..?
As you say, these settings stick to the proc at compile time, it doesn't matter how the settings are in the connection using the procs.
Have you tried recompiling the affected procs, ensuring that the mentioned settings are correct?
/Kenneth
-
Kenneth: Given that the other procs don't reference this view, what are the correct settings? I believe we have ARITHABORT ON and we tried QUOTED_IDENTIFIER ON and OFF. But it shouldn't matter, should it, since only my one proc references the view?
Thanks, Dave
There is no "i" in team, but idiot has two. -
>>Given that the other procs don't reference this view...<<
No but the reference the BASE tables that the view is using and DUE TO THE SCHEMABINDIG on the view anything touching those tables has to obey the "set" rules imposed by indexed views
Probably some of them were compiled in EM and some others in QA wich have a different connection settings by default. So follow Kenneth advice and recomplie
hth
* Noel -
It sounds like you are saying that because of the SCHEMABINDING, I need to have all these (required for views with indexes) set:
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
and then recompile each stored proc that references the base tables used in my view. That makes sense. Can we also run sp_dboption and then bounce SQL Server to reload everything?
Thanks, Dave
There is no "i" in team, but idiot has two. -
Dave you got it!
The idea is that some storeprocedures have being SAVED (compiled) with those options in different settings, sp_dboption works at the database level, any new will get it but what was there is still there that's why you need to recompile each one of them
hth
* Noel -
Thanks, guys, I'll have to file this one away for later use. Our DBA swears that we have mysteriously flipped on a global setting. Our "alpha" programmer has ordered that the view be ripped out and the base tables be referenced. So that's what I've been working on all day. Thanks again.
There is no "i" in team, but idiot has two. -
That was in my mind as well..
If this is the only proc that references this view, and if it's likely that in the forseeable future no other proc or code would reference this view, then the point of the view seems kinda moot, at least from a layer-isolation perspective.
You could actually save some administrative work by not having the view, and instead having a 'raw' SQL statement in the proc doing the same stuff instead.
/Kenneth
-
Dave,
The Global setting your DBA is talking about is maybe
sp_configure 'user options' ,...
but that affects everything new and it does simplify the life of your DBA
And I again agree with your team and Kenneth, if it is the only proc that uses the view you may not need to create the view
Cheers!
* Noel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply