| T O P I C R E V I E W |
| McMarkis |
Posted - 06/09/2009 : 11:09:53
I was receiving the following error: "CREATE INDEX failed because the following SET options have incorrect settings 'ARITHABORT'"
Upon further research it appears the following must be done in order to correct this probelm:
SET ARITHABORT must be set to ON when you create or manipulate indexes on computed columns or indexed views
My question is shouldn't DBGhost be setting ARITHABORT on by default?
Each time I script out this table and package the DB I would receive this error.
I know I could make the change in source control but what happens when someone scripts from the db. They would receive this error all the time when they package.
version: 5.0.0.1067
----------SCRIPT IN QUESTION-----------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[IterationReserve]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE [dbo].[IterationReserve]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[IterationReserve](
[IterationAllocationId] [int] NOT NULL,
[RunningAllotment] [numeric] (18,0) NOT NULL CONSTRAINT [DF_IterationReserve_RunningAllotment] DEFAULT (0),
[Adjustment] [numeric] (18,0) NOT NULL CONSTRAINT [DF_IterationReserve_Adjustment] DEFAULT (0),
[AdjustedAllotment] AS ([RunningAllotment] + [Adjustment]),
[WorkingFlag] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[IterationReserve] ADD CONSTRAINT [IterationReserve_PK] PRIMARY KEY
CLUSTERED
(
[IterationAllocationId] ASC
,[WorkingFlag] ASC
) ON [PRIMARY]
GO
CREATE STATISTICS [_dta_stat_1186103266_1_4] ON [dbo].[IterationReserve]([IterationAllocationId],[AdjustedAllotment])
GO
CREATE STATISTICS [_dta_stat_1186103266_4_5] ON [dbo].[IterationReserve]([AdjustedAllotment],[WorkingFlag])
GO
CREATE STATISTICS [_dta_stat_1186103266_5_1_4] ON [dbo].[IterationReserve]([WorkingFlag],[IterationAllocationId],[AdjustedAllotment])
GO
---------------------------------
|
| 5 L A T E S T R E P L I E S (Newest First) |
| McMarkis |
Posted - 09/10/2009 : 09:05:52
quote:
Originally posted by leachm
Hi,
Could you expand on this?
Thanks,
Malcolm
Hi Malcolm, my last post was incorrect.
After capturing all events in profiler it appears just before DBGhost alters the table to create the index it runs the following:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
This causes an exception Error: 1934, Severity: 16, State: 1
According to books online:
"When creating indexed views or manipulating rows in
tables participating in an indexed view, seven SET options
must be assigned specific values. The SET options
ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be ON. The
SET option NUMERIC_ROUNDABORT must be OFF."
This is causing me a major headache during the build. Is there any suggestion you have to avoid this. Maybe something with the before or after build scripts? Like overriding the dboptions in the post and then adding the index?
Mark.
|
| leachm |
Posted - 07/15/2009 : 15:32:14
Hi,
Could you expand on this?
Thanks,
Malcolm |
| McMarkis |
Posted - 07/15/2009 : 09:00:41
Hi Guys,
This issue has been addressed for the create statement however the error still exists during the drop.
|
| leachm |
Posted - 06/20/2009 : 12:27:19
This issue has been fixed in the latest release - version 5.0.1080. |
| leachm |
Posted - 06/11/2009 : 10:15:15
The detection logic for including the ARITHABORT ON clause is clearly not working here.
I've raised this as an issue and we'll get back to you when the fix makes it into the production release. |
|
|
