SQL Server Replication - Automatically mark items for re-initialization

I am using transactional replication.

I have 4 subscribers. My publisher and distributor are on the same node. When data is sent to the publisher, the data is automatically sent to the subscribers.

Question to be answered: I want to simulate the following manual action in transact-sQL:
  - Reinitializa all subscriotions
    with following options checked: use a new snapshot, generate the new snapshot now
-  Mark for re-initialization

There are times when my replication fails to some or all subscribers. This seems to be mostly due to network performance. I usually get the following error: The row was not found at the Subscriber when applying the replicated command.

When I execute the manual procedure indicated the error is eliminated and each subscribers gets updated.
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm affraid I didn't understand your question.
What the problem in simulating what you want?
adimitAuthor Commented:
From the object explorer in sql server 2005, go to replication. From there go to local publications. Right mouse click on a publication and select reinitialize all subscriptions. From the ensuing menu select the option for create a new snapshot and the check box for generate the new snapshot now. Then click on mark for re-initialization  I want to perform this processes using sql.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. Now I know what you want :)

Depends on what type of subscription (Push or Pull) you have to run the correspondent stored procedure

-- Execute at the Publisher to reinitialize the push subscription.
EXEC sp_reinitmergesubscription [subscriberServer], [subscriptionDB], [publication]

-- Execute at the Subscriber to reinitialize the pull subscription.
EXEC sp_reinitmergepullsubscription [PublicationServer], [publicationDB], [publication]

Then execute this in the publisher database to generate new snapshot:
EXEC sp_startpublication_snapshot 'publicationName'

This should be enough. Why do you need to mark no reinitialization again?

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

adimitAuthor Commented:
command that I ended up usig was sp_initsubscription. This command was executed at the distributor.
Vitor MontalvãoMSSQL Senior EngineerCommented:
The problem was solved?
adimitAuthor Commented:
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.