Records not replicated when inserted by custom replication stored procedure.

Question

I've just recently setup a custom replication for my subscriber database, as described in another post here. Basically, when the publisher pushes a new record to the subscribers, the stored procedure will also insert a replicated time into an extra column in the table, and insert a new record to a log table.

My problem occurs when trying to replicate the log table back to the main publication database. This is what I did:

1. In the database where the log table is located, I setup a new transactional replication, and set it to create a snapshot.
2. Once the publication is created, I create a new push subscription, and set it to initialize immediately.
3. Once the subscription is created, I checked the synchronization status and confirm that the snapshot is applied successfully.

Now here's the weird part: if I manually add a record to the log table using the SQL Server Management Studio, the record will be replicated fine. If the record is added by the custom replication stored procedure, it will not. The status will always display "No replicated transactions are available".

I have no clue why the publication is behaving this way: I really don't see how it is treating the data inserted by the custom replication stored procedure differently.

Can someone explain what may I've done wrong?

UPDATE: I finally have an answer for this problem a few months ago, just that I never got around to update this question. We have to log a support call to Microsoft, but we got a working solution.

---

ANSWER: To resolve the problem, when adding a subscription, you need to run the script like below:

sp_addsubscription @publication = 'TEST', ..., @loopback_detection = 'false'

The key to the solution is the last parameter shown above. By default, the generated subscription script will not have this parameter.

Answer 1

I see this is a very old question now so you've probably resolved this, but anyway...

The problem you describe certainly doesn't seem to make sense. The replication will be invoked further to any change to the source table via the replication trigger. The only thing that doesn't look right in your process description (though I may be misreading) is that you are creating a snapshot before pushing the subscription. Typically you should be setting up the replication, pushing the subscription and then creating / pushing a snapshot. Don't trust the sync status as this isn't checking anything, it's simply saying it has no transactions to copy, it doesn't know that the tables are synched.

As to why your manual insert works but not the automated one I would check and recheck your workings, as fundamentally, if the replication is working then any change to this table will be replicated, irrespective of the source.

If you have long since resolved this I'd be interested to hear the resolution.

Edit:

A late thought: when you are updating your datetime field using your custom proc that then fires triggers back into the replication database, you could be causing deadlocking problems between the replication model and your inserts. This could potentially be causing the failure to replicate back. Bit complex to figure out without running tests, but it's a possibility.

Answer 2

I finally have an answer for this problem a few months ago, just that I never got around to update this question. We have to log a support call to Microsoft, but we got a working solution.

To resolve the problem, when adding a subscription, you need to run the script like below:

sp_addsubscription @publication = 'TEST', ..., @loopback_detection = 'false'

The key to the solution is the last parameter shown above: @loopback_detection = 'false'. By default, the generated subscription script will not have this parameter.