Converting native fdb to sql database not using backup

Amaraa

Hello all,

I need to convert native db to sql.
The problem is I cannot make a back of existing fdb database because of the error (error xxxx in module xx).

So, the question is: "Is there anyway to convert native db to sql, other than making backup and restore it in sql db?".

Please give me a hand on this.

Thank you,
Amaraa

bestianhow

I think that is only way.

Amaraa

bestianhow wrote:

I think that is only way.

so then it isn't so funny ](*,) , gotta find a way

garak

Amaraa wrote:

...because of the error (error xxxx in module xx)...

Could you describe the error (if the error message is displayed, press Ctrl+C and paste the error message here).

Amaraa

garak wrote:

Amaraa wrote:

...because of the error (error xxxx in module xx)...

Could you describe the error (if the error message is displayed, press Ctrl+C and paste the error message here).

error 1190 in module 19.
when I check this in most case there is a code field with empty data.
even when I enter data by hand there the error still remains.

kine

Have you searched for this error on this forum? This error is solved in some hotfixes and could be signal of problematic network etc.

Amaraa

kine wrote:

Have you searched for this error on this forum? This error is solved in some hotfixes and could be signal of problematic network etc.

yes I did searched about this error. but no fine solution #-o

garak

From David's wiki (thanks David for sharing)

http://wiki.dynamicsbook.com/index.php? ... _Module_19

But u know in which table this error is (like Customer, G/L Account, or so ...). So, you can "fix" the datas

garak

Maybe there are more then one table.
So u must fiy all the corrup datas in these tables.

Here in this link is a liitle NAV codesnippes.

viewtopic.php?t=21295

Regards

Amaraa

garak wrote:

Maybe there are more then one table.
So u must fiy all the corrup datas in these tables.

yes, there are dozens of tables that have this error.
I've checked this and tried to run. However, it doens't go with all tables.

I have G/L Account table which is causing error on No. field.
If I see the content everything is fine with the numbers. There were no special character or lower case characters.
But still having this error. Have you guys have an idea on that??? :?: :?: :?:

But again, if somebody has an idea, please share.
Amaraa

kine

Once I have case when I just exported the table, deleted all records (without calling trigger), and imported the data back (without calling triggers). The values in CODE fields were than correct and backup goes without problem. Before that I was able to look at the data without error, but when trying to backup or when testing keys, there was error. All was because wrong values in CODE fields on SQL.

garak

Try this:

Copy your database files to an other server (like a testing database).

use new testdatabase:

Export all datas of the corrupt tables (for example with an dataport) as kine described.
Delete all datas in this corrupt table (now the table is empty).
Import during the dataport the datas back.
Now the table should work. Do this step with the other tables ....

ara3n

you can use custom dataport to export the data from native and migrate to SQL using the dataport.

Amaraa

garak wrote:

Try this:

Copy your database files to an other server (like a testing database).

use new testdatabase:

Export all datas of the corrupt tables (for example with an dataport) as kine described.
Delete all datas in this corrupt table (now the table is empty).
Import during the dataport the datas back.
Now the table should work. Do this step with the other tables ....

I have tried this too, but some of the tables doesn't want to import. I don't know reason, there was an code field but somehow it turns to decimal and don't want to import the data. Maybe cursor points wrong?!

Amaraa

And again isn't there anyway to convert native db to sql without using BACKUP AND RESTORE?!

kine

Amaraa wrote:

And again isn't there anyway to convert native db to sql without using BACKUP AND RESTORE?!

No, there isn't. Only export&import.

kine

Amaraa wrote:

garak wrote:

Try this:

Copy your database files to an other server (like a testing database).

use new testdatabase:

Export all datas of the corrupt tables (for example with an dataport) as kine described.
Delete all datas in this corrupt table (now the table is empty).
Import during the dataport the datas back.
Now the table should work. Do this step with the other tables ....

I have tried this too, but some of the tables doesn't want to import. I don't know reason, there was an code field but somehow it turns to decimal and don't want to import the data. Maybe cursor points wrong?!

It could be because the " in data. Set another text delimiter in the dataport or use fixed length.

garak

check kine's suggestion like this:

FileFormat -> Fix to use, in the table defined, Fieldlenght of the fields
FieldStartDelimiter -> None
FieldEndDelimiter -> None
FieldSeparator -> ° (or a other chare that isn't used, but not , or ; <- these chars are ofthen in the description)
Copy all fields (wihtout the BLOB Fields) in the Field Designer (open field Designer, open also Field Menu, mark all fields in the field menu (blue), click then in the Field Designer and confirm the message with YES.) Now all fields are in the Field Designer with the fixed length. Delete now the BLOB and FLOW fields.

Also, check after export your data for example with wordpad or notepad to find "corrupted" chars (they look for example like a square)

regards

Amaraa

garak wrote:

check kine's suggestion like this:

FileFormat -> Fix to use, in the table defined, Fieldlenght of the fields
FieldStartDelimiter -> None
FieldEndDelimiter -> None
FieldSeparator -> ° (or a other chare that isn't used, but not , or ; <- these chars are ofthen in the description)
Copy all fields (wihtout the BLOB Fields) in the Field Designer (open field Designer, open also Field Menu, mark all fields in the field menu (blue), click then in the Field Designer and confirm the message with YES.) Now all fields are in the Field Designer with the fixed length. Delete now the BLOB and FLOW fields.

Also, check after export your data for example with wordpad or notepad to find "corrupted" chars (they look for example like a square)

This works fine!
Thanks all of you,
Amaraa

David_Singleton

As you can see from the description on my wiki (thanks Garak) this error is due to incorrect data being entered into Navision. Probably through some ODBC connection.

Step one has to be to find the source of the error. Most likely source is that you have an external program that is writing into a code field (I think you now this by now), but for some reason it is converting the upper case incorrectly (as far as Navision is concerned).

Once you find the program externally that is making the error. It may be possible to write a routine that can correct the data. The big issue you have, is if this field is a part of the primary key. If its a secondary key you can probably even write a non printing report in Navision with some code like

XYZ := UPPERCASE(XYZ);
Modify;

That will generally fix the error. If the code error is the primary key you have problems, because Navision wont read the record. Often you can resolve this by sorting on secondary key. If that doesn't work, you might need to create a new field (integer) populate the field and then convert this to the primary key. Then you can run through the old primary key (like the code above) and fix it, then move it back to primary.

If you do decide to do this outside Navision, then really make sure you know what you are doing. And keep in mind that this fixing project is a major task, you want your absolute best consultant and developer working on it.

And next time you write and code in Navision TEST TEST TEST. Then you are less likely to get this level of corruption again.

garak

You're welcome, and read also Davids post and find the reason why your datas are corrupted (maybe through an external application (C/ODBC / C/Front,etc.)

kine

In my case, the corruption was because wrong non-unicode application language settings of windows when restoring database or wrong collation. Than NAV wrote wrong UPPERCASE characters into SQL because wrong transcript table was used.

garak

kine wrote:

In my case, the corruption was because wrong non-unicode application language settings of windows when restoring database or wrong collation. Than NAV wrote wrong UPPERCASE characters into SQL because wrong transcript table was used.

Also possible, but if i understand it correct, he want to make a backup from a native database

David_Singleton

The priority should be to fix the corrupt database. Exporting the data and manually importing to SQL should be the last resort.

kine

garak wrote:

kine wrote:

In my case, the corruption was because wrong non-unicode application language settings of windows when restoring database or wrong collation. Than NAV wrote wrong UPPERCASE characters into SQL because wrong transcript table was used.

Also possible, but if i understand it correct, he want to make a backup from a native database

Ah, sorry, wrong memory cluster... :-D

My case was on Native too... something like restoring backup of database created on another windows language setting... Nothing about SQL...

It means, if you are handling Native DB, you need to restore in same environment like when taking the backup (non-unicode app language).

Eugene

you should never try converting database without making a backup. From what you told us my understanding is that your customer is running without making any backups of the data at all which is already extremely bad

Your primary task should be to fix the database so the data is consistent and the customer can start making regular backups. Fix the data in your current database!!!

Amaraa

kine wrote:

garak wrote:

kine wrote:

In my case, the corruption was because wrong non-unicode application language settings of windows when restoring database or wrong collation. Than NAV wrote wrong UPPERCASE characters into SQL because wrong transcript table was used.

Also possible, but if i understand it correct, he want to make a backup from a native database

Ah, sorry, wrong memory cluster... :-D

My case was on Native too... something like restoring backup of database created on another windows language setting... Nothing about SQL...

It means, if you are handling Native DB, you need to restore in same environment like when taking the backup (non-unicode app language).

Yes, I use different language(non-unicode app language). Since navision doesn't support unicode it becomes little difficult.
And in another hand there is an empty rows in code field may cause the error.
But, I cannot delete them