Transaction replication error for updatable subscription: the column name 'msrepl _ tran_version 'is invalid.

Problem description:

First of all, I don't know if you have any similar experience. In the transaction replication that can be updated and subscribed, when the subscription is initialized, an error similar to this occurs: the column name 'msrepl _ tran_version 'is invalid. (Source: MSSQLServer; error code: 207)

Figure 1. Check errors by copying the monitor

　　

Figure 2. Check errors through job history

Problem Analysis:

We know that the msrepl_tran_version column will be added to the table when you allow the release and release of tables that can be updated and subscribed. In this article, in the product release table, the msrepl_tran_version column is added to the table.

View code

At the beginning of this article, it describes that the msrepl_tran_version column is invalid during subscription initialization. In the details, the error occurs in the create procedure [DBO]. [sp_msins_dboproduct] process. Before the create procedure [DBO]. [sp_msins_dboproduct] action, the msrepl_tran_version field is not added to the product table of the subscription database. Next, let's take a look at the project attributes of the product release table:

　　

Figure 3. Project Properties of the published table Product

Figure 3. You can see that the "operation when the name is used" in the "target object" is "delete data. If the project has a row filter, only the data that matches the filter is deleted. This will result in that the msrepl_tran_version field is not added to the table product during subscription initialization. This is also caused by the error in the beginning of the article: The column name 'msrepl _ tran_version 'is invalid. (Source: MSSQLServer; error code: 207)

　　

Prompt

When publishing a table that allows updatable subscription, the column
Msrepl_tran_versionAdd to the table. This column is used for change tracking and conflict detection. If the data copy updated by the subscription server is out of date, an update conflict will occur.

 

Solution:

The column 'msrepl _ tran_version 'is invalid, and the cause of the problem is identified. Next, we will solve the problem. Here we will solve the problem in two ways. You can choose your actual production environment, which of the following is a quicker method:

 

　　1. In the project attribute of the release table, select Delete existing object and create new object in the Action column of the target object]

Figure 4. Set the project attributes of the product release table.

After the configuration is complete, you need to re-Initialize each subscription so that it is OK.

　　

Figure 5. Synchronization replication Initialization

　　

　　2. Add the msrepl_tran_version field to the database subscription table.

Alter table product add msrepl_tran_version uniqueidentifier not null default (newid ())

After the msrepl_tran_version field is added to the product table of the subscription database, keep the previous published table and delete the data from the "operation when the name has been used" option in the "target object. If the project has a row filter, only the data that matches the filter is deleted ]. Or select [keep existing object unchanged ]. Then initialize the subscription, or manually start the initialization subscription.

 

(End .)

No incapable variable t unthinkable.

MSN:Weiguohao008@hotmail.com