Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question Unanswered: help: indexed view question

    I have created a unique clustered index on a view.
    The view does a GROUP BY on 3 of the columns and
    uses the COUNT_BIG aggregate function.
    I used the following SET commands before creating the view and the index:

    SET ARITHABORT ON
    SET CONCAT_NULL_YIELDS_NULL ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_NULLS ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    SET NUMERIC_ROUNDABORT OFF

    I can insert and delete rows from the base table, and the indexed view is updated fine.

    However, when a scheduled job does effectively the same thing (delete some rows, and insert some new rows) I get the following error:

    Executed as user: NT AUTHORITY\SYSTEM. DELETE failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934). The step failed.

    Why am I getting this error?

    The same SET commands above are in the Transact-SQL code for the job before the delete and before the insert statements.

    Thanks,
    Tom

  2. #2
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Talking YES!!!!! I found a simple solution!!!

    YES!!!!! I found a simple solution!!!

    I simply put a GO after the SETs.
    Although the code was not exactly in a stored procedure I suppose it acted like it was (the code was in a T-SQL script as one of the steps of a SQL Server agent job in Management Studio).
    It may not sound like much, but this will help save 1 hour a month for an end user ... for probably 3-4 years!

    Thanks Dan Guzman (SQL Server MVP) and Alex Kuznetsov!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •