I am trying to setup automatic transactional replication. Basically I have some script files that are executed after each other. They set up a replication from one server to another and this works just fine. Only problem is, that it is never starts. If I look into the Snapshot-Agents status, I can see that it has never been run before. If I just click start in that dialogue, everything works as expected afterwards. But I don't want the user to have to Open the sql server studio and activate the snapshot agent manually.

What I already tried:

  • running the snapshot.exe with all params set. It seems to work fine, but afterwards I get an error in my replicationmonitor that something from a path could not be read, but there arent any settings for a path, so I don't know how to fix this.

  • exec msdb.dbo.sp_start_job .. here I tried some params but I have no clue what to put here. My best guess was @job_name='myreplication' but I just get an error that it cant be found.

What is the easiest way to automate this?


1 Answer 1


I believe you are looking for sp_startpublication_snapshot. You'll want to execute this at the Publisher on the publication database.

Regarding the error: from a path could not be read, you'll want to ensure the Snapshot Agent process account has sufficient permissions to generate the snapshot. The process account needs the following permissions per BOL:

  • At minimum, be a member of the db_owner fixed database role in the distribution database.
  • Have write permissions on the snapshot share.
  • The account that is used to connect to the Publisher must at minimum be a member of the db_owner fixed database role in the publication database.

This is covered in Replication Agent Security Model.

  • sounds like the command im looking for, gonna try it. @Error: The snapshot can be written, but the subscriber cant access it, which is wierd because as i read that the distributor should not be on the publisher, it instead is the same machine as the subscriber. The wierd thing is, that if i start the snapshot agent via gui, it works (but the files are written to the same dir ... it is just linked a little different, but i found no parameters for that)
    – Flo
    Dec 14, 2011 at 8:29
  • Ok, forget about the error, the "sp_startpuublication_snapshot" works fine, thats exactly what i was hoping for :) Thx
    – Flo
    Dec 14, 2011 at 8:53
  • @Flo Glad I could help. I'd recommend posting on the SQL Server Replication Forum for any replication questions you have in the future. Dec 15, 2011 at 3:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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