0

I got MS SQL 2008 running on Windows Server 2008 R2.

Everything is fresh install running on a VM.

I can make a Snapshot Publication without problems. But I am unable to make a Transactional Publication, I get errors even before I try to configure a subscriber. Here's how I did things:

  1. I configure Distribution. (DEFAULT SETTINGS)

  2. I start the New Publication Wizard.

    • Transactional publication
    • I select "Creat a snapshot immediately"
    • I configure the security settings for the Snapshot Agent and Log Reader Agent (Using the Windows account Domain Admin)
    • The Publication is created succesfully, no errors.
    • The "View Snapshot Agent Status" report no errors - 100% success.
    • Going to the Replication Monitor, that's where the errors begin - Error with the Log Reader Agent - "The process could not execute 'sp_replcmds' on 'SERVERNAME'..." [see picture]

What am I doing wrong? Or what am I missing? enter image description here

Improve this question

2 Answers 2

Reset to default
1

From BOL:

Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_replcmds.

So, who is the logreader agent running as? That is, how is it connecting to your publisher database? If that user doesn't have db_owner or is a sysadmin, you'll get a failure.

Improve this answer
7
  • Like I said in the original post: I configure the security settings for the Snapshot Agent and Log Reader Agent (Using the Windows account Domain Admin) - If that doesn't answer the question, can you tell me how to check who is the logreader agent running as? Commented Jul 28, 2011 at 19:48
  • I took that to mean "as a domain admin, I configured the security". One other thing to check is who the dbo user maps to in your database. Since I see in one of your other replies that this is a restore from somewhere else, it's possible that it doesn't map to a valid login. If that's the case, fix that and see if your problem persists. Commented Jul 28, 2011 at 19:57
  • No idea how.. mind holding my hand? :p But seriously I'm a SQL noob, no idea how to check who the dbo user maps to in the database :( Commented Jul 28, 2011 at 20:02
  • Is that what you're looking for? img543.imageshack.us/img543/1971/85318219.jpg Commented Jul 28, 2011 at 20:10
  • 1
    OK i'm definitely getting somewhere, I tried again with a different database and it worked! And I compared the dbo properties between the 2 databases, and I noticed that the database with the problem is missing a "Login name" (as seen blank in the screenshot above) so now I just need to figure out how to fill that grayed out field Commented Jul 28, 2011 at 20:17
1

If the subscriber database has been restored from a backup to a different instance, check if the domain admin user is orphaned.

use [subscriber] go exec sp_change_users_login 'report' go

Improve this answer
1
  • The database I'm using is restored from a backup that comes from a SQL 2005, is that the problem? Commented Jul 28, 2011 at 19:46

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.