Post reply

After Replication

  • July 23, 2007 at 6:03 am

    #179723

    Hi,

    We have situation that we are clients and get Program/schedules Information from Source (Head) office that is situated in another country. Their database is different from our local database. We have two tables that we are replicating (using Transactional replication Type) so that users can avail exactly same trainings that is provided in Head office. Basically it is schedule or you can say it’s a daily time table provided by head office.

    Same or slightly different table structure exists for Head office database.

    1- ProgramMaster (3 faculties may have 3 different programs)

    ProgramMaster may contain:

    ProgramMasterId

    Faculty

    ProgramTitle

    ProgramDate

    1

    PH

    Physics Basic

    11-Jun-07

    2

    CH

    Chemistry I

    11-Jun-07

    3

    MTH

    Maths1

    11-Jun-07

    4

    PH

    Physics Adv.

    11-Jun-07

    5

    CH

    Chemistry II

    11-Jun-07

    2- ProgramScheduleDetail (every faculty my have one or more schedules per day).

    ProgramScheduleDetail contains:

    ProgramScheduleDetails Table:

     

     

     

     

     

    ScheduleID

    ProgramMasterId

    ProgramDate

    ScheduleTitle

    ScheduleStartime

    Duration

    ScheduleEndTime

    1

    1

    11-Jun-07

    Physics at basic level1

    9:30

    30

    10:00

    2

    1

    11-Jun-07

    Physics at basic level2

    10:10

    50

    11:00

    3

    1

    11-Jun-07

    Physics at basic level3

    11:15

    15

    11:30

    4

    2

    11-Jun-07

    Chemistry part I First Level

    9:15

    15

    9:30

    5

    2

    11-Jun-07

    Chemistry part I Second Level

    9:30

    30

    10:00

    6

    2

    11-Jun-07

    Chemistry part I Third Level

    10:00

    30

    10:30

    7

    2

    11-Jun-07

    Chemistry part I Fourth Level

    10:30

    45

    11:15

    8

    2

    11-Jun-07

    Chemistry part I Fifth Level

    11:15

    25

    11:40

    9

    2

    11-Jun-07

    Chemistry part I Sixth Level

    11:40

    20

    12:00

    10

    3

    11-Jun-07

    Math 1 basic

    9:30

    30

    10:00

    11

    3

    11-Jun-07

    Math 2 basic

    10:10

    50

    11:00

    12

    3

    11-Jun-07

    Math 3 basic

    11:15

    15

    11:30

    13

    4

    11-Jun-07

    Physics Adv. I

    9:15

    15

    9:30

    14

    4

    11-Jun-07

    Physics Adv.II

    9:30

    30

    10:00

    15

    4

    11-Jun-07

    Physics Adv. III

    10:00

    30

    10:30

    16

    4

    11-Jun-07

    Physics Adv. IV

    10:30

    45

    11:15

    17

    5

    11-Jun-07

    Chemistry II Begin 1

    9:15

    15

    9:30

    18

    5

    11-Jun-07

    Chemistry II Begin 2

    9:30

    30

    10:00

    19

    5

    11-Jun-07

    Chemistry II Begin 3

    10:00

    30

    10:30

    20

    5

    11-Jun-07

    Chemistry II Begin 4

    10:30

    45

    11:15

     The above data is just for providing you sample and relation/logic that I’m using here for explaining my problem.

    The problem is that we adjust some schedules locally, when there is holiday in head office we have to provide timetable locally. So we keep their daily schedules in our local database in two different tables other than our existing local Schedule table.

    Suppose Local Tables are called as

    1-     Local_ProgramMaster

    2-     Local_ProgramScheduleDetail

    Both has auto Increment in master and detail table as explained in above tables from Head office Table structure. My query is that How can I update my Local_ Master and detail tables that is already getting changes from Head Office in above Tables. We are not Mapping with their auto Increment field, but Program Title, Faculty and Program date.

    What is best solution to fix this, I there are two entries per day in master and 10 entries in detail, how can I update my Local_ tables? I want to avoid use of cursor and Trigger. Is there anyway using SELECT T-SQL? Like

    Insert into Local_Master

    Select mapped Columns from ProgramMaster where Faculty, proramTitle and schedule date matches ?

    This will insert auto Increment IDs but how can I put their related data in details table?

     

    Please understand my question and guide ….

     

    Thanks in advance.

    Shamshad Ali.

     

  • July 23, 2007 at 1:35 pm

    #721649

    Use triggers on the Subscriber and join the inserted/deleted tables with the Local_*

    tables as needed


    * Noel

  • July 23, 2007 at 10:31 pm

    #721748

    As I told already,I have two ways, One is to use Triggers and the second is use of CURSOR.

    Draw back of Trigger is that, we have to foward our own Information to our sub office as well, and in that case the server is not always online on network, there are 90% chances that the subOffice will be offline. in that case we have to see another way. and that is Cursor, because when changes comes in subscriber database 1 row in Master table and 1 or many related rows in child, how can i know which record belongs to its master so i can't use simple select and need some logic to keep log of changes and save in local_* tables first with their own auto Increment key (Primary key) and can't use

    insert into MasterTable

    (select * from subcriberMasterTable where schedule_date = GetDate()).

    If more than 1 row is inserted in MasterTable then how can I know which autoIncrement should be assigned to its Detail rows?

    Plz. make sure you get my problem clearly.

    Thanks in advance for any suggestion and help

    Shamshad Ali.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply