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 need to execute a command before a replication executes on the publisher, and after, on the subscriber.

I seem to have it working on the publisher, by adding another step in the replication agent.

But i have yet to discover how i can execute another step on the subscriber. I't has to be possible to somehow trigger a job on the subscriber, after a replication has succeded.

Do anyone know how this can be done?

share|improve this question
    
This is snapshot replication I take it? If that's the case then why not schedule a step after replication that uses SQLCMD or Invoke-SQLCmd to run exec sp_start_job on the remote server? –  Nic Oct 24 '12 at 22:33
    
As long as the command is executed on the remote server / publisher :) This seems like a fine solution to the issue. Could you by any chance post an answer with a little more detail? –  Moulde Oct 25 '12 at 7:39
    
Does it matter what type of replication it is? –  Moulde Oct 25 '12 at 16:02
    
Yes. Snapshot replication will execute and then stop, whereas transactional replication will not stop executing (as it will continue to apply transactions on the remote server). Or are you just refering to the you needing to run processes before and after the snapshot (or if the replication job fails)? –  Nic Oct 25 '12 at 17:53
1  
Transactional replication job execution is ongoing, but you can schedule the snapshot as often as you like. It will not actually take a snapshot if nothing has been reinitialized, but you still go through all the steps. –  Nic Oct 25 '12 at 22:04

1 Answer 1

up vote 1 down vote accepted

Add another job step to the snapshot agent which runs sqlcmd with what ever T-SQL command needs to be run using the -S parameter to specify to connect to the remote SQL Server.

share|improve this answer
    
I need to execute a job on the remote server, which then executes a powershell remoting script. –  Moulde Oct 25 '12 at 18:17
    
Then have the job on the publisher run sqlcmd -S Subscriber -Q "exec msdb.dbo.sp_start_job @name='What ever the name is'" and have the job on the subscriber be the PowerShell. That or have the job on the subscriber just run the PowerShell script remotely. –  mrdenny Oct 25 '12 at 18:41
    
This is all working quite nice! Thanks How is security when using "sqlcmd -S"? –  Moulde Oct 28 '12 at 13:29
    
-S tells you the server name. By default it's using Windows Auth. -U {username} -P {password} will use SQL Auth if needed. –  mrdenny Oct 30 '12 at 0:03

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.