SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Transaction Replication - Identity Insert
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dolphin123
Yak Posting Veteran

USA
78 Posts

Posted - 05/12/2014 :  16:28:44  Show Profile  Reply with Quote
Hi,

I set up transaction replication for certain tables. I am getting the error:

Cannot insert explicit value for identity column in table '<tablename>' when IDENTITY_INSERT is set to OFF.


I see the cause of the issue. Publisher table is trying to dump the data to the Subscriber table where it's identity column is not allowing any data.

One way to workaround this issue is making the subscriber database's identity column not IDENTITY. but we have to keep both table's schema identical. Is there a configuration in the replication that I missed causing this issue? Surely Someone in the past would have wanted to keep the same value on both tables.

Any help is appreciated.

D

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 05/12/2014 :  16:33:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
http://dba.stackexchange.com/questions/21831/sql-server-2008-r2-transactional-replication-cannot-insert-explicit-value-for-i

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

dolphin123
Yak Posting Veteran

USA
78 Posts

Posted - 05/13/2014 :  16:33:14  Show Profile  Reply with Quote
Thanks so much.

Now, I am trying to change the subscriber tables' primary keys' "no for replication" property to Yes.

If I try to do it using UI (Design table option), it says it cannot be done. I have to recreate the table.

If I use the command:

EXEC sys.sp_identitycolumnforreplication <tableobjectid>, 1

It says "Command ran successfully" but it doesn't change the option.

Is there a step I am missing?

Again, appreciate your help
Go to Top of Page

dolphin123
Yak Posting Veteran

USA
78 Posts

Posted - 05/13/2014 :  16:35:14  Show Profile  Reply with Quote
Could it be because I am changing this on subscriber and the publisher's property is changing it back to NO?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 05/13/2014 :  18:25:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
You need to run it on the publisher.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

dolphin123
Yak Posting Veteran

USA
78 Posts

Posted - 05/14/2014 :  01:07:49  Show Profile  Reply with Quote
It is quite odd. If I close the SQL management studio and reopen it again after running the SP command, it works fine.

Tara - I am curious. Why should I make this change on publisher? It is the subscriber that needs this ability (No for replication) right?

My understanding is that "no for replications" will do the following:
- it sees a direct insert, it creates an ID that is an incremental from the previous ID.
- it sees an replication insert, it would take the value comes in and update the rows.

If I am right, why would I run this on publisher? it should only be on subscriber. why am I wrong here?

Thank you !!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 05/14/2014 :  11:56:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
Wingman explains it here: http://www.sql-server-performance.com/forum/threads/not-for-replication-clause-for-identity-field.29371/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

dolphin123
Yak Posting Veteran

USA
78 Posts

Posted - 05/14/2014 :  13:58:49  Show Profile  Reply with Quote
Thanks a lot AGAIN!!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 05/14/2014 :  15:08:42  Show Profile  Visit tkizer's Homepage  Reply with Quote


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000