Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Dec 14 '11 at 1:57

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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 '11 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 '11 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. –  Brandon Williams Dec 15 '11 at 3:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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