pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Data migration to sql server 2008
3+ messages / 1 participants
[nested] [flat]
* Re: Data migration to sql server 2008
@ 2012-08-08 06:57 Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 3+ messages in thread
From: Craig Ringer @ 2012-08-08 06:57 UTC (permalink / raw)
To: karthi keyan <karthi2itnet@gmail.com>; +Cc: pgsql-admin
On 08/07/2012 07:06 PM, karthi keyan wrote:
> For some interlink application purpose , i need to *migrate data into
> Sql server 2008*.
The simplest way is usually to connect with psql and export CSV data with:
\copy (SELECT ....) to '/path/to/file.csv' csv
or for a whole table:
\copy tablename to '/path/to/file.csv' csv
For usage of COPY see:
\h COPY
and http://www.postgresql.org/docs/current/static/sql-copy.html
... then load that CSV into MS SQL Server 2008 like you would anything else.
A more complicated but more powerful way is to use an ETL tool like
Pentaho or Talend to connect to both databases and merge/convert data.
--
Craig Ringer
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Data migration to sql server 2008
@ 2012-08-09 12:49 Craig Ringer <ringerc@ringerc.id.au>
parent: Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 3+ messages in thread
From: Craig Ringer @ 2012-08-09 12:49 UTC (permalink / raw)
To: karthi keyan <karthi2itnet@gmail.com>; pgsql-admin
[Replying via mailing list]
On 08/09/2012 08:24 PM, karthi keyan wrote:
> Thank you for u r reply.
> I have establish the same by sql server *linked server method* .its is
> working fine.
> But right now i am facing the problem of .
>
> -- Query i used in sql
> Select * from openquery(Postgre,'
> dblink_connect(''mycon'',''hostaddr=localhost; dbname=explore
> ;user=openerp; password=xplore)')
>
>
> --- the response
>
> Cannot process the object "
> dblink_connect('mycon','hostaddr=localhost; dbname=explore
> ;user=openerp; password=xplore)". The OLE DB provider "MSDASQL" for
> linked server "Postgre" indicates that either the object has no
> columns or the current user does not have permissions on that object.
>
> Pl help me to fix it out.
You haven't really shown enough information. In particular, where does
this 'Postgre' variable/table/whatever come from? What is it?
You should look at the PostgreSQL server logs to see if PostgreSQL is
sending an error when you attempt the connection and if so, what it is.
Consider also setting log_statement = 'all' in postgresql.conf so you
can see exactly what queries this "openquery" tool runs against PostgreSQL.
Please reply via the mailing list ("reply all") in future.
--
Craig Ringer
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Data migration to sql server 2008
@ 2012-08-10 05:19 Craig Ringer <ringerc@ringerc.id.au>
parent: Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 0 replies; 3+ messages in thread
From: Craig Ringer @ 2012-08-10 05:19 UTC (permalink / raw)
To: karthi keyan <karthi2itnet@gmail.com>; +Cc: pgsql-admin
On 08/10/2012 12:35 PM, karthi keyan wrote:
>
>
> Actually my need is import data from PostgreSQL , and the same
> process into sql server.
>
I'm guessing you didn't see what I wrote in reply to your message, where
I said:
You haven't really shown enough information. In particular, where does
this 'Postgre' variable/table/whatever come from? What is it?
You should look at the PostgreSQL server logs to see if PostgreSQL is
sending an error when you attempt the connection and if so, what it is.
Consider also setting log_statement = 'all' in postgresql.conf so you
can see exactly what queries this "openquery" tool runs against PostgreSQL.
By the way, the easiest way to do this sort of thing is usually to
COPY tablename TO '/some/file.csv' CSV
from PostgreSQL, then load that file into MS SQL server. That's trivial
to script with vbscript or whatever you prefer, using psql to get the
data out of PostgreSQL.
--
Craig Ringer
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-08-10 05:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-08-08 06:57 Re: Data migration to sql server 2008 Craig Ringer <ringerc@ringerc.id.au>
2012-08-09 12:49 ` Craig Ringer <ringerc@ringerc.id.au>
2012-08-10 05:19 ` Craig Ringer <ringerc@ringerc.id.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox