Current Status of MergePullSubscription

• Christopher Klein

  SSCrazy

  Points: 2648

  More actions

  May 7, 2004 at 2:03 pm

  #65134

  Hi There,

  Another fun day of SQLDMO programming and pounding my head against the object model diagram wondering why it doesn't seem to give me what I need.

  Two processes I need to check, one to determine if the snapshot on the publisher is finished, the other to determine if the pull on the subscriber has finished.

  First one:

   Set SQLServer = Server.CreateObject("SQLDMO.SQLServer")

   SQLServer.LoginTimeout = 15

   SQLServer.Connect db,usr,pwd

   For Each oDB In SQLServer.Replication.ReplicationDatabases

    For Each oPublication In oDB.MergePublications

     Do while oPublication.SnapshotAvailable = FALSE

      WaitState(1000)

     Loop

    Next

   Next

   SQLServer.DisConnect

   Set SQLServer = Nothing

  I haven't figured out how to get the SPECIFIC snapshot job but since there are only two and they both need to be completed it works out using SQLServer.Replication.ReplicationDatabases.

  The problem is on the subscriber side... I just cant figure out the coding.  I thought it SHOULD be under Replication.MergePullSubscriptions but I can find the name of the job but not its status.

  Examples of this stuff are damn scarce, so any help would be appreciated....  I basically need to do the same thing as I do above, I need to poll the subscription and wait on it to finish before I continue along.

  Thanks,

  chris

   

• May 10, 2004 at 8:00 am

  This was removed by the editor as SPAM

• MacroTrenz Consulting

  SSC-Addicted

  Points: 405

  More actions

  May 10, 2004 at 12:41 pm

  #505857

  only two ways i know . . . both return a query result set:

  sql-dmo - "EnumJobInfo" method

  stored proc - "sp_helpmergepullsubscription"

  both are in bol.

• Christopher Klein

  SSCrazy

  Points: 2648

  More actions

  May 10, 2004 at 12:48 pm

  #505858

   

  Ok, I got it figured out.  I had 3 things going against me

  WaitState(10000)

  for each oItem in SQLServer.JobServer.Jobs

       If instr(1,oItem.Name,"DYNAMIC") Then

            Do while oItem.CurrentRunStatus = SQLDMOJobExecution_Executing

                 WaitState(5000)

                 oItem.Refresh

            Loop

       End If

  next

  Ok, so first off CurrentRunStatus=1 doesn't work.  Needs to be CurrentRunStatus=SQLDMOJobExecution_Executing

  A curse on all enumerated type variables

  Second, you actually need to refresh the job status with oItem.Refresh since its not a realtime status.

  Lastly, even after I had this part working, I still needed to put a 10 second delay at beginning of it because the pullsubscription job hadn't been created yet from the previous step.

  Now I know why people get paid so damn much money to design this stuff...

  Thanks,

  Chris