Hello,

I'm having an issue with computed columns, it is throwing the following error:

An exception was caught during the execution of an action query: 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.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

The exception is being thrown in this portion of the code:

        Protected Overrides Overloads Function InsertEntity() As Boolean
            Dim dao As SMSClientDAO = CType(CreateDAOInstance(), SMSClientDAO)
            Return dao.AddNew(MyBase.Fields, MyBase.Transaction)
        End Function

I tried using the SetArithAbortFlag, but it does not seem to work.

Here is the query generated by LLBLGen:

Query: SET ARITHABORT ON;INSERT INTO [MyDB].[sms].[SMSClient] ([CountryCode], [AreaCode], [PhoneNo], [ShortCode], [Keyword], [CampaignID])  VALUES (@CountryCode, @AreaCode, @PhoneNo, @ShortCode, @Keyword, @CampaignID);SELECT @SMSClientID=SCOPE_IDENTITY();SET ARITHABORT OFF
    Parameter: @SMSClientID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: <undefined value>.
    Parameter: @CountryCode : AnsiString. Length: 10. Precision: 0. Scale: 0. Direction: Input. Value: "1".
    Parameter: @AreaCode : AnsiString. Length: 10. Precision: 0. Scale: 0. Direction: Input. Value: "555".
    Parameter: @PhoneNo : AnsiString. Length: 25. Precision: 0. Scale: 0. Direction: Input. Value: "5551234".
    Parameter: @ShortCode : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12345.
    Parameter: @Keyword : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "".
    Parameter: @CampaignID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: <undefined value>.

Here is the code that is failing (the .Save() line):

            Dim SMSClient As New SMSClientEntity

            With SMSClient
                .CountryCode = PhoneNumber.CountryCode
                .AreaCode = PhoneNumber.AreaCode
                .PhoneNo = PhoneNumber.Number
                .ShortCode = ShortCode
                .Keyword = Keyword
                .CampaignID = CampaignID

                'SET ARITHABORT ON 
                DbUtils.SetArithAbortFlag(True)

                .Save()

                .Refetch()
            End With

Any ideas what I can do?

Hi mshe,

LLBLGen version and runtime library version? Database version? (http://llblgen.com/TinyForum/Messages.aspx?ThreadID=7717)

Also, please check this, could be realated (I think it applies to SQL2000): http://support.microsoft.com/kb/305333

Furthermore, this error may occur even if "SET ARITHABORT ON" is included in the batch or stored procedure that attempts the INSERT. ... To successfully insert a row into a table that is referenced in an indexed view, the SQL ARITHABORT configuration setting must be set to ON. Furthermore, the statement that applies this configuration setting must be executed in its own batch. Because stored procedures contain only one batch, adding the statement to the procedure does not work.

It such case, try using this trick: http://llblgen.com/TinyForum/Messages.aspx?ThreadID=13509&StartAtMessage=0&#75232

Yes, I enabled SET ARTITHABORT ON as you can see in the LLBLGen generated SQL Statements.

I'm using SQL Server 2005 with the latest SP.

Runtime is 2.6.09.0807.

I saw the post http://llblgen.com/TinyForum/Messages.aspx?ThreadID=13509&StartAtMessage=0&#75232... but that's for adapter method... is there a way to do the same for self-servicing? I think it's exactly the same issue I am having...

Is this an error/issue with LLBLGen's implementation of SET ARITHABORT?

Is this an error/issue with LLBLGen's implementation of SET ARITHABORT?

I don't think so, as the command is correctly generated as you see in the trace output. Also as shown in this thread: http://llblgen.com/TinyForum/Messages.aspx?ThreadID=8061

What happen if you copy the generated SQL into SQL Management Studio and tried to run it from there?

Walaa wrote:

What happen if you copy the generated SQL into SQL Management Studio and tried to run it from there?

In SQL Server Management Studio, I've turned off SET ARITHABORT to mimic ADO.NET's behaviour.

The first time the query runs it's OK; but the second time I get the following error:

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.

Basically I think there are two things:

• A fresh connection is required.
• Once SET ARITHABORT OFF is called, ON cannot be called again.

There are two things LLBLGen must do:

• Use a fresh connection if SET ARITHABORT flag is enabled/disabled
• Do no change the state of SET ARITHABORT after each call.

A fresh connection won't help, as it's a problem with batches: each ADO.NET command executed through SqlClient is wrapped inside sp_executesql calls by the .NET SqlClient itself. (btw, on our sqlserver 2000 system it works, but perhaps it fails with some other parameters having different values )

Anyway, if you want to execute the SET ARITHABORT ON/OFF statements before the insert, you can. It requires a subclass of the Transaction class (which is generated).

In your derived Transaction class, you override CreatePhysicalTransaction. In that override, you first call the base method, which creates the transaction, and then you grab the connection (which is now opened!) from the base type, via base.ConnectionToUse.

On that connection you execute a SqlCommand with SET ARITHABORT ON;.

You add a new method SwitchOfArithAbort which will call a SqlCommand SET ARITHABORT OFF; on the base' ConnectionToUse.

Then use it as follows:

Using trans As New YourTransactionClass(IsolationLevel.ReadCommitted, "arithtrans")
    // do your code here. Be sure you add all entities to this trans using the Add method!

   // at the end
   trans.SwitchOfArithAbort()
   trans.Commit()
End Using

it's a little cumbersome, but as everything is hidden away from you by selfservicing, it's not really doable otherwise.