Thursday, May 3, 2012

error:process could not execute 'sp_replcmds' error on Publisher with Remote Distributor

Today the replication failed on our Publisher. We have a transactional replication running from Publisher to the  Subscriber and they  are in two differnt machines, both using  SQL 2008 R2. The exact error we got was
  1. Replication failed: process could not execute 'sp_replcmds' error on Publisher with Remote Distributor
  2. The database owner SID recorded in the master database differs from the database owner SID recorded in database 'databasename'
Tried  the following step with success.

 --STEP 1

exec sp_changedbowner [domain\user]

This made the [domain\user]  account the owner and mapped it to dbo 

--STEP 2

In SSMS find the replication and expand it
Expand the local publication folder
Select the publication and right click on it and seclet "View Log Reader Agent Status"
Stop the Agent and Restart the agent


Now all ther replications were back in sync.

I ran the follwoing query to see if they are back up running

select * from distribution..MSrepl_transactions order by entry_time