From ringerc@ringerc.id.au Wed Aug  8 06:57:29 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 9020E7ACC82
	for <pgsql-admin@postgresql.org>; Wed,  8 Aug 2012 03:57:29 -0300 (ADT)
Received: from mail-yw0-f46.google.com ([209.85.213.46])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ringerc@ringerc.id.au>) id 1Sz0Cs-0007t7-6R
	for pgsql-admin@postgresql.org; Wed, 08 Aug 2012 06:57:28 +0000
Received: by yhmm54 with SMTP id m54so431312yhm.19
	for <pgsql-admin@postgresql.org>; Tue, 07 Aug 2012 23:57:11 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:x-gm-message-state;
	bh=CceBlNQiPyYdLj1nxDQofWEkBnFCMjJmPp/DGiSvu9E=;
	b=iUZLZ8GYaDIS/b3Duy082vikRSFRTtDZFTGjYq4WpdARONNWvkp4DXTbNTpH4H27y2
	64JYRu6oaMvlAZrzfqiPV6ZAUzmJ5xxwmo3Q21fl0mUfvGCFns58UfvW5YHvj/wh+EZQ
	qaR53jj6qUeWP0RfUgWF3HM5qfb7Dz0Rrcw4VyKbNf9De9PGfuvBB6BUWPHSMHOifFoz
	SD5N/rl2KeUwCaoWjEbaLn1ca+UgdYX3vNwqEagF5IpTxEQZCfcwCARA3JiJAegNRbpB
	qn14oKjKMe6w/ocREWhr3y84mumqlPGmkSjFl+q2lhbcaTAYht6LhtdgmLA6oUbeyk4P
	ahKw==
Received: by 10.66.83.161 with SMTP id r1mr31599659pay.69.1344409030649;
	Tue, 07 Aug 2012 23:57:10 -0700 (PDT)
Received: from ayaki.localdomain (office1.postnewspapers.com.au.
	[150.101.171.128])
	by mx.google.com with ESMTPS id qt6sm5855469pbb.9.2012.08.07.23.57.08
	(version=SSLv3 cipher=OTHER); Tue, 07 Aug 2012 23:57:09 -0700 (PDT)
Message-ID: <50220DC1.8030007@ringerc.id.au>
Date: Wed, 08 Aug 2012 14:57:05 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: karthi keyan <karthi2itnet@gmail.com>
CC: pgsql-admin@postgresql.org
Subject: Re: Data migration to sql server 2008
References: 
 <CAM-vHq+wi0jckw+-mHFJeX98FGutkmSPcDnYR75yHinqtHixrg@mail.gmail.com>
In-Reply-To: 
 <CAM-vHq+wi0jckw+-mHFJeX98FGutkmSPcDnYR75yHinqtHixrg@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------060707070002080307020203"
X-Gm-Message-State: 
 ALoCoQmk5mwAsJummgEo2K95Upuq9lOPNRw03wE1ZQSmDtosJG5bXJoFJmEBQByovVaF6Id28V4j
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201208/53
X-Sequence-Number: 39874

This is a multi-part message in MIME format.
--------------060707070002080307020203
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------060707070002080307020203
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 08/07/2012 07:06 PM, karthi keyan
      wrote:<br>
    </div>
    <br>
    <blockquote
cite="mid:CAM-vHq+wi0jckw+-mHFJeX98FGutkmSPcDnYR75yHinqtHixrg@mail.gmail.com"
      type="cite"><span>For some interlink application purpose , i need
        to <b>migrate data into Sql server 2008</b>.</span><br>
    </blockquote>
    <br>
    The simplest way is usually to connect with psql and export CSV data
    with:<br>
    <br>
    &nbsp;&nbsp;&nbsp; \copy (SELECT ....) to '/path/to/file.csv' csv<br>
    <br>
    or for a whole table:<br>
    <br>
    &nbsp;&nbsp;&nbsp; \copy tablename to '/path/to/file.csv' csv<br>
    <br>
    For usage of COPY see:<br>
    <br>
    &nbsp;&nbsp;&nbsp; \h COPY<br>
    <br>
    and
    <meta http-equiv="content-type" content="text/html;
      charset=ISO-8859-1">
    <a
      href="http://www.postgresql.org/docs/current/static/sql-copy.html">http://www.postgresql.org/docs/current/static/sql-copy.html</a><br>
    <br>
    <br>
    ... then load that CSV into MS SQL Server 2008 like you would
    anything else.<br>
    <br>
    <br>
    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.<br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------060707070002080307020203--



From ringerc@ringerc.id.au Thu Aug  9 12:50:16 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 3DDB71E4A567
	for <pgsql-admin@postgresql.org>; Thu,  9 Aug 2012 09:50:16 -0300 (ADT)
Received: from mail-pb0-f46.google.com ([209.85.160.46])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ringerc@ringerc.id.au>) id 1SzSBn-0003Iw-T6
	for pgsql-admin@postgresql.org; Thu, 09 Aug 2012 12:50:15 +0000
Received: by pbbrr13 with SMTP id rr13so868446pbb.19
	for <pgsql-admin@postgresql.org>; Thu, 09 Aug 2012 05:49:57 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type:x-gm-message-state;
	bh=AHEuWDWB9HZEl3SyAUjd7c8a5THCvSmz/Ssn0iWh/EA=;
	b=Zuj5md1kPxSb7cl40VJqOoqykny2qNB3i50bD5w924zu70BTlpbJTEYY7mqUZHZEur
	xBB+sJaSderkYGgmtZizFCfd4BqYEiZYlBXCR6VNBf63cCBAL11Mld6zZS9XC/yS3v0j
	UMFpBJYaw3/SSYPcdsdX2tYSivpRSnDmSZkC6dXLbVlFWmLKZyXa3q8kYT0DzTffJG9S
	r6X1fBI+GzskB1V1HbbYcr8sl0DQjDsFoi6qGxwta3h/JfILxhLRgBOQYX1R3vAz8OGC
	eT0P6x7/MCPWN63KmIcKK/e8FkLdQ1ywf/OR0wW4A7mSeUPztXG2gjRFhXSFMDWzm40K
	yvXg==
Received: by 10.68.129.73 with SMTP id nu9mr4074698pbb.59.1344516597177;
	Thu, 09 Aug 2012 05:49:57 -0700 (PDT)
Received: from ayaki.localdomain (124-169-173-54.dyn.iinet.net.au.
	[124.169.173.54])
	by mx.google.com with ESMTPS id sj5sm1102409pbc.30.2012.08.09.05.49.54
	(version=SSLv3 cipher=OTHER); Thu, 09 Aug 2012 05:49:55 -0700 (PDT)
Message-ID: <5023B1EF.8060709@ringerc.id.au>
Date: Thu, 09 Aug 2012 20:49:51 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: karthi keyan <karthi2itnet@gmail.com>,
	"'pgsql-admin@postgresql.org'" <pgsql-admin@postgresql.org>
Subject: Re: Data migration to sql server 2008
References: 
 <CAM-vHq+wi0jckw+-mHFJeX98FGutkmSPcDnYR75yHinqtHixrg@mail.gmail.com>
	<50220DC1.8030007@ringerc.id.au>
	<CAM-vHqKAfzcdQ3jt6iSZw5beU86v=aNAc7FbhcYjEJ+_ZOcUQw@mail.gmail.com>
In-Reply-To: 
 <CAM-vHqKAfzcdQ3jt6iSZw5beU86v=aNAc7FbhcYjEJ+_ZOcUQw@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------090807050306070605070702"
X-Gm-Message-State: 
 ALoCoQnbwvTfvfebDectutS58w4Ex0l7/MghpSdyc6ZfHn9PooJairx4u+IpB8YcDOST1p1Ms31M
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201208/63
X-Sequence-Number: 39884

This is a multi-part message in MIME format.
--------------090807050306070605070702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

[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

--------------090807050306070605070702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">[Replying via mailing list]<br>
      <br>
      On 08/09/2012 08:24 PM, karthi keyan wrote:<br>
    </div>
    <blockquote
cite="mid:CAM-vHqKAfzcdQ3jt6iSZw5beU86v=aNAc7FbhcYjEJ+_ZOcUQw@mail.gmail.com"
      type="cite">
      <meta http-equiv="Context-Type" content="text/html;
        charset=ISO-8859-1">
      Thank you for u r reply.<br>
      I have establish the same by sql server <b>linked server method</b>
      .its is working fine.<br>
      But right now i am facing the problem of .<br>
      <br>
      -- Query i used in sql<br>
      &nbsp;Select *&nbsp;&nbsp;&nbsp;&nbsp; from openquery(Postgre,'
      dblink_connect(''mycon'',''hostaddr=localhost; dbname=explore
      ;user=openerp; password=xplore)') <br>
      <br>
      <br>
      --- the response<br>
      <br>
      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.<br>
      <br>
      Pl help me to fix it out.<br>
    </blockquote>
    <br>
    You haven't really shown enough information. In particular, where
    does this 'Postgre' variable/table/whatever come from? What is it?<br>
    <br>
    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.<br>
    <br>
    Please reply via the mailing list ("reply all") in future.<br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------090807050306070605070702--



From ringerc@ringerc.id.au Fri Aug  10 05:20:17 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id BB0B5192E3B7
	for <pgsql-admin@postgresql.org>; Fri, 10 Aug 2012 02:20:17 -0300 (ADT)
Received: from mail-gg0-f174.google.com ([209.85.161.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ringerc@ringerc.id.au>) id 1Szhdv-0007HI-85
	for pgsql-admin@postgresql.org; Fri, 10 Aug 2012 05:20:17 +0000
Received: by ggdk6 with SMTP id k6so1268274ggd.19
	for <pgsql-admin@postgresql.org>; Thu, 09 Aug 2012 22:20:02 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:x-gm-message-state;
	bh=XQy0/8Rn17HQ0r4bdPA3XEXDnMcZj1ztzqZEE4rkwHo=;
	b=OrxqrcOU/dm9JK3JHmjfKFaTrWhqMgHxQEfIBbGA/P5gFh5EdPor9TTPkI5iw2f1t+
	yhlm0ovELd3kxH9TzF0gHxNZ/lrOcgkxTm0Vd0PJMErwR4r+zn3Ul2MI1SHdl1RFDynL
	nvpOQ3j3AyH7PO7qRMshNaPvy4abm44TQVRuyVqLkAUWlmuS0EPGDzM0VP4BrSi79/e8
	rySQ2O02lTuCTGiKXP6gLRgs5hEBCPF4fe9meA1nMYHnGDKD/1C3azpKbeeXsxO0xzvJ
	ZV8AfRGKragricovWqZbz6eyW2te75OgKmsmA0l9L7hlXcr9zIsRifEpzEQ/t28CwS0q
	+iCg==
Received: by 10.66.73.5 with SMTP id h5mr3426899pav.79.1344576001710;
	Thu, 09 Aug 2012 22:20:01 -0700 (PDT)
Received: from ayaki.localdomain (124-169-173-54.dyn.iinet.net.au.
	[124.169.173.54])
	by mx.google.com with ESMTPS id qp6sm2587691pbc.55.2012.08.09.22.19.59
	(version=SSLv3 cipher=OTHER); Thu, 09 Aug 2012 22:20:00 -0700 (PDT)
Message-ID: <502499FC.6050505@ringerc.id.au>
Date: Fri, 10 Aug 2012 13:19:56 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: karthi keyan <karthi2itnet@gmail.com>
CC: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Data migration to sql server 2008
References: 
 <CAM-vHq+wi0jckw+-mHFJeX98FGutkmSPcDnYR75yHinqtHixrg@mail.gmail.com>
	<50220DC1.8030007@ringerc.id.au>
	<CAM-vHqKAfzcdQ3jt6iSZw5beU86v=aNAc7FbhcYjEJ+_ZOcUQw@mail.gmail.com>
	<5023B1EF.8060709@ringerc.id.au>
	<CAM-vHqJhXsY_i=+-uqpO=i=5s669xqvcyvYgmd-bCohR=N+uTw@mail.gmail.com>
In-Reply-To: 
 <CAM-vHqJhXsY_i=+-uqpO=i=5s669xqvcyvYgmd-bCohR=N+uTw@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------050406010706010902050005"
X-Gm-Message-State: 
 ALoCoQlfS+hpXnqzRvDeJR1T1B8O72lCBo1a1sBnqEeGkNMW/VrzC7uW+Vfjj4g3QwUzNZdvmHhJ
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201208/75
X-Sequence-Number: 39896

This is a multi-part message in MIME format.
--------------050406010706010902050005
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------050406010706010902050005
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 08/10/2012 12:35 PM, karthi keyan
      wrote:<br>
    </div>
    <blockquote
cite="mid:CAM-vHqJhXsY_i=+-uqpO=i=5s669xqvcyvYgmd-bCohR=N+uTw@mail.gmail.com"
      type="cite">
      <meta http-equiv="Context-Type" content="text/html;
        charset=ISO-8859-1">
      <br>
      <br>
      Actually my need is import data from PostgreSQL&nbsp; , and the same
      process into sql server.<br>
      <br>
    </blockquote>
    I'm guessing you didn't see what I wrote in reply to your message,
    where I said:<br>
    <br>
    You haven't really shown enough information. In particular, where
    does this 'Postgre' variable/table/whatever come from? What is it?<br>
    <br>
    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.<br>
    <br>
    <br>
    By the way, the easiest way to do this sort of thing is usually to <br>
    <br>
    &nbsp;&nbsp;&nbsp; COPY tablename TO '/some/file.csv' CSV<br>
    <br>
    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.<br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------050406010706010902050005--