Failed because incorrect arithabort setting

Question

I created a unique index (case description should be unique if IsDelete != 1)

CREATE UNIQUE NONCLUSTERED INDEX [UniqueCaseDescription]
ON [tblCases] ([fldCaseDescription] ASC) 
WHERE [IsDeleted] = CAST(0 AS varbinary(1))
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Then when I run the following procedure it throws 'UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with filtered indexes.'

ALTER PROC [usp_DeleteCase] (@fldCaseID UNIQUEIDENTIFIER)
AS
BEGIN   
    UPDATE tblCases
    SET IsDeleted = 1
    WHERE fldCaseID = @fldCaseID

    RETURN 1
END

I tried adding SET ARITHABORT ON before the UPDATE statement, but that didn't do anything.

Any help is greatly appreciated!

Hydrozoan

What DB compatibility level are you using? Try setting it to a higher version (ideally, the actual version of your DB engine :)). – Smyth

Answer 1

If you use SQL Server try to apply the following settings:

1) Open SQL Server Management Studio. 2) Right click the database name you use and select Properties>Options menu. Then set Arithmetic abort enabled = True from the opened dialog.

Note: I also tried to apply the same settings by using script, but using this method via SSMS is better to apply this setting.

Hope this helps...

Joanjoana

Turned out I didn't need to use a filtered index, so I didn't get a chance to try your solution. Thank you for your help anyways! Maybe it'll help somebody else having the same problem. – Hydrozoan