Transaction is not commited for some users

kine

On sunday our customer installed MS SQL 2005 SP2 and trasnfered the database from MS SQL 2000 server to this new server. They are using 4.0.3.25638 (NAV 4.00SP3+update6) version of NAV client, objects are on 3.70B version.

Today we found out that in some situations and for some users aren't some transactions commited into DB.

Details:
Customer is using some part of customized application, where on opening one specific form are generated new data if they are not already generated, and these data are than showed in the form (generated are in OnOpen trigger of the form). For some users the data are correctly generated without problems. For some the form is behaiving in this way:
- after opening the form, there is just first line of data generated correctly - the line is showed as inserted (without asterisk on the left side).
- after e.g. checking table filters (just opening the Table Filters and closing it), the line is showed as not inserted (with asterisk on the left side).
- in database there are no records inserted for this case.

Conclusion: The data are inserted, but during "reading" the records into the form the data are lost (missing commit).

Research: I did research in this way:
1) Because user is using Citrix, same procedure were reproduced without using citrix - same wrong behaviour.
2) I found out that the behaviour depends on user, under which the process is done. If we enable db_owner role on the MS SQL server for the user having the problem, problem was solved. But this is not a solution.
3) Not every non-db_owner user has this problem. Some users which are not db_owners has not this problem.
@TRANCOUNT > 0 COMMIT TRAN" statement after them but in the non-working situation this statement is missing!

Question:
Have someone any information about such problem? Is there a solution? We are in situation, that we do not know where this "bug" can create some problems within processes in the company and if the DB consistency is not in a danger.

Thanks for any info.

kriki

I never heard about such a bug.
Some things I would try:
-Use standard security model (I hate the enhanced one)
-apply the latest hotfixes of SQL2005SP2 and Navision
-Put this code just after inserting the records in the OnOpenForm-trigger.

COMMIT;
SELECTLATESTVERSION;

And maybe also a SELECTLATESTVERSION at the beginning of the trigger before any other code.

garak

are you sure, that there is no transaction error :?:
for example, the system will insert the record per if rec.insert then; :oops:
and will not show the error :?:

kine

MS SQL 2005 was installed yesterday with all available updates (SP).
NAV is 4.00SP3 + update 6
We are using the standard security model, else I will not be able to sync all the users... :-)

There is no sign of error in SQL profiler or in the client. It leads to some permissions problem because the db_owner rights solved this. But this is not solution and if all is running under application role, why it has impact to this?

Another interesting point is, that I can see just my session in Session table like if the trace 4616 is not enabled, but it is. Else the client will not run... I never saw this happend on our SQL 2005 server we are using in our company.

They are using 64bit version of SQL 2005.

I have answer from MS that this problem is not know for them.

May be that there is some problem originated in transfer of the DB from MS SQL 2000 to MS SQL 2005 - the database is still in the compatibility mode... but we will see after all connected actions will be done and the compatibility flag will be switched to 90 level...

kriki

kine wrote:

May be that there is some problem originated in transfer of the DB from MS SQL 2000 to MS SQL 2005 - the database is still in the compatibility mode... but we will see after all connected actions will be done and the compatibility flag will be switched to 90 level...

I remember something that it is recommended to use compatibility flag=90.

kine

Ok, I found all the answers. And because it is long story, I am preparing BLOG post about that issue. Uff... once again something what made my borign life much brighter....

kriki

kine wrote:

Ok, I found all the answers. And because it is long story, I am preparing BLOG post about that issue. Uff... once again something what made my borign life much brighter....

We are all waiting for the BLOG post!

kine

Blog posted. May be it is too complicated to read, but I have so much information and I do not want to write long boring article that it was hard to keep it simple but with all context...

You can see it on my 3 blogs:
http://www.mibuso.com/blogs/kine
http://dynamicsuser.net/blogs/kine/
http://msmvps.com/blogs/kine/