I bumped up against this problem a short while ago on a project. I eventually concluded that the problem occurs because the default constraint is given an arbitrarily assigned name on the subscriber and because that name is different to the name assigned by the publisher when you add the column, it does not know what to delete at the subscriber when you drop the column. I concluded as did you: drop the constraint at the subscriber before you drop the column at the publisher using the using the repl stored procs or EM.
Nope. Never got that far. If I was going to drop a column at the publisher, I first dropped the default constraint at the subscriber. Simple.
I ran into a similar problem when adding a column with a default to a publication. The subscribers are all using SQLCE which I guess doesn't support default constraints so they would error out when trying to replicate the new column. Do you know of any way to add a column with a default constraint but not replicate the default constraint?
Hi: Thanks for the valuable information. We have encountered another problem with transactional replication I wonder if you can help us.
Our user got an error: "Length of text, ntext, or image data (75916) to be replicated exceeds configured maximum 65536."
My question:
1. How big should we set sp_configure 'max text repl size'? Do we have to worry about memory when we set it too high?
2. How do we calculate the max text repl size from the tables we have? Some ot our tables have several image and ntext columns. Is there a formula to calculate this?
Thanks in advance for your help.
Cynthia
