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

Mark Johnson 21 Reputation points
2022-08-30T13:51:46.447+00:00

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.

SQL Server | Other
{count} votes

3 answers

Sort by: Most helpful
  1. CathyJi-MSFT 22,396 Reputation points Microsoft External Staff
    2022-08-31T03:27:01.827+00:00

    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".

    0 comments No comments

  2. Mark Johnson 21 Reputation points
    2022-08-31T03:52:40.743+00:00

    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


  3. CathyJi-MSFT 22,396 Reputation points Microsoft External Staff
    2022-08-31T07:05:26.607+00:00

    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?

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.