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.

In SQL Server I have set up transational 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 year old post on msdn 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?

share|improve this question
    
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
add comment

1 Answer

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.
share|improve this answer
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.