3

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.

user207421
  • 305,947
  • 44
  • 307
  • 483
Laurence
  • 980
  • 12
  • 31
  • What version of SQL Server are you using? If later than SQL2000 it shouldn't care about this unless `ANSI_WARNINGS` is also off (unless you have the database in SQL 2000 compatibility level) http://sqlblog.com/blogs/kalen_delaney/archive/2008/06/19/things-keep-changing.aspx – Martin Smith Jul 18 '10 at 08:44
  • Its SQL 2005. Yes, it is on automatically for manual queries run through Management Studio, but OLEDB has it turned off (so if you are writing an application the setting will be off for the application's SQL connections). – Laurence Jul 19 '10 at 16:16
  • Laurence, are you sure that OLE DB connections explicitly turn off this setting? The documentation for [ALTER DATABASE](https://msdn.microsoft.com/en-us/library/bb522682.aspx) does not say that it does (nor for `NUMERIC_ROUNDABORT`), yet does state that the other options are explicitly stated for ODBC and OLE DB clients. I tested via .NET (OLE DB) and SQLCMD.EXE (ODBC) and neither of them required any setting of `ARITHABORT` in order for me to SELECT and UPDATE an Indexed View. Were you getting an error prior to doing the helper class as noted in your answer? – Solomon Rutzky Mar 20 '15 at 19:35
  • The exception occurred running an insert/update/delete operation against tables that were part of an indexed view, not the view itself. I can only say I had this issue with Linq to SQL, and also later with Entity Framework. – Laurence Mar 21 '15 at 22:11

1 Answers1

3

What I ended up doing was writing my own method in my own "helper" class to create the datacontext and using this every time I need a datacontext, e.g.

      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)

The idea here is to use the datacontext constructor that takes a connection as a parameter. I create and open a SqlConnection, run "set arithabort..." on it, and pass it to the DC (credit goes to poster here).

Laurence
  • 980
  • 12
  • 31
  • I'm having issues with this leaving connections open. Did you run into this? What can I to avoid this. – drizzie Sep 03 '14 at 18:38
  • No sorry, I did not get that issue. Maybe look at whether you are disposing of your datacontext? – Laurence Sep 04 '14 at 21:14