question

MinMa-1432 avatar image
0 Votes"
MinMa-1432 asked SeeyaXi-msft answered

Errors out with adding article when creating Oracle publisher

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://docs.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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MinMa-1432,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @MinMa-1432,

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

Best regards,
Seeya



1.png (46.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the information, looks like this is reason, we user SQL server 2019 and Oracle 19c, replication is not supported.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered MinMa-1432 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Captured the SQL statement in trace, when adding article

Msg 21614, Level 16, State 1, Procedure sys.sp_IHsyncmetadata, Line 320 [Batch Start Line 1]
Index column 'SCHEDULED_COMPLETION_DATE' not found in table 'WIP_DISCRETE_JOBS',

Our Oracle version is 19c, wondering if SQL server replication supports Oracle 19c?

0 Votes 0 ·

The next step would be do

EXEC sp_helptext 'sys.sp_IHsyncmetadata'

And take a look at what is going on line 320. Assuming that is, that you get any code at all. I'm uncertain where this guy is hiding.

Then again, with the screenshot Seeya posted, your prospects appear bleak. Only 10g and earlier? Cough! it's dusty here.

0 Votes 0 ·

yes, looks like replication is not supported from Oracle 19c to SQL server 2019. We have to consider other options, like running SSIS packages.

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered SeeyaXi-msft converted comment to answer

Hi @MinMa-1432,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The index column do exists in that table and Oracle login user has the following permission on Oracle:

DROP PUBLIC SYNONYM
CREATE TABLE
DELETE ANY TABLE
UPDATE ANY TABLE
CREATE VIEW
CREATE PUBLIC SYNONYM
INSERT ANY TABLE
SELECT ANY TABLE
CREATE PROCEDURE
CREATE SESSION
CREATE ANY TRIGGER
CREATE SEQUENCE

Thank for the link for troubleshooting for Oracle publishers, unfortunately there is no information for the error we receive here.

0 Votes 0 ·