Errors out with adding article when creating Oracle publisher

Min Ma 41 Reputation points
2021-10-23T00:34:43.447+00:00

We have Oracle Distributor created and are working on adding Oracle publisher on SQL server 2019, on Articles window, I can see the tables which to be published, but if clicking + in front of the table, there is no columns displayed. On Filter Table Rows window, again no columns show up. At the end, it errors out with adding article:
TITLE: New Publication Wizard

------------------------------

SQL Server Management Studio could not create article 'WIP_DISCRETE_JOBS'.

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Index column 'SCHEDULED_COMPLETION_DATE' not found in table 'WIP_DISCRETE_JOBS',
Changed database context to 'distribution'. (Microsoft SQL Server, Error: 21614)

For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-21614-database-engine-error

------------------------------

BUTTONS:

OK

------------------------------

There are over 100 columns, including "SCHEDULED_COMPLETION_DATE" column on this table in Oracle, why it errors out with this message? Please advise how to fix it. Attached screenshots143102-oracle-rep-error-3.png143103-oracle-rep-error-1.png143084-oracle-rep-error-2.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,619 questions
Accepted answer
  1. Seeya Xi-MSFT 16,436 Reputation points
    2021-10-26T01:40:39.323+00:00

    Hi @Min Ma ,

    Yes, your suspicion is correct.
    143545-1.png
    For more information, please see this link: https://learn.microsoft.com/en-us/sql/relational-databases/replication/non-sql/non-sql-server-publishers?view=sql-server-ver15

    Best regards,
    Seeya

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-10-23T09:27:40.763+00:00

    As I said in another thread, I have no experience of replicating from Oracle, and I fear that there may not be very many others here who have. So I will contribute with some troubleshooting ideas, even if they are on the advanced level. And they may eventually not lead much further.

    What I note in the error message is that it comes from execution of T-SQL. This makes me think: is that T-SQL looking at some local table and finds none? That would indicate that things have gone wrong earlier. You could use Profiler to spy on what is going on. You would add these events: Error:UserMessage, SP:StmtStarting, SP:StmtCompleted, SQL:StmtStarting and SQL:StmtCompleted. (I'm assuming that you are running this on an idle test server. This trace is not good to run on a production server.) Once you have gotten the error, find the error message and then look at the preceding statements to see how it arrived at this conclusion.

  2. Seeya Xi-MSFT 16,436 Reputation points
    2021-10-25T06:49:50.187+00:00

    Hi @Min Ma ,

    You can first check if the index column exists in that table. The index cannot be found is your current reported error.
    In addition, there is Troubleshooting for Oracle Publishers here, so you can easily find other common errors.

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.