How to import a navision database into SQL Server?

hazem

Hello evreybody,
I have a custom navision database custom.fdb, how to import it into SQL Server?

ermannog

Make a Backup
Create a New Database in Navision SQL Server Option
Do the Restore on the new database

kriki

In case the restore gives errors on invalid dates (e.g. a date 01/02/0005 will not be accepted by SQL), you will have to run the SQL-migration tool on the Navision DB and fix the dates before making a backup.

somnathbasu9002

Steps Of migration from the Navision Standard Database into the SQL Option Database:

Upgrade the database into the Microsoft Business Solutions-3.70 from the earlier version.

prepare the customer’s Navision 3.70 installation for migrating:

1 Verify that both your solution developer’s license file and the customer’s license file
have been upgraded to SQL Server Option for Navision 3.70.

2 Identify the user ID and password of a superuser in the system.

3 Make a backup of the entire database before you begin the migration process.
Keep the backup in a safe place, and keep it for a long time

4 Make a copy of the customer’s database, and migrate the copy.

5 Make sure no other users are connected to the system before you carry out each
part of the migration process.

6 Install Microsoft SQL Server 2000 on the server computer.

CHECKING THE OLD DATABASE

To determine the state of the customer’s current database and correct any database
errors that might exist, follow the procedure described in this section. This will ensure
that no errors exist in the database that will be used as the basis for the upgrade.

1 Open the customer’s Navision 3.70 database, and check that no other users are
currently using the system.

2 Run a database test to determine the state of the customer’s Navision 3.70
database.

Test everything except field relationships between tables. If the test fails, you must
follow the workflow for repairing damaged databases (contact your local Microsoft
Business Solutions Solution Center for details).

3 Run the remaining part of the database test, that is, test field relationships between
tables.

This will allow you to determine the extent of any data inconsistency that exists in
the database. If error messages appear during the test, note their content and
number. Decide whether or not these messages will affect the migration process.

4 Compile all the objects in the database.
Make a list of the objects that cannot be compiled. At some point, you must decide
what to do with the objects that cannot be compiled. They will create problems if
you ignore them.

5 Open the Object Designer and import the Migrate.fob file.

This file is located in the Upgtk folder on the product CD. The import begins and a
message appears.

Click Yes to import the objects.

The Following objects are imported:
Type No. Name
Table 104010 Incorrect Data Value
Table 104011 Code Field Information
Form 104010 Incorrect Data Values
Form 104013 Code Fields Information
Codeunit 104010 Create Field Checking Code
Codeunit 104011 Date Check Management
Codeunit 104012 Code Check Management
Codeunit 104013 Date Check Indicator Mgt.
Codeunit 104014 Date Check Indicator Mgt. 2
Codeunit 104015 Field Check

6 Click Codeunit in the Object Designer. Run codeunit 104010, Create Field
Checking Code.
Codeunit 104010 generates some new code in a text file with this path and name:
c:\fieldchk.txt. You can change the file name and path by modifying the
codeunit.

Import the c:\fieldchk.txt file.

The following objects are imported:

7 Select codeunit 104015, and compile it.

Type No. Name
Codeunit 104015 Field Check

8 Run codeunit 104015.

Codeunit 104015 checks that every date, text, code and decimal value in the
database can be stored in a SQL Server database. It also checks that nonzero
decimal values will not be rounded to zero when they are stored in the SQL Server
database. A progress indicator will help you monitor this process. This involves the
program reading most of the records in the database and may therefore take some
time. If any values need to be changed, the program shows a list of the incorrect values
and the suggested new values in the Incorrect Data Values window:


You can modify these suggestions if you want. When you close the window, a
message will appear asking you whether or not you want to implement the
changes. If you click Yes, the program will implement the changes. The program
will read and modify a small number of records in the database during this step.
Codeunit 104015 also checks the code fields in your data. If your code fields
contain numeric values of varying lengths, it will list them for you. If you sort by
these fields the resulting sorting will be incorrect. Furthermore, any filters using
these fields and containing numeric ranges will give unexpected results. One way
of overcoming this sorting problem is to represent these code fields as integers.
This can be done if the code fields only contain numbers and these numbers do not
start with zeros.
A progress indicator will help you monitor the Field Check codeunit while it is being
run. This step involves the program reading most of the records in the database
and may therefore take some time. If there are any inconsistencies, they will be
listed in the Code Field Information form.
To open this form, click Forms in the Object Designer and select the Code Field
Information form and click Run. The Code Field Information window appears:



This window lists all of the code fields used in the database. It also contains
information about whether the code field is numeric only, a compatible integer and if
it is zero padded. The window displays the minimum and maximum number of
digits that the field contains. The window also displays the SQL Data Type that is
used in the field and whether it contains any numbering conflicts and the name of
any linked tables.
This multitude of columns means that there are numerous ways of sorting this
information.
The SQL Data Type field shows how a code field is represented on SQL Server.
You can change the SQL Data Type property for each code field. If you set the
SQL Data Type field to Integer for a code field, you will be allowed to store only
positive numbers in the code field. This will ensure that numeric sorting is done
correctly.
For more information about numbering and sorting in the SQL Server Option for
Navision, see the manual Application Designer’s Guide.
You can represent a code field that is already in use as an integer, only if the
Numeric Only field and the Compatible Integer field are checked and the Zero
Padded field is cleared.
You must check whether any of the following fields are listed in the Numbering
Conflicts window.

Table Name Table ID Field Name Field No.
G/L Account 15 No. 1
Acc. Schedule Line 85 Row No. 3
VAT Statement Line 256 Row No. 4

If any of these fields appear in the Numbering Conflicts window, you should be
aware that any totals based on them may be inconsistent. When possible, this
situation can be corrected either by changing the data, or by changing the SQL
Data Type property for these fields to Integer.

MIGRATING THE OLD DATABASE

Now that the old database has been checked and modified to ensure that it is
compatible with SQL Server, you can migrate to the SQL Server Option for Navision
3.70.
1 Open the customer’s 3.70 database in Navision 3.70, and check that no other users
are currently using the system.

2 Make a backup of the customer’s Navision 3.70 database and name it, for example,
data.fbk.

3 Create a new database with the SQL Server Option for Navision 3.70.

4 Restore everything from the backup saved in data.fbk.

Restoring the backup into the SQL Server Option for Navision 3.70 database will
take some time and will generate a very large transaction log. We recommend that
you make a new SQL Server backup of the database before you start to work with
it. This will truncate the transaction log and give you a new SQL Server backup as
your starting point.
5 Test the newly migrated database to ensure that you do not encounter version
control conflicts.

If you receive error messages informing you that a record has been modified by
another user, even though you are the only user currently using the system, you have
version control conflicts. These problems occur because the SQL Server Option for
Navision 3.70 has stricter version control than previous versions of Navision
Financials. Any attempt to modify or delete a record will fail if the timestamp on the
version of the record that you have read is different from the timestamp on the actual
record stored in the database.

hazem

Hello,
I'm using The SQL Migration Tool,i got the list of the incorrect format Dates. When i was trynig to correct some of them i got this eroor message:
"There's no field in the Filter, Filter: Table No 337,Field Name Preparation date".
What should i do? :?