Programming the SQL Distribution ActiveX Control

The SQL Distribution control is implemented as a Microsoft® ActiveX® in-process component. It provides a way to move schema and data to Subscribers for snapshot replication and to control the synchronization of subscriptions for transactional replication. The control is comparable to the Distribution Agent. Its primary class, the SQLDistribution object, includes the ability to:

• Add, drop, copy, and register subscriptions for synchronization using Windows Synchronization Manager. For more information, see Common SQL Distribution Control and SQL Merge Control Functionality.
  
  
• Specify an alternate snapshot folder from which the initial snapshot for a subscription can be applied.
  
  
• Specify a working directory to which snapshot files are copied when File Transfer Protocol (FTP) is used to retrieve the initial snapshot.
  
  
• Generate a specially formatted subscription file (usually an .msf) in a specified location. These files can be attached to create a synchronized subscription that is registered at the Publisher as part of an attachable subscription database.
  
  
• Specify a Data Transformation Services (DTS) package that transforms command rowsets before applying them to a Subscriber.
  
  
• Set the Subscriber options to immediate updating or queued updating for changes made at the Subscriber.
  
  
• Synchronize push, pull, or anonymous subscriptions in a transactional publication.

For more information, see SQLDistribution Object.

Instantiating the SQL Distribution Control

This code segment demonstrates how a Microsoft Visual Basic® program configures the SQLDistribution object to synchronize data with an anonymous SQL Server Subscriber.

In the example, Distributor properties are not set, so the Publisher is also the Distributor. The SubscriberDatabase must already exist when this code is executed. Replace the fields in italic with appropriate values.

'SQLDistribution control declaration.
Private mobjDistr    As SQLDISTXLib.SQLDistribution
. . .

Set mobjDistr = New SQLDISTXLib.SQLDistribution

With mobjDistr
    'Set up the Publisher
    .Publisher = "PublisherServer"
    .PublisherDatabase = "PublisherDatabase"
    .Publication = "PublicationName"
    .PublisherSecurityMode = NT_AUTHENTICATION

    'Set up the Subscriber.
    .Subscriber = "SubscriberServer"
    .SubscriberDatabase = "SubscriberDatabase"
    .SubscriberDatasourceType = SQL_SERVER
    .SubscriberSecurityMode = NT_AUTHENTICATION

    'Set up the Subscription.
    .SubscriptionType = ANONYMOUS
    .SynchronizationType = AUTOMATIC

    'Synchronize the data.
    .Initialize
    .Run
    .Terminate
End With

Note  When adding an anonymous subscription, you do not have to call the AddSubscription method of the SQLDistribution object. If the anonymous subscription does not exist, it will be added automatically when the first synchronization is performed, as in the previous sample.

When using a merge publication, the same example can be used for programming anonymous subscriptions with the SQLMerge object. Replace references to the SQLDistribution object with references to the SQLMerge object.

Creating Anonymous Internet Subscriptions Sample

ActiveX replication controls can be programmed to synchronize data over the Internet. After the Publisher and Distributor are configured for publishing over the Internet and a publication enabled for anonymous subscriptions is created, an application using an ActiveX replication control can synchronize with the publication data. FTP is used for the snapshot download, both during the initial application and when the subscription is re-initialized. All other synchronizations use SQL packets over TCP/IP to transfer data between Publisher and Subscriber.

Examples

Distribution Control Using IP Address

This code segment demonstrates how a Visual Basic program configures the SQL Distribution control to create a Subscriber database and synchronize data with an anonymous SQL Server Subscriber over the Internet. The Distributor and Publisher are reached using an IP address.

In this example, no distributor properties are set, so the Publisher is also used as the Distributor. Replace the fields in italic with appropriate values. The port numbers shown are the numbers that are typically used, but they may also need to be changed.'SQLDistribution control declaration.

Private mobjDistr    As SQLDISTXLib.SQLDistribution
. . .

Set mobjDistr = New SQLDISTXLib.SQLDistribution

With mobjDistr
    'Set up the Publisher.
    .Publisher = "PublisherServer"
    .PublisherDatabase = "PublisherDatabase"
    .Publication = "PublicationName"
    .PublisherSecurityMode = DB_AUTHENTICATION
    .PublisherLogin = "PublisherUserID"
    .PublisherPassword = "PublisherPassword"
    .PublisherAddress = "157.56.17.27,1433"
    .PublisherNetwork = TCPIP_SOCKETS

    'Set up the Subscriber.
    .Subscriber = "SubscriberServer"
    .SubscriberDatabase = "SubscriberDatabase"
    .SubscriberDatasourceType = SQL_SERVER
    .SubscriberSecurityMode = NT_AUTHENTICATION

    'Set up the Subscription.
    .SubscriptionType = ANONYMOUS
    .SynchronizationType = AUTOMATIC

    'Synchronize subscription.
    .Initialize
    .Run
    .Terminate
End With

When using a merge publication, the same example can be used for programming anonymous subscriptions with the SQL Merge control. Replace references to the SQLDistribution object with references to the SQLMerge object.

Creating Pull Subscriptions to an ODBC Data Source Sample

The SQL Distribution control can be programmed to synchronize Publishers with ODBC data sources. This feature is one method by which applications can synchronize subscriptions to heterogeneous data sources.

Before using the SQL Distribution control, an ODBC data source name (DSN) must be created at the client computer on which the application hosting the SQL Distribution control is running. If a pull subscription is to be created, the ODBC DSN must also be created at the Distributor if it is a different computer from the client. The DSN at the Distributor must be configured as a Subscriber (for example, by using the sp_addsubscriber stored procedure).

This code segment demonstrates how a Visual Basic program configures the SQL Distribution control to create a pull subscription and synchronize data with an ODBC Subscriber. If an anonymous subscription is used, it is not necessary to call AddSubscription or to create the DSN at the Distributor. Replace the fields in italic with appropriate values.

'SQLDistribution object declaration.
Private mobjDistr    As SQLDISTXLib.SQLDistribution
. . .

'Configure the control for an ODBC subscription.
Set mobjDistr = New SQLDISTXLib.SQLDistribution

With mobjDistr
    'Set up the Publisher.
    .Publisher = "PublisherServer"
    .PublisherDatabase = "PublisherDatabase"
    .Publication = "PublicationName"
    .PublisherSecurityMode = NT_AUTHENTICATION

    'Set up the Distributor.
    .Distributor = "DistributorServer"
    .DistributorSecurityMode = NT_AUTHENTICATION

    'Set up the Subscriber.
    .Subscriber = "The_ODBC_DSN"
    .SubscriberDatasourceType = ODBC_DSN
    .SubscriberSecurityMode = DB_AUTHENTICATION

    'Subscriber login information needed only if the
    ' ODBC DSN does not contain login information.
    .SubscriberLogin = "SubscriberUserID"
    .SubscriberPassword = "SubscriberPassword"

    'Set up the subscription.
    .SubscriptionName = "SubscriptionName"
    .SubscriptionType = PULL
    .SynchronizationType = AUTOMATIC

    'Create and synchronize the subscription.
    .AddSubscription EXISTING_DATABASE, NONE
    .Initialize
    .Run
    .Terminate
End With

Creating Pull Transactional Subscriptions to an OLE DB Data Source Sample

You can program the SQL Distribution control to synchronize data with OLE DB data sources. This feature is one method by which applications can synchronize subscriptions to heterogeneous data sources.

Before running the control, a linked server to the OLE DB database must be created at the Distributor. This can be done in SQL Server Enterprise Manager or by using the sp_addlinkedserver stored procedure. A heterogeneous Subscriber using the linked server must be created at the Publisher (for example, by using the sp_addsubscriber stored procedure).

SQL Server 2000 does not support anonymous subscriptions using OLEDB_DATASOURCE with the SubscriberDatasourceType property. You can create OLE DB anonymous subscriptions to instances of SQL Server by setting SubscriberDatasourceType to SQL_SERVER.

This code segment demonstrates how a Visual Basic program can configure the SQL Distribution control to create a pull subscription and synchronize data with an OLE DB Subscriber.Replace the fields in italic with appropriate values.

'Declare the SQLDistribution object.
Private mobjDistr    As SQLDISTXLib.SQLDistribution
. . .

'Configure the control for an OLE DB subscription.
Set mobjDistr = New SQLDISTXLib.SQLDistribution

With mobjDistr
    'Set up the Publisher.
    .Publisher = "PublisherServer"
    .PublisherDatabase = "PublisherDatabase"
    .Publication = "PublicationName"
    .PublisherSecurityMode = NT_AUTHENTICATION

    'Set up the Subscriber.
    .Subscriber = "LinkedServerName"
    .SubscriberDatasourceType = OLEDB_DATASOURCE
    .SubscriberSecurityMode = DB_AUTHENTICATION

    'Subscriber login information is needed only if the OLE DB
    ' linked server definition does not include login information.
    .SubscriberLogin = "SubscriberUserID"
    .SubscriberPassword = "SubscriberPassword"

    'Set up the subscription.
    .SubscriptionName = "SubscriptionName"
    .SubscriptionType = PULL
    .SynchronizationType = AUTOMATIC

    'Create and synchronize the subscription.
    .AddSubscription EXISTING_DATABASE, NONE
    .Initialize
    .Run
    .Terminate
End With

See Also

Replication and Heterogeneous Data Sources

sp_addlinkedserver

sp_addsubscriber

SubscriberDatasourceType Property