In SQL Server I have set up transactional replication using the "initialize with backup" option. Data is being replicated fine, as are schema changes to tables. However, schema changes to stored procedures are not appearing as a replicated command and therefore are not being made on the subscriber.

I found this post which seems to describe my problem, but I have been unable to implement the workarounds.

In my testing I'm positive I had this working and I think it is because I first created the replication using the GUI, which turns on Snapshot replication even though I wasn't using it. This leads me to believe that I should be able to turn on parts of the Snapshot Replication process and get this to work (I think this is what the first workaround in the blog post is alluding to).

Can anyone provide further information on the issue or suggest how I can get this working?

  • Dumb question, but I have to ask: Are the stored procedures set up as articles in the publication? – Matt M Oct 7 '11 at 17:10
  • Not dumb at all :) but yes - they are articles. Also further tested has confirmed that when I set up the replication using the GUI which forces me to semi-create snapshot stuff the stored procedures definitely get replicated. – David A Gibson Oct 8 '11 at 13:14
  • @DavidAGibson - did you ever get this working or find a fix? – JNK Feb 14 '12 at 14:59
  • @JNK, - I think I came to the conclusion that "initialize with backup" precludes this option. I'm not 100% sure that's correct but there appears to be so much undocumented that it made alot of sense when I came to this conclusion. In our situation we ended up not replicating tables or stored procudures - just the data. – David A Gibson Feb 14 '12 at 15:57

Probably not the issue, but double check this:

  • On the publication, go to properties
  • Go to Subscription Options
  • make sure Replicate schema changes is set to true.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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