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
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>