readable data on non committed update

postire

Dear expert,
I have installed NAV 5.0 on SQL 2005.
I have also external windows programs that should update directly fields in the database, accessing SQL.
During the update phase if I insert a value in one field and I open the table through a form (the Customer card for example) I can see the changed value even if the commit of the transaction is not yet present.
And this happens also if I run some report with this field in it...

So - if for some reason I have a rollback on this transaction - I may have wrong values in my reports.

Is there any way (Nav or SQL setup) to block the read permissions on the record until the transaction is committed?
Thanks in advance!

kine

you need to change the transaction isolation level. If you use another level, it will not read uncommted data (dirty read). There is one property on the report to change that. In another objects you can use C/AL code to change that.

postire

Thanks a lot for your answer. But which is the proprierty you refers to? I tried to change the transaction type but it had not effects...if you give me a small example I will be... !!

kine

It is TransactionType. This is correct property, but you need to set correct Type. You need to set e.g. Snapshot (read-only report). In code the transaction isolation level can be changed through CURRENTTRANSACTIONTYPE command. Or you can use the LOCKTABLE at OnPreDataItem to read the data with locking and thus preventing read of uncommited data.

postire

Thank you Kine. I really appreciate your help! 8)