DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=reh2Z2IuvlufMw1ZXaA4Htl1fb/iVmyANlSx3DiHkItkFprH6JhKOfpQAJmWV+oenXT1+di5vm+cTtAVcd7lwl7Gt6YpudYfojpZOt6s5Faa6gqep95m4IKrtLDDAfRsjL0KpNUhOfmMXSkP2drj8fGNpUQnPPolsgd6fmNl2wg=
	;
Message-ID: <20060710175733.15412.qmail@web31806.mail.mud.yahoo.com>
Date: Mon, 10 Jul 2006 10:57:33 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: MS-SQL<->Postgres sync
To: Aaron Bono <postgresql@aranya.com>,
	Kevin Bednar <kevin@stockwelldesigngroup.com>, pgsql-sql@postgresql.org
In-Reply-To: <bf05e51c0607101015y20b200e4o26c7c0aee1e8487f@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

> On 7/10/06, Kevin Bednar <kevin@stockwelldesigngroup.com> wrote:
> >
> >  Thanks Aron. What I'm actually trying to do is this:
> >
> > Postgress in physical store, being used by POS system as the back end.
> > MS-SQL being used on web server by ecommerce system.
> >
> > Table structures are different of course, but some common fields. What I
> > want to do is when an item is sold in the store, update the quantity field
> > for that sku number on the web site and vice versa. Only 2 fields basically
> > need to be updated on each side, the SKU number and quantity. This is to
> > keep the product table in sync and try to avoid selling product that isnt in
> > stock and setting a flag on the web system stating such. Thanks for your
> > help.
> >
> 
> 
> For something this simple you are probably better off doing some custom
> coding.
> 
> If you have the ability to modify the databases, I would recommend putting a
> trigger on each database so when there is a product sold, that sale is
> recorded in a temp table (which serves as a queue of data that needs to be
> synched).  Then have a process read from these temp tables and feed the data
> back to the other database.  Of course, I am assuming you have full control
> to change the databases - some vendors do not allow that.
> 
> You may be able to connect the databases - MS SQL Server will definitely
> allow you to connect via ODBC to another database and feed data back and
> forth.  I think there are add on modules for PostgreSQL but I have not tried
> to have PostgreSQL talk to other databases before.

I am not sure if this applys directly to the problem here, but this link my be useful also.

http://archives.postgresql.org/pgsql-general/2006-07/msg00298.php

Regards,

Richard Broersma Jr.