"Invalid Operation" when adding Oracle subscriber to SQL Server 2016 replication publication RRS feed

  • Question

  • Hi,

    I'm trying to set up transactional replication from SQL Server 2016 to Oracle 12c.  (I realize that heterogeneous replication is deprecated but this should be for a limited time period).  I am following the general guidance here: https://msdn.microsoft.com/en-us/library/aa337389.aspx  and the Oracle-specific info here: https://msdn.microsoft.com/en-us/library/ms151864.aspx.  I am finding that when I try to add the non-SQL Server subscriber, I get this error:

    Invalid operation. The connection is closed. (System.Data)

    Program Location:

       at System.Data.SqlClient.SqlConnection.GetOpenConnection()
       at System.Data.SqlClient.SqlConnection.get_ServerVersion()
       at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.GetServerVersion()
       at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.get_IsServer130OrLater()
       at Microsoft.SqlServer.Management.UI.SubWizardSubscriber..ctor(String name, SubscriberType subscriberType, Boolean isSelected, Boolean isRegistered, ReplicationSqlConnection sqlConn, WizardInputs inputs)
       at Microsoft.SqlServer.Management.UI.PageChooseSubscribers.AddNonSqlSubscriber_OnClick(Object sender, EventArgs e)

    I have tried to follow the Oracle/heterogenous recommendations, including

    1) Installing recent 64-bit Oracle client and verifying basic connectivity to database

    2) Ensuring the the distribution account has read access to the Oracle OLEDB provider directory

    3) Setting public snapshot format to Character

    4) Setting publication's subscription options to Allow non-SQL Server subscribers

    5) Configuring my source SQL Server as distributor

    Any other suggestions?  Any known guides or walk-throughs for this scenario?



    Monday, January 30, 2017 9:28 PM

All replies

  • Hi Martin,

    We are currently looking into this issue and will give you an update as soon as possible.

    Thank you for your understanding and support.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 31, 2017 9:28 AM
  • "Invalid Operation" when adding Oracle subscriber to SQL Server 2016(13.0.5102.14) replication publication"


    I am also facing the same issue 

    I have installed Oracle 19.0.0 full client on Distributor server.

    Through SQLPlus I can able to connect to Oracle Database successfully.

    Also through ODBC 64 bit DSN test connection done successfully.

    I have given Read & Exec permission to the SQL Server Distributor Agent account.

    TNSPing command also verified successfully.

    But when I try to add Data Source Name in "Add non SQL Server Subscriber under "New Subscriber Wizard" it is giving error like "Invalid operation. The connection is closed."

    Please suggest.

    Keeran kumar

    Thursday, July 23, 2020 7:05 AM
  • Hi,

    Looking at my old notes, it seems one key step (undocumented as far as I know) was:

    From cmd prompt, connected using dedicated administrator connection  

    sqlcmd -S <SQLserverName>-E –A


    And Executed

    use msdb


    exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '12'


    exec sys.sp_MSrepl_MSSQL_ORA_datatypemappings @source_dbms = N'MSSQLSERVER', @destination_dbms = N'ORACLE', @destination_version = '12'

    My notes show that I then created the subscription using  sp_addsubscription as follows, but I don't remember anymore if that was actually necessary or if the GUI would have worked just as well

    first "use" the database that is enabled for publication, then

    exec sp_addsubscription @publication = N'<subscription_name>', @subscriber = N'<oracle_TNS_NAME>, @destination_db = N'(default destination)', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 3;

    Hope this helps!

    • Edited by MartinBH Thursday, July 23, 2020 12:40 PM
    Thursday, July 23, 2020 12:40 PM
  • Hi,

    After following above steps, I am getting below error.

     "Agent message code 20029. The required parameter '-SubscriberDB' is missing. "

    Could you please suggest me.

    SQL server 2016 sp2 : Publisher & Distributor

    Oracle 12102 : Oracle subscriber. I have installed full client of Oracle 12c1.

    For adding Oracle subscriber:

    From sqlcmd I ran,

    >exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '12'

    >exec sys.sp_MSrepl_MSSQL_ORA_datatypemappings @source_dbms = N'MSSQLSERVER', @destination_dbms = N'ORACLE', @destination_version = '12'

    From SSMS

    use [DBname]

    exec sp_addsubscription @publication = N'DB_To_oracle_<TNS>_Trans_Publication', @subscriber = N'ORCLDC', @destination_db = N'(default destination)', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 3;

    After that by default, replication created new Linked server with provided @subscriber name and it is using the provider "Microsoft OLE DB Provider for SQL Server"

    After that added subscriber connection: SQL Server Authentication(user should have access on Oracle DB)

    TNSPing : succesfull.

    Provided Read & exec permissions to Distributor agent service account on Oracle directory.

    Environment variable: Added Oracle home & bin path, TNS_admin path.

    Publication Access list: Added agent service account & distributor_admin

    Snapshot folder: service account has access to it.

    Snapshot format: Character 

    Allow non-SQL server subscribers : True

    ODBC Data source Admin 64bit : System DSN configuration : Test successful 

    If I configure new Linked server (with provider " Oracle Provider for OLE DB" and Remote login & password): It is successfully connecting to Oracle DB (on Linked server ).

    Tuesday, August 4, 2020 5:19 PM
  • Hello,

    Sorry, I do not have an idea about that error message.  It's been years since I set this up.

    Good luck!


    Tuesday, August 4, 2020 5:25 PM