We have a third-party that feeds data to us. Their client application feeds directly to some source tables in our Oracle 10g database. We have triggers on these tables that sort and process the rows as they come in.
We have a new operation and we're trying to write some of this incoming data now to an SQL Server database via heterogeneous services - basically the same exact data in two databases. I have a linked database that works fine for selects, but I have never tried to write Oracle PL/SQL to write to an SQL Server 2008 DB. My first attempt was greeted with the following error: "ORA-02047: cannot join the distributed transaction in progress".
I found another thread where they say that the only way to do this is by using an autonomous transaction, but they don't give an example. Here is the section of the trigger I am using:
select to_char(new_date,'MM-DD-YYYY') into sql_txt from dual;
insert into mancamp_location@sqlweb
("UnitID", "ManCampID", "Lat", "Long", "UpdateDT", "VehSpeed", "VehDirection", "Landmark")
values (v_truck, f_unit, f_lat, f_long, sql_txt, f_spd, f_dir, f_ldmk);
Can someone point me at a way to accomplish this simple insert?