Hi everyone.

First the required info:

-- Version 1.0.2005.1 Final (july 6th, 2006) -- Runtime file (SD.LLBLGen.Pro.ORMSupportClasses.NET10.dll) version 1.20051.7.307 -- Self-servicing - general scenario

So this is a codebase that has been used successfully for over a year on installations with either sql server 2000 or sql server 2005 as the DBMS.

Today I get this email from my account guy:

The web server is regular 32 bit with a 64 bit db server. When adding a new user I receive the error below. It seems as though anytime there is a llblgen insert followed by a command that gets the last foreign key this issue arises. Llblgen pulls a blank userid which then throws the foreign key error….however the user does get inserted and a key is correctly generated. One item of note is this was a fresh install so any sql settings that are needed for this to work might not be set.

An exception was caught during the execution of an action query: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tUserApplication_tUsers". The conflict occurred in database "TotalRecord", table "dbo.tUsers", column 'UserID'.
The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of an action query: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tUserApplication_tUsers". The conflict occurred in database "TotalRecord", table "dbo.tUsers", column 'UserID'.
The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

The code that is roughly around where this is happening looks like this:

'''' some other property value setting happens here
_User.IsNew = True
_User.Save()

Dim _applications As New ApplicationCollection()
_applications.GetMulti(Nothing)
If _applications.Count > 0 Then
    Dim _userApplication As New UserApplicationEntity()
    _userApplication.UserID = _User.UserID
    _userApplication.ApplicationID = _applications.Item(0).ApplicationID
    _userApplication.IsNew = True
    _userApplication.Save()
End If

We don't have a 64-bit environment locally, so debugging this one has been kind of tough. Apparently the new entry goes into the User table fine.. gets an autogenerated ID like it's supposed to, but that ID doesn't get put back into the entity for use later.

This is happening anywhere in the code that an insert is happening and then the newly-created ID is referenced for something else; it's returning null.

Any thoughts as to why this would be different in a 64-bit SQL 2005 environment as opposed to 32-bit?

As always help is most appreciated.

Matt

Hi Matt, seems that somebody of your team asked this before at HelpDesk forum.

• Would you please use the overload of the Save() method that accepts a boolean for refetch. and set it to true?

• Also would you please examine the SQL Profiler to see if there are any queries generated for the @@IDENTITY or SCOPE_IDENTITY(), and if it exists, please try to execute it manually and check the returned value.

• It might be that the table field isn't defined as identity field in the database. Could you check that please?

• Also check that the involved field is declared IsIdentity true at LLBLGenPro Designer.

Hi Dealmo,

Thank you for the prompt reply.

Ok, so I went back and did some investigating... I'll try and answer your questions point by point and then throw out a few possible ideas of my own.

• This is single-class self-servicing, so there is no refetch Save() overload like there is for the adapter projects. At least not in the version I am using. There is a boolean overload, but it is to save related objects "recurse".

• My coworker was able to get on with profiler and check out the save. it's SCOPE_IDENTITY() that is called (I saw in the Llblgen project that this should be the case as well), and it indeed returned a UserID value like it was supposed to.

• The field is an identity field in the DB.

• Designer shows the IsIdentity field property is set to true.

So all that checks out.

Here are my only thoughts so far:

1. The code started a long time ago, which is why we are still using the 1.0 runtimes. I wonder if moving to the 1.1 or 2.0 runtimes would help things.

2. My coworker has the sneaking suspicion that because it's a new installation of SQL server maybe there is some weird setting that is not right... but I can't think of what that magic switch or button could be.

3. Please keep in mind this same codebase is running at a bunch of other customer sites without problem. The database schemas are the same as well; we use the vs.net db compare tool to make sure all client DB schemas are kept in sync.

Thanks again!

Do you have the SqlServerDQECompatibilityLevel set to any value in the application config file?

Walaa wrote:

Do you have the SqlServerDQECompatibilityLevel set to any value in the application config file?

Hi Walaa,

No, the key was not in the web.config file, but we tried adding it in with a value of "2" for SQL2005 with the same results as before.

Thanks,

Matt

Just to be sure, are Saving the User entity in a transaction? What does _User.UserID evaluates to?

Walaa wrote:

Just to be sure, are Saving the User entity in a transaction? What does _User.UserID evaluates to?

No, this is not in a transaction, but probably should be.

I can't directly debug with the code against the 64-bit sql server, but since it's throwing the FK error and the value is actually being created in the DB and SCOPE_IDENTITY() is being called and returning the right value, my guess would be that _User.UserID is null.

I'm really sorry that some of the info I have is vague or incomplete. Maybe the 64-bit thing is a red herring (i.e. not the real cause of the problem but makes me think that it is), but I know that it has worked in other SQL 2005 installations.

Would there be anything different about the 64-bit architecture that returns too big of a number or something? I wouldn't think so... the datatype is just int and not even bigint.

I don't think the 64bit SQL Server causes any problems.

Can you try the following code and see if it works:

'''' some other property value setting happens here
_User.IsNew = True

Dim _applications As New ApplicationCollection()
_applications.GetMulti(Nothing)
If _applications.Count > 0 Then
    Dim _userApplication As New UserApplicationEntity()
    _userApplication.User = _User
    _userApplication.ApplicationID = _applications.Item(0).ApplicationID
    _userApplication.IsNew = True
End If

_User.Save(true)

You are right; it doesn't. I finally was able to get on the server yesterday and find the cause of the problem.

In SQL Management Studio: Database Properties -> Connections -> Default connection options

"cursor close on commit" was checked off.

I unchecked this and now the saves work as expected. Nothing to do with 64-bit, just some bad SQL server configuration. Thank you so much for your help; sorry it wound up being a simple config issue.

matt

Walaa wrote:

I don't think the 64bit SQL Server causes any problems.

Glad it's sorted, and thanks for reporting the info, so others who might run into this issue will find a solution