SQL Server Heterogenous Replication ORA-00922 Error Since Upgrading to SQL Server 2016

Question

SQL Server Heterogenous Replication ORA-00922 Error Since Upgrading to SQL Server 2016

Mark Johnson 21

Since upgrading a SQL Server database from SQL Server 2012 to SQL Server 2016, we experience an "Agent message code 922. ORA-00922: missing or invalid option" error while attempting to replicate a datetime field using SQL Server heterogenous replication to Oracle. We tried upgrading the SQL Server 2012 database to SQL Server 2014 and heterogenous replication to Oracle worked.

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-08-30T21:43:15.427+00:00

This is an error from Oracle, so your troubleshooting needs to start on that side. Exactly what statement is Replication trying to execute on Oracle which leads this this message? What is the problem with it?

by the way, when you say SQL 2016, I really hope that means SQL 2016 SP3, and not the RTM version.
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2022-09-05T06:58:35.477+00:00

Hi @Mark Johnson ,

Any update for this thread?
Mark Johnson 21 Reputation points

2022-09-05T07:21:26.1+00:00

Hi Cathyji-msft,
At your suggestion we setup the SQL Server Migration Assitant(SSMA) for Oracle and ran it. SSMA suggested using a datetime2 for the table instead of a datetime:

datetime2(0) NOT NULL

Will discuss this with our developers, then make the change and test it.

Thanks
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2022-09-05T08:21:40.737+00:00

Hi @Mark Johnson ,

Thanks for your reply.

Waiting for your good news.

If an answer is helpful, please click "Accept Answer" and kindly upvote it. If you have further question, please feel free to let us know.
Mark Johnson 21 Reputation points

2022-09-07T22:15:55.657+00:00

[
Hi Cathyji-msft,
Dropped the article and subscription, then updated the table definition using the suggestion from SSMA:

[WRITE_DATETIME] datetime2 NOT NULL

Then recreated the article and subscription, ran the snapshot, and experienced the same error:

ORA-00922: missing or invalid option
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-09-08T20:57:36.043+00:00

So have you done any troubleshooting on the Oracle side yet? As you long as you are only working in SQL Server, you are fumbling around in a completely dark room.

3 answers

Your answer

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-08-30T21:43:15.427+00:00

This is an error from Oracle, so your troubleshooting needs to start on that side. Exactly what statement is Replication trying to execute on Oracle which leads this this message? What is the problem with it?

by the way, when you say SQL 2016, I really hope that means SQL 2016 SP3, and not the RTM version.
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2022-09-05T06:58:35.477+00:00

Hi @Mark Johnson ,

Any update for this thread?
Mark Johnson 21 Reputation points

2022-09-05T07:21:26.1+00:00

Hi Cathyji-msft,
At your suggestion we setup the SQL Server Migration Assitant(SSMA) for Oracle and ran it. SSMA suggested using a datetime2 for the table instead of a datetime:

datetime2(0) NOT NULL

Will discuss this with our developers, then make the change and test it.

Thanks
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2022-09-05T08:21:40.737+00:00

Hi @Mark Johnson ,

Thanks for your reply.

Waiting for your good news.

If an answer is helpful, please click "Accept Answer" and kindly upvote it. If you have further question, please feel free to let us know.
Mark Johnson 21 Reputation points

2022-09-07T22:15:55.657+00:00

[
Hi Cathyji-msft,
Dropped the article and subscription, then updated the table definition using the suggestion from SSMA:

[WRITE_DATETIME] datetime2 NOT NULL

Then recreated the article and subscription, ran the snapshot, and experienced the same error:

ORA-00922: missing or invalid option
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-09-08T20:57:36.043+00:00

So have you done any troubleshooting on the Oracle side yet? As you long as you are only working in SQL Server, you are fumbling around in a completely dark room.

Answer 1

Hi @Mark Johnson ,

Suggest you applying SP3 to SQL 2016. You can get it from here.

If the issue still exists, please you can start to troubleshoot this issue from Oracle side. May be you can use SQL server migration assistant for Oracle tool to compare the converted objects to the original objects to identify potential problems, and address them based on the recommendations. After that you may can replicate the data successfully. Please refer to this MS document to get detail steps.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Mark Johnson 21

Thanks Cathyji-msft

We are using SP3. This is from the select @@version output: "Microsoft SQL Server 2016 (SP3-CU1-GDR) (KB5015371)"

The problem occurs with this table:

CREATE TABLE [Test_Sync].[T_REPLICATION_TEST](
[WRITE_DATETIME] [datetime] NOT NULL,
CONSTRAINT [PK_WRITE_DATETIME] PRIMARY KEY CLUSTERED

The table contains 1 row:

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

2022-07-19 15:19:38.423

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-08-31T21:28:02.113+00:00

I have a feeling of what may be going on, but we really need to know what is happening on the Oracle side. That is, what statement does replication submit to Oracle?

Without this knowledge, we are just shooting in the dark.

Answer 3

CathyJi-MSFT 22,396 Microsoft External Staff

Hi @Mark Johnson ,

I test this query in my environment, it works well when I removed comma after Not Null. Does this table only have one column?
Did you use SSMA tool to map the datatype and schema between Oracle and SQL 2016 as I mentioned above? Did you get any error or suggestions in it?

Share via

SQL Server Heterogenous Replication ORA-00922 Error Since Upgrading to SQL Server 2016

3 answers

Your answer