Hi, We have been doing some work with the Navision change log (3.70 W1) and it is great, apart from the fact it does not capture anything that is done through code. For certain areas in our system it is critial that we capture all changes, so we let the code write directly to the change log table, recording old and new values. This all works great, from various cards the user can now hit a “history button” and see who changed what and where, even when the change was done through a piece of code we wrote. However, it appears a user can only write to the change log table if he/she is a member of the db_owner role in SQL. The bizarre thing is that the user can write to any other table without any problems but not table 405. It appears this also happens on Cronus, e.g. create a database user in SQL (not the owner), assign him/her the super role in Navision, log into Cronus, open table 405 and manually try to create a record. You should get the SQL ODBC error message: The current user is not the database or object owner of table “test.dbo.cronus$Change Log Entry”, cannot perform the set operation. Anybody have any ideas? They would be much appreciated, the thought of having to run all users as db_owner makes me very nervous [:)] and we need the functionality… Thanks Meint
This is a license issue, your license does not give you permission to write to the table. Contact your NSC, and have them write a Navision COde unit that will pass through the required write permissions for you. This is a task that can only be done with an NSC developers license. It is a very simple task, and they will know exactly what is requred.
David, Thanks for your response but this piece of code was done with an NSC development license by their developer. We double checked the permissions on the table in this piece of code and they read rimd as they should. The NSC has now logged it with Navision and we await a response. More suggestions welcome though…
The SQL error refers to the SQL statement being sent by Navision for this table: SET IDENTITY_INSERT ON . This SQL operation can only be performed by a db_owner role or a user that owns that table. Neither are true in this scenario. This operation is sent before a NON-ZERO value is placed into an AutoIncrement field of a new record, and the record inserted. It allows a SQL IDENTITY field, which is used for the AutoIncrement behaviour, to have its auto-generated value overriden with the value placed in Navision. The operation is not used if a zero value is being placed in the field (which lets SQL auto-generate the value). If this is unclear, check out the ‘rules’ for AutoIncrement in the ADG. The question is, why is the Navision code needing to place a non-zero in the AutoIncrement field of the change log table - can you see why in the code? I would expect that they just require the default auto-generating behaviour. There is no way around the permissions required in SQL to execute the SET command needed to override an IDENTITY value.
Thanks Robert, This has given us some very useful insight as to what is going on. Always enjoy reading your detailed insights so keep up the good work… You are correct, we do just require the auto generating behaviour and having had a quick look at the code with the debugger, I can already see that the developer is trying to find the last number used, increment it by one and then use this new number as the Entry No., rather than letting SQL automatically generate one. I will print the ADG for him and beat him over the head with it, until he can recite it in his sleep [:D]… Hopefully we can now fix it, I will keep you posted on progress. Meint
quote:
Originally posted by meintd
Thanks Robert, I will print the ADG for him and beat him over the head with it, until he can recite it in his sleep …
Yeah, like a developer is going to read a manual. … It maybe in his sleep, but it will be in your dreams. [;)] sorry to any developers out there that read manuals, this was of course meant to apply only to those other developers. [:D][:D][:D]
Hurray, changing the Entry No. to zero on the inserts fixed the issue. Many thanks to Robert for saving us a few days of frustration…