msrepl_tran_version

Question

msrepl_tran_version

Garutyunyan

SSCrazy

Points: 2629
More actions
May 15, 2007 at 4:09 pm

#103361

Hi,
Does anyone know if there's a way to prevent from the msrepl_tran_version field being created when setting up replication. I noticed that SQL Server added this field at the end of all my tables that have PK's in them, skipping tables that do not.
Has anyone seen this, and is there a workaround to this?
Many thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

aureolin Hall of Fame Points: 3520 More actions · Answer 1

Tables involved in replication need at least one uniqueidentifier column. If you don't have such a column one will be provided for you. Actually, it's slightly more complicated than that. Transactional and snapshot publications that allow updateable subscriptions require this column (and, I think, the "MSrepl_tran_version" column name). Merge subscriptions also require a row identifier column. Basically, this column is used to track changes to the replicated data. BOL has some, occasionally contradictory, information on this field, but not a whole lot.

My experience with non-updating transactional replication is that you get this column whether you like it or not. We stopped doing replication a little over a year ago and I'm still weeding these out of my database.

Steve G.

Garutyunyan SSCrazy Points: 2629 More actions · Answer 2

Garutyunyan

SSCrazy

Points: 2629

May 16, 2007 at 12:20 pm

#706916

Thank you Steve.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

Hello SQL DBAs,

I needed little more clear information on this column: 'Msrepl_Tran_Version'.

I configured the 'Transactional Publication with Updatable Subscriptions'. I created the DB: "sourav" and included only one article(table):' tab_pk'. I inserted 7 records there. Now, after the replication was setup I found one more Column i.e, "msrepl_tran_version" was added to it.

After the snapshot executed I found that those 7 records successfully replicated to the subscriber. Now I wanted to add some more rows to the "publication DB" Table: 'tab_pk'.

insert into tab_pk values('sachin',default)

But, I'm getting the below error msg:

"

Msg 213, Level 16, State 1, Line 1

Insert Error: Column name or number of supplied values does not match table definition.

"

The table had 4 columns:

id: Identity

name: Char

Address: Varchar default (Bangalore).

Check the 2 attachments for more details.

Suggest me, how can I insert records to the "publication table" so that those new details can be replicated to the Subscriber end??

Thanks.

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 4

Hey Sourav-657741,

As far as I know the MSREPL_tran_version column has a default on it so it should never affect your insert statements.

What you should be doing is explicitly naming the columns you want to insert

INSERT INTO tbl(col1,col2) VALUES(col1value,col2Value)

If you have an identity you don't include that and you don't have to include any colums that have defaults (unless you want to insert a specific value)

Any good ?

Graeme

-m- SSC Enthusiast Points: 160 More actions · Answer 5

I have a table with a uniqueidentifier column (EmployeeTrx). Is it possible to set/use this column for Transactional Replication with Updatable Subscription so it does not create MSrepl_tran_version anymore?

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 6

I believe you're out of luck on this one.

Frequently Asked Questions for Replication Administrators

Joie Andrew
"Since 1982"

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 7

I think you would be better off leaving replication to manage uniqueness, by all means have your own PK.

-m- SSC Enthusiast Points: 160 More actions · Answer 8

-m-

SSC Enthusiast

Points: 160

September 21, 2010 at 5:04 pm

#1225286

ok. thanks guys.