Re: Reality check

From: Paul Nielsen (pnielsen_at_us.ci.org)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 11:42:03 -0700

Keeping two database in sync with different schema is tricky, but it can be
done. Think of it as two ETLs to a data warehouse. I recommend getting one
database pushing to the other smooth before going two-way.

columns: Sync, Updated
use a trigger to update the updated column with every update. And deafult it
to getdate() for inserts.

1) set @SyncNow = GetDate()

2) update table
     set sync = @Syncnow
    Where updated > sync
3) push all rows with the synch = @syncnow to the other server using a query
to transform the data to the otehr serve's schema. 

-- 
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Sean M. Severson" <sseverson@nerdsolutions.biz> wrote in message 
news:OwnmlHl0EHA.1400@TK2MSFTNGP11.phx.gbl...
> Could someone please verify that what I want to do with sql server is
> possible  (I'm new).
>
> I have two different sql server databases that contain contact 
> information.
> I need to keep the contacts synchronized.  However, the formats of the
> contact tables are different and the databases are from different vendors
> (commercial software).  I need to update certain contact information in 
> one
> database when an addition or change is made in another database.  Can I 
> use
> a trigger on one contacts table to insert a record into another contacts
> table?  I would need to use the contact information that was just inserted
> or modified.
>
> Thanks for your help.
>
> Sean M. Severson
> Software Technology Solutions, LLC
>
>

Relevant Pages

  • Re: Wireless Synchronization
    ... desktop database and PPC database. ... Directly Querying SQL Server using SqlClient (Bit Slow while ... Web Services ... Services as the sync option. ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Need help on how to organize users and objects
    ... Yes Oracle is a bit different than Sql Server, but I think you will get the ... It doesnt become part of any schema until you assign it. ... a new developer started to work. ... need the COMMON database which stores tables with generic data and generic ...
    (microsoft.public.sqlserver.security)
  • Re: How to Synch up a device and desktop
    ... desktop database and PPC database. ... RDA or Merge Replication of SQL CE (RDA is Good to start with, ... SQL Server CE takes up memory if stored in CE tables ... Services as the sync option. ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Reality check
    ... the impression that SQL Server was going to be more difficult. ... > Keeping two database in sync with different schema is tricky, ... > database pushing to the other smooth before going two-way. ... > use a trigger to update the updated column with every update. ...
    (microsoft.public.sqlserver.programming)
Loading