NOT NULL column property not replicated to subscriber

[jcaulder]

I have an existing merge replication running on a publisher that is SQL Server 2K. Subscribers are MSDE.

I add a new column by going to the publications-->Properties and selecting Add New Column. In here I specify the column to be of type bit, default value = 1 and NOT NULL. On my publisher, the column is correctly created and populated with a 1 for all existing rows. However on my subscribers the column gets created with ALLOW NULLS. Because this happens, all rows get populated with NULL. Default values only get inserted into existing rows if ALLOW NULLS is false on the new column.

What can I do to prevent this? Why does NOT NULL propagate to the subscribers?

Related to this question, will the merge agent EVER know that these rows have different values in this column(i.e. the publisher has a 1 while the subscriber has a NULL)?? I know that it will if the row is updated by the user but how about if the row is never updated?

TIA!

J

 

[jcaulder]

Although I won't have this luxury in the Production environment, I dropped the subscribers and recreated the publication and this time it correctly propagated the NOT NULL property and set the values to 1 at the subscriber.

It appears to be taking a very long time to add the column initially and then the first synch is taking forever also. Not sure why though because Profiler shows it is only calling a stored procedure on the subscriber that adds the column. It's not like it is issuing thousands of updates to set each row to 1.

Guess I'll keep experimenting. Something is obviously happening behind the scenes that I'm not aware of.

J

 

[mrdenny]

The merge agent will never retransmit the record until the record is updated. SQL Server Replication doesn't do data validation between the publisher and subscribers.

Try running the add column command again, but this time with SQL Profiler running against the subscriber.

What patch level are all of your machines? There may be a bug which Microsoft isn't aware of. You can submit this to them via

http://connect.microsoft.com/.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com