Changing table properties

I have 7 tables that I want to remove the checkbox for "Enforce relationship
for replication". These tables are all articles in a publication and I want
them to get down to the merge synchronization laptop subscribers. On a test
system, I unchecked them in EM and the changes took without any errors
(actually I expected to be told I cannot do that on published articles). I
assume that I can just re-create the snapshot and have the laptops
"reinitialize" at their next synch. Am I correct or missing anything.
Thanks.
David
David,
this is fine, although you might be able to just apply a script on the
subscriber to drop the FKs directly (or through sp_addscriptexec) without
doing the reinitialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I tried this in a test environment but when I looked at the database on the
laptop it still had the box checked. Did I miss something?
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:etWoZ9JQHHA.1380@.TK2MSFTNGP05.phx.gbl...
> David,
> this is fine, although you might be able to just apply a script on the
> subscriber to drop the FKs directly (or through sp_addscriptexec) without
> doing the reinitialization.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||David - can you post up the script you were using and I'll take a look.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
I was trying to do it in Enterprise Manager instead of script. I then went
into the Snapshot Agent and restarted the snapshot agent, which I thought
would pick up the new settings. I then re-initialized the subscription at
the laptop but it didn't pick up the removal of "Enforce relationship for
replication".
Do I need to completely re-create the publication? If I can do it in a
script then what script do I use to run on the laptops and where can I find
the syntax for changing this relationship property? Thank you.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OZyzv$SQHHA.1200@.TK2MSFTNGP02.phx.gbl...
> David - can you post up the script you were using and I'll take a look.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Also, do I need to run sp_addscriptexec at both the publisher and
subscriber? I read the BOL and they said you have to be sysadmin to run it
but the laptops are not. Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OZyzv$SQHHA.1200@.TK2MSFTNGP02.phx.gbl...
> David - can you post up the script you were using and I'll take a look.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Hi David - have answered your other thread below.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||David,
the script is in the form:
ALTER TABLE dbo.yourFKtable
DROP CONSTRAINT yourFKtable1
GO
ALTER TABLE dbo.yourFKtable WITH NOCHECK ADD CONSTRAINT
yourFKtable1 FOREIGN KEY
(
PKColumn
) REFERENCES dbo.YourPKTable
(
pkcolumn
) NOT FOR REPLICATION
GO
You'll need to run this at the publisher to change the publisher tables then
add it to the publication using sp_addscriptexec.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul. Will I have to run sp_addscriptexec at the subscribers also or
will that happen when they synch?
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ufa1ml4QHHA.4632@.TK2MSFTNGP04.phx.gbl...
> David,
> the script is in the form:
> ALTER TABLE dbo.yourFKtable
> DROP CONSTRAINT yourFKtable1
> GO
> ALTER TABLE dbo.yourFKtable WITH NOCHECK ADD CONSTRAINT
> yourFKtable1 FOREIGN KEY
> (
> PKColumn
> ) REFERENCES dbo.YourPKTable
> (
> pkcolumn
> ) NOT FOR REPLICATION
> GO
> You'll need to run this at the publisher to change the publisher tables
> then add it to the publication using sp_addscriptexec.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||David - the merge agent will take care of applying it to the subscribers.
Cheers,
Paul Ibison