DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 319649 - Last Review: December 26, 2003 - Revision: 5.6

This article was previously published under Q319649

On This Page

SUMMARY

This step-by-step article demonstrates how to program the SQL Snapshot and SQL Distribution controls by using Microsoft Visual C# .NET.

You can use the SQL Server 2000 Replication ActiveX controls to embed replication functionality in custom applications.

Prerequisites

Before you insert the sample code in a Microsoft Visual C# .NET project, follow these steps:
  1. Make sure the publisher, distributor, and subscriber are configured properly and that they are all running SQL Server 2000.
  2. Create a snapshot publication and name it SampleSnapshotPublication. The publishing database is Northwind.
  3. After you create the publication, look at the publication properties of the publication.
  4. Click the Subscription Options tab.
  5. Click to select the Use a Distribution Agent that is independent of other publications from this database and Snapshot files are always available to initialize new subscriptions immediately check boxes.
  6. Click OK to close the Publication Properties dialog box.

Steps to Use Visual C# .NET to Program the SQL Snapshot and SQL Distribution Control

The sample code provided in this section generates a snapshot by using the SQL Snapshot Control. The SQL Distribution Control then creates a subscription database named Northwind_replica. Make sure that the Northwind_replica database does not exist before you run the sample code. The SQL Distribution Control creates a pull subscription in the Northwind_replica database and applies the snapshot at the subscriber.

Inside the Visual C# .NET project, add references to both:
  • Microsoft SQL Snapshot Control 8.0.
  • Microsoft SQL Distribution Control 8.0 COM objects.
Next, insert this code in the C# class file for your project:
using System;
using System.Runtime.InteropServices;
using SQLINITXLib;
using SQLDISTXLib;

namespace SqlRepl
{
	//This class demonstrates use of the SQL Server Snapshot and Distribution Agent replication controls. 
	class SnapshotApp
	{
		/*	Prior to running this code, you must set up replication as follows:
		// 	
		//		Create a snapshot publication named "SampleSnapshotPublication" and configure it to allow pull
		//		subscriptions. 
		//		
		//		You must also set the following subscription options 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 first generates the snapshot by using the SQLSnapshotClass object. The subscription database
		//	and pull subscription are then created through code. Next, the snapshot is applied at the subscriber by using
		//	the SQLDistributionClass object. 
		// 
		//	Set references to the following COM DLL files:
		//		-Microsoft SQL Snapshot Control 8.0
		//		-Microsoft SQL Distribution Control 8.0		 
		*/ 

		[STAThread]
		static void Main(string[] args)
		{
			string strPublisher, strDistributor, strSubscriber, strPublisherDatabase, strSubscriberDatabase, strPublication;
			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";
			strSubscriberDatabase = "Northwind_replica";

			SQLSnapshotClass 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 distributor.
			oSnap.Distributor = strDistributor;
			oSnap.DistributorSecurityMode = SQLINITXLib.SECURITY_TYPE.NT_AUTHENTICATION;
			
			//Generate the snapshot.
			try
			{
				Console.WriteLine("Generating snapshot...");
				oSnap.Initialize();
				oSnap.Run();
				oSnap.Terminate();
				Console.WriteLine("Snapshot generated.");
			}
			catch (Exception e)
			{
				Console.WriteLine(e.StackTrace);
				Console.WriteLine(e.Message);
			}
			
			//Now that the snapshot is created, use the Distribution Agent control to apply the snapshot at the subscriber. 

			SQLDistributionClass oDist = new SQLDistributionClass();
			
			//Set up the publisher.
			oDist.Publisher = strPublisher;
			oDist.PublisherSecurityMode = SQLDISTXLib.SECURITY_TYPE.NT_AUTHENTICATION;
			oDist.PublisherDatabase = strPublisherDatabase;
			oDist.Publication = strPublication;

			//Set up the distributor.
			oDist.Distributor = strDistributor;
			oDist.DistributorSecurityMode = SQLDISTXLib.SECURITY_TYPE.NT_AUTHENTICATION;
			
			//Set up the subscriber.
			oDist.Subscriber = strSubscriber;
			oDist.SubscriberDatabase = strSubscriberDatabase;
			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.
			try
			{
				Console.WriteLine("Running distribution agent...");
				oDist.Initialize();
				oDist.Run();
				oDist.Terminate();
				Console.WriteLine("Distribution agent completed.");
			}
			catch (Exception e)
			{
				Console.WriteLine(e.StackTrace);
				Console.WriteLine(e.Message);
			}

			
		
		}
	}
}
				


REFERENCES

For a Microsoft Visual Basic .NET version of this article, see the following article in the Microsoft Knowledge Base:
319648  (http://support.microsoft.com/kb/319648/EN-US/ ) HOW TO: Program the SQL Snapshot and SQL Distribution Control by Using Visual Basic .NET


For samples written in previous versions of Microsoft Visual Basic and Microsoft Visual C++, see the following Web addresses:

Using SQL Merge and SQL Distribution Controls in a Custom Visual Basic Application (http://msdn.microsoft.com/en-us/library/aa827347.aspx)

Using SQL Merge and SQL Distribution Controls in a Custom Visual C++ Application (http://msdn.microsoft.com/en-us/library/aa827334.aspx)


APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft Visual C# .NET 2002 Standard Edition
Keywords: 
kbhowtomaster KB319649
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support