Re: Migration from MSSQL to PGSQL

Jeff,

> I am migrating from Microsoft SQL 7.0 to PostgreSQL 7.2.1.
> I am using DTS to transfer the data.
> When trying to transfer a table, DTS fails with the following error, "Row
> value out of range".

When MS implemented DTS, they failed to support any ODBC drivers other than
those that ship with the MDAC. For example, you will find the MySQL ODBC
driver completely useless for DTS (at least, as of MySQL 3.2.3 and MDAC 2.7),
even though it works perfectly for MS Access.

As such, you can expect further problems using DTS to transport data to
Postgres. A number of alternatives are possible:

1. Use a PHP or Perl script to transport the data. PHP and Perl are
multi-platform, and support connections to both databases. There is an
article at Linux Journal (www.linuxjournal.com) describing how to connect to
MSSQL from a Linux machine using port 1433, for that matter.

2. Use DTS to export the data to tab-delimited text, and then use PostgreSQL's
COPY command to load the data.

3. Use MS Access to transfer the data. This is inefficient and crash-prone,
but MS Access does not seem to have the problems with 3rd party ODBC drivers
that DTS does.

Please note that if you have tables with multi-column primary keys, you may
find that you *must* use methods 1) or 2) because of deficiencies in the ODBC
spec concerning multi-column primary keys.

--
-Josh Berkus
Aglio Database Solutions
San Francisco