none
How to SET ARITHABORT ON for all connections in Linq To SQL

    Question

  • By default, the SQL connection option ARITHABORT is OFF for OLEDB connections, which I assume Linq To SQL is using. However I need it to be ON. The reason is that my DB contains some indexed views, and any insert/update/delete operations against tables that are part of an indexed view fail if the connection does not have ARITHABORT ON. Even selects against the indexed view itself fail if the WITH(NOEXPAND) hint is used (which you have to use in SQL Standard Edition to get the performance benefit of the indexed view).

    Is there somewhere in the data context I can specify I want this option ON? Or somewhere in code I can do it??

    I have managed a clumsy workaround, but I don't like it .... I have to create a stored procedure for every select/insert/update/delete operation, and in this proc first run SET ARITHABORT ON, then exec another proc which contains the actual select/insert/update/delete. In other words the first proc is just a wrapper for the second. It doesn't work to just put SET ARITHABORT ON above the select/insert/update/delete code.

    Friday, June 04, 2010 12:53 PM

Answers

  • For anyone interested, here is my code to implement KristoferA's suggestion (and it works):

              Dim conn As New SqlConnection(Config.GetConnectionString("SiteSqlServer"))
              Dim command As New SqlCommand("set arithabort on;", conn)
              command.Connection.Open()
              command.ExecuteNonQuery()
              Dim dc = New SiteDataContext(conn)

    • Proposed as answer by Roahn Luo Tuesday, June 08, 2010 9:10 AM
    • Marked as answer by LaurenceN Wednesday, June 09, 2010 11:36 AM
    Sunday, June 06, 2010 12:38 PM

All replies

  • Use the datacontext constructor that takes a connection as a parameter. Create and open a SqlConnection, run "set arithabort..." on it, and pass it to the DC...

     


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)

    • Proposed as answer by Roahn Luo Tuesday, June 08, 2010 9:09 AM
    Friday, June 04, 2010 2:54 PM
  • I have never had to create the datacontext in code before (I am a beginner with LINQ and have just been creating the DBML and using LinqDataSources as the data sources of various ASP.NET controls). With this approach I have no idea when & where the datacontext is created!

    If I continue with this approach is there a place I can add your code, or do I have to start writing LINQ code to explicitly bind my controls?

    Friday, June 04, 2010 5:01 PM
  • For anyone interested, here is my code to implement KristoferA's suggestion (and it works):

              Dim conn As New SqlConnection(Config.GetConnectionString("SiteSqlServer"))
              Dim command As New SqlCommand("set arithabort on;", conn)
              command.Connection.Open()
              command.ExecuteNonQuery()
              Dim dc = New SiteDataContext(conn)

    • Proposed as answer by Roahn Luo Tuesday, June 08, 2010 9:10 AM
    • Marked as answer by LaurenceN Wednesday, June 09, 2010 11:36 AM
    Sunday, June 06, 2010 12:38 PM