Thread: PostgreSQL - linked with MSSQL
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.
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.
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@adnc.net>
Do: "pk2@o2.pl" <pk2@o2.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@o2.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.
>
pk2@o2.pl wrote: > 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 The quick solution (and the one I always use) is to setup ODBC for postgresql, then create an MS-Access database that links to both. Add a bunch of queries/vba and you're done. -- Richard Huxton Archonet Ltd
> pk2@o2.pl wrote: > > 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 > > The quick solution (and the one I always use) is to setup ODBC for > postgresql, then create an MS-Access database that links to both. Add a > bunch of queries/vba and you're done. Another solution along the same vein is to use DTS (Data transformation services) to manipulate the data. The nice thing about dts is it can integrate right into the scheduler so you can 'publish' the data between different data sources. DTS is free with the desktop edition of sql server, :-). Merlin
---- Wiadomość Oryginalna ---- Od: Merlin Moncure <merlin.moncure@rcsonline.com> Do: Richard Huxton <dev@archonet.com> Kopia do: pgsql-odbc@postgresql.org, pk2@o2.pl Data: Fri, 29 Apr 2005 09:28:49 -0400 Temat: RE: [ODBC] PostgreSQL - linked with MSSQL > Another solution along the same vein is to use DTS (Data transformation it works a little bit. Probably I have the wrong OLE DB Provider. It's unstable. How can I check the version of my OLE DB?