alter identity property of a column to NOT FOR REPLICATION

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!!dayong (reedmb89@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
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!|||Michael Reed (anonymous@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
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!

Alt+X doesent work! argghhh..

must have done something magical in QA, cant run my statements..going mad...when pressing f5 or alt+x i get this f****ng save results box..have tried everything, not even the so trustworthy restart helped..
help!?What about [CTRL]+E...

It's got to be a setting, though I can't find it...

Are you on a client?

Maybe a reinstall....|||Check in the options under the results tab. Make sure you are not funneling output to a file.|||Press Ctrl+D & then use Alt+X

Already have sql 2005 sp1 want to install sql 2000 on same box

Most of the posts here for co-existence of sql 2005 and sql 2000 are for upgrades to 2005 or installing sql 2005 on a box already having sql 2000. This is a bit different..

I have an application installed on sql 2005 sp1, on a default instance, no other instances installed. Because of issues the application is having with sql 2005, the vendor is recommending install of sql 2000 on the same box ('should not have any problems' they say), then script out the user databases in SQL 2005 and back in to the sql 2000 instance.

Has anyone tried this (installing sql 2000 after install of sql 2005) or can point me to an article about it? I've googled around and searched the knowledgebase to no avail.

thx

I have done this several times, and have seen some posts in these groups on the same topic (Where the users have not had a problem). You will need to make sure that you use a different instance name for the sql 2000 database system.

|||

Glenn,

Thanks for the reply, I'll test it out. I did search back in this forum, and found a few messages, but with mixed results, i.e. some who said to be sure to install sql 2000 first, implying some issues if done the other way around..see link below.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=122124&SiteID=1

|||

One issue I could see arise would be that the file extensions for the tools would be changed to reference sql 2000 applications. But you should be able to move these back. With the binerys these are all installed to seperate directories, the same is with the data files. With SQL 2000 they normally save the bin files to MSSQL\80 and for SQl 2005 MSSQL\90 (From memory). And the data files, SQL 2000 are saved inside a directory MSSQL$<instancename> where sql 2005 are save MSSQL.1 and so on... for example MSSQL.2 would be the second instance. There directory names might be a little different as I am going from memory and have not got them installed here. If I remember i will check and update tonight.

Allow zero length strings

In Microsoft Access there is the option to set a field to accept zero length
strings.
This of course means that an empty text box could be entered into the
database without an error occuring.
Is there any way I can do this in SQL Server.
I know that when developing a front end I could write some code to check
each text box and when a textbox is empty, push a " " into it before
insertion but I'm sick of this.
Is there any other way ?We use a Default in SQL Server 2000 like the following:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[EmptyString]') and OBJECTPROPERTY(id,
N'IsDefault') = 1)
drop default [dbo].[EmptyString]
GO
create default [Space] as ''
GO
>--Original Message--
>In Microsoft Access there is the option to set a field to
accept zero length
>strings.
>This of course means that an empty text box could be
entered into the
>database without an error occuring.
>Is there any way I can do this in SQL Server.
>I know that when developing a front end I could write
some code to check
>each text box and when a textbox is empty, push a " "
into it before
>insertion but I'm sick of this.
>Is there any other way ?
>
>.
>|||You can insert an empty (zero-length) string into a varchar (or nvarchar)
column:
CREATE TABLE MyTable
(
MyColumn varchar(10) NOT NULL
)
INSERT INTO MyTable VALUES('')
SELECT
MyColumn,
DATALENGTH(MyColumn)
FROM MyTable
GO
I don't know much about Access programming so there may be programming
considerations. Does an empty text box return in a zero-length string value
or does this return a NULL value?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:ePYQ%23tctDHA.980@.TK2MSFTNGP10.phx.gbl...
> In Microsoft Access there is the option to set a field to accept zero
length
> strings.
> This of course means that an empty text box could be entered into the
> database without an error occuring.
> Is there any way I can do this in SQL Server.
> I know that when developing a front end I could write some code to check
> each text box and when a textbox is empty, push a " " into it before
> insertion but I'm sick of this.
> Is there any other way ?
>