Not For Replication - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Not For Replication

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-03-31 : 13:16:48

I need to update all of my foreign keys on multiple databases to be NOT FOR REPLICATION. When I run the following...

Update sys.foreign_keys 
Set is_not_for_replication = 1
where
  is_not_for_replication = 0 AND
   Is_System_Named = 0

I get an error...
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.

1. Does anyone know if this update would work or will I screw something up?

2. How can I get around the error?

TIA,
Ken
2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-31 : 13:23:42

it wont work. You cant update system tables/views like this. can i ask what you're trying to do here?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-03-31 : 21:44:04

Try ALTER TABLE.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-04-01 : 11:47:07

I'm trying to change all of the Foreign Keys in a database to NOT FOR REPLICATION. I can't figure out the ALTER TABLE syntax to do such...

alter table [t_spn_item_count_hist] check constraint [FK_T_SPN_IT_REFERENCE_TM_SPN_I] NOT FOR REPLICATION

gives me a syntax error

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-04-02 : 18:28:16

Check if this link helps - http://msdn.microsoft.com/en-us/library/ms152529.aspx

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-02 : 22:43:15

I don't even copy my foreign keys to the replicated database. I bring over the primary keys and all indexes, but not the foreign keys.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-04-03 : 00:22:23

Since I usually start from a backup from the primary database, I disable the FKs after I restore the backup on the subscriber..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-04-05 : 10:51:30

Nope - no help guys. I know I can disable them but I am trying to keep an exact copy of the db for possible restoration. Plus I am not in control of the creation of the original DB otherwise they would already have the NOT FOR REPLICATION set.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-04-06 : 13:35:28

You would restore the database from your publisher onto your subscriber and disable the constraints on the subscriber. Your source DB would not be changed. Of course, when you setup replication there will be a bunch of replication related stored procs on the publisher DB. The NOT FOR REPLICATION option would be set on the subscriber. Basically you are telling the SQL Server that the configuration option be disabled for REPLICATION.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-04-07 : 08:30:22

I understand what you are saying, however the subscriber db is where all of the data is generated. The publisher is just collecting the data from the subscriber. I know this is somewhat backwards, but it's because we use web synchronization so our clients do not have to make any firewall changes. I could disable the constraints on the publisher, but I just don't like to do that for various reasons.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources