Does ordinal position of columns matter for SQL Server replication
3
votes
1
answer
885
views
I am setting up replication on SQL Server 2017. Transaction replication with push subscriptions such that many smaller database are replicated into a single larger database for reporting.
The snapshots wouldn't initialize for some tables in some databases because of "field size too small" errors. The tables had the same "schema" in that they had all the same fields with the same datatypes and primary key. However, the ordinal position of the columns varies for some of the tables in question between databases. I think the snapshots wouldn't initialize because the bcp command it uses expects the tables' columns to be in the same order.
Two questions:
1. Can anyone confirm that the the ordinal position matters for replication as far as snapshots initializing goes? It makes sense to me that it would but I can't find anything that says that it does explicitly.
2. If I initialize the subscription manually (allowing me to skip the snapshot initialize step) would replication work with the ordinal position of the columns being different for the tables but the tables otherwise being identical? Is this supported or do I need to reorder all the columns to make sure they are the same?
Asked by Booji Boy
(155 rep)
Oct 25, 2019, 12:46 PM
Last activity: Apr 27, 2025, 01:06 AM
Last activity: Apr 27, 2025, 01:06 AM