Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

msrepl_tran_version Expand / Collapse
Author
Message
Posted Tuesday, May 15, 2007 4:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:36 PM
Points: 150, Visits: 607
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.



Post #366140
Posted Wednesday, May 16, 2007 11:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:47 AM
Points: 498, Visits: 976

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.




Post #366494
Posted Wednesday, May 16, 2007 12:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:36 PM
Points: 150, Visits: 607
Thank you Steve.


Post #366508
Posted Wednesday, November 18, 2009 2:13 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:50 PM
Points: 858, Visits: 1,389
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.

  Post Attachments 
table_structure.jpg (15 views, 170.15 KB)
error.jpg (8 views, 103.96 KB)
Post #820636
Posted Thursday, November 26, 2009 8:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:32 AM
Points: 1,221, Visits: 695
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



Post #825252
Posted Sunday, September 19, 2010 9:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 30, 2010 5:43 PM
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?
Post #989086
Posted Monday, September 20, 2010 3:02 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
I believe you're out of luck on this one.

Frequently Asked Questions for Replication Administrators


Joie Andrew
"Since 1982"
Post #989796
Posted Tuesday, September 21, 2010 12:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:32 AM
Points: 1,221, Visits: 695
I think you would be better off leaving replication to manage uniqueness, by all means have your own PK.


Post #989970
Posted Tuesday, September 21, 2010 5:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 30, 2010 5:43 PM
Points: 2, Visits: 14
ok. thanks guys.
Post #990826
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse