10 Replies Latest reply: Sep 4, 2013 12:11 PM by user5536970 RSS

    Create Table Trigger to replicate data from MSSQL2K5 to Oracle 11G on Linux

    nsidev

      I am trying to create a trigger on my MSSQL 2k5 server so that when a record is inserted, a replicated record is created in a table on an Oracle 11g database on a Linux server (Oracle Linux 6).

      Creating the trigger is easy, but when I test it I am getting an error stating the following:

      .NetSqlClient Data Provider The operation could not be performed because OLE DB Provider 'OraOLEDB.Oracle' for linked server "<myserver>" was unable to begin the distributed transaction.

      OLEDB Provider "OraOLEDB.Oracle" for linked server "<myserver>" returned: "New transaction cannot enlist in the specified transaction coordinator"

       

      Here is the trigger (MSSQL):

      SET ANSI_NULLS ON

      GO

      SET QUOTED_IDENTIFIER ON

      GO

      CREATE PROCEDURE insert_aban8_state

          @an8 int,

          @st nvarchar(3)

      AS

      BEGIN

          SET NOCOUNT ON;

          declare @c numeric

          select @c = count(*) from [e9db]..[CRPDTA].[ABAN8_STATE$] where alan8=@an8 and aladds=@st

          if(@c =0)

           begin

              insert into [e9db]..[CRPDTA].[ABAN8_STATE$]

              values(@an8, @st)

           end

          END

      GO

       

       

       

      After reviewing the MS Transaction Coordinator, I am now totally confused. I checked the services and have the MS DTC enabled and running, but am not sure what to do on the Linux side.

      Does the Oracle Services for Microsoft Transaction Server (OraMTS) work on Linux? I could only find references for this for Oracle 11g on Windows.

       

      What do I need to do to enable this replication via mssql table trigger to Oracle11g on Linux?

        • 1. Re: Create Table Trigger to replicate data from MSSQL2K5 to Oracle 11G on Linux
          sb92075

          Can you manually succeed to issue INSERT against Oracle table?

          Unless & until that basic functionality works without error you are wasting your time messing around with trigger code

          • 2. Re: Create Table Trigger to replicate data from MSSQL2K5 to Oracle 11G on Linux
            nsidev

            sb92075 wrote:

             

            Can you manually succeed to issue INSERT against Oracle table?

            Unless & until that basic functionality works without error you are wasting your time messing around with trigger code

            Absolutely. The first thing I did was create a basic SQL command to insert the value from MSSQL 2k5 to the Oracle system.

             

            Here is a valid and working SQL:

            insert into [e9db]..[crpdta].[ABAN8_STATE$]

            select *

            from dbo.ABAN8_STATE$

            where alan8=9

             

            Result: (1 Row(s) affected), insert successful.

            • 3. Re: Create Table Trigger to replicate data from MSSQL2K5 to Oracle 11G on Linux
              sb92075

              nsidev wrote:

               

              sb92075 wrote:

               

              Can you manually succeed to issue INSERT against Oracle table?

              Unless & until that basic functionality works without error you are wasting your time messing around with trigger code

              Absolutely. The first thing I did was create a basic SQL command to insert the value from MSSQL 2k5 to the Oracle system.

               

              Here is a valid and working SQL:

              insert into [e9db]..[crpdta].[ABAN8_STATE$]

              select *

              from dbo.ABAN8_STATE$

              where alan8=9

               

              Result: (1 Row(s) affected), insert successful.

               

              Realize that Oracle DB does not know or care about the "flavor" of the client connection.

              By flavor I mean is can not & does not differentiate between JDBC, ODBC, OCI, SQL Server, etc.

               

              With this as the accepted reality, any valid INSERT statement will be successfully processed by the Oracle engine; regardless of the client source.

               

              There is no evidence that this problem ever actually gets to the Oracle DB.

              Since you are getting errors, this problem should be posted on some MS SQL Server forum, since this is where the error occurs.

              • 4. Re: Create Table Trigger to replicate data from MSSQL2K5 to Oracle 11G on Linux
                nsidev

                While I would agree in part, it appears from the message that the trigger is requiring the Transaction Service to be enabled on both the host and target. The point of this post is to determine what, if anything, I need to do on my Oracle DB to allow the trigger to complete successfully.

                 

                There are many posts found with Google concerning the OraMTS service on the Oracle system, but they all appear to be for Windows based systems. My question is, is this service part of the Linux based Oracle DB and if so, how do I initialize it?

                 

                If I am mistaken and this is truly an issue with the MSSQL server, I will replicate the post in those forums. I am just looking for direction and help.

                • 5. Re: Create Table Trigger to replicate data from MSSQL2K5 to Oracle 11G on Linux
                  sb92075

                  nsidev wrote:

                   

                  While I would agree in part, it appears from the message that the trigger is requiring the Transaction Service to be enabled on both the host and target. The point of this post is to determine what, if anything, I need to do on my Oracle DB to allow the trigger to complete successfully.

                   

                  There are many posts found with Google concerning the OraMTS service on the Oracle system, but they all appear to be for Windows based systems. My question is, is this service part of the Linux based Oracle DB and if so, how do I initialize it?

                   

                  If I am mistaken and this is truly an issue with the MSSQL server, I will replicate the post in those forums. I am just looking for direction and help.

                   

                  1) I have NEVER heard that Oracle has, knows about, or supports any "Transaction Service".

                  2) Consider what I previously posted regarding the flavor of client source.

                  If your assertion about this mythical service were correct, then the Oracle DB would have to be able to "know" that this client connection was originated by SQL Server.

                  I don't understand how or why Oracle should behave differently depending upon whether INSERT is done inside or outside a MS SQL Server trigger.

                  Please explain & elaborate why Oracle should behave different depending upon the source of any INSERT statement.

                  3) From Oracle DB standpoint an INSERT is an INSERT; regardless of the client.

                  • 6. Re: Create Table Trigger to replicate data from MSSQL2K5 to Oracle 11G on Linux
                    nsidev

                    I agree 100%, hence my general confusion as to what the issue, how to isolate and resolve it.

                     

                    Too much conflicting documentation.

                     

                    Thanks for your help, I will continue looking elsewhere.

                     

                    BTW: OraMTS is NOT a mythical service.

                    • 7. Re: Create Table Trigger to replicate data from MSSQL2K5 to Oracle 11G on Linux
                      sb92075

                      nsidev wrote:

                       

                      I agree 100%, hence my general confusion as to what the issue, how to isolate and resolve it.

                       

                      Too much conflicting documentation.

                       

                      Thanks for your help, I will continue looking elsewhere.

                       

                      BTW: OraMTS is NOT a mythical service.

                       

                      Please post URL to ORACLE documentation where this service is detailed; especially on *NIX

                      • 8. Re: Create Table Trigger to replicate data from MSSQL2K5 to Oracle 11G on Linux
                        Gamblesk

                        Is there a reason not to use SQL Server's built in replication rather than rolling your own?

                         

                        have to check the licensing requirements as it has been a few years since I did this but it does work.

                        • 9. Re: Create Table Trigger to replicate data from MSSQL2K5 to Oracle 11G on Linux
                          nsidev

                          Actually, yes. Part of my requirement is to replicate only the data that is inserted into this table at the time it was inserted.

                           

                          I will admit that I am not 100% familiar with the replication services on the SQL server as I have never had a need for it until now.

                           

                          My initial attempt at replication generated error messages that I am trying to resolve. It just appeared that creating a simple trigger would do the job nicely.

                           

                          I was surprised it has issues.

                          • 10. Re: Create Table Trigger to replicate data from MSSQL2K5 to Oracle 11G on Linux
                            user5536970

                            I'm running into a similar problem, however I'm not using triggers.  If I issue an INSERT statement from SQL Server to insert into an Oracle 11g database on Linux, the insert is successful.  However, I want to wrap the INSERT inside of a transaction using BEGIN TRANSACTION.  Doing so fails with the errors shown in the original post.  It makes sense that the latter operation requires MS DTC while the former does not. 

                             

                            In your case, maybe your trigger is wrapped in a transaction?  Maybe the trigger forces a transaction to occur?  Somehow, a BEGIN TRANSACTION is being generated and it fails because MS DTC cannot communicate with the remote Linux server.  That's a MS problem, not an Oracle issue.

                             

                            As regards replication, it's very easy to do in SQL Server using log shipping.  Google for how to do it.  Unfortunately, you can't replicate to Oracle using this method.  I would strongly recommend against replicating using triggers.  There are too many issues that need to be overcome should things go awry.  For example, what happens if the remote database is not available?  Your trigger will wait forever for the insert to occur.  You can put a timeout in, but then the data will be lost to the remote system.

                             

                            I must use transactions and haven't found a workaround.