sql job to watch pull subscription connection? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

sql job to watch pull subscription connection?

Hello, I have a typical remote publisher/local subscriber setup using non-updatable transactional replication. We don’t have a deticated line to the publisher so every so often our internet connection drops/hiccups and our pull subscription fails. I have managed to edit the job that controls the replication distributor on the subscribers side so that in the event of a connection loss the job keeps trying to establish the connection (until it succeeds or I intervine). So, Step 1: Run Agent. // Type : Replication Distributor
If this step succeeds, the agent continues to run and replication continues. But if this Step fail it goes to the next step: Step 2: Notify Admin of Connection Loss // Type: TSQL
This uses RAISERROR(50002, 7,1) to send notice of the connection loss. If
this step succeeds, then it will jump back to Step 1 and try again. If
step 2 fails then the job exits reporting failure. So all is well up to this point. What I’d like to add is a step to confirm it’s connection being re-established. I created another error (all errors are being logged to Event Viewer) 50003 which tells me of the connection being made. However, I can’t succefully add it to the current job described above. I’ve tried setting Step 1’s "exit reporting success" to run an additional step (Step 3)
that raises the 50003 error, I then tried to just set the notifications tab in the job setting window, but non of this has worked. The job only tells me when a connection is down, not when it’s back up. Can anyone help me, or tell me how they are alerted when your pull subscription is down for a bit then back up? Thanks in advance
Try setting this notification as another job and call that job from the current job. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.