SQL Clone
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 (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 767
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
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 1051

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

Group: General Forum Members
Points: 188 Visits: 767
Thank you Steve.



SQL-DBA-01
SQL-DBA-01
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3627 Visits: 3283
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 (34 views, 170.00 KB)
error.jpg (17 views, 103.00 KB)
Graeme100
Graeme100
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1432 Visits: 785
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-
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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
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: 2275 Visits: 2032
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.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

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



-m-
-m-
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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