August 21, 2012 at 12:47 pm
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.
August 22, 2012 at 11:56 pm
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
August 23, 2012 at 12:29 am
The quickest way will be re-configure the replication for the specified database.
----------
Ashish
August 24, 2012 at 8:07 am
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.
August 27, 2012 at 1:31 am
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
August 27, 2012 at 3:12 am
Hi Chandra,
Its required a key primary or foreign not mandatory....
---
Satish
August 27, 2012 at 3:30 am
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.
August 27, 2012 at 4:43 am
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
August 27, 2012 at 12:38 pm
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.
Jared
CE - Microsoft
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply