Check constraints not being replicated properly

Hello, I'm at a loss with a tough problem I've yet to find a solution for:
SCENARIO:
I am replicating as a form of disaster-recovery. Our published application
uses local partitioned views, partitioned by month... so there is one
database per month. We have a date_created partitioning column in each
table which specifies that records should only go in that month. Example
for our Feb partition on the Processor_File table:
ALTER TABLE [dbo].[Processor_File] WITH CHECK ADD CONSTRAINT
[DF_Processor_File_date_created] CHECK (([date_created]>='02/01/2008' AND
[date_created]<'03/01/2008'))
When I add the tables as articles to the database's publication, I AM
setting the Copy Check Constraints = true option. I WANT this to occur,
because there's this crazy condition with partitioned views whereby the
check constraints can be enabled and yet somehow they are not "confident" in
the status of their checked data and therefore views that include the table
are considered non-updateable (which sucks -- can anybody explain THAT to me
either?!)
Regardless... I WANT the Check Constraints to be copied over AND I want them
enabled for replication. The problem, when the SNAPSHOT is generated to
create the database schema, it looks just like this:
IF OBJECT_ID(N'[dbo].[DF_Processor_File_date_created]') IS NULL
BEGIN
ALTER TABLE [dbo].[Processor_File] ADD CONSTRAINT
[DF_Processor_File_date_created] CHECK NOT FOR REPLICATION
([date_created]>='02/01/2008' AND [date_created]<'03/01/2008')
ALTER TABLE [dbo].[Processor_File] CHECK CONSTRAINT
[DF_Processor_File_date_created]
END
Notice: the NOT FOR REPLICATION clause is set. THAT IS EVIL!!! I do NOT
want that clause to be set!!! That clause is _NOT_ set in the source
description of my published table. How can I make sure the snapshot does
not create the tables on the subscriber side with that option?
TIA for your support,
Eric Gavin
Alliance Payment Technologies
Do you want this constraint enforced on the subscriber when the DML is
occurring due to a replication process? If so you do not want that
constraint to be not for replication? It sounds like this is likely
something you don't want, but I don't think it is necessarily evil.
Please post your schema of the table with the constraints and I'll show you
how to create your publication.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AACH Developers" <AACHDevs@.community.nospam> wrote in message
news:ubzHrdXWIHA.4448@.TK2MSFTNGP03.phx.gbl...
> Hello, I'm at a loss with a tough problem I've yet to find a solution for:
> SCENARIO:
> I am replicating as a form of disaster-recovery. Our published
> application uses local partitioned views, partitioned by month... so there
> is one database per month. We have a date_created partitioning column in
> each table which specifies that records should only go in that month.
> Example for our Feb partition on the Processor_File table:
> ALTER TABLE [dbo].[Processor_File] WITH CHECK ADD CONSTRAINT
> [DF_Processor_File_date_created] CHECK (([date_created]>='02/01/2008' AND
> [date_created]<'03/01/2008'))
> When I add the tables as articles to the database's publication, I AM
> setting the Copy Check Constraints = true option. I WANT this to occur,
> because there's this crazy condition with partitioned views whereby the
> check constraints can be enabled and yet somehow they are not "confident"
> in the status of their checked data and therefore views that include the
> table are considered non-updateable (which sucks -- can anybody explain
> THAT to me either?!)
> Regardless... I WANT the Check Constraints to be copied over AND I want
> them enabled for replication. The problem, when the SNAPSHOT is generated
> to create the database schema, it looks just like this:
> IF OBJECT_ID(N'[dbo].[DF_Processor_File_date_created]') IS NULL
> BEGIN
> ALTER TABLE [dbo].[Processor_File] ADD CONSTRAINT
> [DF_Processor_File_date_created] CHECK NOT FOR REPLICATION
> ([date_created]>='02/01/2008' AND [date_created]<'03/01/2008')
> ALTER TABLE [dbo].[Processor_File] CHECK CONSTRAINT
> [DF_Processor_File_date_created]
> END
> Notice: the NOT FOR REPLICATION clause is set. THAT IS EVIL!!! I do NOT
> want that clause to be set!!! That clause is _NOT_ set in the source
> description of my published table. How can I make sure the snapshot does
> not create the tables on the subscriber side with that option?
> TIA for your support,
> Eric Gavin
> Alliance Payment Technologies
>
|||[Note to Microsoft folks -- I still need help please!]
Thanks for trying to help, but part of the frustration I have is that I seem
to WANT to be doing something that nobody else really wants to do. And so
I've scrolled through a million google posts trying to figure out how to do
what nobody else wants to do. I DO want to enforce the constraints at the
DML during replication because of the partitioned view requirements.
Not meaning to sound rude, but my original post has every answer to your
question already in it, so I'll just re-post it as a response:
[Repost]
> occurring due to a replication process?

> constraint to be not for replication?
[vbcol=seagreen]
Lastly, the schema of the constraint in the published table and the schema
that the snapshot generates has already been posted. May I ask why it would
be important to know the whole schema of the table?
Thanks, and I'm sorry if any offense was given. I have been at this for
quite some time now and am still frustrated because after reading the books
I appear to be doing everything right, but the snapshot just "refuses" to
create WITHOUT the "NOT FOR REPLICATION" option set during constraint
creation. I NEED that constraint to NOT be used when the snapshot as
applied at the subscriber.
Thanks,
Eric Gavin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23vC48aeWIHA.6140@.TK2MSFTNGP02.phx.gbl...
> Do you want this constraint enforced on the subscriber when the DML is
> occurring due to a replication process? If so you do not want that
> constraint to be not for replication? It sounds like this is likely
> something you don't want, but I don't think it is necessarily evil.
> Please post your schema of the table with the constraints and I'll show
> you how to create your publication.
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "AACH Developers" <AACHDevs@.community.nospam> wrote in message
> news:ubzHrdXWIHA.4448@.TK2MSFTNGP03.phx.gbl...
>
|||Confusion posted by me on the last bit:

> Thanks, and I'm sorry if any offense was given. I have been at this for
> quite some time now and am still frustrated because after reading the
> books I appear to be doing everything right, but the snapshot just
> "refuses" to create WITHOUT the "NOT FOR REPLICATION" option set during
> constraint creation. I NEED that constraint to NOT be used when the
> snapshot as applied at the subscriber.
Please correct this to:
I NEED the _OPTION_ (NOT FOR REPLICATION) to NOT be used when the snapshot
is applied.
For further clarity, I must, want, desire and need the check constraints to
be checked during the application of DML because of replication.
Eric
|||UPDATE: Apparently the schema_option:
0x10000
Replicates CHECK constraints as NOT FOR REPLICATION so that the
constraints are not enforced during synchronization.
Exists, but BOL seems to leave out the small (yet necessary detail) that it
is simply un-settable from the GUI. So I think this problem is solved if
I'm willing to run sp_changearticles for all of my table articles with check
constraints (which I am.)
But if MS-folk read this and thinking I'm headed in the wrong direction, or
misguided in my belief that the UI doesn't allow for this setting to be
updated, I'd appreciate learning so. Thanks!!
Eric Gavin
"AACH Developers" <AACHDevs@.community.nospam> wrote in message
news:e31zdvfWIHA.5132@.TK2MSFTNGP02.phx.gbl...
> Confusion posted by me on the last bit:
>
> Please correct this to:
> I NEED the _OPTION_ (NOT FOR REPLICATION) to NOT be used when the snapshot
> is applied.
> For further clarity, I must, want, desire and need the check constraints
> to be checked during the application of DML because of replication.
> Eric
>
|||Hi Eric,
I understand that you would like to have the snapshot does not create the
tables on your subscriber with the NOT FOR REPLICATION clause.
If I have misunderstood, please let me know.
The option is a default option for SQL Server 2000/2005 replication when
there is any foreign key constraints or check constraints; however you can
programmatically use T-SQL statements to change the replication schema
options (schema_option) by yourself. Please refer to the following articles:
Frequently Asked Questions for Replication Administrators
-- How do I manage constraints on published tables?
http://msdn2.microsoft.com/en-us/library/ms151740.aspx
Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION
http://msdn2.microsoft.com/en-us/library/ms152529.aspx
How to: Specify Schema Options (Replication Transact-SQL Programming)
http://msdn2.microsoft.com/en-us/library/ms147887.aspx
sp_addarticle (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms147887.aspx
sp_changearticle (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms175980.aspx
For your other concern, "When I add the tables as articles to the
database's publication, I AM setting the Copy Check Constraints = true
option. I WANT this to occur, because there's this crazy condition with
partitioned views whereby the check constraints can be enabled and yet
somehow they are not "confident" in the status of their checked data and
therefore views that include the table are considered non-updateable (which
sucks -- can anybody explain THAT to me either?!)", I could not understand
it very clearly. To let me better understand your issue, could you please
answer me the following questions:
1. What it the type of your replication?
2. Did you mean that your views on your publisher became non-updatable
after you set the replication option "Copy Check Constraints = true" for
those table articles?
If you have any other questions or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====