Database Change Management
Database Change Management
Home | Profile | Register | Active Topics | Active Polls | Members | Private Messages | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?




 All Forums
 Support
 DB Ghost Support Forum
 Create Index ARITHABORT problem
 New Topic  New Poll New Poll
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

McMarkis


11 Posts

Posted - 06/09/2009 :  11:09:53  Show Profile Send McMarkis a Private Message  Reply with Quote
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

---------------------------------

Edited by - McMarkis on 06/09/2009 11:12:48

Google AdSense

USA
Mountain View


leachm



125 Posts

Posted - 06/11/2009 :  10:15:15  Show Profile  Click to see leachm's MSN Messenger address Send leachm a Private Message  Reply with Quote
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.
Go to Top of Page

leachm



125 Posts

Posted - 06/20/2009 :  12:27:19  Show Profile  Click to see leachm's MSN Messenger address Send leachm a Private Message  Reply with Quote
This issue has been fixed in the latest release - version 5.0.1080.
Go to Top of Page

McMarkis



11 Posts

Posted - 07/15/2009 :  09:00:41  Show Profile Send McMarkis a Private Message  Reply with Quote
Hi Guys,

This issue has been addressed for the create statement however the error still exists during the drop.
Go to Top of Page

leachm



125 Posts

Posted - 07/15/2009 :  15:32:14  Show Profile  Click to see leachm's MSN Messenger address Send leachm a Private Message  Reply with Quote
Hi,

Could you expand on this?

Thanks,

Malcolm
Go to Top of Page

McMarkis



11 Posts

Posted - 09/10/2009 :  09:05:52  Show Profile Send McMarkis a Private Message  Reply with Quote
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.


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  New Poll New Poll
 Reply to Topic
 Printer Friendly
Jump To:
Database Change Management © Copyright 2005 Innovartis Ltd. Go To Top Of Page
Snitz Forums 2000
RSS Feed 1 RSS Feed 2
Powered by ForumCo 2000-2008
TOS - AUP - URA - Privacy Policy
ForumCo Free Blogs and Galleries
Signup for a free forum or Go Banner Free