up vote 0 down vote favorite
share [g+] share [fb]

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

link|improve this question
74% accept rate
feedback

2 Answers

up vote 1 down vote accepted

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.

link|improve this answer
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? – ToastMan Jul 28 '11 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. – Ben Thul Jul 28 '11 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 :( – ToastMan Jul 28 '11 at 20:02
Is that what you're looking for? img543.imageshack.us/img543/1971/85318219.jpg – ToastMan Jul 28 '11 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 – ToastMan Jul 28 '11 at 20:17
show 2 more comments
feedback
up vote 1 down vote

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

link|improve this answer
The database I'm using is restored from a backup that comes from a SQL 2005, is that the problem? – ToastMan Jul 28 '11 at 19:46
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.