SQL Server 2008 Merge Replication Expired - Cannot Reinitialize

I'm running a SQL Server 2008 R2 server with a publication set up for multiple clients running 2008 SQL Express R2.  One of the client subscriptions expired.

What is the easiest way to get this Merge replication going again?  

 I've attempted re-initializing the subscription which didn't help.  

I've deleted the subscription which removed itself from the server and client, then re-created which didn't work.  The subscription re-creates, but on the server side simply says "Not Initialized" when looking at the Replication Monitor.  The odd part is on the client it says successfully synchronized, but it never goes.  

I've completely deleted the database on the client. Then re-created the subscription and re-created the database.  No matter what I do, the server never leaves "Not Initialized" and when looking at the properties of the re-created subscription it says "Inactive".

Any advice / assistance would be greatly appreciated.
Info TechIT DepartmentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
what is the status of merge agent job any error there.
Info TechIT DepartmentAuthor Commented:
It's an On-Demand merge subscription so there is no merge agent job.  When I look at Replication Monitor, I have 5 clients that are all good because those didn't expire.  The one problem client says "Uninitialized Subscription".

No matter what I've tried I cannot get this client to merge replicate again.
Deepak ChauhanSQL Server DBACommented:
Repl-Merge catagory job should be there, only schedule will be disable if it is On-Demand. Please expand the sql server agent job on publisher and look for like mention below and start this job if it is stopped.

This is SQL agent job naming convention.
<Publisher server name> - <Publication name> - <Subscriber server name> - <id like 123>
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Info TechIT DepartmentAuthor Commented:
Hmmm, I checked each job's properties and none of them are a REPL-Merge Category job.  I've attached an image of the jobs that are currently listed.  Is this why the expired client is unable to start up again?  

My only concern is the other clients all work fine, so I don't want to do something that causes them to break.  As I stated, the users run the synchronization on the client to sync up with the database.  Nothing is done on the primary server side of things.

Agent Jobs
Deepak ChauhanSQL Server DBACommented:
Ok. I guess this is Publisher server snapshot and replication set up with pull subscription.

Now follow these step hope subscription will be sync. But as mentioned by you this is ondemand so it will be stop after sync.

1 double click on the unintialised subscription entry in the replication monitor.
2 click on the action button in new window opened after double click
3 click on start synchronise.
4 check the status.

As you said clients are SQL express may i know how other subscriptions are syncing.

Are you using Windows sync manager.
Info TechIT DepartmentAuthor Commented:
You are correct.  This is a snapshot based replication set up with a pull subscription.

The clients are using SQL Express and yes, we are using Windows Sync Manager to syncronize the laptops back to the server each night.  There are a total of 6 laptops.  5 of them work perfect.  However, this one particular laptop didn't check in within the 14 day expiration period.  

Now - when I open Replication Monitor - Click Action - Start Sync is not an option.  I've attached another screen shot here.  Repl Monitor
Deepak ChauhanSQL Server DBACommented:
Oh Sorry Your case is pull subscription and the above steps for Push subscription.

You have to sync with this command as express edition does not have SQL agent. So you can sync it using commandline or Windows sync manager.

Comand line code replace the <?> with correct parameter.

open the command prompt and type

Cd "C:\Program Files\Microsoft SQL Server\100\COM\"

then run the command.

REPLMERG.EXE" -Publication <?> -Publisher <?> -Subscriber <?> -Distributor <?> -PublisherDB <?>  -SubscriberDB <?> -PublisherSecurityMode 1 -outputVerboseLevel 2  -SubscriberSecurityMode 1  -SubscriptionType 1 -DistributorSecurityMode 1 -Validate 3  -ParallelUploadDownload 1

if you want to sync it with Windows Sync mgr , you have to enable the subscripton property at subscriber server.
Info TechIT DepartmentAuthor Commented:
How do I enable the Subscription Property at the Subscriber Server? The odd part is when I choose to Sync at the client using Windows Sync Manager - it says Successful.  But it isn't syncing anything.  

When I tried the command line above, it says "input string too long".

See attached screen shot. If I could just get this to say "Active" I think the sync would be successful again.

  Agent Properties
Deepak ChauhanSQL Server DBACommented:
Seems you are checking on publisher server. Please go to subscriber server and expand the local subscription folder. There you can check the subscription property (in case of pull) and set the "Use Window sync manager" to Enable.

The command syntex which i shared usually i use it in my environment. It should not through the error.

This is the syntex as per your envoronment

REPLMERG.EXE" -Publication EMSSQL -Publisher DCSERVER\MSSQLSerVER2008 -Subscriber SERVICELT-1\MSSQLSERVER2008 -Distributor DCSERVER\MSSQLSerVER2008 -PublisherDB EMSSQL  -SubscriberDB EMSSQL -PublisherSecurityMode 1 -outputVerboseLevel 2  -SubscriberSecurityMode 1  -SubscriptionType 1 -DistributorSecurityMode 1 -Validate 3  -ParallelUploadDownload 1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Info TechIT DepartmentAuthor Commented:
That script did the trick!  Thanks!!!
Deepak ChauhanSQL Server DBACommented:
Great! Good to know

Thanks for giving points
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.