SQLServerWiki

“The Only Thing That Is Constant Is Change”

Replication Design issue.

Posted by database-wiki on October 5, 2011

Here is the reason for the “the Genesys Interactions Schema could not be replicated because of the duplicate column name” with error:

Error Message:

Column names in each table must be unique.
Column name ‘reason_code’ in table ‘conflict_IPRelayPStrans_interactions’ is specified more than once.

Enocuntered server error 0 while executing <xp_execresultset>

Changed database content to ‘IPRelayPS’. (Microsoft SQL Server, Error: 2705)

Explanation:

In our case scenario, the interactions table is used as the article that’s in the publisher database IPRelayPS from the publish server.

This table has got a column called reason_code also when you setup transaction replication it is also trying to create meta data table

Called ‘conflict_IPRelayPStrans_interactions’.

The usage of this meta data table can be explained as below.

When the publisher server receives the new transaction, the GUID of the transaction is compared to that in the publication database.

If the old GUID of the transaction matches with the one in the publication database, the publication is updated and the row is updated to the

GUID of the Subscriber server so that both GUIDs match.

If, however, the GUID of the transaction is not in harmony with the publication in the publisher server, a conflict is detected

and both the GUIDs will be present in the publication database. This indicates that there are two different versions of the row.

One row contains the transaction with the GUID from the subscriber server, and the other is the one that already existed in the
publication.

SQL Server also adds another table, called ‘conflict_IPRelayPStrans_interactions’, which is a conflict table. The system columns added are specific to queued updates:

• msrepl_tran_version

• origin_datasource

• conflict_type

reason_code

• reason_text

• pubid

• tranid

• insertdate

• qcfttabrowid

Since the reason_code column is add by the replication to the conflict table ‘conflict_IPRelayPStrans_interactions’.
If you have a column in your base table interactions, then you will need to change the name of that column for replication to be able to create the conflict table ‘conflict_IPRelayPStrans_interactions’ successfully.

The thing is, in replication the base table and meta data table should not have same column name.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: