Home Dashboard Directory Help
Search

dropping computed column in merge replicated table causes all other computed columns in table to stop replicating by Russ Thomas


Status: 

Closed
 as Fixed Help for as Fixed


1
0
Sign in
to vote
Type: Bug
ID: 766009
Opened: 10/3/2012 5:16:41 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

We recently dropped a computed column from a table that was participating in merge replication. After issuing the statement the other two columns remained in the table but stopped replicating out to the subscribers.

This became apparent while building a new subscriber I noticed that these other computed columns were missing and causing the snapshot to not apply since one of the views required these columns.

Checking the article list I could see those columns were available but the check box next to "publish" was missing on the selector. When I tried to add it manually it said I'd have to reinitialize every subscriber. Didn't want to do that as reinitializing takes a couple hours per client and there are over 30.

To get the columns to begin replicating again I decided to drop them ALL, and then add only the ones that I wanted.

ALTER Table Child DROP Column [Age]
ALTER Table Child ADD [Age] AS (floor(datediff(day,[DOB],getdate())/(365.25)))    
ALTER Table Child DROP Column [ChildFull]    
ALTER Table Child ADD [ChildFull] AS (IsNull([First],'') + ' ' + IsNull([Last],''))


Once complete - the computed column for ChildFull was included in the list of replication articles but Age was not. So I ran the DDL statements again like this.

ALTER Table Child DROP Column [Age]
ALTER Table Child DROP Column [ChildFull]    
ALTER Table Child ADD [Age] AS (floor(datediff(day,[DOB],getdate())/(365.25)))    
ALTER Table Child ADD [ChildFull] AS (IsNull([First],'') + ' ' + IsNull([Last],''))


After that everything was back and working no problem.

In three different attempts I was able to verify that dropping one computed column turns replication off for all other computed columns in the table.
Details
Sign in to post a comment.
Posted by Russ Thomas on 11/20/2012 at 2:45 PM
Great, thank you.
Posted by Microsoft on 11/20/2012 at 11:09 AM
Hi Russ Thomas,

given my previous message, I'm archiving this work item for now.

Best regards
Jean-Yves Devant 
Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server
Program Manager SQL Server Replication, Change Data Capture, Change Tracking
Posted by Microsoft on 11/12/2012 at 12:11 PM
Hi Russ Thomas,

This issue has been fixed in SQL Server 2012 and SQL Server 2008 SP2 and higher as well as SQL Server 2008 R2 SP1 and higher. This has not and will not be fixed in SQL Server 2008 SP1.

Best regards
Jean-Yves Devant
Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server
Program Manager SQL Server Replication, Change Data Capture, Change Tracking
Posted by Microsoft on 11/12/2012 at 12:10 PM
Hi Russ Thomas,

This issue has been fixed in SQL Server 2012 and SQL Server 2008 SP2 and higher as well as SQL Server 2008 R2 SP1 and higher. This has not and will not be fixed in SQL Server 2008 SP1.

Best regards
Jean-Yves Devant 
Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server
Program Manager SQL Server Replication, Change Data Capture, Change Tracking
Posted by Microsoft on 10/8/2012 at 4:27 PM
Hi Russ Thomas ,
    
thanks for taking the time to share your feedback, this is really important to us.
We will investigate the issue and get back to you.

Best regards
Jean-Yves Devant 
Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server
Program Manager SQL Server Replication, Change Data Capture, Change Tracking
Sign in to post a workaround.