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 2012 Forums
 SQL Server Administration (2012)
 Trans Replication for Identity Columns

Author  Topic 

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2014-07-21 : 20:02:10
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

38200 Posts

Posted - 2014-07-21 : 20:05:17
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

396 Posts

Posted - 2014-07-21 : 20:56:24
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

396 Posts

Posted - 2014-07-21 : 22:40:58
** 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

38200 Posts

Posted - 2014-07-22 : 12:47:12


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

- Advertisement -