Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Milo Cold
Starting Member
14 Posts |
 Posted - 2005-06-22 : 18:06:05
|
| 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 LossType: 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 windows) 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 these has worked. So I resorted to kicking the server around for a bit...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 adanceM. Cold |
|
|
|
|
|
Sql Job to watch Pull subscription connection