So far it can subscribe and unsubscribe just fine but when it comes to the syncing process it gets a little weird, here's why.
- If I run the SQL GUI Sync tool, it sinks fine. So that setup is working.
- When I run the C# App it blows up witht the error: "Process cannot connect to Distributor"
- Here's the weird part: When I set a breakpoint on my custom method SyncData (this method sets up the connection info and properties and then calls Syncronize( ) ) and step through the code when it gets to Syncronize( ) it runs perfectly fine, no execeptions.
I thought that maybe the Syncronize( ) method was being called too fast before the connection properties got to fully setup and connect, so I added a Thread.Sleep( ) method for 10 secs after each connection call and just before calling Syncronize( ). It still didn't work. My class is a static class by design, however, I changed it into an ordinary class and then placed the connection info in the constructor hoping it would do it's connection when the object gets initialized, that didn't work either.
If someone could please help me out with this I would greatly appreciate it.
Here's my code:
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Replication;
using Microsoft.SqlServer.Replication.BusinessLogicSupport;
using Microsoft.SqlServer.Management.Common;
using System.Windows.Forms;
using System.Threading;
namespace Emds.Briefcase.BriefcaseSubscriber.BLL
{
public class SyncDataClass
{
#region Members
private static string m_statusMessage = string.Empty;
private static byte m_percentComplete;
public delegate void StatusTextChangeHandler(int percent, string status);
public static event StatusTextChangeHandler OnStatusChange;
#endregion Members
#region Methods
public static string SyncData()
{
//Delay();
// Define the server, publication, and database names.
string publicationName = "Chart";
string publisherName = @."JSMITH\SQL2005";
string subscriberName = @."JDOE\SQL2005";
string subscriptionDbName = "DataSubscriber";
string publicationDbName = "DataPublisher";
string message = string.Empty;
// Create a connection to the Subscriber.
ServerConnection conn = new ServerConnection(subscriberName);
MergePullSubscription subscription;
try
{
// Connect to the Subscriber.
conn.Connect();
// Delay();
// Define the pull subscription.
subscription = new MergePullSubscription();
subscription.ConnectionContext = conn;
//Delay();
subscription.DistributorSecurity.WindowsAuthentication = false;
subscription.DistributorSecurity.SqlStandardLogin = "sa";
subscription.DistributorSecurity.SqlStandardPassword = "russell";
// Delay();
subscription.PublisherName = publisherName;
subscription.PublicationDBName = publicationDbName;
subscription.PublicationName = publicationName;
subscription.PublisherSecurity.SecurityMode = ReplicationSecurityMode.SqlStandard;
subscription.PublisherSecurity.SqlStandardLogin = "sa";
subscription.PublisherSecurity.SqlStandardPassword = "russell";
// Delay();
subscription.DatabaseName = subscriptionDbName;
subscription.SubscriberSecurity.WindowsAuthentication = false;
subscription.SubscriberSecurity.SqlStandardLogin = "sa";
subscription.SubscriberSecurity.SqlStandardPassword = "russell";
// Delay();
// If the pull subscription exists, then start the synchronization.
if (subscription.LoadProperties())
{
// Check that we have enough metadata to start the agent.
if (subscription.PublisherSecurity != null || subscription.DistributorSecurity != null)
{
// Synchronously start the Merge Agent for the subscription.
subscription.SynchronizationAgent.Status += new AgentCore.StatusEventHandler(SynchronizationAgent_Status);
// Delay();
subscription.SynchronizationAgent.Synchronize();
message = "Data Syncronization is a success!";
}
else
{
throw new ApplicationException("There is insufficent metadata to " +
"synchronize the subscription. Recreate the subscription with " +
"the agent job or supply the required agent properties at run time.");
}
}
else
{
// Do something here if the pull subscription does not exist.
throw new ApplicationException(String.Format(
"A subscription to '{0}' does not exist on {1}",
publicationName, subscriberName));
}
}
catch (Exception ex)
{
// Implement appropriate error handling here.
throw new ApplicationException("The subscription could not be " +
"synchronized. Verify that the subscription has " +
"been defined correctly.", ex);
}
finally
{
conn.Disconnect();
}
return message;
}
static void SynchronizationAgent_Status(object sender, StatusEventArgs e)
{
m_percentComplete = e.PercentCompleted;
m_statusMessage = e.Message;
//Fire custom event
if (OnStatusChange != null)
{
OnStatusChange(m_percentComplete, m_statusMessage);
}
}
#endregion Methods
}
}Moved to SQL Server Replication forum.|||
You may want to try setting the distributor security on the Agent object directly before calling subscription.SynchronizationAgent.Synchronize() like so:
subscription.SynchronizationAgent.DistributorSecurityMode = SecurityMode.Standard
subscription.SynchronizationAgent.DistributorLogin = "sa"
subscription.SynchronizationAgent.DistributorPassword = "russell"
Hope that helps,
-Raymond
|||Raymond,Thanks for your response. If you look at my code listing I think you will see that I am already doing that, unless I missunderstood what you were suggesting.
Thanks again,
Chris|||
There is actually a subtle difference: I believe your code is setting the security info on the subscription object, whereas my code is setting it on the SynchronizationAgent object.
-Raymond
|||Ok, I will give it a shot. Thank you again!!|||That was it!!! Thank you very, very much!!!!!Take care!!!
