SQL Server 2008 Transactional Replication Between Databases That Have Table With Same Columns Name But Different Column Order

Question

I'm new with replication. My company is using Ms. Dynamics Navision 2009. Now I need to replicate all databases from the branches to a single database for reporting purpose.
This is the structure of the Databases

SOURCE

DB Name     : [BRANCH ONE]
TABLE Name  : [Branch One$Table1]
COLUMN Name : C1, C2, C3, C4
PRIMARY KEY : C1
--------------------------------------
DB Name     : [BRANCH TWO]
TABLE Name  : [Branch Two$Table1]
COLUMN Name : C1, C2, C3, C4
PRIMARY KEY : C1
--------------------------------------
    . . .
--------------------------------------
DB Name     : [BRANCH XXX]
TABLE Name  : [Branch XXX$Table1]
COLUMN Name : C1, C2, C3, C4
PRIMARY KEY : C1

DESTINATION

DB Name     : [REPORT DB]
TABLE Name  : [Company$Table1]
COLUMN Name : C1, C4, C2, C3
PRIMARY KEY : C1, C4
Note: Column C4 contain Branch ID. In Navision, column is sorted by Primary Key first.

When I try to setup these replication, the value of column C4 (REPORT DB) is always filled by the value of column C2 (BRANCH DB). I have try using custom store procedure for INSERT, but the result is always the same as previous.

I know how to setup a one-to-one transactional replication, but never had a successfull run with many-to-one setup. Please help me to setup this replication. I have googling for many-to-one replication example/tutorial but with no luck. Especially replication between tables with different column order. Thank You..


Best Regards,

Arfan Septianto

Answer 1

Could you drop the column C4 and Add it back to the table on Destination?

If not, you can modify the stored procedures for replication, naming MsIns*****, MsUpd*****, to reflect the inconsistent column order.