Columns not propogating

Question

Columns not propogating

TimothyAWiseman

SSCrazy Eights

Points: 8819
More actions
October 24, 2008 at 11:08 am

#200355

I have a transactional replication set between two servers, but not all the rows appear to be properly synched. In particular, there are some rows that appear in the replicated table that do not appear in the table it is being replicated two.
I tried marking it for reinitilization and they still did not appear after the last synch.
The Publisher is SQL Server 2000 and the subscriber is SS25K SP2.
Any ideas what can cause this?
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 1

If I understand what you're saying correctly, your subscriber doesn't have rows that exist on your publisher. It sounds like you might have a filter in place which is limited what gets sent to the subscriber.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 2

Thank you. That is a good thought, but I checked, and I don't have filter. The other thought I had is someone could have deleted the rows from the subscriber after they replicated, but only 4 people have that type of access and they all say they did not.

It was easy to fix (I forced it to reinitialize), but I am still baffled as to the cause or ways of preventing it from reoccurring.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

ConvolutedDBA SSC Enthusiast Points: 131 More actions · Answer 3

I've noticed a similar problem, I was running an alter table and couldn't get the column to propagate to the re-publisher or subscribers, I eventually altered the table and dropped the column and ran sp_addreplcolumn which seems to have solved the problem -

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 4

Looking back, I'm not sure why this topic is titled "Columns not propogating" when the original poster said it was rows that were missing from the subscriber...but in any case, ConvolutedDBA, what you're referring to is replication of DDL commands. That feature is enabled by default for new publications in SQL 2005 but it could have been disabled for your publication. To check, go to the publications properties, select the "Subscription Options" page in the left hand pane, and look at the value for "Replicate schema changes".

You can change it via the GUI, or if you want change this for multiple publications you can use this script to generate the statements:

[font="Courier New"]-- Run in database on publisher

-- Value = 0 indicates DO NOT replicate DDL changes

-- Value = 1 indicates DO replicate DDL changes

SELECT 'exec sp_changepublication @publication = N''' + name + ''', @property = N''replicate_ddl'', @value = N''0'''

FROM syspublications WITH (NOLOCK)

ORDER BY name[/font]

Changing this value does not require subscriptions to be resynchronized.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 5

Yes, I meant rows not propogating. I mislabeled it. The problem has not returned since I reinitialized it, I am just confused as to why it happened in the first place.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

ConvolutedDBA SSC Enthusiast Points: 131 More actions · Answer 6

Have you found this KB Article yet?

http://support.microsoft.com/kb/954054/