Hello,
we’re having this strange case: Our client has costing problems which are not corrected by the cost adjustment procedure. We are receiving Navision backup from the client and after restore of this backup and run of the cost adjustment procedure the problems are corrected. At clients premises, the sysadmin make SQL backup of the database and restore it as a new test database. The problems remain. Objects are the same everywhere!
So my question is what is the difference betweeen Navision backup and restore and SQL Server backup and restore? Is it possible that the clients database has some problem at SQL Server level that is inherited during SQL Server backup and restore and that is repaired during Navision backup and restore?
Navision version is 3.1, the client is 4.0 SP1.
I’ll appreciate any opinion.
10x
I would say it is wuite possible. There are a lot of differneces, and I am sure our local SQL experts will comment on those, but form the basics,
Navision Backup and restore does a lot of checeking a long the way. And in particular secondary keys are re-built during a restore.
In SQL for example, you could have invalid Navision data in a field that is part of a secondary key, inthe SQL restore that invalid data is brought across, but through Navision it is corrected. Also you could have had a bad netwrok connection, or a bad disk sector that allowed bad data to be written to the database. Again the Navision backup may not hav looked at this error if it was a part of a secondary key.
A good example, is an external program that writes lower case data into a CODE field. I had a client once that used ODBC to update thier database directly, and the program put lower case data in Code fields. Everythign worked sort of Ok, since the data was not accessed, and they never used Navision backups, they had some other way of doing it. One day they needed to do a Navision backup restore, and it took me many many hours to find and fix the problem.
At this point I would suggest that they first run a full DB test on their existing database to look for errors, or if possible, backup, and restore a completely new Database using Navision’s backup.
I would suggest though that as a long term solution, you sit with the clients IT people, and find out if they are “Playing” and “Tweaking” the Navision database directly with SQL tools. It could even be a case of them inserting records in a table without knowing how, and say not entering the time stamp correctly, or some other such mistake.
10x for your answer. However I still can not understand why the costing procedure produces different results in Navision restored and in SQL Server restored databases. Do you think that it is possible a data to be modified with SQL tools and not to be readable by navision?
Yes that is sort of what I am guessing.
Lets say that someone told the IT department. "We have the wrong cost entered on this Purchase Invoice. Its this record int he Value Entry table, but we keep getting an error message when we try to change it.
SO the IT person says, no problem, let me find my No. 7 Rachet screw driver. Then finds the record, modifies it, and say reenters location in lower case, or puts the wrong time stamp or somethign else.
Now SQL brings the data across wrongly, so keeps getting wrong results.
Bascically the IT department need to be aware that SQL access is for reading data. If they need to write to the database, they need to call the Ghost Busters (aka their NSC).
is there a way this to be checked in sql server? if I look for small letters in code fields in 5802 in SQL Query Analyzer for example?
Sorry, I am just guessing, it could be one of many things, and I am not a SQL expert, I just know many things that one needs to take into account when using SQL. I was more trying to give you a scenario that shows that what you are seeing is possible, and give you a starting point.
I would say that oits best right now to fix it by doing the backup restore, other wise you want to get in a Navision SQL expert to do a full analysis of your database.
The last time I tried to solve a SQL problem like this, I started the other way round. Likethis:
I created a GOOD database, where things worked. Then using SQL Enterprise Manager and some other tools, I kept tryingto break the good database to have the same error as the Broken database (It was quite small, and I just copied a new DB each test). Once I was able to break the database, I then new what COULD cause the error, so then researched the live database to searhc for that type of error.
FOr me the starting point was to try and get honest information out of the SQL team, to find out what they had done. That wont be easy, becasue there is no way that they want to admit to doing anything that may have knocked out the ERP system.