SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication Gotcha - Columns with Defaults


Replication Gotcha - Columns with Defaults

Author
Message
Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: Moderators
Points: 26023 Visits: 2749
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
morpheuz
morpheuz
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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..."

buddy__a
buddy__a
Right there with Babe
Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)

Group: General Forum Members
Points: 756 Visits: 257
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?



morpheuz
morpheuz
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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..."

morpheuz
morpheuz
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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..."

morpheuz
morpheuz
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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..."

Mike Rochleau-Rice
Mike Rochleau-Rice
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 134

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?


morpheuz
morpheuz
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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..."

Cynthia Huang
Cynthia Huang
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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


Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: Moderators
Points: 26023 Visits: 2749
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search