Post reply

msrepl_tran_version

  • 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.

  • May 16, 2007 at 11:57 am

    #706904

    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.

  • May 16, 2007 at 12:20 pm

    #706916

    Thank you Steve.

  • November 18, 2009 at 2:13 am

    #1080739

    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.

  • November 26, 2009 at 8:42 am

    #1084618

    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

  • September 19, 2010 at 9:46 pm

    #1223782

    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?

  • September 20, 2010 at 3:02 pm

    #1224401

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

    Frequently Asked Questions for Replication Administrators

    Joie Andrew
    "Since 1982"

  • September 21, 2010 at 12:19 am

    #1224544

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

  • September 21, 2010 at 5:04 pm

    #1225286

    ok. thanks guys.

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

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