subscriber db synch issues

Hi,

I have a publisher db with a table having a trigger that updates a
column in a newly added row. So, for example, there is a table A with
column B. When a new row is added to A, trigger updates column B.

The replicate (subscriber) db's table A doesnt have above mentioned
trigger.

Now, the scenario below is not properly synching subscriber database,

1. New row ROW1 is added to table A of remote/subscriber db.
2. ROW1 is synched to master database in table A
3. trigger is called and column B in master database's table A is
updated.
4. Now column B in remote database's table A doesnt get updated for
some reason.

Following settings have been selected while setting up a publication,

Publication Type: ‘Transactional publication with updateable
subscriptions’
Snapshot Agent : ‘Create a snapshot immediately…’

For subscriber configuration,

1. ‘Run all agents at the Distributer…’
2. Agent Scheduler : ‘Run continuously’\
3. Commit at publisher: Queue changes and commit when possible
4. Initialize When: Immediately

Any help to resolve the above issue will be highly appreciated.

Thanks,
Usman

Hi Usman,

I think that if you add the trigger at the subscriber and mark it as "NOT
FOR REPLICATION" it can work (it will depend on what type of trigger is and
what you do inside your trigger). The problem is that the rowversion for the
same row is updated twice (one for the insert and another one due to the
trigger update) so it fails the final consistency check at the end of the
replication trigger at the subscriber. Another workaround can be to force to
maintain the first value of msrepl_tran_version adding to your trigger
update SET clause "msrepl_tran_version=msrepl_tran_version":

UPDATE A
SET B=whatever, msrepl_tran_version=msrepl_tran_version

You can also detect that you are running inside a replication trigger and
change your trigger logic (sp_check_for_sync_trigger:
http://msdn.microsoft.com/en-us/library/ms178612(SQL.90).aspx). Updatable
subscriptions and triggers are a difficult mix.


Regards,

Rubén Garrigós
Solid Quality Mentors

wrote in message

Hi,

I have a publisher db with a table having a trigger that updates a
column in a newly added row. So, for example, there is a table A with
column B. When a new row is added to A, trigger updates column B.

The replicate (subscriber) db's table A doesnt have above mentioned
trigger.

Now, the scenario below is not properly synching subscriber database,

1. New row ROW1 is added to table A of remote/subscriber db.
2. ROW1 is synched to master database in table A
3. trigger is called and column B in master database's table A is
updated.
4. Now column B in remote database's table A doesnt get updated for
some reason.

Following settings have been selected while setting up a publication,

Publication Type: ‘Transactional publication with updateable
subscriptions’
Snapshot Agent : ‘Create a snapshot immediately…’

For subscriber configuration,

1. ‘Run all agents at the Distributer…’
2. Agent Scheduler : ‘Run continuously’\
3. Commit at publisher: Queue changes and commit when possible
4. Initialize When: Immediately

Any help to resolve the above issue will be highly appreciated.

Thanks,
Usman