Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ALTER TABLE breaks replication Expand / Collapse
Author
Message
Posted Tuesday, May 21, 2013 9:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 10:32 AM
Points: 72, Visits: 292
I have a table-article being replicated as part of a publication that replicates DDL commands. Historically, executing ALTER statements on replicated tables has not caused any problems.

However, I recently executed the following ALTER:
ALTER TABLE my_table ADD my_column INT NULL DEFAULT(0)

I immediately started receiving the following error on one subscriber:
Procedure or function 'sp_MSupd_mytable' expects parameter '@pkc1', which was not supplied.

And this error on another subscriber:
rocedure or function 'sp_MSins_mytable' expects parameter '@c7', which was not supplied.

I was under the impression the sp_MS-stored procs would update automatically to reflect schema changes and that there shouldn't be any problem if the Log Reader is processing changes as it should. Anything jump out as the specific cause of this problem? Let me know if I can provide any other information that would help.

Thanks
Post #1455090
Posted Tuesday, May 21, 2013 1:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 10:32 AM
Points: 72, Visits: 292
The article has "Copy check contraints" set to False. This ALTER would certainly add a constraint to default new values to 0. It seems to me that the Log Reader should just end up picking up the ALTER and applying the change on subscribers, resulting in a new constraint on the downstream DBs. Just thinking out loud about the possibility of a conflict between the article properties and what the subscribed servers would end up doing.
Post #1455206
Posted Wednesday, May 22, 2013 8:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 10:34 AM
Points: 832, Visits: 1,177
If you make Schema changes to Publisher they are propagated to Subscriber by default, unless set otherwise while creating replication.

So as you said, you can wait if it resolves the issue by self, else you can manually modify the SPs in question on the subscriber DB.
Post #1455553
Posted Wednesday, May 22, 2013 8:44 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 10:34 AM
Points: 832, Visits: 1,177
Just observed one more thing. your default in not named. Always create named defaults if you are using replication. It creates issue if you drop or alter defaults.
Post #1455561
Posted Wednesday, May 22, 2013 8:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:26 AM
Points: 498, Visits: 426
Check the setting of Copy INSERT, UPDATE, DELETE stored procedures for this article should be set to true.
Post #1455565
Posted Wednesday, May 22, 2013 9:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 10:32 AM
Points: 72, Visits: 292
I don't think the problem would "resolve itself" unless I had manually modified those SPs. Strangely enough "Copy INSERT, UPDATE and DELETE stored procedures" is set to True so I'm not sure why they didn't update automatically
Post #1455578
Posted Wednesday, May 22, 2013 9:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 10:32 AM
Points: 72, Visits: 292
I did read here http://msdn.microsoft.com/en-us/library/ms151870.aspx that naming the constraint is recommended. Just wish I knew if that is the cause of the original problem. Thanks for your replies
Post #1455581
Posted Wednesday, May 22, 2013 2:01 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 10:34 AM
Points: 832, Visits: 1,177
No. Named constrains will cause problem only when they are either altered or deleted at publisher.
Post #1455688
Posted Wednesday, December 4, 2013 4:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 9:02 PM
Points: 3, Visits: 3
DR_DBA (5/22/2013)
I did read here http://msdn.microsoft.com/en-us/library/ms151870.aspx that naming the constraint is recommended. Just wish I knew if that is the cause of the original problem. Thanks for your replies

thanks alot for the link


شركه تنظيف منازل بالرياض تنظيف شقق شركة تنظيف مكافحة البق مكافحة البق المنزلي
مبيد حشرى للصراصيرشركات مكافحة البق في جدةمكافحة النمل الابيض في المنزل
Post #1519826
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse