Trigger that inserts data into a linked server produces error

8 Sep 2005 4:27 PM

marco

All,

I am using SQL Server 2000 standard edition. I need to be able to insert
data from a SQL Server table into an Oracle table, via a linked server, when
data is inserted into the SQL Server table. Please see my example code below:

INSERT ORATest..TESTSCHEMA.ACCOUNT
    (
        ACCOUNT_ID ,
        ACCOUNT_LAST_NAME ,
        ACCOUNT_FIRST_NAME
    )

    Select    123123,
        'SMITH',
        'JOHN'

When I executed the above code in Query Analyzer it executes just fine and
the Oracle table is updated accordingly. The minute I put this insert
statement into a Trigger and fire it I receive the following error...

"The operation could not be performed because the OLE DB provider
'OraOLEDB.Oracle' was unable to begin a distributed transaction."

Any ideas??? I don't understand why this works just fine in QA but not when
executed within a trigger. Any info is appreciated...Thanks in advance.

Marco

8 Sep 2005 5:04 PM

Mark

Just make sure that MSDTC service is running on MS SQL Server.
HTH
Nitin

Show quote

"marco" wrote:

> All,
>
> I am using SQL Server 2000 standard edition. I need to be able to insert
> data from a SQL Server table into an Oracle table, via a linked server, when
> data is inserted into the SQL Server table. Please see my example code below:
>
> INSERT ORATest..TESTSCHEMA.ACCOUNT
>     (
>         ACCOUNT_ID ,
>         ACCOUNT_LAST_NAME ,
>         ACCOUNT_FIRST_NAME
>     )
>
>     Select    123123,
>         'SMITH',
>         'JOHN'
>
> When I executed the above code in Query Analyzer it executes just fine and
> the Oracle table is updated accordingly. The minute I put this insert
> statement into a Trigger and fire it I receive the following error...
>
> "The operation could not be performed because the OLE DB provider
> 'OraOLEDB.Oracle' was unable to begin a distributed transaction."
>
> Any ideas??? I don't understand why this works just fine in QA but not when
> executed within a trigger. Any info is appreciated...Thanks in advance.
>
> Marco
>
>

8 Sep 2005 5:12 PM

marco

Mark,

I forgot to include that in my original post. I already have the MSDTC
service running. Even if I didn't have that service running wouldn't it fail
across the board, not just with triggers? Any other ideas? It even works
fine if I put it inside a stored proc. I only run into the problem when I
put it in a trigger.

Thanks,

Marco

Show quote

"Mark" wrote:

> Just make sure that MSDTC service is running on MS SQL Server.
> HTH
> Nitin
>
> "marco" wrote:
>
> > All,
> >
> > I am using SQL Server 2000 standard edition. I need to be able to insert
> > data from a SQL Server table into an Oracle table, via a linked server, when
> > data is inserted into the SQL Server table. Please see my example code below:
> >
> > INSERT ORATest..TESTSCHEMA.ACCOUNT
> >     (
> >         ACCOUNT_ID ,
> >         ACCOUNT_LAST_NAME ,
> >         ACCOUNT_FIRST_NAME
> >     )
> >
> >     Select    123123,
> >         'SMITH',
> >         'JOHN'
> >
> > When I executed the above code in Query Analyzer it executes just fine and
> > the Oracle table is updated accordingly. The minute I put this insert
> > statement into a Trigger and fire it I receive the following error...
> >
> > "The operation could not be performed because the OLE DB provider
> > 'OraOLEDB.Oracle' was unable to begin a distributed transaction."
> >
> > Any ideas??? I don't understand why this works just fine in QA but not when
> > executed within a trigger. Any info is appreciated...Thanks in advance.
> >
> > Marco
> >
> >