Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> LLBLGen v2.6 Self-Servicing Computed Columns
 

Pages: 1
Bugs & Issues
LLBLGen v2.6 Self-Servicing Computed Columns
Page:1/1 

  Print all messages in this thread  
Poster Message
mshe
User



Location:
Markham, Canada
Joined on:
02-Feb-2006 23:31:34
Posted:
167 posts
# Posted on: 28-Sep-2009 22:33:04.  
Hello,

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

Code:

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:

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:

Code:

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):

Code:
            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?
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8086 posts
# Posted on: 29-Sep-2009 06:22:43.  
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
Quote:
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


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
mshe
User



Location:
Markham, Canada
Joined on:
02-Feb-2006 23:31:34
Posted:
167 posts
# Posted on: 29-Sep-2009 06:25:45.  
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?
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14529 posts
# Posted on: 29-Sep-2009 11:51:26.  
Quote:
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?


  Top
mshe
User



Location:
Markham, Canada
Joined on:
02-Feb-2006 23:31:34
Posted:
167 posts
# Posted on: 29-Sep-2009 15:54:08.  
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:

Code:
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.
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37634 posts
# Posted on: 30-Sep-2009 12:06:43.  
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:
Code:

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.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.