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

Replication Gotcha - Columns with Defaults Expand / Collapse
Author
Message
Posted Monday, May 24, 2004 1:49 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:35 PM
Points: 6,779, Visits: 1,868
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #117391
Posted Tuesday, May 25, 2004 3:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 7, 2006 10:00 AM
Points: 38, Visits: 1

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.

 




"See how deep the rabbit hole goes..."
Post #117467
Posted Tuesday, May 25, 2004 7:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 4:50 PM
Points: 324, Visits: 217
So, to test your theory did you create default on the subscriber with the same name as on the publisher then try and drop the column?


Post #117527
Posted Tuesday, May 25, 2004 9:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 7, 2006 10:00 AM
Points: 38, Visits: 1

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.

 




"See how deep the rabbit hole goes..."
Post #117587
Posted Tuesday, May 25, 2004 9:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 7, 2006 10:00 AM
Points: 38, Visits: 1

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.

 




"See how deep the rabbit hole goes..."
Post #117588
Posted Tuesday, May 25, 2004 9:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 7, 2006 10:00 AM
Points: 38, Visits: 1

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.

 




"See how deep the rabbit hole goes..."
Post #117589
Posted Tuesday, May 25, 2004 9:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, May 25, 2014 8:47 PM
Points: 353, Visits: 130

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?

Post #117601
Posted Wednesday, May 26, 2004 3:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 7, 2006 10:00 AM
Points: 38, Visits: 1
Yes. Add the oclumn using the repl stored procs or EM Repl tools, but add the default contraints directly in the table editor in EM or via queyr analyser. That way the publication will not be aware of the default constraint.


"See how deep the rabbit hole goes..."
Post #117746
Posted Friday, August 20, 2004 2:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 21, 2004 8:27 AM
Points: 1, Visits: 1

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

 

Post #133189
Posted Friday, August 20, 2004 3:45 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:35 PM
Points: 6,779, Visits: 1,868
I know of no way to figure it out other than query every column to figure out what the largest value is. Then set the max to that or larger. Im sure it has some impact, but my guess - only a guess - is that it wouldnt be substantial, no worse than what it took to insert the value on the publisher to start with.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #133193
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse