none
INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'

    Question

  • Hi

     

    I am currently running the following query from Query Analyser, I am connected to Server_A and inserting records into

    Server_B.Database_B.dbo.MyTable from Server_A .Database_A.dbo.TableRef

     

    Insert into Server_B.Database_B.dbo.MyTable(Field1,Field2,Field3)

    Select Field1_Ref,

    Field2_Ref,

    Field3_Ref

    from    Server_A .Database_A.dbo.TableRef

     

    However Server_B.Database_B.dbo.MyTable is referenced within an Indexed View and whenever I run this query I get the following error:

     

    Msg 1934, Level 16, State 1, Line 1

    INSERT 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.

     

    I have tried setting ARITHABORT to ON & OFF within the Query and within the database properties but still recieve the same error.

     

    Does anyone have any ideas on why this would not work?

     

    Thanks

     

    Tuesday, October 23, 2007 11:27 AM

Answers

  • I had the same problem and the solution in my case was to change DB compatibility level from SQL Server 2000 to SQL Server 2005.

    Friday, October 10, 2008 1:35 PM
  • Friends,

    I had the same problem, I came across this below article. This article speaks about SQL Server 2000, but is good for SQL Server 2005 too.

    http://support.microsoft.com/kb/305333


    Monday, October 20, 2008 2:00 PM
  • What I forgot to mention is that SERVER_B is actually a linked server
    set up with a SQL OLE DB connection.

    And the interesting thing is when I run my query on SERVER_A it runs
    fine so I am now lead to believe this error has something to do with the
    Linked Server settings? Spark any ideas?


    Yes, by default when you connect through any client API, ARITHABORT is OFF.

    And on the top of my head, I can think of a way to set it ON through the
    connection string, which is the only place you have to play with if you to
    INSERT through a four-part name.

    As Kalman Toth suggests, using a stored procedure is a better way to, but if
    you want a minimal change, you could have an INSTEAD OF trigger on the
    target table which performs SET ARITHABORT ON and then redos the INSERT.

    Of you could upgrade to SQL 2005/2008 where this particular requirement has
    been waived...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Saturday, July 17, 2010 3:46 PM

All replies

  • Try setting it as one of the first statements in the batch before you issue the INSERT statement.

    • Proposed as answer by Tim Dartt Tuesday, October 22, 2013 5:21 PM
    Tuesday, October 23, 2007 11:38 AM
  • The issue is not in your current session, when you create the view you have to set the proper settings (like when you create the index on the views..)

    Alter your view with following settings, will work fine..

    Code Block

    SET NUMERIC_ROUNDABORT OFF;

    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT ON;

    SET QUOTED_IDENTIFIER, ANSI_NULLS ON;

    Go

     

    alter view <Your View Name> with schemabinding ...

     

     

     

    Tuesday, October 23, 2007 11:55 AM
  • Hi

     

    Thanks for the prompt reply however I have removed all my indexed views and recreated them using your suggested approach. ie

     

    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

    GO

     

    CREATE view [dbo].[uvw_ViewA]

    WITH SCHEMABINDING...

     

    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

    GO

     

    CREATE .... INDEX [IX_ViewA]] ON [dbo].[uvw_ViewA](...) WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

     

    However I still get the same error? have I missed something?

    Tuesday, October 23, 2007 2:33 PM
  • What I forgot to mention is that SERVER_B is actually a linked server set up with a SQL OLE DB connection.

    And the interesting thing is when I run my query on SERVER_A it runs fine so I am now lead to believe this error has something to do with the Linked Server settings? Spark any ideas?

     

     

    Tuesday, October 23, 2007 3:32 PM
  • Whenever you want to connect the other SQL Server from SQL Server 2005(using linked list), use the NATIVE CLIENT.

    Read more about native client - http://msdn2.microsoft.com/en-us/library/ms131456.aspx

     

    But again, if you remove the Settings (SET Statements) before creating the view, again you will get the same error.

     

    Tuesday, October 23, 2007 5:04 PM
  • In SQL2005

    Right click [Database_B]-->Properties-->Options-->Other Options-->Misscellaneous-->Arithmetic Abort Enabled -->true

    Then run the following sql statment:

    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

     

    It should be ok

     

    If not please try to execute "sp_dbcmptlevel VitalDoc46New,90" in sql analyzer

    Friday, May 16, 2008 9:43 AM
  • Thanks for your help guys, however I am still having the same issue with this query.

     

    What I find is that due to the fact I have indexed views on Server A associate with a table, I cannot insert records into this table via Server B. Unless you have any more suggestions I think I am going to resign to the fact that this cannot be done and try and find a workaround.

     

    Cheers

     

    Monday, July 14, 2008 1:43 PM
  • Amstel,

     

    Were you able to resolve this?  I am getting the same SQL Exception, but during a 'Drop Column' statement.  I have successfully run this command on a different SQL Server with the same database.

     

    Thanks!

    Rob

     

    Thursday, September 04, 2008 6:47 PM
  • Hey Rob

     

    Apologise I havent replied sooner, been away on holiday.

     

    Firstly if the table you are ALTERing is being referenced by a Indexed View then you will never be able to ALTER the column.

     

    To resolve my issue I followed either of these routes:

     

    1.Remove the indexes on the views and update any procedures referencing these views with an alternative route of  optimising speed.

    2. Create a stored procedure on the remote database that does the work for you rather than trying to run a distibuted transaction.

     

    I hope this helps you, but if not if you explain your workings in a little more detail I am sure I could help.

     

    Cheers

    Amstel

     

    Monday, September 15, 2008 9:40 AM
  • I had the same problem and the solution in my case was to change DB compatibility level from SQL Server 2000 to SQL Server 2005.

    Friday, October 10, 2008 1:35 PM
  • Friends,

    I had the same problem, I came across this below article. This article speaks about SQL Server 2000, but is good for SQL Server 2005 too.

    http://support.microsoft.com/kb/305333


    Monday, October 20, 2008 2:00 PM
  • The issue is not in your current session, when you create the view you have to set the proper settings (like when you create the index on the views..)

    Alter your view with following settings, will work fine..

    Code Block

    SET NUMERIC_ROUNDABORT OFF;

    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT ON;

    SET QUOTED_IDENTIFIER, ANSI_NULLS ON;

    Go

     

    alter view <Your View Name> with schemabinding ...

     

    The SET statements change the current session handling of specific information and have no impact on other sessions. I’ll explaine the solution in another post.

    Saturday, July 17, 2010 1:31 PM
  • To solve the problem just add the following SET statements before data modifications (insert, update).

     

    SET ARITHABORT ON

    SET ANSI_NULLS ON

    Or simply apply them after the session starts (The SET statements change the current session handling of specific information). It can be done by setting these options on your costume Data Access connection settings such as BDE, ADO connection.... and/or server connection properties and/or database connection properties. Even applications such as SSMS have thaire own connection properties.

    These options apply in a hierarchical way if no option setted in application the database options will apply and if not presented the server options will aplly and so on. It's the cause of problem when you are connecting to a linked server because it can has it's own connection settings.

    Regards

    Saturday, July 17, 2010 1:50 PM
  • What I forgot to mention is that SERVER_B is actually a linked server set up with a SQL OLE DB connection.

    And the interesting thing is when I run my query on SERVER_A it runs fine so I am now lead to believe this error has something to do with the Linked Server settings? Spark any ideas?

    It is better to build stored procedures on the linked (remote) server, rather than use 4-part naming queries.

    Likelyhood is high that your issue would go away if you apply remote stored procedures.

    Another way of data transfer is the SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Saturday, July 17, 2010 2:01 PM
  • What I forgot to mention is that SERVER_B is actually a linked server
    set up with a SQL OLE DB connection.

    And the interesting thing is when I run my query on SERVER_A it runs
    fine so I am now lead to believe this error has something to do with the
    Linked Server settings? Spark any ideas?


    Yes, by default when you connect through any client API, ARITHABORT is OFF.

    And on the top of my head, I can think of a way to set it ON through the
    connection string, which is the only place you have to play with if you to
    INSERT through a four-part name.

    As Kalman Toth suggests, using a stored procedure is a better way to, but if
    you want a minimal change, you could have an INSTEAD OF trigger on the
    target table which performs SET ARITHABORT ON and then redos the INSERT.

    Of you could upgrade to SQL 2005/2008 where this particular requirement has
    been waived...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Saturday, July 17, 2010 3:46 PM
  • I have sungl application where I am etting this error 1934.

    The service_broker is enable in this.

    I have got below error after running trace in profiler:

    UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.


    Thanks

    Tuesday, August 14, 2012 1:33 PM