Convert back Navision SQL database option version.

mandyk

Hi all,
There is a situation where by opening Navision client with higher version will popup a confirmation message whether you want to convert databatase. Once if you accidentally click "YES" then you can't use original Navision Client to open your Database anymore.

Now, I found the easiest way how to convert it back.
Use SQL enterprice Manager and open your Navision SQL database that
already been converted.
Open table $ndo$dbproperty, and you will get only one record.
Find and see the value of databaseversionno field.
I notice for Database Attain 3.10 the value should be 13 and 14 for
Attain 3.60.
So, Once if your 3.10 database converted to 3.6 and you want to reverse it back , simple re-keyin again number 13 and you will have back your database to the original version.
However, I am just lucky to get it this over for my customer database which is in GB size and wonder if this way can work for other situation.

have fun !

HalMdy

Wonderfull Trick !!!

I also find following numbers :

15 : Version 3.70
17 : Version 3.70 A
20 : Version 4

Anybody else knows other codes ?

fb

Careful -- changing the databaseversionno may not be enough... There was an excellent post by robertc over on the 'competition' that described is some detail what goes on when you 'accidentally' upgrade an SQL db:

http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=11299

The important contents are worth repeating here:

robertc wrote:

The SQL version [undergoes] conversion when its views need updating or the database property table [$ndo$dbproperty] layout changes to support new features or fixes. For [certain] updates, changing the databaseversionno of this table back to the previous version will work provided that the contents of existing fields have not changed - mainly the chartable BLOB. But if they have, changing this number is not enough. This is a list of versions and the changes that occured, for 3.01 onwards:

• 3.01, 3.01A, 3.01B
  databaseversionno=12
  Added identifiers field to $ndo$dbproperty
• 3.10, 3.10A
  databaseversionno=13
  Added maintainrelationships field to $ndo$dbproperty
• 3.60
  databaseversionno=14
  Added databaselicense field to $ndo$dbproperty
  Added invalididentifierchars field to $ndo$dbproperty
  Updated Session view
• 3.70
  databaseversionno=15
  Updated chartable in $ndo$dbproperty
  Added checkcodepage, quickfind, maintaindefaults fields to $ndo$dbproperty
  Updated Session and Database File views
• 3.70 Hotfix 5
  databaseversionno=16
  Updated Session view
• 3.70 Hotfix 12
  databaseversionno=17
  Update identifiers and invalididentifierchars in $ndo$dbproperty

In another post, he suggested a way to restore values to your production db by creating a 'scratch' db using the proper (previous) client, and then using Query Analyzer to poke the correct values into the production db from the scratch db:

http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=3281

robertc wrote:

.... The chartable field in your [$ndo$dbproperty] table has been modified by a version later than 2.60F (therefore an Attain version) and now the 2.60F client cannot read the binary data for that field.

You can re-construct the data for that field as follows:

1. Using your 2.60F Financials client, create a new (empty) database on the same server called 'abc' (without quotes).

2. Open the Query Analyzer SQL Server tool. Connect to the server and select the database you want to open - the bad database - as the current database.

3. Execute the following SQL statement:

UPDATE [$ndo$dbproperty]
SET [databaseversionno] = (SELECT [databaseversionno] FROM abc.dbo.[$ndo$dbproperty]),
[chartable] = (SELECT CAST([chartable] AS VARBINARY(8000))
FROM abc.dbo.[$ndo$dbproperty])

5. Now try to open the database with your 2.60F client.

6. You can delete the abc database.

fb

...And a jump to v4.0 is probably irreversible! See

http://www.mibuso.com/forum/viewtopic.php?p=18564#18564

Luc_VanDyck

fb wrote:

There was an excellent post by robertc over on the 'competition' ...

Not on the 'competition' [-X but the 'other' =D> Navision community website.

kine

HalMdy wrote:

Wonderfull Trick !!!

15 : Version 3.70
17 : Version 3.70 A
20 : Version 4

18: Navision 3.70 B

RobertMo

fb wrote:

...And a jump to v4.0 is probably irreversible! See http://www.mibuso.com/forum/viewtopic.php?p=18564#18564

I haven't found it reported here on the forum, but it is possible to "fix" the SQL DB that was (accidentally) converted to 4.0SPx. After applying fix you can open it again with 3.x0. In my case I have done it from 4.0SP1 back to 3.60, but I think the fix is general enough to be successful in other combinations.

It was even harder in my case, since accidental conversion was somehow interrupted by other users that were posting docs. The DB changed only partly, leaving $ndo$* & some other tables corrupted and it was not possible to open the DB with any version of client! Obviously the need to rescue 70GB of the data was strong enough for motivation.

Waldo

If you go from 4.0SP2 to 4.0SP3, it's going to create an extra field in the $ndo$dbproperty table, namely the securityoption-field. But while converting, you see the NAV client going over every table in the application. Somebody knows what he is doing ... en may be better question ... why isn't it a problem when going back to SP2 ... doesn't all these tables have to be converted back to normal?

ara3n

I believe it changes the stored procedures on the tables for sift tables. And until you change the object or key structure it won't change it.


One thing about sp3, the new field is the security option. People who upgrade the sql db and it doesn't complete on the first try, will get a sql error about cannot insert NULL in $ndo$dbproperty security option. So the way around is to delete the field from sql and let upgrade continue.

Waldo

ara3n wrote:

One thing about sp3, the new field is the security option. People who upgrade the sql db and it doesn't complete on the first try, will get a sql error about cannot insert NULL in $ndo$dbproperty security option. So the way around is to delete the field from sql and let upgrade continue.

Indeed.

One more thing. See that you have enough space for your Transaction Log File. It's difficult to say how large it will grow, but you'll have to take in count that it'll grow untill at least half the size of your db.

Waldo

Waldo wrote:

ara3n wrote:

One thing about sp3, the new field is the security option. People who upgrade the sql db and it doesn't complete on the first try, will get a sql error about cannot insert NULL in $ndo$dbproperty security option. So the way around is to delete the field from sql and let upgrade continue.

Indeed.

One more thing. See that you have enough space for your Transaction Log File. It's difficult to say how large it will grow, but you'll have to take in count that it'll grow untill at least half the size of your db.

One addition:
If you put your recovery model to "simple" first, and after the conversion put it again to "full" ... this won't be an issue.
After putting it to "full" again, make sure to make a backup to have a new entry point for your transaction log backups ...

David_Singleton

fb wrote:

There was an excellent post by robertc ...
http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=11299

In another post, he suggested a way to restore values to your production db ...

http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=3281

MBSOnline is not redirecting, the original posts you can now find at:

Dynamics User Group - conversion 3.6 to 3.7 INVOLUNTARILY?

Dynamics User Group - Version Check

Dynamics User Group - Error in File DMEMSTREAM