January 29, 2015 at 2:45 am
hi,
I have two databases one is entry database (DB1) Where entery is done, other database is reporting database (D2). both will be on same server
we are trying to transfer data using stored procedure from db1 to db2. the sp will be scheduled to run after every 20 to 30 sec.
in db1 we keep three cols in all tables transferedcol, updatedcol, activecol.
--activecol gets 1 if when record is entered first time and remain same while updation.
this col gets 2 if deleted( we do not do physical deletion we only mark 2 in activecol and updagtedcol)
--transferedcol gets 1 if not transfered to db2, if transfered to db2 then we put 0 in it.
if record gets updated then it gets 1 again so that we can transfer it again.
--updatedcol gets 0 for new entry of each record and 1 if updated after transfer of the record to db2 else if any one tries to updated it before transfer then it will be 0 for new record , after the transfer of that record if some one updates the record then it gets 1 and remains 1 throught out the life . yes it gets 2 if record is deleted ( deletion is not physical , it is logical by setting 2 in activecol and updatedcol).
in db2 we have only required cols for reporting, but if required we can keep some cols like transfered ,active and updated.
this db2 has fk and table gets record from joins of many tables of db1 which also has fks
that is schema is not same.
Q1) i want to transfer only the records having transfered col marked as 1 and after transfering i want to make them 0 from the stored procedure which runs after every 20 30 sec.
please suggest some solution with isolation level.
note: on db1 i want that entery should not be blocked to much and at the same it want stored procedure to transfer the data from db1 to db2 so that people can see the report at the very time.
yours sincerley
January 29, 2015 at 7:47 am
Look at the OUTPUT function used with UPDATE.
It was hard to follow your information but would a trigger be a better way than a job that runs every 20 seconds?
February 17, 2015 at 11:43 pm
well i have made a stored procedure with a loop which calls the main stored procedure after 20 seconds.
I am planning to make one windows service which will call my stored prcedure after 20 or 10 sec, which inturn will call many stored procedure in app isolation level.
yours sincerely
February 18, 2015 at 12:23 am
You building what is sometimes referred to as "poor man's replication". Wouldn't it just be easier to setup Transactional Replication and be done with it?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply