Re: Migration from MSSQL to PGSQL

Another good option along these lines is to use the Pgadmin2 tool.

First you would export your file as tab delimited.

Then use the pgadmin tool to import this file to your server.

The only problems with this are:

1) Takes a little bit of setting up to get pgadmin running, on
both client and server.

2) I've had problems getting it to import large files. The ODBC
receive buffer on the server would overflow and crash the import.

The advantage of the tool is you don't have to worry about converting
your tab delimited text file from dos to unix format.

Though for a recent 76,000 row table I ended up just using the COPY
command.

Overall though, pgadmin2 is a great tool with alot of other time
saving (and SQL Server-like) features that make it worth trying
out.

http://pgadmin.postgresql.org/pgadmin2.php?ContentID=1

----- Original Message -----
From: Josh Berkus <josh(at)agliodbs(dot)com>
Date: Wed, 24 Jul 2002 10:06:44 -0700
To: Jeffrey Trueblood <jtrueblood(at)pilenet(dot)com>, "''pgsql-novice(at)postgresql(dot)org''" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: [NOVICE] 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

--
_______________________________________________
Get your free email from http://www.graffiti.net

Powered by Outblaze