Chaz Blogs

RMO Pull Subsciption and SQL Server Express 2005 Hell!

Update:  I am putting the update at the top since this entry is soooo long.

Well, aparently there are two things that I needed to do to solve my problems.
1. I needed to realize that the SQL Server 2000 was my Distributor even though I wanted my remote databases to handle the distribution info.  I guess because I was using SQL Express 2005 this wasn't possible.

2. Security, security, security.  One of the most troubling error messages I recieved after resolving the distributor issue was that when trying to replicate for the first time, my SQL Server 2005 couldn't find the snapshot file.  I moved the snapshot file to a custom folder.  That still didn't help.  Only after I mapped a drive from my local PC to the snapshot folder did everything cleared up.

I am not 100% sure I have everything set up as it should be, but I am successfully replicating a Pull subscription on demand now.

I suppose I will never know.

--chaz

Curse you SQL Server Express 2005!  I don't usually post problems until I have solutions, but I am at the end of my rope.  There seems to be plenty of info on Replicaton Management Object (RMO).  There seems to be plenty of hits when searching for SQL Express and RMO.  But darn it there is just no specific code example with step by step instructions for RMO & SQL Server Express 2005.  Hey let's throw in just one more curve, there is little out there to cover RMO, SQL Express 2005 with a Snapshot Pull subscription from Enterprise SQL Server 2000!

I've used SQL Server 2005 Books OnLine article "How to: Synchronize a Pull Subscription (RMO Programming)" as my beacon in the fog...  and yet every time I think I'm in for some clear sailing I run aground.

My latest error message is "The process could not connect to Distributor <ServerName>."

I have 2 methods.  The first method I execute is createPullSubscription() does what it says.  And when I check the Publisher, sure enough my subscription is registered.  Then I execute the executePull() method.  And boom I get the above error on the line;

subscription.SynchronizationAgent.Synchronize();

So here is my code:

public static void createPullSubscription()

// Define the Publisher, publication, and databases.

ServerConnection subscriberConn = new ServerConnection(subscriberName,"XXXUID","XXXPwd");

ServerConnection publisherConn = new ServerConnection(publisherName, "zzzUID", "zzzPwd");

// Create the objects that we need.

TransPublication publication;

TransPullSubscription subscription;

try

// Connect to the Publisher and Subscriber.

// Ensure that the publication exists and that

// it supports pull subscriptions.

publication = new TransPublication();

if (publication.IsExistingObject)

if ((publication.Attributes & PublicationAttributes.AllowPull) == 0)

publication.Attributes |= PublicationAttributes.AllowPull;

// Define the pull subscription.

subscription = new TransPullSubscription();

// Specify the Windows login credentials for the Distribution Agent job.

subscription.SynchronizationAgentProcessSecurity.Login = @"xyzUID";

subscription.SynchronizationAgentProcessSecurity.Password = "xyzPwd";

// Make sure that the agent job for the subscription is created.

subscription.CreateSyncAgentByDefault = true;

// By default, subscriptions to transactional publications are synchronized

// continuously, but in this case we only want to synchronize on demand.

subscription.AgentSchedule.FrequencyType = ScheduleFrequencyType.OnDemand;

subscription.Type = PublicationType.Snapshot;

// Create the pull subscription at the Subscriber.

//subscription.Create();

Boolean registered = false;

// Verify that the subscription is not already registered.

foreach (TransSubscription existing

in publication.EnumSubscriptions())

if (existing.SubscriberName == subscriberName

registered = true;

if (!registered)

// Register the subscription with the Publisher.

SubscriptionSyncType.Automatic,

TransSubscriberType.ReadOnly);

else

// Do something here if the publication does not exist.

throw new ApplicationException(String.Format(

"The publication '{0}' does not exist on {1}.",

catch (Exception ex)

// Implement the appropriate error handling here.

throw new ApplicationException(String.Format(

"The subscription to {0} could not be created.", publicationName), ex);

finally

public static void executePull()

// Define the Publisher, publication, and databases.

string publicationName = "XXXTerms";

string publisherName = "XXXXXX95034";

string subscriberName = @"XXXXXX\sqlexpress";

string subscriptionDbName = "Terms";

string publicationDbName = "XXXdb";

// Create a connection to the Subscriber.

ServerConnection conn = new ServerConnection(subscriberName, "XXXUID", "XXXpwd");

TransPullSubscription subscription;

try

// Connect to the Subscriber.

// Define the pull subscription.

subscription = new TransPullSubscription();

// 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)

// Synchronously start the Distribution Agent for the subscription.

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}",

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();

}

}

In the createPullSubscription method, I am bothered by the following two lines:

subscription.SynchronizationAgentProcessSecurity.Login = @"xyzUID";
subscription.SynchronizationAgentProcessSecurity.Password = "xyzPwd";

I don't think they are valid since this is a SQL Express instance.  There is no Agent.  However, when setting up the publication I had to say let the Distributor run at the Subscriber to allow for Pull subscriptions.  Hmmmm, is this also a problem?

I will not rest until I find an answer to this mystery.  It is probably user error ;-)  When I do find the answer I will post it for all the other lost soles who have unanswered questions about this on the boards...

--chaz

Print | posted on Thursday, February 08, 2007 9:24 PM

Copyright © Chip Lemmon

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski