SQL error "column does not allow nulls, Insert Fails"

rsaritzky

Hi,

We've added a couple of custom fields to Vendor Ledger Entry. Currently, there is no code to populate these fields (working on that in development). But the fields do exist in the table.

I'm getting an error when a non-SUPER user tries to post. They get the big error window that says (among other things) "Cannot insert the value NULL into column 'Archive Status', table "xxx.dbo.Company$Vendor Ledger Entry'; column does not allow nulls. INSERT fails."

The field "Archive Status" is type [Option].

We're running NAV2009 R2 under Classic Client, so no service tier is involved.

If I try to post the identical transaction logged in as a SUPER user, the error does not occur.

Standard code in Codeunit 12 does an INIT on VendLedgEntry before it is populated. This should initialize any Option field to a value of 0 and should not be NULL.

I have a similar problem with posting of another transaction where Shortcut Dimension 3 is the field that causes the same error (field cannot be null). Dimension 3 is optional.

Has anyone seen this type of problem?

geordie

If you are running with Enhanced security model, did you already try synchronizing the users?

rsaritzky

Running Standard security. But good thought...

kriki

Looks like with 1 session you added some fields and then with another session that was already started BEFORE the fields were added, you are inserting a new record.
Did you try to restart that session?

rsaritzky

Thanks for the suggestion. I don't think this is the case. I logged out of all NAV sessions, had the user with limited permissions log out, log back in, verify that the new fields were in the table, but still had the same problem. The only thing I didn't do was restart the SQL Server. I did restart the service tier (even though we were using classic client). Not sure what the problem was, but I believe you are on the right track - some older version of the object was cached. After a few hours, the problem went away.

Hmmmmmmmmmm.

kriki

The buffer of SQL Server shouldn't be the problem. Once an object is changed in NAV, the SQL Server buffer knows it (even if it is a BLOB). I can hardly think that this could be a bug in SQL Server.
But if you don't use the RTC or the webservice, why do you have them running? It is useless and only consumes resources.

Just to be sure:
-What is the version of SQL Server and its build?
-If you don't have other DB's on your SQL Server, you can restart it so the cache gets cleaned completely. In case you have other databases, you can't do that but you can take the database offline and then back online. This should clean the SQL cache for that database but not other databases. After this, you can try again to see if it still has a problem.

rsaritzky

kriki wrote:

The buffer of SQL Server shouldn't be the problem. Once an object is changed in NAV, the SQL Server buffer knows it (even if it is a BLOB). I can hardly think that this could be a bug in SQL Server.
But if you don't use the RTC or the webservice, why do you have them running? It is useless and only consumes resources.

Just to be sure:
-What is the version of SQL Server and its build?
-If you don't have other DB's on your SQL Server, you can restart it so the cache gets cleaned completely. In case you have other databases, you can't do that but you can take the database offline and then back online. This should clean the SQL cache for that database but not other databases. After this, you can try again to see if it still has a problem.

Thanks for the reply.

We have about half the workstations on RTC, but the problem above was only on the Classic Client. We are on separate servers for the service tier, NAS and SQL, and performance is fine. The problem seems to have resolved itself. Still don't really know why. It could have been because of our periodic restart of all the servers, but it stopped being a problem even before we restarted SQL. So this is 'something that makes you say "hmmmmmmmm"'