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
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Trans Replication for Identity Columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jbates99
Constraint Violating Yak Guru

384 Posts

Posted - 07/21/2014 :  20:02:10  Show Profile  Reply with Quote
hi experts,
I have about 100 tables to be replicated to a reporting database. Most of the tables have an Identity column and I will use transactional replication in SQl Server 2012.

When the tables at the subscriber have the identity setting, I found that new rows are not written to the subscription table. (Changes, updates are getting to the subscription table)

I tried changing the 'Create Schemas at Subscriber' to False, in the published article. But when i ran the snapshot, the subscriber table received the Identity setting. How is the best way to resolve this?

Any ideas? Thanks

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 07/21/2014 :  20:05:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
You need to change the tables at the publisher so that they have the NOT FOR REPLICATION option.

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

jbates99
Constraint Violating Yak Guru

384 Posts

Posted - 07/21/2014 :  20:56:24  Show Profile  Reply with Quote
Thanks, Tara.
I scripted the table:
Company_ID int, (IDENTITY 1,1], NOT FOR REPLICATION, NOT NULL

Or are you referring to a setting within the Publisher articles?
Jack

Go to Top of Page

jbates99
Constraint Violating Yak Guru

384 Posts

Posted - 07/21/2014 :  22:40:58  Show Profile  Reply with Quote
** Resolved**
Tara, I had failed to reinitialize at the Publisher. I have Marked it for Reinit and ran the Snapshot job.

Now both inserts and updates are being pushed to the subscription.
The Identity column is NOT part of the PK
Both published table and subs table have the column as Identity AND both show NOT FOR REPLICATION is TRUE

As far as I know, the tables at the Subscriber will not need to be updated.

Seems to be working well now.
Thanks for your tip. Jack
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 07/22/2014 :  12:47:12  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.06 seconds. Powered By: Snitz Forums 2000