Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replication problem Expand / Collapse
Author
Message
Posted Tuesday, August 21, 2012 12:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 9:16 AM
Points: 2, 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.
Post #1348040
Posted Wednesday, August 22, 2012 11:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 8:06 AM
Points: 167, 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
Post #1348869
Posted Thursday, August 23, 2012 12:29 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:28 PM
Points: 882, Visits: 4,111
The quickest way will be re-configure the replication for the specified database.


----------
Ashish
Post #1348879
Posted Friday, August 24, 2012 8:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:50 AM
Points: 398, Visits: 568
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.
Post #1349687
Posted Monday, August 27, 2012 1:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 11:40 PM
Points: 466, Visits: 1,923
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
Post #1350233
Posted Monday, August 27, 2012 3:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 8:06 AM
Points: 167, Visits: 414
Hi Chandra,
Its required a key primary or foreign not mandatory....


---
Satish
Post #1350274
Posted Monday, August 27, 2012 3:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 11:40 PM
Points: 466, Visits: 1,923
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.
Post #1350281
Posted Monday, August 27, 2012 4:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 8:06 AM
Points: 167, 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
Post #1350308
Posted Monday, August 27, 2012 12:38 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:58 AM
Points: 2,696, Visits: 3,402
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
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1350530
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse