1

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?

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?

0

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.

  • Yeah, I realized that. But we cant initialize without a snapshot. But we are creating a script that will add the column to the bcp file. We tested it manually and it works. I think it's gonna work. we are worried about the performance when working with big files BUT since we wont do a snapshot so often, is more for the initial load, and it's not a prod database for the clients... I think it will be ok. Better then pay 50K/client for Attunity ! Thanks @Greg – LKen Jun 30 '15 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.