UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'.

UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'.

Post by Aruna Kathiriy » Fri, 04 Apr 2003 03:11:14



Hello:
I am getting the error "UPDATE failed because the following SET options have
incorrect settings: 'ARITHABORT'." when I try to execute one stored proc
which insert the new record in the table "SOProduct".

I don't do any arithmetic operation during the insertion. I also try to use
SET ARITHABORT OFF in my sp. however there is no successfull execution of
the sp.

The code for SP is:
/*******************************/
/****** Object:  Stored Procedure dbo.Texture_Insert_sp    Script Date:
7/19/2002 5:50:39 PM ******/
CREATE PROCEDURE [DBO].[SOProduct_Insert_sp]
@SalesOrderId int,
@ItemId int,
@FinishId int,
@LineItem int,
@Quantity Numeric,
@AdjustmentInPrice int =0,
@AdjustmentTypeId int = 0,
@StatusId Int,
@LocationId int,
@Note varchar(255)= NULL,
@ReturnId int =0 OUTPUT
 AS
SET ARITHABORT OFF
Begin Transaction
 Declare @SalesOrderProductId as Int, @ErrorVar  int, @SOHistId int /*,
@LocationId int*/
 Declare @AvailableAtLocation int, @TotalInStock int, @Inquiry int
 Set @ErrorVar = 0
 set @SalesOrderProductId = NULL
  If @StatusId <> 1 and @StatusId <> 4
     Begin
   /*Select @LocationId = LocationId From SOOrder where SalesOrderId =
@SalesOrderId*/

   Select @AvailableAtLocation = AvailableAtLocation,
    @TotalInStock = TotalInStock
     from InventoryProduct_View
     Where LocationId= @LocationId
      AND ItemId = @ItemId
      AND FinishId= @FinishId

   If @AvailableAtLocation > @Quantity
    Set @StatusId =  2
   Else If @TotalInStock > @Quantity
    Set @StatusId =  5
   Else
    Set @StatusId =  3
     End
   Select @SalesOrderProductId = SalesOrderProductId From SOProduct
     Where SalesOrderId = @SalesOrderId
         AND LineItem = @LineItem

 If @SalesOrderProductId is not  null
     Begin

  Update SOProduct Set
    Quantity = @Quantity,
    AdjustmentInPrice = @AdjustmentInPrice,
    AdjustmentTypeId=@AdjustmentTypeId,
    StatusId = @StatusId,
    Note = @Note,
    LocationId= @LocationId,
    UpdatedOn =  Current_timeStamp
   where SalesOrderId = @SalesOrderId
                  AND ItemId = @ItemId
    AND FinishId= @FinishId and LineItem= @LineItem
  If @@ERROR <> 0 Set @ErrorVar = 1
  Set @ReturnId = @SalesOrderProductId
     End
 Else
     Begin
  INSERT INTO SOProduct (SalesOrderId,
LineItem,ItemId,FinishId,Quantity,AdjustmentInPrice,AdjustmentTypeId,StatusI
d,Note,LocationId)
  values
(@SalesOrderId,@LineItem,@ItemId,@FinishId,@Quantity,@AdjustmentInPrice,@Adj
ustmentTypeId,@StatusId,@Note,@LocationId)
    If @@ERROR <> 0 Set @ErrorVar = 1
  SET @ReturnId = @@IDENTITY
     End
 Select @Inquiry = Inquiry from SOOrder where SalesOrderId = @SalesOrderId
 If  @Inquiry = 0
 Begin
  select top 1 @SOHistId = SalesOrderHistoryId from SOOrderHistory where
SalesOrderId = @SalesOrderId order by SalesOrderHistoryId desc
  INSERT INTO SOProductHistory
(SalesOrderProductId,SalesOrderHistoryId,ItemId,FinishId,Quantity,Adjustment
InPrice,AdjustmentTypeId,StatusId,Note)
  values (@ReturnId
,@SOHistId,@ItemId,@FinishId,@Quantity,@AdjustmentInPrice,@AdjustmentTypeId,
@StatusId,@Note)
  If @@ERROR <> 0 Set @ErrorVar = 1
 End
If  @ErrorVar = 0
 Commit Transaction
Else
  Rollback Transaction
GO
/****************************************

The script for related object is:
*****************/

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SOProductHistory_SOOrderHistory]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SOProductHistory] DROP CONSTRAINT
FK_SOProductHistory_SOOrderHistory
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Address_AddressType]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Address] DROP CONSTRAINT FK_Address_AddressType
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Merchant_Bank]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Merchant] DROP CONSTRAINT FK_Merchant_Bank
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_State_Country]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[State] DROP CONSTRAINT FK_State_Country
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_PriceType_Currency1]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PriceType] DROP CONSTRAINT FK_PriceType_Currency1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_InventoryTransfer_Employee]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[InventoryTransfer] DROP CONSTRAINT
FK_InventoryTransfer_Employee
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_InventoryTransfer_Employee1]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[InventoryTransfer] DROP CONSTRAINT
FK_InventoryTransfer_Employee1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_InventoryTransfer_Employee2]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[InventoryTransfer] DROP CONSTRAINT
FK_InventoryTransfer_Employee2
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_InventoryTransfer_Employee3]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[InventoryTransfer] DROP CONSTRAINT
FK_InventoryTransfer_Employee3
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_InventoryTransfer_Employee4]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[InventoryTransfer] DROP CONSTRAINT
FK_InventoryTransfer_Employee4
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_InventoryTransfer_Employee5]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[InventoryTransfer] DROP CONSTRAINT
FK_InventoryTransfer_Employee5
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_InventoryTransfer_Employee6]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[InventoryTransfer] DROP CONSTRAINT
FK_InventoryTransfer_Employee6
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_InventoryTransfer_Employee7]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[InventoryTransfer] DROP CONSTRAINT
FK_InventoryTransfer_Employee7
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Payment_Employee]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Payment] DROP CONSTRAINT FK_Payment_Employee
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_POOrder_Employee]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[POOrder] DROP CONSTRAINT FK_POOrder_Employee
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_POReceive_Employee]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[POReceive] DROP CONSTRAINT FK_POReceive_Employee
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_POReceive_Employee1]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[POReceive] DROP CONSTRAINT FK_POReceive_Employee1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_POReceive_Employee2]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[POReceive] DROP CONSTRAINT FK_POReceive_Employee2
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_POReceive_Employee3]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[POReceive] DROP CONSTRAINT FK_POReceive_Employee3
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SOOrder_Employee]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SOOrder] DROP CONSTRAINT FK_SOOrder_Employee
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SOOrder_Employee1]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SOOrder] DROP CONSTRAINT FK_SOOrder_Employee1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_UserInfo_Employee]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UserInfo] DROP CONSTRAINT FK_UserInfo_Employee
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Location_IPAddress]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Location] DROP CONSTRAINT FK_Location_IPAddress
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Location_IPAddress1]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Location] DROP CONSTRAINT FK_Location_IPAddress1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Terminal_IPAddress]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Terminal] DROP CONSTRAINT FK_Terminal_IPAddress
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Location_LocationType1]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Location] DROP CONSTRAINT FK_Location_LocationType1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_POOrder_OrderSource]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[POOrder] DROP CONSTRAINT FK_POOrder_OrderSource
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SOOrder_OrderSource]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SOOrder] DROP CONSTRAINT FK_SOOrder_OrderSource
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Customer_OrderTerm]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Customer] DROP CONSTRAINT FK_Customer_OrderTerm
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_SOOrder_OrderTerm]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SOOrder] DROP CONSTRAINT FK_SOOrder_OrderTerm
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Address_SourceType]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Address] DROP CONSTRAINT ...

read more »

 
 
 

UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'.

Post by Tibor Karasz » Fri, 04 Apr 2003 19:18:38


My guess is that you have an index on a view or a computed column. Note that ARITHABORT need to
be ON, not OFF.

(And no, I will not go through 1500 lines of code to look at details <g> )
--
Tibor Karaszi, SQL Server MVP
For help on TSQL, please provide code we can execute in Query Analyzer


Quote:> Hello:
> I am getting the error "UPDATE failed because the following SET options have
> incorrect settings: 'ARITHABORT'." when I try to execute one stored proc
> which insert the new record in the table "SOProduct".

> I don't do any arithmetic operation during the insertion. I also try to use
> SET ARITHABORT OFF in my sp. however there is no successfull execution of
> the sp.

<snip>