How to program the SQL Snapshot and SQL Distribution control by using Visual Basic .NET

This article was previously published under Q319648
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
You can use the SQL Server 2000 Replication ActiveX controls to embed replication functionality inside custom applications. This article describes how to program the SQL Snapshot and SQL Distribution controls by using Microsoft Visual Basic .NET.

back to the top

Before you insert the sample code

Before you insert the sample code in a Visual Basic .NET project, follow these steps:
  1. Make sure to properly configure the publisher, the distributor, and the subscriber and that they are all SQL Server 2000.
  2. Create a snapshot publication called "SampleSnapshotPublication"; the publishing database is Northwind.
  3. After you create the publication, view its properties.
  4. On the Subscription Options tab, click the following options:
    • Use a Distribution Agent that is independent of other publications from this database
    • Snapshot files are always available to initialize new subscriptions immediately
  5. Click OK to close the Publication Properties dialog box.
back to the top

Insert the sample code

Use the following sample code to generate a snapshot by using the SQL Snapshot Control. The SQL Distribution Control will create the Northwind_replica subscription database (make sure that it does not exist before you execute the sample code). After the creation of the subscription database, the SQL Distribution Control creates a pull subscription in the Northwind_replica database, and then the snapshot is applied at the subscriber.

Inside the Visual Basic .NET project, add references to the Microsoft SQL Snapshot Control 8.0 and Microsoft SQL Distribution Control 8.0 COM objects, and then insert the following code:
Imports SQLDISTXLibImports SQLINITXLib'This class demonstrates using the SQL Server Snapshot and Distribution Agent replication controls. Module SnapshotApp    Sub Main()        '	Prior to running this code, replication needs to be setup as follows:        ' 	        '		Create a snapshot publication called "SampleSnapshotPublication" and configure it to allow pull        '		subscriptions.         '		        '		The following subscription options must also be set for the publication:        '			-Use a distribution agent that is independent of other publications from this database.         '			-Snapshot files are always available to initialize new subscriptions.         '	        '	This code will first generate the snapshot using the SQLSnapshotClass object. Then the subscription database        '	and pull subscription will be created through code. Then the snapshot will be applied at the subscriber using        '	the SQLDistributionClass object.         '        '	You will also need to set references to the following COM dll's:        '		-Microsoft SQL Snapshot Control 8.0        '		-Microsoft SQL Distribution Control 8.0		         '        Dim strPublisher As String        Dim strDistributor As String        Dim strSubscriber As String        Dim strPublisherDatabase As String        Dim subscriberDatabase As String        Dim strPublication As String        Dim oSnap As SQLSnapshotClass        Dim oDist As SQLDistributionClass        strPublisher = "PUBLISHER"   'change to the name of your publisher        strDistributor = "DISTRIBUTOR" 'change to the name of your distributor        strSubscriber = "SUBSCRIBER"  'change to the name of your subscriber        strPublication = "SampleSnapshotPublication"        strPublisherDatabase = "Northwind"        subscriberDatabase = "Northwind_replica"        oSnap = New SQLSnapshotClass()        'Set up the Publisher.        oSnap.Publisher = strPublisher        oSnap.PublisherSecurityMode = SQLINITXLib.SECURITY_TYPE.NT_AUTHENTICATION        oSnap.PublisherDatabase = strPublisherDatabase        oSnap.Publication = strPublication        'Set up the strDistributor.        oSnap.Distributor = strDistributor        oSnap.DistributorSecurityMode = SQLINITXLib.SECURITY_TYPE.NT_AUTHENTICATION        'Generate the snapshot.        Console.WriteLine("Generating snapshot...")        oSnap.Initialize()        oSnap.Run()        oSnap.Terminate()        Console.WriteLine("Snapshot generated.")        'Now that the snapshot is created, use the distribution agent control to apply the snapshot at the strSubscriber.         oDist = New SQLDistributionClass()        'Set up the strPublisher.        oDist.Publisher = strPublisher        oDist.PublisherSecurityMode = SQLDISTXLib.SECURITY_TYPE.NT_AUTHENTICATION        oDist.PublisherDatabase = strPublisherDatabase        oDist.Publication = strPublication        'Set up the strDistributor.        oDist.Distributor = strDistributor        oDist.DistributorSecurityMode = SQLDISTXLib.SECURITY_TYPE.NT_AUTHENTICATION        'Set up the strSubscriber.        oDist.Subscriber = strSubscriber        oDist.SubscriberDatabase = subscriberDatabase        oDist.SubscriberSecurityMode = SQLDISTXLib.SECURITY_TYPE.NT_AUTHENTICATION        'Set up the subscription.        oDist.SubscriptionType = SQLDISTXLib.SUBSCRIPTION_TYPE.PULL        oDist.SynchronizationType = SQLDISTXLib.SYNCHRONIZATION_TYPE.AUTOMATIC        oDist.SubscriptionName = "PullSnapshotSubscription"        'Create the database and subscription.        oDist.AddSubscription(SQLDISTXLib.DBADDOPTION.CREATE_DATABASE, SQLDISTXLib.SUBSCRIPTION_HOST.NONE)        'Synchronize the subscription.        Console.WriteLine("Running distribution agent...")        oDist.Initialize()        oDist.Run()        oDist.Terminate()        Console.WriteLine("Distribution agent completed.")    End SubEnd Module				
back to the top
REFERENCES
For a Microsoft Visual C# .NET version of this article, see 319649.

For samples that are written in earlier versions of Microsoft Visual Basic and Microsoft Visual C++, see the following SQL Server Books Online topics:back to the top
Properties

Article ID: 319648 - Last Review: 01/17/2015 05:27:20 - Revision: 3.7

  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • kbnosurvey kbarchive kbhowtomaster KB319648
Feedback