Subscription.Create Method

Definition

Namespace:: Microsoft.SqlServer.Replication

Assembly:: Microsoft.SqlServer.Rmo.dll

Important

Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.

Creates the subscription registration at the Publisher.

public:
 void Create();

public void Create();

member this.Create : unit -> unit

Public Sub Create ()

Examples

This example creates a push subscription to a transactional publication.

 // Define the Publisher, publication, and databases.
 string publicationName = "AdvWorksProductTran";
 string publisherName = publisherInstance;
 string subscriberName = subscriberInstance;
 string subscriptionDbName = "AdventureWorks2012Replica";
 string publicationDbName = "AdventureWorks2012";

 //Create a connection to the Publisher.
 ServerConnection conn = new ServerConnection(publisherName);

 // Create the objects that we need.
 TransPublication publication;
 TransSubscription subscription;

 try
 {
 // Connect to the Publisher.
 conn.Connect();

 // Ensure that the publication exists and that 
 // it supports push subscriptions.
 publication = new TransPublication();
 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 TransSubscription();
 subscription.ConnectionContext = conn;
 subscription.SubscriberName = subscriberName;
 subscription.PublicationName = publicationName;
 subscription.DatabaseName = publicationDbName;
 subscription.SubscriptionDBName = subscriptionDbName;

 // Specify the Windows login credentials for the Distribution Agent job.
 subscription.SynchronizationAgentProcessSecurity.Login = winLogin;
 subscription.SynchronizationAgentProcessSecurity.Password = winPassword;

 // By default, subscriptions to transactional publications are synchronized 
 // continuously, but in this case we only want to synchronize on demand.
 subscription.AgentSchedule.FrequencyType = ScheduleFrequencyType.OnDemand;

 // Create the push subscription.
 subscription.Create();
 }
 else
 {
 // Do something here if the publication does not exist.
 throw new ApplicationException(String.Format(
 "The publication '{0}' does not exist on {1}.",
 publicationName, publisherName));
 }
 }
 catch (Exception ex)
 {
 // Implement the appropriate error handling here.
 throw new ApplicationException(String.Format(
 "The subscription to {0} could not be created.", publicationName), ex);
 }
 finally
 {
 conn.Disconnect();
 }

' Define the Publisher, publication, and databases.
Dim publicationName As String = "AdvWorksProductTran"
Dim publisherName As String = publisherInstance
Dim subscriberName As String = subscriberInstance
Dim subscriptionDbName As String = "AdventureWorks2012Replica"
Dim publicationDbName As String = "AdventureWorks2012"

'Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.
Dim publication As TransPublication
Dim subscription As TransSubscription

Try
 ' Connect to the Publisher.
 conn.Connect()

 ' Ensure that the publication exists and that 
 ' it supports push subscriptions.
 publication = New TransPublication()
 publication.Name = publicationName
 publication.DatabaseName = publicationDbName
 publication.ConnectionContext = conn

 If publication.IsExistingObject Then
 If (publication.Attributes And PublicationAttributes.AllowPush) = 0 Then
 publication.Attributes = publication.Attributes _
 Or PublicationAttributes.AllowPush
 End If

 ' Define the push subscription.
 subscription = New TransSubscription()
 subscription.ConnectionContext = conn
 subscription.SubscriberName = subscriberName
 subscription.PublicationName = publicationName
 subscription.DatabaseName = publicationDbName
 subscription.SubscriptionDBName = subscriptionDbName

 ' Specify the Windows login credentials for the Distribution Agent job.
 subscription.SynchronizationAgentProcessSecurity.Login = winLogin
 subscription.SynchronizationAgentProcessSecurity.Password = winPassword

 ' By default, subscriptions to transactional publications are synchronized 
 ' continuously, but in this case we only want to synchronize on demand.
 subscription.AgentSchedule.FrequencyType = ScheduleFrequencyType.OnDemand

 ' Create the push subscription.
 subscription.Create()
 Else
 ' Do something here if the publication does not exist.
 Throw New ApplicationException(String.Format( _
 "The publication '{0}' does not exist on {1}.", _
 publicationName, publisherName))
 End If

Catch ex As Exception
 ' Implement the appropriate error handling here.
 Throw New ApplicationException(String.Format( _
 "The subscription to {0} could not be created.", publicationName), ex)
Finally
 conn.Disconnect()
End Try

This example creates a push subscription to a merge publication.

// Define the Publisher, publication, and databases.
string publicationName = "AdvWorksSalesOrdersMerge";
string publisherName = publisherInstance;
string subscriberName = subscriberInstance;
string subscriptionDbName = "AdventureWorks2012Replica";
string publicationDbName = "AdventureWorks2012";
string hostname = @"adventure-works\garrett1";

//Create a connection to the Publisher.
ServerConnection conn = new ServerConnection(publisherName);

// Create the objects that we need.
MergePublication publication;
MergeSubscription subscription;

try
{
 // 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;
 subscription.HostName = hostname;

 // Set a schedule to synchronize the subscription every 2 hours
 // during weekdays from 6am to 10pm.
 subscription.AgentSchedule.FrequencyType = ScheduleFrequencyType.Weekly;
 subscription.AgentSchedule.FrequencyInterval = Convert.ToInt32(0x003E);
 subscription.AgentSchedule.FrequencyRecurrenceFactor = 1;
 subscription.AgentSchedule.FrequencySubDay = ScheduleFrequencySubDay.Hour;
 subscription.AgentSchedule.FrequencySubDayInterval = 2;
 subscription.AgentSchedule.ActiveStartDate = 20051108;
 subscription.AgentSchedule.ActiveEndDate = 20071231;
 subscription.AgentSchedule.ActiveStartTime = 060000;
 subscription.AgentSchedule.ActiveEndTime = 100000;

 // Specify the Windows login credentials for the Merge Agent job.
 subscription.SynchronizationAgentProcessSecurity.Login = winLogin;
 subscription.SynchronizationAgentProcessSecurity.Password = winPassword;

 // Create the push subscription.
 subscription.Create();
 }
 else
 {
 // Do something here if the publication does not exist.
 throw new ApplicationException(String.Format(
 "The publication '{0}' does not exist on {1}.",
 publicationName, publisherName));
 }
}
catch (Exception ex)
{
 // Implement the appropriate error handling here.
 throw new ApplicationException(String.Format(
 "The subscription to {0} could not be created.", publicationName), ex);
}
finally
{
 conn.Disconnect();
}

' Define the Publisher, publication, and databases.
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim publisherName As String = publisherInstance
Dim subscriberName As String = subscriberInstance
Dim subscriptionDbName As String = "AdventureWorks2012Replica"
Dim publicationDbName As String = "AdventureWorks2012"
Dim hostname As String = "adventure-works\garrett1"

'Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.
Dim publication As MergePublication
Dim subscription As MergeSubscription

Try
 ' 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 Then
 If (publication.Attributes And PublicationAttributes.AllowPush) = 0 Then
 publication.Attributes = publication.Attributes _
 Or PublicationAttributes.AllowPush
 End If

 ' Define the push subscription.
 subscription = New MergeSubscription()
 subscription.ConnectionContext = conn
 subscription.SubscriberName = subscriberName
 subscription.PublicationName = publicationName
 subscription.DatabaseName = publicationDbName
 subscription.SubscriptionDBName = subscriptionDbName
 subscription.HostName = hostname

 ' Set a schedule to synchronize the subscription every 2 hours
 ' during weekdays from 6am to 10pm.
 subscription.AgentSchedule.FrequencyType = ScheduleFrequencyType.Weekly
 subscription.AgentSchedule.FrequencyInterval = Convert.ToInt32("0x003E", 16)
 subscription.AgentSchedule.FrequencyRecurrenceFactor = 1
 subscription.AgentSchedule.FrequencySubDay = ScheduleFrequencySubDay.Hour
 subscription.AgentSchedule.FrequencySubDayInterval = 2
 subscription.AgentSchedule.ActiveStartDate = 20051108
 subscription.AgentSchedule.ActiveEndDate = 20071231
 subscription.AgentSchedule.ActiveStartTime = 60000
 subscription.AgentSchedule.ActiveEndTime = 100000

 ' Specify the Windows login credentials for the Merge Agent job.
 subscription.SynchronizationAgentProcessSecurity.Login = winLogin
 subscription.SynchronizationAgentProcessSecurity.Password = winPassword

 ' Create the push subscription.
 subscription.Create()
 Else

 ' Do something here if the publication does not exist.
 Throw New ApplicationException(String.Format( _
 "The publication '{0}' does not exist on {1}.", _
 publicationName, publisherName))
 End If
Catch ex As Exception
 ' Implement the appropriate error handling here.
 Throw New ApplicationException(String.Format( _
 "The subscription to {0} could not be created.", publicationName), ex)
Finally
 conn.Disconnect()
End Try

Remarks

The Create method can only be called by members of the sysadmin fixed server role at the Publisher or by members of the db_owner fixed database role on the publication database.

Calling Create is equivalent to executing sp_addsubscription or sp_addmergesubscription.

URL: https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.replication.subscription.create?view=sqlserver-2016