|
My brief was to convert the database to SQL and also upgrade from Version 5.0
Dynamics to Version 6.0 eEnterprise. The upgrade at NMM was to be a new one
for me; the incumbent database was Btrieve, which is always a favorite of mine.
Dynamics was at Version 5.0 and Btrieve was 6.15.451 or similar. Because of
my unfamiliarity with the database migration and also this being the first of
our Version 6 upgrades it was insisted the first step be done as a pilot in our
Wellington office before attempting to go onsite and perform the process with
live data.
From the information given to me by our support crew in Sydney the steps to upgrade could go a number of ways but in reality there were two options. Version 5.0 Dynamics was using Btrieve Microkernal Database engine 6.15.451, version 5.5 would require Btrieve engine version 7.5 (which is part of the Pervasive SQL 2000 suite of software) to be installed on the server before upgrading. The two options:
The choice of which option was going to be the best was further involved because the migration tool for Dynamics version 5.0 only migrates the Company tables not the system tables. However the migration tool for Dynamics 5.5 and 6.0 migrates the Dynamics system tables as well. Doing the migration at version 5.0 would mean security and users as well as other Dynamics system info would be missing. Alternatively if we were real keen on keeping the System info the additional step of upgrading the Btrieve engine would need to be completed first to allow for the upgrade to Dynamics 5.5 to be achieved. With the later version Btrieve engine operational the upgrade could have gone from Dynamics 5.0 to 5.5 or to 6.0 and then migrated to SQL without the loss of the Dynamics Sytem tables. Mainly because of the small number of users it was decide to run with the first option. With an empty SQL 7.0 database, eEnterprise 5.0 utilities were launched and the DYNAMICS and blank NMM Company databases were created. Into the Dynamics 5.0 application folder the migration chunk file was included, shrink, rebuild and checklinks were performed, and the migration application launched from the menu. Following the instructions, the name of the SQL server dsn, login and password were entered and the migration process started. The version 5.0 Dynamics application then proceeded to read the data out of the Btrieve Database and stuff it into the Blank company SQL database. This took some time.... maybe 4 - 6 hours and completed without errors. Next step was the upgrade of the tables from eEnterprise 5.0 to eEnterprise 6.0. Since the 2 version 5.0 databases' on the SQL server was newly created the usual DBCC checks were omitted. On the workstation in the eEnterprise 6.0 folder, the latest runtime was copied together with the Dynutils SP2. The dex.ini was altered to include "Synchronize=TRUE". The Dynutils launch file was dragged over the Dynamics.exe runtime and Dynamics Utilities were launched. The upgrade of the DYNAMICS database was successful with the green tick; the Company upgrade was not as successful however. The Problem The upgrade of the company database stopped with an error in the VAT10301 'VAT Line item Work file" table. Utilities reported error "Other error (135)" saying "The conversion Process encountered an error and the Temporary table did not get removed". This table appeared to be empty so the choice of "Update Again" was taken and the Company database appeared to complete the upgrade successfully, however unknown to anyone there was a problem with the GL tables. On launching eEnterprise and checking Cards - Financial - Account there was an error on reading the first record from the GL. "A get / change next operation on table 'GL_Account_MSTR' failed accessing SQL data", "More Info" revealed 3 invalid Column names 'ADJINFL', INFLAREV'. INFLAEQU'. When I checked the GL_Account_MSTR with "Select * from GL00100" I found three columns missing. Indeed the columns 'ADJINFL', INFLAREV'. INFLAEQU' were nowhere to be seen and a call to support in Sydney followed. With some ideas from support I did some checking myself and trialed the database upgrade from eEnterprise 5.0 to 6.0 a number of times further. Each time I restoring back to the data migrated to eEnterprise 5.0. As the update began I regularly checked its progress with scripts below: use DYNAMICS select fileNumber, companyID, errornum, status from DU000030 where fileNumber=400 select fileNumber, companyID, errornum, Status from DU000030 where errornum>0 or Status>0 select FileNumber, fileOSName, fileName from DU000010 where fileNumber in(select fileNumber from DU000030 where errornum>0 or Status>0) select * from db_UPgrade From the script above filenumber 400 is the 2nd conversion on the GL00100 where it is taken form 5.5 to 6.0. When I could see the GL00100 was converted without an error I checked the table and found the extra 3 version 6 columns as I should have expected. Dynutils eventually finished and came up with a red X. The error was the same "Other error" on table "VAT Line Item Work File" (VAT10301). A check on the DU000030 confirmed the error on the "VAT Line Item Work File" (VAT10301). Before doing anything further a check on the GL00100 indicated it was still correct in version 6 format as it had the 3 extra columns ADJINFL, INFLAREV, INFLAEQU needed by version 6. With DynUtils still open I selected update again. As it went through the update process (5 minutes this time) I used my scripts above again and also checked the company database with "select * from GL00100" to see what happened to the GL table... To my surprise the GL00100 changed and the 3 extra columns just put there 10 miniutes back by the upgrade for version 6 were now missing once again. However DynUtils now finished the upgrade process with a big green tick and the check using my scripts above confirmed the database was now correct at version 6. A check using with "select * from GL00100" found the 3 columns ADJINFL, INFLAREV, INFLAEQU missing which was also indicated by the error when in cards - financial - account. So now I knew when it happened, but what to do about it. To fix the problem I restored the data and ran Dynutils from eEnterprise 6.0 again and proceeded with the upgrade until the error in the VAT10301 'VAT Line item Work file" table was presented. This time I choose the "Ignore Error" option in the "Table Error Detail" screen. Dynutils then proceeded to complete the update of the Company database where I was presented with a big green TICK . So what about the VAT10301 table which did not get the update. Well as there appeared to be no data in the VAT10301 table, I removed the table at database level and recreated it with the relevant code from table, procs and Index.cmp. From the lack of feedback from the Team working at NMM there seems to have been no problems with this work around to the problem. Back Richard 24th July 2001 |