An error occurred applying the changes to the Distributor

Min Ma 41 Reputation points
2021-10-19T07:16:21.357+00:00

We are trying to replicate tables/views from Oracle 19c to SQL server 2019. Based on Configure an Oracle Publisher at https://learn.microsoft.com/en-us/sql/relational-databases/replication/non-sql/configure-an-oracle-publisher?view=sql-server-ver15, we have completed first three steps, now I am working on step four - Configure the Oracle database as a Publisher at the SQL Server Distributor. added Oracle publisher on Distributor Properties window, once click on "OK" button, getting "An error occurred applying the changes to the Distributor", here is full message:

TITLE: Distributor Properties

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

An error occurred applying the changes to the Distributor.

For help, click: https://go.microsoft.com:80/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.0.18386.0&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476

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

ADDITIONAL INFORMATION:

SQL Server could not enable 'uat' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)

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

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

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

Cannot insert explicit value for identity column in table '#hquery' when IDENTITY_INSERT is set to OFF.
Changed database context to 'master'.
Error: 156, Sev: 15, State: 1, Msg: Incorrect syntax near the keyword 'PUBLIC'. (Microsoft SQL Server, Error: 544)

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

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

BUTTONS:

OK

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

Screenshot with error is also attached141610-publishererror.png

Please help to identify what might be wrong and how to fix this issue. Thanks for the help!

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,653 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-10-21T21:30:03.517+00:00

    Please observe that you are running the RTM version of SQL 2019. You should apply the most recent Cumulative Update, which is CU13.

    No guarantees that this will resolve the issue, but you should try this before you take the next step, which would be to open a support case, as this is the only way you can get a bug fixed quickly. Since this seems to be a blocking issue, I think your chances for a fix are good, if you have a decent support contract.

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,656 Reputation points
    2021-10-19T09:47:06.467+00:00

    Instead of applying the configuration using "Ok" button use the "Script" button; how does the script look like?

    0 comments
  2. Min Ma 41 Reputation points
    2021-10-19T16:05:45.763+00:00

    "Script" button is greyed out, it's not available for clicking 141853-publishererror1.png

    0 comments
  3. YufeiShao-msft 7,051 Reputation points
    2021-10-20T03:33:41.027+00:00

    Hi @Min Ma ，

    if you can see this document: https://social.msdn.microsoft.com/forums/sqlserver/en-US/cdf19c22-bb1f-4590-a6eb-741a80bf9c97/oracle-publisher-transactional-replication-sql-server-2005

    you can try to run the below command at the ORACLE side.

    drop public synonym MSSQLSERVERDISTRIBUTOR;

    and every table in ORACLE that needs to be published requires explicit GRANT SELECT, UPDATE, INSERT and DELETE permissions to that ORACLE user.

    you can use T-SQL to finish this step.

    Cannot insert explicit value for identity column in table '#hquery' when IDENTITY_INSERT is set to OFF.

    https://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity
    https://www.mssqltips.com/sqlservertip/1274/change-not-for-replication-value-for-sql-server-identity-columns/

    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.

  4. Min Ma 41 Reputation points
    2021-10-21T17:37:03.787+00:00

    This issue seems like a bug that need to be fixed. Below are SSMS and SQL Servers that we use:

    SQL Server Management Studio version information:

    SQL Server Management Studio 15.0.18390.0
    SQL Server Management Objects (SMO) 16.100.46521.71
    Microsoft Analysis Services Client Tools 15.0.19714.0
    Microsoft Data Access Components (MDAC) 10.0.19041.1
    Microsoft MSXML 3.0 6.0
    Microsoft .NET Framework 4.0.30319.42000
    Operating System 10.0.19042

    SQL Server 2019 Enterprise Evaluation version:
    Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19042: ) (Hypervisor)

    SQL Server 2019 Developer version:
    Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19042: ) (Hypervisor)

    0 comments