Results 1 to 9 of 9

Thread: Sync database

  1. #1
    Join Date
    Jul 2010
    Posts
    11

    Unanswered: Sync database

    I am look for either an example code or a tool that can synchronize a Progress database through ODBC to MS SQL 2005 database. When it synchronize, it should only add or updates records to the SQL database and not replace the entire database as there are other records/tables.

    1. Are there third party utility that can accomplish this?
    2. Does SQL 2005 provide any utility that will do this? Stored Procedure? Will T-SQL be able to help with this?
    3. Any example of C# or VB code will also work
    4. Example of Select statement to do the actual updates will really help

    Can someone help point me to the right direction?

    Thank you!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So you want something that will do a wholesale copy of every table that is currently in your Progress databases into your Microsoft SQL database? If you have tables A, B, and C in your Progress database and tables A, B, C, X, Y, and Z in your Microsoft database, then you want to completely replace the existing A, B, and C in the Microsoft database with the contents of the A, B, and C from the Progress database without disturbing the existing X, Y, and Z in the Microsoft database?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2010
    Posts
    11
    Actually not the database but it's more like taking new or updated records from the A B C table of the Progress database and merge into A B C table of the Microsoft database. If the key fields are the same, then it should update the records. It should not replace the tables entirely. For example,

    A table in Progress Database
    Name* Age
    ------- -----
    Jimmy 38
    Alice 36
    Emily 60

    A table in SQL database
    Name* Age
    ------ -----
    Alice 21
    Jonathan 22
    Tiffany 18

    When it sync, the SQL database will be updated like this
    Name* Age
    ------- -----
    Alice 36
    Jonathan 22
    Tiffany 18
    Jimmy 38
    Emily 60

    In this case, Name is the key field. Alice's age is updated to 36 from 21 because it has the same key. The rest will simply be merged to the table.

  4. #4
    Join Date
    Jul 2010
    Posts
    11
    No one? Please help!!!

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This is almost 1-to-1 from BOL:
    Code:
    if object_id('[Progress.TableA]') is not null
    drop table [Progress.TableA]
    if object_id('[SQL.TableA]')is not null
    drop table [SQL.TableA]
    select * into [Progress.TableA] from (
    select [Name*] ='Jimmy',[Age] = 38 union all
    select 'Alice', 36 union all
    select 'Emily', 60
    ) x
    select * into [SQL.TableA] from (
    select [Name*] = 'Alice',[Age] = 21 union all
    select 'Jonathan', 22 union all
    select 'Tiffany', 18
    ) x
    go
    merge [SQL.TableA] as target
    using [Progress.TableA] as t
    on target.[Name*] = t.[Name*]
    when matched
    then update set target.[Age] = t.[Age]
    when not matched by target
    then insert ([Name*], [Age]) values (t.[Name*], t.[Age]);
    
    Last edited by rdjabarov; 07-28-10 at 17:45. Reason: formatting
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jul 2010
    Posts
    11
    Thank you! Dumb question, but what is BOL? SQL Server Books Online? Can you point me to the original link?

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/952595a6-cf1d-4ff5-8927-66f9090cf79d.htm

    or

    http://technet.microsoft.com/en-us/l.../bb522522.aspx
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jul 2010
    Posts
    11
    Thank you so much!!!!!!! You just made my day!!!

    Also, how do I connect to a Progress database through ODBC using this? and connect to local MS SQL database?

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Look up "Linked Servers" in BOL
    George
    Home | Blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •