Very Computer

What I need to do, however, is an insert query on the linked server from
within a trigger.  When I try an insert query inside the trigger, I get
the following message:

Server: Msg 7399, Level 16, State 1, Procedure insMY_TABLE, Line 6
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: An Oracle Error occured, but message
could not be determined]

I am using 4-part names for the insert query ( i.e. INSERT INTO
OracleServer..Schema.Table).  I noted also that when the query is run
from inside the trigger, it goes through MSDTC--I can watch it abort
from the MSDTC Administrative Console.

I'm guessing that the the environment the query is running in from
inside the trigger is different than the one that Query Analyzer gives
me, which is why it's working in one place and not in another.  I'm not
quite sure where to start looking for problems (a slightly more
informative error message might have helped!), so if anyone has had
similar problems, or has experience with this kind of thing, I would
appreciate any help or insights you might have.

---------
Ryan May
Systems Coordinator
Xactware, Inc.

I don't have an answer for you, but possibly "narrow down your search path".

I can only assume that SQL Server need to do the INSERT inside a distributed
transaction when it is executed through a trigger. The code in a trigger is
"protected" by a transaction automatically. The call to MSDTC seem to
confirm it.

In order for this to work, the OLEDB provider has to support
ITransactionJoin. Check out doc's for your OLEDB provider if it supports
that interface.

You might need a transaction monitor at the other end (just as you use MSTDC
at your end), but I'm not sure about this.

You can search Books Online for "ITransactionJoin", and find some reading.
There might be some KB articles as well..
--
Tibor Karaszi
MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please do not respond by e-mail unless explicitly asked for.