getting {"could not execute batch command.[SQL: SQL not available]"} error from NHibernate

Question

I took a look at other related posts but couldn't find any solution.
Sometimes on sesstion.Flush() I get the following error:

{"could not execute batch command.[SQL: SQL not available]"}

and the Inner Exception :

{"The UPDATE statement conflicted with the FOREIGN KEY constraint FK1377052553ABF955. The conflict occurred in database ProcessDebug, table dbo.Adjustment, column 'AdjustmentId'.The statement has been terminated."}

a piece of Process class mapping :

   References(p => p.CurrentAdjustment)
        ;

    References(p => p.DefaultAdjustment)
        ;

    HasMany(p => p.Adjustments)
        .Cascade.AllDeleteOrphan()
        .Inverse()
        ;

All these properties above are of type of Adjustment. As long as I get this error once in a while I couldn't track it down. For an entity it might happen now, but not next time in a same piece of code....

Any idea what might cause the problem?
I'm using NH 3.2 and FluentNhibernate
Thanks in advance

Samphire

Did you ever figure this one out? I have the same error but only when calling the AddOrUpdate method in a transaction. – Aleksandrovsk

@MrAH, I guess it was happening when two machines were simultaneously working on same rows. I'm not sure though. We changed many things and finally, we didn't get the error. But I assume working on same rows caused the error. – Samphire

Answer 1

In my situation, it was "NULL" in one of the databese columns. Check your database data.

FOREIGN KEY -> this means, that you propably have null i column, that is use for "join".

p.s. Take SQL Profiler and check the SQL generated by nHibernate.

Novgorod

Answer 2

You need to look at the sql that is actually trying to execute.

It appears as though you are trying to update the primary key ("AdjustmentId") to something that does not exist. Hence the foreign key violation.

Apophthegm

Answer 3

it seems about you database, not your nHibernate codes, check the SQL in log file, and try to exec it

Genitive

Answer 4

To find the actual cause, you'll need to see the SQL that is being generated by nHibernate. You can either use nHibernate log or nHibernate profiler to get these SQL. Few of the common issues related to the above error message, include:

• String or binary data would be truncated. An example of this issue is whenever you provide a string value that is larger than the defined varchar/nvarchar field size in database.
• Expected primary or foreign key value is null

Guadalcanal

Answer 5

may be its not NHibernate problem! please check in database which column is Not Null/Null. For this,I set null those columns. You can do anything! set value when insert or set null on those column on table.

Tarry

Answer 6

In my case I'm adding a data with column whose type is DateTime, I insert 2100/2/2 get this error, but when insert more reasonable time 2001/2/2 get success,

so maybe the problem is in your data, and should follow some rule in database.

Amok