locked
Setting Time of Subscription of SQL Replication by RMO RRS feed

  • Question

  • So i am trying this :

            Sub.AgentSchedule.FrequencyType = ScheduleFrequencyType.Daily;// sub is Merge Push subscription as it goes MergeSubscription

             Sub.AgentSchedule.FrequencyInterval = 1;
             Sub.AgentSchedule.FrequencyRecurrenceFactor = Days; // And days Set by the program user Int

             Sub.AgentSchedule.FrequencySubDay = ScheduleFrequencySubDay.Once;
             Sub.AgentSchedule.ActiveStartTime = 120000;
             Sub.AgentSchedule.ActiveEndTime = 122000;

    _______________________________________________________________________________________________________________________

    by this peace of code i am trying to set the push subscription to start in set by user number of days Days  once after 12 : 00 : 00 PM can anyone tell me what i am doing Wrong

    Friday, November 11, 2011 2:20 PM

Answers

  • If your Sub.AgentSchedule.FrequencySubDay is set to ScheduleFrequencySubDay.Once and your Sub.AgentSchedule.ActiveStartTime is set to 120000, you'll notice that Job Schedule Properties - Replication agent schedule that Daily frequency should be set to Occurs once at: 12:00:00 PM.

    I was able to get this working with the following code snippet:

    // Connect to the Publisher.
    conn.Connect();
    
    // Ensure that the publication exists and that 
    // it supports push subscriptions.
    publication = new MergePublication();
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;
    publication.ConnectionContext = conn;
    
    if (publication.IsExistingObject)
    {
    if ((publication.Attributes & PublicationAttributes.AllowPush) == 0)
    {
        publication.Attributes |= PublicationAttributes.AllowPush;
    }
    
    // Define the push subscription.
    subscription = new MergeSubscription();
    subscription.ConnectionContext = conn;
    subscription.SubscriberName = subscriberName;
    subscription.PublicationName = publicationName;
    subscription.DatabaseName = publicationDbName;
    subscription.SubscriptionDBName = subscriptionDbName;
    
    // Define the synchronization schedule
    subscription.AgentSchedule.FrequencyType = ScheduleFrequencyType.Daily;
    subscription.AgentSchedule.FrequencyInterval = 1;
    subscription.AgentSchedule.FrequencyRecurrenceFactor = 1;
    subscription.AgentSchedule.FrequencySubDay = ScheduleFrequencySubDay.Once;
    subscription.AgentSchedule.ActiveStartDate = 20111113;
    subscription.AgentSchedule.ActiveEndDate = 20121113;
    subscription.AgentSchedule.ActiveStartTime = 120000;
    subscription.AgentSchedule.ActiveEndTime = 122000;
    
    // Create the push subscription.
    subscription.Create();
    


    Hope this helps.
    www.sqlrepl.com

    • Proposed as answer by Peja Tao Monday, November 14, 2011 7:49 AM
    • Unproposed as answer by X-AL Monday, November 14, 2011 10:55 AM
    • Proposed as answer by Brandon Williams Sunday, November 20, 2011 8:40 PM
    • Marked as answer by X-AL Monday, November 21, 2011 9:40 AM
    Sunday, November 13, 2011 8:37 PM
  • You want the job to run every 3 days at 12 PM?

    Change the line:

    Sub.AgentSchedule.FrequencyInterval = 1;
    
    To:

    Sub.AgentSchedule.FrequencyInterval = Convert.ToInt32(0x003);
    


    Hope this helps.
    www.sqlrepl.com

    • Marked as answer by X-AL Tuesday, November 15, 2011 9:33 PM
    • Unmarked as answer by X-AL Wednesday, November 16, 2011 9:14 AM
    • Proposed as answer by Brandon Williams Sunday, November 20, 2011 8:40 PM
    • Marked as answer by X-AL Friday, December 30, 2011 6:20 AM
    Tuesday, November 15, 2011 8:23 PM
  • Right.  As mentioned in AgentSchedule Property, once the schedule is set, you cannot change it using RMO.

    Instead you must utilize the JobSchedule class or execute sp_update_schedule to change an existing schedule using the AgentJobID.


    Hope this helps.
    www.sqlrepl.com

    • Proposed as answer by Brandon Williams Sunday, November 20, 2011 8:40 PM
    • Marked as answer by X-AL Monday, November 21, 2011 9:41 AM
    Wednesday, November 16, 2011 3:37 PM
  • Right, I understand the confusion.  It took me a while to figure it out too.

    You'll need to add references to Microsoft.SqlServer.Smo and Microsoft.SqlServer.Management.Sdk.Sfc.

    Then, an example of how to access the JobSchedule after creating the subscription:

    // These namespaces are required
    using Microsoft.SqlServer.Management.Smo.Agent;
    using Microsoft.SqlServer.Management.Smo;
    
    ...
    
    // Create the push subscription.
    subscription.Create();
    
    // Access the subscription job schedule
    Server server = new Server(serverName);
    Job mySubcriptionJob = server.JobServer.Jobs[subscription.SynchronizationAgentName];
    JobSchedule mySubcriptionJobSchedule = mySubcriptionJob.JobSchedules[0];
    

    Good luck.


    Hope this helps.
    www.sqlrepl.com

    • Proposed as answer by Brandon Williams Sunday, November 20, 2011 8:40 PM
    • Marked as answer by X-AL Monday, November 21, 2011 9:41 AM
    Friday, November 18, 2011 9:06 AM

All replies

  • Hi X-AL,

    What problem are you having?

    According to How to: Specify Synchronization Schedules (RMO Programming), we must set the AgentSchedule fields prior to calling the Create method to create the subscription.  There's also an example provided that shows how to create a push subscription to a merge publication and specifies the synchronization schedule.


    Hope this helps.
    www.sqlrepl.com

    Friday, November 11, 2011 5:27 PM
  • Also, if the schedule is already set, you cannot change it using RMO.  In this case, you must utilize the JobSchedule class or sp_update_schedule to change an existing schedule.  This is documented in Subscription.AgentSchedule Property.

    Hope this helps.
    www.sqlrepl.com

    Friday, November 11, 2011 10:51 PM
  • I am doing this in Define function just before .Create() metod  the supscription is just fine but the time is not set. Any ideas?

     

    Sunday, November 13, 2011 7:41 PM
  • If your Sub.AgentSchedule.FrequencySubDay is set to ScheduleFrequencySubDay.Once and your Sub.AgentSchedule.ActiveStartTime is set to 120000, you'll notice that Job Schedule Properties - Replication agent schedule that Daily frequency should be set to Occurs once at: 12:00:00 PM.

    I was able to get this working with the following code snippet:

    // Connect to the Publisher.
    conn.Connect();
    
    // Ensure that the publication exists and that 
    // it supports push subscriptions.
    publication = new MergePublication();
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;
    publication.ConnectionContext = conn;
    
    if (publication.IsExistingObject)
    {
    if ((publication.Attributes & PublicationAttributes.AllowPush) == 0)
    {
        publication.Attributes |= PublicationAttributes.AllowPush;
    }
    
    // Define the push subscription.
    subscription = new MergeSubscription();
    subscription.ConnectionContext = conn;
    subscription.SubscriberName = subscriberName;
    subscription.PublicationName = publicationName;
    subscription.DatabaseName = publicationDbName;
    subscription.SubscriptionDBName = subscriptionDbName;
    
    // Define the synchronization schedule
    subscription.AgentSchedule.FrequencyType = ScheduleFrequencyType.Daily;
    subscription.AgentSchedule.FrequencyInterval = 1;
    subscription.AgentSchedule.FrequencyRecurrenceFactor = 1;
    subscription.AgentSchedule.FrequencySubDay = ScheduleFrequencySubDay.Once;
    subscription.AgentSchedule.ActiveStartDate = 20111113;
    subscription.AgentSchedule.ActiveEndDate = 20121113;
    subscription.AgentSchedule.ActiveStartTime = 120000;
    subscription.AgentSchedule.ActiveEndTime = 122000;
    
    // Create the push subscription.
    subscription.Create();
    


    Hope this helps.
    www.sqlrepl.com

    • Proposed as answer by Peja Tao Monday, November 14, 2011 7:49 AM
    • Unproposed as answer by X-AL Monday, November 14, 2011 10:55 AM
    • Proposed as answer by Brandon Williams Sunday, November 20, 2011 8:40 PM
    • Marked as answer by X-AL Monday, November 21, 2011 9:40 AM
    Sunday, November 13, 2011 8:37 PM
  • Yea but what if we want to do it in a custom number of day in exact same hour example 3 days replicatuin then 4 days replication and so on any idea how that is Done? I can't still get it working.
    Monday, November 14, 2011 7:42 AM
  • When i load the Subscription Properties after all are set back to 0 or default. If You have any idea what i am doing wrong i will be gratefull
    Monday, November 14, 2011 9:12 AM
  • Can you post the entire code here please?

    Hope this helps.
    www.sqlrepl.com

    Tuesday, November 15, 2011 2:47 AM
  • well they  are couple of functions in some classes  that  one is like

    public MergeSubscription DefineTime (MergeSubscription Sub )

    {

             Sub.AgentSchedule.FrequencyType = ScheduleFrequencyType.Daily;// sub is Merge Push subscription as it goes MergeSubscription

             Sub.AgentSchedule.FrequencyInterval = 1;
             Sub.AgentSchedule.FrequencyRecurrenceFactor = Days; // And days Set by the program user Int

             Sub.AgentSchedule.FrequencySubDay = ScheduleFrequencySubDay.Once;
             Sub.AgentSchedule.ActiveStartTime = 120000;
             Sub.AgentSchedule.ActiveEndTime = 122000;

             return Sub;

    }

    then in other function the defined  Subscription is created by .Create() method all is fine except the time line.Can u post me Subscription  that hapend  each 3th day in 12 PM for example

    Tuesday, November 15, 2011 8:11 PM
  • You want the job to run every 3 days at 12 PM?

    Change the line:

    Sub.AgentSchedule.FrequencyInterval = 1;
    
    To:

    Sub.AgentSchedule.FrequencyInterval = Convert.ToInt32(0x003);
    


    Hope this helps.
    www.sqlrepl.com

    • Marked as answer by X-AL Tuesday, November 15, 2011 9:33 PM
    • Unmarked as answer by X-AL Wednesday, November 16, 2011 9:14 AM
    • Proposed as answer by Brandon Williams Sunday, November 20, 2011 8:40 PM
    • Marked as answer by X-AL Friday, December 30, 2011 6:20 AM
    Tuesday, November 15, 2011 8:23 PM
  • and any ide how to change to chustom mumber of days set by the user in some kind of int value ?
    Tuesday, November 15, 2011 9:35 PM
  • And one more I still can't read AgentSchedule in my program my best Guess is that it because the data is Hold by the Agent Job but in RMO there are no AgentJob object the best that i find is subscription.AgentJobId; wich gives me some kind of id but what next what if the user wants to edit the subscription timeline ?
    Wednesday, November 16, 2011 9:16 AM
  • Right.  As mentioned in AgentSchedule Property, once the schedule is set, you cannot change it using RMO.

    Instead you must utilize the JobSchedule class or execute sp_update_schedule to change an existing schedule using the AgentJobID.


    Hope this helps.
    www.sqlrepl.com

    • Proposed as answer by Brandon Williams Sunday, November 20, 2011 8:40 PM
    • Marked as answer by X-AL Monday, November 21, 2011 9:41 AM
    Wednesday, November 16, 2011 3:37 PM
  • Can u give me example of how to get read the time of the replication in my program ? 

     

    Friday, November 18, 2011 7:52 AM
  • I am reading about  JobSchedule class but i have hard time to uderstand how to use it.
    Friday, November 18, 2011 8:24 AM
  • Right, I understand the confusion.  It took me a while to figure it out too.

    You'll need to add references to Microsoft.SqlServer.Smo and Microsoft.SqlServer.Management.Sdk.Sfc.

    Then, an example of how to access the JobSchedule after creating the subscription:

    // These namespaces are required
    using Microsoft.SqlServer.Management.Smo.Agent;
    using Microsoft.SqlServer.Management.Smo;
    
    ...
    
    // Create the push subscription.
    subscription.Create();
    
    // Access the subscription job schedule
    Server server = new Server(serverName);
    Job mySubcriptionJob = server.JobServer.Jobs[subscription.SynchronizationAgentName];
    JobSchedule mySubcriptionJobSchedule = mySubcriptionJob.JobSchedules[0];
    

    Good luck.


    Hope this helps.
    www.sqlrepl.com

    • Proposed as answer by Brandon Williams Sunday, November 20, 2011 8:40 PM
    • Marked as answer by X-AL Monday, November 21, 2011 9:41 AM
    Friday, November 18, 2011 9:06 AM