Not for Replication property - 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

SQL Server Administration (2005)

Not for Replication property

Author

Topic

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-10-23 : 12:28:07

For replication 'Not for replication' property on identity columns should be set to 'Yes' right?? in both Publisher and Subscriber database.

Any inputs would help

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-23 : 13:06:20

Yes.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-10-23 : 18:58:03

You just set it on Subscriber, not on publisher. If it is 2000, there is no other way to do it than through GUI via Enterprise Manager. In 2005 you can do ALTER TABLE SET NOT FOR REPLICATION.

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-23 : 20:56:28

quote:
Originally posted by dinakar

You just set it on Subscriber, not on publisher. If it is 2000, there is no other way to do it than through GUI via Enterprise Manager. In 2005 you can do ALTER TABLE SET NOT FOR REPLICATION.

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

dinakar, correct me if I am wrong. We set it on both publisher and subscriber.Do you see any disadv with this?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-10-26 : 14:12:08

quote:
Originally posted by sodeep

quote:
Originally posted by dinakar

You just set it on Subscriber, not on publisher. If it is 2000, there is no other way to do it than through GUI via Enterprise Manager. In 2005 you can do ALTER TABLE SET NOT FOR REPLICATION.

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

dinakar, correct me if I am wrong. We set it on both publisher and subscriber.Do you see any disadv with this?

Sodeep

We dont need to set it on publisher. The NOT FOR REPLICATION means if the INSERT is coming through via replication agent, the IDENTITY column not be incremented and just take the value that is coming through from the agent.

You can set it on publisher. however, since the inserts are all through users and not via replication agents on publisher, you wont see any errors.

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

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2008-10-27 : 13:13:51

amsqlguy,

I would say it depends. Especially,Transactional Replication (updatable subscription -immidiate) shows inconsistent behaviour with NFR setting on SQL Server 2000 and SQl Server 2005. For Instance you have triggers both on publisher and subscriber which are set to NFR, means any update on subscriber should not fire trigger on publisher.But in SQl 2000 it doesn't fire trigger and in SQL 2005 it does. Because Subscriber updates publisher using 2PC which is considered as user transaction, not Repl agent transaction. In 2000 it was considered as a BUG which is fixed in SQL 2005. Interesting. Isn't it?

Now to answer your question: If you are using Transactional Replication (updatable subscription -immidiate)
sql 2000 -yes
For sql2005 - Doesn't matter

If you have hundreds of tables you can use following method to setup things quickly:

SQL Server 2005 system stored procedure sys.sp_identitycolumnforreplication will help you to turn on or turn off this "Not For Replication" setting.

This script turns it YES for all tables that have an identity column.
EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'

For SQl Server 2000
I don't remember exact syntax (please use our friend google) but updating syscolumns (colstat) this can be accomplished .
Note- updating system table is not recommended.

For accurate answers please elaborate your question and include SQL Server/OS versions,Replication and platform.

LovelyLady
Starting Member

1 Post

Posted - 2011-05-26 : 21:53:58

I am using SQL Server 2008 R2
Doing Trans Replication from the Nav databse to Database A.
Merger replication from Database A to several tablets .
The Trans Replication gives Error message while replicating
Cannot insert explicit value for identity column in table 'Record Link' when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)

the id column where Identity is set to True in all the 3 databases.
Not for replication = True in all the 3 databases.
Any suggestions would be great help !

jchamiak
Starting Member

1 Post

Posted - 2011-06-15 : 00:32:46

I would recommend using sp_browsereplcmds to see what is triggering this replication failure. Recently, I ran into a similar issue and found several triggers residing in other tables that were modifying the table named in the replication error. Setting NFR appropriately on those triggers resolved the problem.

quote:
Originally posted by LovelyLady

I am using SQL Server 2008 R2
Doing Trans Replication from the Nav databse to Database A.
Merger replication from Database A to several tablets .
The Trans Replication gives Error message while replicating
Cannot insert explicit value for identity column in table 'Record Link' when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)

the id column where Identity is set to True in all the 3 databases.
Not for replication = True in all the 3 databases.
Any suggestions would be great help !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources