question

MinMa-1432 avatar image
0 Votes"
MinMa-1432 asked ErlandSommarskog commented

An error occurred applying the changes to the Distributor

We are trying to replicate tables/views from Oracle 19c to SQL server 2019. Based on Configure an Oracle Publisher at https://docs.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://docs.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-generalsql-server-transact-sql
publishererror.png (39.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.

What version of SSMS do you have? What does "SELECT @@version" report?

This certainly looks like embarrassing bugs that one would hope have been fixed.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 2
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.

After applying latest patch CU13 to SQL server 2019. Oracle publisher can be created on distributor without error. Thank you very much for the help!!

Another quick question: except tables, what other objects could be replicated from Oracle to SQL server, can we replicate views and synonyms as well? Thanks again

0 Votes 0 ·

Great to hear that the CU resolved the problem.

I will have to pass on the second question. I have no experience of Oracle or replication from Oracle. Hopefully someone else can fill in. Then again, it may be better to start a new thread to keep things apart.

(Since my post addressed your original question, you should probably mark my post as the Answer to your question to help others to run into the same problem and find this question.)

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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

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.

MinMa-1432 avatar image
0 Votes"
MinMa-1432 answered

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



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.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered MinMa-1432 commented

Hi @MinMa-1432

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.





· 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. The link at https://social.msdn.microsoft.com/forums/sqlserver/en-US/cdf19c22-bb1f-4590-a6eb-741a80bf9c97/oracle-publisher-transactional-replication-sql-server-2005 doesn't apply to our case. In our case, it errors out before Oracle publisher gets created.

Based on the instructions on MS at https://docs.microsoft.com/en-us/sql/relational-databases/replication/non-sql/configure-an-oracle-publisher?view=sql-server-ver15, we only need to grant SELECT permission for the Oracle admin user for the tables that need to be published.

"For the tables that you publish, grant SELECT permission directly on each of them (not through a role) to the Oracle administrative user you created in step one." Do we really need to grant UPDATE, INSERT and DELETE permissions to Oracle admin user as well?

The other two links are helpful if we need to deal with the user tables in the database. However the temp table "'#hquery" gets created by internal replication/publisher creation process that we have no control over it, based on the error message.

We are getting same error when try on SQL Server 2019 Enterprise Evaluation edition and 2019 Developer edition as standard edition seems doesn't support Oracle publisher.

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

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)

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.