SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication problem


Replication problem

Author
Message
mistihad
mistihad
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 189
Hello,

We are replicating few databases (which are identical in structures) in SQL Server 2008 R2 with transactional replication. The replication for all databases has been initialized from backups. Now the replication is running fine for all databases except for one, which is giving the following error:

"Explicit value must be specified for identity column in table tablename either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column."

But for all databases, we have "NOT FOR REPLICATION " option set to 'Yes' for the identity columns at subscriber, so not sure why the job is failing for one database. The only difference I can find is that this database (for which the replication is failing) has been replicated before and therefore at publisher this database already had "NOT FOR REPLICATION " option set to 'Yes' for identity columns. But for the other databases this option was not enabled before at publisher (I guess the replication creation process enabled it). I am not sure how this difference can cause the problem because at the subscriber all the databases have "NOT FOR REPLICATION " option set to 'Yes'.

Please let me know what can cause this problem and how I can solve it.

Thanks.
saidapurs
saidapurs
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 414
Hi Mistihad,

Replication will happen on 2 major condition.

1. Table should be required a index (Cluster or non-cluster)
2. Table structure should be same on Publish & subscriber.

Replication will happen on Table level not DB level as of my knowledge.

Thanks & regards
Satish
crazy4sql
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1745 Visits: 4495
The quickest way will be re-configure the replication for the specified database.

----------
Ashish
Jason Bunn
Jason Bunn
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 647
You should be able to compare the replication stored procedures in the subscribers to see what is different between those that are working and those that are not.

A trace to see exactly what command is coming across is also helpful.

From there, you could alter the stored proc if there is a difference, or else, like others said, set up replication again for the failing publisher-subscriber pair.
chandan_jha18
chandan_jha18
SSC Eights!
SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)

Group: General Forum Members
Points: 921 Visits: 2134
saidapurs (8/22/2012)
Hi Mistihad,

Replication will happen on 2 major condition.

1. Table should be required a index (Cluster or non-cluster)
2. Table structure should be same on Publish & subscriber.

Replication will happen on Table level not DB level as of my knowledge.

Thanks & regards
Satish



Point # 1 should be a primary key. Indexes are not a necessary condition as far as I know. Can you elaborate the first point please.

Chandan
saidapurs
saidapurs
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 414
Hi Chandra,
Its required a key primary or foreign not mandatory....


---
Satish
chandan_jha18
chandan_jha18
SSC Eights!
SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)

Group: General Forum Members
Points: 921 Visits: 2134
saidapurs (8/27/2012)
Hi Chandra,
Its required a key primary or foreign not mandatory....


---
Satish


you mean to say primary key is not a requirement for transactional replication?? If yes, I disagree with this. Without a primary key, the transactional replication cannot be configured as far as I know.
saidapurs
saidapurs
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 414
Hi Chandan,

What i said mean, one key is required in particular table where you can give primary key or foreign key not an issue.


---
Satish
Jared Karney
Jared Karney
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5126 Visits: 3694
saidapurs (8/22/2012)
Hi Mistihad,

Replication will happen on 2 major condition.

1. Table should be required a index (Cluster or non-cluster)
2. Table structure should be same on Publish & subscriber.

Replication will happen on Table level not DB level as of my knowledge.

Thanks & regards
Satish
Not sure what you are getting at. First of all, both of those statements are wrong. A Primary Key is required, not just an index (although a primary key is enforced with an index). Table structures do not have to be the same, they can be different; i.e. I can choose which columns to replicate as long as the key columns are replicated.

Replication is set up at the database level. You can choose which articles to publish (tables, views, stored procs, functions...), but you cannot set up 1 publication to replicate tables from 2 different databases.

Nevertheless, none of your points address the OP's issue. So, to the OP... You are going to want to double-check the settings on that one publication. I have found that sometimes it is just easier to delete the subscription, delete the publication, and then do it all over again. However, that will depend on how big your db is and how the business tolerates the subscription being down for a bit.

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search