Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


msrepl_tran_version


msrepl_tran_version

Author
Message
Garutyunyan
Garutyunyan
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 764
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.



aureolin
aureolin
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
Points: 519 Visits: 1047

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
Garutyunyan
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 764
Thank you Steve.



SQL-DBA-01
SQL-DBA-01
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2331 Visits: 3180
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.
Attachments
table_structure.jpg (22 views, 170.00 KB)
error.jpg (14 views, 103.00 KB)
Graeme100
Graeme100
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1249 Visits: 760
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-
-m-
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 14
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
Joie Andrew
SSC Eights!
SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)

Group: General Forum Members
Points: 970 Visits: 1912
I believe you're out of luck on this one.

Frequently Asked Questions for Replication Administrators

Joie Andrew
"Since 1982"
Graeme100
Graeme100
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1249 Visits: 760
I think you would be better off leaving replication to manage uniqueness, by all means have your own PK.



-m-
-m-
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 14
ok. thanks guys.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search