Re: PostgreSQL - linked with MSSQL

Hello Max and thanks for your letter!

I'm keen on PostgreSQL and since 8.0 has been released,
(simple installation on Windows),
I would like to use it instead of MSSQL.

Now the company has two MSSQLs:
- one is "local" on my PC, where I transform/analyse data.
- the other is remote, and it's the database of a system that we use
(outsourcing).
"Inside" MSSQL it was simple to "link" this remote server (two SQL
Servers
- I didn't even need to insert any connection parameters - only user,
password, IP address or server name and it works with no problem).
Everyday I need to import data from the remote serwer.
I do it making a simple:

INSERT INTO [Local_mssql].mydatabase.dbo.mytable
(id, data1, data2, data3)
SELECT
id, data1, data2, data3
FROM [remote_mssql].remotedatabase.dbo.table2
And the data is beeing transferred between two serwers
(thousands of records)
Then I "forget" the remote server and work locally

It is hypothetycally possible to link in this way a PostgreSQL Server,
In Enterprise Manager - SQl Server
but in this way there are PARAMETERS I have to insert and I don't
know them...
Linked server: "localhost" or name of the server (I think)
Provider Name: "PostgreSQL OLE DB Provider"
Product name: ????????
Data source: ?????? (servername??)
Provider string: (no idea)
Location: (database name???).
I've made a lot of tests with various settings but it didn't work.
That would be one of the solutions ("less interesting" as I called
it in my 1st e-mail).

Another solution ("more interesting") would be doing it beeing in
Postgres,
doing an ODBC/JDBC link or something else (I've no idea how it works and
how to do it and maybe I'm wrong).
First I was thinking that in PgAdminIII there's possibility to link
MSSQL
Server in the same way as in Enterprise Manager of MSSQL, but it
probably
only works between two PostgreSQL servers.

So I probably need to do something in psql but have no idea how...

Generally I would like to have the same possibility to make an:
INSERT INTO [Postgres_server].mydatabase.user.mytable
(id, data1, data2, data3)
SELECT
id, data1, data2, data3
FROM [remote_mssql].remotedatabase.dbo.table2

after transferring data from remote MSSQL to Postgres I would
"forget" this remote MSSQL and work "inside" Postgres,
studying its possibilities (stored procedures, SQL "dialect" and so on)

It's very important for me because without this possibility of
transferring
portions of data from Mssql to Postgres I won't have any possibility to
work with "real data". Now I can make a "test_databases" with some
objects,
but it's not the same as solving real problems. This could help me to
discover the great possibilities of PostgreSQL (I strongly believe
it's like that!).
Then I could help other people to use Postgres as I deeply believe
in Open Source.

I know "sounds simple but is probably difficult".
That's why I wrote to your mailing-list
because in Poland (pl.comp.bazy-danych) there was no-one who could
help me.

Best regards,
Piotr

---- Wiadomość Oryginalna ----
Od: Max Cohan <mcohan(at)adnc(dot)net>
Do: "pk2(at)o2(dot)pl" <pk2(at)o2(dot)pl>
Data: Thu, 28 Apr 2005 20:47:45 -0500
Temat: Re: [ODBC] PostgreSQL - linked with MSSQL

>
> Things simply don't work that way...
>
> Is this a 1 time thing...or do you want to keep them synchronized?
>
> If it is one time, the the simplest thing you can do is to export
> in CSV or XML format... Then import into the other database.
>
> Otherwise, it's going to take quite a bit of work.
>
> Max
>
> On Fri, Apr 29, 2005 at 02:32:17AM +0200, pk2(at)o2(dot)pl wrote:
> > Hello, nice to meet you!
> >
> > My name is Piotr. I live in Poland.
> >
> > I'm going to make a connection between
> > PostgreSQL and MSSQL.
> > I have WinXP+MSSQL2k+PostgreSQL 8.0.2 (psql, PgAdminIII),
> > on the same machine.
> > I need to import data from MSSQL to PostgreSQL
> > or make SELECT from MSSQL beeing on PostgreSQL.
> > That would be perfect (I mean beeing in Postgres
> > and importing data from MSSQL. Then I tranform the data,
> > make analysies).
> > Another solution (less interesting for me but probably easier)
> > would be linking Postgres Server in Ent. Manager.
> > To be honest I'm a complete "green horn"
> > when talking about configuring odbc/jdbc/ole db etc.
> > Are you able to help me? Would you have enough patience?
> > What information do you need from me?
> > I'll describe everything I did in details.
> > Thanks,
> > Piotr.
>