alter identity property of a column to NOT FOR REPLICATION

[dayong]

i need to alter all foreign keys in my database and uncheck the
"Enforce relationship for replication" check box. Using the EM, I
extracted the code snippet below. unfortunately, when i run this test
from query analyzer, then go back into the EM, the box is still
checked.

can anyone tell me what i am missing? any advice on unsetting this
attribute globally would be appreciated!

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.CustomerCustomerDemo
DROP CONSTRAINT FK_CustomerCustomerDemo_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.CustomerCustomerDemo WITH NOCHECK ADD CONSTRAINT
FK_CustomerCustomerDemo_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
) NOT FOR REPLICATION

GO
COMMIT

thanks!!

[Erland Sommarskog]

dayong (re******@yahoo.com) writes:

i need to alter all foreign keys in my database and uncheck the
"Enforce relationship for replication" check box. Using the EM, I
extracted the code snippet below. unfortunately, when i run this test
from query analyzer, then go back into the EM, the box is still
checked.
It's not simply a refresh issue? I was not able to reproduce this, of
the simple reason that I was not able find where you poke with FKs in
Enterprise Manager. I prefer to work exclusively with SQL statements
for DDL statements.

You can use "sp_helpconstraint" in Query Analyzer to verify the status
of the constraint.
can anyone tell me what i am missing? any advice on unsetting this
attribute globally would be appreciated!

As long as you know which the foreign keys are, going like the code you
included should not be a problem.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Michael Reed]

first, obviously, i'm new to sql server. thanks for your advice so far.
you are correct, it was a refresh issue. unfortunately, i cannot find a
simple way to find the foreign keys that are set for replication. i
looked at the stored procedure you advised (sp_helpconstraint). it
appears to create a temp table and then query and join info and
eventually has a boolean value where if true is_for_replication and
false not_for_replication.

this code is greek to me in my early stages of sql server
administration. is there a simpler way to locate the keys and columns
that are set is_for_replication?

thanks in advance for any advice!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Michael Reed (an*******@anonymous.com) writes:

first, obviously, i'm new to sql server.
If you find out how get the commands that EM runs, and run them
in Query Analyzer, you have come a long way compared to many other
SQL Server newbies!
this code is greek to me in my early stages of sql server
administration. is there a simpler way to locate the keys and columns
that are set is_for_replication?

This SELECT lists all foreign key constraints that are set for replication,
and the parent table:

select tbl = object_name(parent_obj), fk_name = name
from sysobjects
where xtype = 'F' and objectproperty(id, 'CnstIsNotRepl') = 0
order by tbl, fk_name

I don't know how many constraints you have. If you have only a handful,
you might be able to the rest manually. If you have hundreds of table,
you probably want a list of the columns in each FK. Since I'm lazy, and
I don't have a query ready for that right now, I don't include one. :-)

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Michael Reed]

Erland

please re-post your last response. i can only see the summary. when i
click on the link, your post is nowhere to be found.

please re-post.

thanks!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!