3 Replies Latest reply on Dec 9, 2008 1:31 PM by David. Lewis

    Database error 0x80040E14: SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'.

    David. Lewis

      Hi all:  I have a workbook that I've been developing, and until Friday evening everything was fine.  Then I made  a few changes to the query, most notably I replaced a few fields along the lines of:

       

      Previous subquery:

      ,(SELECT gg.xml_info.value('address_state[1]', 'VarChar(50)')

      FROM dbo._group gg WHERE gg.group_id=o.group_id) AS ShipState

       

      new udf:  

      ,[dbo].[udf_strStateFromShipment](so.Shipment_id) AS ShipState

       

      The reason was I had multiple lines being returned for each record (because there were multiple ShipState's), so I created a udf to concatenate the several shipstates into one string.

       

      That change appears to have broken the workbook.  I had it published to a server, and that  will not run -- error message is :

       

      Warnings occurred while loading the workbook "http://tableau/views/Stan".

      Database error 0x80040E14: SELECT 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.

      Unable to connect to the server "SR-1955-01-07". Check that the server is running and that you have access privileges to the requested database.

       

      The worksheet "Daily Details" has not been opened because it is based on the data source "vwPortalShipments (snbCustomerPortal_Dev)", which could not be opened.

      The worksheet "Weekly Overview" has not been opened because it is based on the data source "vwPortalShipments (snbCustomerPortal_Dev)", which could not be opened.

       

       

      Neither will the workbook open either -- it gives the same errors.

      However, I can run the views etc. with no problem in sql server management studio, and I have verified that the ARITHABORT setting is ON in the view definition.  Please advise.  TIA  D. Lewis

        • 1. Re: Database error 0x80040E14: SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'.
          David. Lewis

          Me, once again.

          This one has me stopped dead in my tracks, so if anyone has any thoughts on it I'd appreciate hearing them. 

          Searching on MSDN forums unearthed a thread on this, and their comment was that the ARITHABORT setting must be ON -- which is pretty much the error message Tableau delivers. 

           

          To troubleshoot I did the following:

          1)Eliminated all suspect fields from the view definition.

          2) Turned ArithAbort ON, redefined the view.

          3) Ran query in SQL management studio and within Tableau (both ok).

          4) Turned ArithAbort OFF.  Redefined view.  Ran it in management studio.  Received error.  Turned it backON and redefined view.

          5)  Re-created the udf with ARITHABORT ON.  Tested it -- OK.

          6)  Added the udf back to the view definition.  Recreated the view with ARITHABORT ON.  Tested within management studio.  Result ok.

          7) Refreshed the Tableau workbook, and received the ARITHABORT error. 

          At this point I'm out of ideas.  It looks to me like Tableau is misbehaving...

          TIA  D. Lewis

          7) When I explicitly turn that ON in the view definition

          • 2. Re: Database error 0x80040E14: SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'.
            David. Lewis

            Me, once again.

            This one has me stopped dead in my tracks, so if anyone has any thoughts on it I'd appreciate hearing them.

            Searching on MSDN forums unearthed a thread on this, and their comment was that the ARITHABORT setting must be ON -- which is pretty much the error message Tableau delivers.

             

            To troubleshoot I did the following:

            1)Eliminated all suspect fields from the view definition.

            2) Turned ArithAbort ON, redefined the view.

            3) Ran query in SQL management studio and within Tableau (both ok).

            4) Turned ArithAbort OFF. Redefined view. Ran it in management studio. Received error. Turned it backON and redefined view.

            5) Re-created the udf with ARITHABORT ON. Tested it -- OK.

            6) Added the udf back to the view definition. Recreated the view with ARITHABORT ON. Tested within management studio. Result ok.

            7) Refreshed the Tableau workbook, and received the ARITHABORT error.

            At this point I'm out of ideas. It looks to me like Tableau is misbehaving...

            TIA D. Lewis

            • 3. Re: Database error 0x80040E14: SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'.
              David. Lewis

              Figured it out:  The ARITHABORT ON setting must be made at the db level, not at the level of CREATE VIEW, or CREATE FUNCTION.