Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Answered: Replication Fails on Publisher: Can Not Execute 'sp_replcmds'

    I took a full COPY-ONLY back from a SQL Server box: SERVER-A and then copied the .bak over to SERVER-B. I created the same SQL logins on both machines to manage replication and they have the same permissions on SERVER-A like they do on SERVER-B.

    I did the RESTORE DATABASE option and pointed the restore to the .bak file I copied over. I even selected the properties of the database and saw the owner was set accordingly (not the same DB owner as SERVER-A) but the files section didn't have an owner set which I then configured.

    I create the publication from the SERVER-A who will act as his own distributer and push records out to SERVER-B as the subscriber. After everything is said and done, I see an error inside Replication Monitor which tells me:

    "Error messages:

    The process could not execute 'sp_replcmds' on 'SERVER-A'."

    I have checked everything I know. There is a share path for the repldata/unc folder on the distributer/publisher and I don't see anything making it's way to the subscriber end.

  2. Best Answer
    Posted by Pat Phelan

    "Based on the error message, the replication task is running and it can't execute the sp_replcmds stored procedure which is the core of the beast. I'm 95% sure that this is an installation problem of the replication components.

    Are the SQL Agent Jobs present on the Publisher and the Distributor. They both ought to be Server-A based on your description.

    What version(s) of SQL Server are you running on Server-A and Server-B? At least until you get replication debugged, I recommend that they ought to be precisely the same build.

    Microsoft is pretty conservative in how they implement Replication. It can be a challenge to set it up the first few times you try to do it, but once you get the hang of Replication it is pretty clean and efficient.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on the error message, the replication task is running and it can't execute the sp_replcmds stored procedure which is the core of the beast. I'm 95% sure that this is an installation problem of the replication components.

    Are the SQL Agent Jobs present on the Publisher and the Distributor. They both ought to be Server-A based on your description.

    What version(s) of SQL Server are you running on Server-A and Server-B? At least until you get replication debugged, I recommend that they ought to be precisely the same build.

    Microsoft is pretty conservative in how they implement Replication. It can be a challenge to set it up the first few times you try to do it, but once you get the hang of Replication it is pretty clean and efficient.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    Based on the error message, the replication task is running and it can't execute the sp_replcmds stored procedure which is the core of the beast. I'm 95% sure that this is an installation problem of the replication components.

    Are the SQL Agent Jobs present on the Publisher and the Distributor. They both ought to be Server-A based on your description.

    What version(s) of SQL Server are you running on Server-A and Server-B? At least until you get replication debugged, I recommend that they ought to be precisely the same build.
    They're both the same version when I do SELECT @@VERSION. SEVER-A/B are both SQL Server 2008 R2 Std. The SERVER-A box has no other DB's being replicated from it so I'm pretty unsure what to blame. I was able to generate the snapshot of the publication but the SQL Log Agent fails to run on SERVER-A. Could this be an issue w/ SQL Server Agent? I verified the agent is "started" in the SQL Server Agent of Services. I don't see the "little green bubble in the SQL Server Agent when I refresh my view in Object Explorer but is appears to be running or started according to Windows services.

  5. #4
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    I checked today for clarification and here is the versions between the two instances:

    SERVER-A:
    Code:
    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)   Apr 22 2011 19:23:43   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
    SERVER-B:
    Code:
    Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)   Aug 19 2014 12:21:34   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
    I've attached the screenshot of my Replication Monitor showing errors for Log Reader Agent and the details are also attached (but not inline displayed)
    Click image for larger version. 

Name:	Screen Shot 2016-03-04 at 1.20.33 PM.png 
Views:	3 
Size:	31.0 KB 
ID:	16798
    Attached Thumbnails Attached Thumbnails Screen Shot 2016-03-04 at 12.48.50 PM.png  
    Last edited by CacheDrive; 03-04-16 at 13:22.

  6. #5
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Still getting the same error and so far Google hasn't produced any solutions that have fixed this. I'm confident the issue fault is with SERVER-A but how to narrow down why the Log Reader Agent is failing after it successfully generates it's subscription snapshot to push out. Is there a place in SQL Server that could point me in the right direction because right now I'm completely clueless how to troubleshoot this and sadly my employer doesn't care we're using 2008 R2 no matter how many times I tell him we're safe to migrate to 2014.

  7. #6
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Issue has been resolved. I sadly failed to verify the owner of the replicated database has role access or grant access to use sp_replcmds SPROC. Changed the owner of the database not to be the person who restored it and verified new owner had proper access and replication was flowing like wine.

    Thanks all!

  8. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmm...I thought you mentioned in your initial post that you changed the owner after restore...

    I even selected the properties of the database and saw the owner was set accordingly (not the same DB owner as SERVER-A) but the files section didn't have an owner set which I then configured.
    Also, are you deliberately keeping the versions of the two boxes different?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #8
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    I restored the db and set ownership on SERVER-B (the subscriber). I simply published an existing database on SERVER-A as noted above. I was not sure what randomly changing ownership of a production DB would cause from an application perspective but my manager advised me it was fine to go ahead and do so which resolved the issue.

    And we plan to upgrade the boxes to be synchronized in upcoming maintenance windows.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •