BCP - Different Schema

Question

I have a transactional Replication (Non-SQL Server Publishers - Oracle) implemented for one SQL Server Database. Let's say Publishers: A, B Subscriber: C

Note:

• I can not modify the Oracle DBs.
• The Publishers have the same DB schema.
• On the subscriber I have the same tables, with one column more "Publisher" to identify where that line comes from.

I used the After-Snapshot Script to create and populate this column for the Initial Load for the Publisher A.

Also, created a (After-Snapshot) script for the publisher B to populate the column.

It works when I do the first snapshot for A, and my bulkload fails for B with the following error:

Replication-Replication Distribution Subsystem: agent AgentName failed. The process could not bulk copy into table '"dbo"."EMployees"

Error Log: Error: 14151, Severity: 18, State: 1.

Replication Monitor Source: MSSQL_REPL, Error number: MSSQL_REPL20037)

Anyone has an idea? Is it due the schema difference between B and C, since when the A does the bulkload there is no table and first I create the table, then do the initial load, the do the alter table to add the column, update values to be ready for the second bulk load?

Answer 1

I'm not sure of the limitations of transactional replication, but with BCP you can create a view on the table that contains only columns that exist in the BCP file, and load into that view rather than the table. How do you define the data for the publisher column at the moment?

Answer 2

it is failing because snapshot agent is generating a bcp file that does NOT contain that extra column, when it runs against Publisher B. Distribution agent is trying to apply a bcp file that has one less column into the subscriber table. I think what you want to do it initialize without a snapshot. I don't know if that is supported for Oracle publisher though, you may need to try it out.