Migration from MS SQL 2000 to PostgreSQL 8.3

Classic Servoy
1

Hi everybody,

I’m trying to migrate my current database from MS SQL 2000 to PostgreSQL 8.3. I’ve tried to suimply export my solution with the data, but as my database contains more than 200000 records, it seems to take ages to export it (not even one bar in progression bar after 1 hour).
So, I’ve read that using the DTS from Enterprise Manager was a good solution. The configuration seems to be fine as when I test it using the DTS wizard, it is ok. But when I try to export the database to PostgreSQL none of the 78 table was exported and I got the error: “couldn’t find the schema db_ag”. db_ag is actually the database I want to export. This database is already created in PostgreSQL with its empty tables.

2

I find it easiest to use Servoy to migrate data. Create an empty database in PostgreSQL with all tables. Then connect Servoy to both the old and the new DB. You can then write a simple method that creates a foundset for a table in the source DB, walk over that foundset and create record by record in the target DB. It is not the fastest way, but I found it to be the most reliable.

3

Hi FooBrother,

Perhaps the following link will help you further:
http://wiki.postgresql.org/wiki/Microso … an_Harding

Hope this helps.

4

ROCLASI:
Hi FooBrother,

Perhaps the following link will help you further:
http://wiki.postgresql.org/wiki/Microso … an_Harding

Hope this helps.

Thx. I’ve already seen this article but It looks harder to setup and maybe longer than patrick’s solution. But patrick’s solution will be very long to implement as well in my case as I have 78 tables to copy and some of them have more than 10 columns. :?

5

Foobrother:
…and some of them have more than 10 columns. :?

A function that probably is helpful for this is databaseManager.copyMatchingColumns(). See the sample code:

//Copies all matching non empty columns (if overwrite boolean is given all columns except pk/ident, if array then all columns except pk and array names), returns true if no error did happen
for( var i = 1 ; i <= foundset.getSize() ; i++ ) {
	var srcRecord = foundset.getRecord(i);
	var destRecord = otherfoundset.getRecord(i);
	if (srcRecord == null || destRecord == null) break;
	databaseManager.copyMatchingColumns(srcRecord,destRecord,true)
}
6

Joas:
A function that probably is helpful for this is databaseManager.copyMatchingColumns(). See the sample code:

//Copies all matching non empty columns (if overwrite boolean is given all columns except pk/ident, if array then all columns except pk and array names), returns true if no error did happen

for( var i = 1 ; i <= foundset.getSize() ; i++ ) {
var srcRecord = foundset.getRecord(i);
var destRecord = otherfoundset.getRecord(i);
if (srcRecord == null || destRecord == null) break;
databaseManager.copyMatchingColumns(srcRecord,destRecord,true)
}

Is that working with 2 different database (different database servers but same structure)?
Looks interesting. Thx

7

Foobrother:
Is that working with 2 different database (different database servers but same structure)?

It even works if the structures are different, because it only copies the matching columns. 8)