I encounter a strange situation about data of User ID field of G/L Entry table.
Most of record the User ID data is exactly the id of user who log in to RTC, but some remaining is wrong. I dont know where the system get these values.
EX: My end user id is ACC_01 but when create a journal and post, the system insert in to USER ID field as TÀS4`ELATIN_100
Here is some strange values:
´¾̃:NAL BATCH NAME
This problem happen for all program area and all screen that has User ID (Posted Sales Invoice, Gl Register, Item Register, Customer Ledger entries...).
Some transaction need to authorize to run by setup user's permission is also difficult to use the system because sometime the permission is not recognized due to USERID wrong such as Posting Range of user when runing Post to G/L batch job.
In this situation, the solution we give to the user is close NAV and restart again.
So any advise, clue... please give us to find the solution to fix it. Many thanks to NAV community.
Great that means that there is no problems with the database itself,
There are now a few things that need to be checked and there are a few conflicting things in what im seeing,
The corruption that you are seeing is normally related to the codepage not recognizing the characters of different languages, for example if you try and view something written in Mandarin on a PC setup for English you will see that sort of corruption of the characters, this would explain what we see except for the fact that you are also seeing values like "APPLIES-TO ENTRY" and that has nothing to do with a user id in a different language
It is still worth checking, does the NAV server, SQL server and Active Directory servers all share the same language and setup, and the user names were not created in a different character set?
Secondly does the database have a collation that matches the languages used in the servers
I am unsure of how NAV actually retrieves the users from the AD but it normally has a reliance on views within the database, for this reason running a NAV native backup and restoring it into a new database may be a solution, the reason for this is that NAV re-creates all the structural parts of the system and if any of these are causing the issue the re-build could do the trick
So create a native backup from within the classic client, create a new Database on the SQL server with a different name from within the NAV client, then restore the backup into it and check if you still see the issue in this test system
Note that this will not fix any of the transactions it will only affect newly created transactions, so in the test make sure you are creating new transactions to test with
Neville FoynNAV Developer, Consultant & Trainer (Since 2001)MCTS NAV, CRM, Sharepoint, SQLMCITP NAVMCT
Strange values displayed in fields could be a sign of database corruption on your SQL server, although there are other possible causes this should be checked first and done immediately as database corruption may be followed by a complete crash of the server,
Make a SQL backup and copy it off the server onto a different machine, test restore the database to ensure it can be restored and open it in NAV to check it still opens, Ensure that database is safely stored on a different machine
On the server in SQL Management studio run the following in a query
DBCC CHECKDB ("databasename")
Where databasename is the name of your actual database on the server, this will check the database for corruption and errors
It could take a while to run on a big database especially if there are errors, hopefully there are no issues we can look at the next steps
Let us know what you find
Many thanks for your useful guide.
We are going to apply this help and tracking this issue. I will let you now the result, may be it take me some days.
Let us know how it goes
After we run the query DBCC CHECKDB on the customer DB and tracking, the issue still happen in the User ID field as follows in G/L Register, Warehouse Register, Item Register... and related entries:
,8L ÏT½ T,½ O_
BANK ACCOUNT NOS_
GET SHIPMENT USED
Please give us the other advance. BTW we already check the login user must be in the Window Account list but the user with wrong User ID also go through and input transaction as usual...
the DBCC CHECKDB does not fix any errors it will just report if it finds any, when it completed the run were there any error messages displayed in the results window, or just a list of the tables that the various processes ran on?
If there were no issues then we can move onto the next step
After running 0 error is found.
Thanks for your support,
Your solution already match with our situation.
We are checking collation of the clients to flag On the Validate Collation of the Database.
I will keep update the result to you after implment this one.
Have a nice day.
Great, Let me know what you find
I cannot feedback sooner because of implementation and observe data.
It's works well, now the number of record that has corrupted data in the User ID field reduced a lot.
The method we approach is that we install right collation at the each PC that input data to the application.
This problem already make us spend a lot of time before. It almost solved, we still tracking and just check the collation of the PC if there is any corruption data.
I and my team want to send you many thanks for great help, wish you all the best.
My team & me.
Happy to help, good luck with the rest of your project
Other Microsoft Sites
I'm a Customer
I'm a Partner
Use the official Twitter tags:
#MSDYNCOMM | #CONV13