Programming the SQL Distribution ActiveX Control

  • Article

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, and copy subscriptions. 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.
  • 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 (Replication ActiveX).

ms146879.security(en-US,SQL.90).gifSecurity Note:
Scripts can be the source of security vulnerabilities; they can invoke system functions without user knowledge or intervention and may contain security credentials in plain text. Review the script for security issues before use. The SQL Distribution control is marked as unsafe for scripting.

Example

Description

This code segment demonstrates how a Microsoft Visual Basic program configures the SQLDistribution Object (Replication ActiveX) object to synchronize a pull subscription.

In the example, Distributor properties are not set because the Publisher uses a local Distributor. The SubscriberDatabase Property (Replication ActiveX) must already exist when this code is executed. Replace the fields in italic with appropriate values.

Code

'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 = PULL
    .SynchronizationType = AUTOMATIC

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

Description

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 (Transact-SQL) 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. Replace the fields in italic with appropriate values.

ms146879.security(en-US,SQL.90).gifSecurity Note:
When possible, prompt users to enter their credentials at run time. Avoid hard-coding or storing credentials in a file. If credentials must be persisted between runs, encrypt them using the Win32 CryptoAPI functions. For more information, see this Microsoft Web site.

Code

'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

Description

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 Management Studio or by using the sp_addlinkedserver (Transact-SQL) stored procedure. A heterogeneous Subscriber using the linked server must be created at the Publisher (for example, by using the sp_addsubscription (Transact-SQL) stored procedure).

Microsoft SQL Server does not support anonymous subscriptions using OLEDB_DATASOURCE with the SubscriberDatasourceType Property (Replication ActiveX) property. You can create OLE DB anonymous subscriptions to instances of SQL Server by setting SubscriberDatasourceType Property (Replication ActiveX) 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.

ms146879.security(en-US,SQL.90).gifSecurity Note:
When possible, prompt users to enter their credentials at run time. Avoid hard-coding or storing credentials in a file. If credentials must be persisted between runs, encrypt them using the Win32 CryptoAPI functions. For more information, see this Microsoft Web site.

Code

'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

Concepts

Programming the SQL Merge ActiveX Control

Help and Information

Getting SQL Server 2005 Assistance