Remote Query failure Oracle to Non-Oracle

I am executing a sql query which will update the local table and upon update a trigger will fire to update the remote SQL server db table using DBlink.

Code:

---

SQL> update ps_len_tmp set nd_rank_title = 'COL' where emplid = '0028379';
update ps_len_tmp set nd_rank_title = 'COL' where emplid = '0028379'
*
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Transparent gateway for MSSQL]
ORA-02063: preceding 2 lines from TG4MSQL
ORA-06512: at "ADM.LENINSERT", line 6
ORA-04088: error during execution of trigger 'ADM.LENINSERT'

---

> DBlink is working
> ODBC connection is fine
> trigger is valid and working

application guys point this to db related error
where to start trouble shoot?

You did not give any database link connection in update statement

Do a tnsping to the sqlserver database and check if it is connecting.
If it is connecting then do a select * from ps_len_tmp@SQLSERVErlink

Quote:

---

Originally Posted by srt

You did not give any database link connection in update statement

---

Can you pl. read the thread again?

Trigger has it.

Quote:

---

Do a tnsping to the sqlserver database and check if it is connecting.
If it is connecting then do a select * from ps_len_tmp@SQLSERVErlink

---

Ping is working, Yes it is getting the result through direct call.

Post the trigger

Quote:

---

Originally Posted by srt

Post the trigger

---

Code:

---

CREATE OR REPLACE TRIGGER ADM.LENINSERT
AFTER INSERT OR UPDATE
ON ADM.PS_LEN_TMP
FOR EACH ROW
BEGIN
          IF :new.badge_expirdt is not null
          and :new.badge_categry is not null
          and :new.badge_categry <> ' ' THEN

                INSERT INTO UDO_TEMP@tg4msql
                              (PERSONNEL_ID,
                          FIRST_NAME,
                          LAST_NAME,
                          MIDDLE_NAME,
                          RANK_TITLE_CODE,
                          PERSONNEL_CATG_CODE,
                          BADGE_EXPIRATION_DATE,
                          PERSONNEL_CLEARANCE,
                          CLEARANCE_SCI,
                          UNEMPLID)
                VALUES
                          (:new.badge_number,
                          :new.first_name,
                          :new.last_name,
                          :new.middle_name,
                          :new.rank_title,
                          :new.badge_categry,
                          :new.badge_expirdt,
                          :new.clearance,
                          :new.sci,
                          :new.emplid); 
                END IF;
END ;

---

when the condition is met the trigger will fire, other wise the local update should happen which is failing.

did you turn on trace on your tg4msql and see what is happening there?

It may be that you need to see if new.badge_categry is 'empty'. Which would be something like and :new.badge_categry <> '', which in my limited understanding is not the same as null.

Quote:

---

Originally Posted by dbtoo

It may be that you need to see if new.badge_categry is 'empty'. Which would be something like and :new.badge_categry <> '', which in my limited understanding is not the same as null.

---

You are right Dbtoo, this column value when null or empty has to be satisfied for the trigger to fire, but the normal dml on the local table is not happening pertaining to the local table only execution as well all the satisfying conditions listed in the trigger.

What is your DB version? There is a bug in 10g where the updates would not happen on the remote database through the DBLINK when you fire an instead of trigger.

Quote:

---

Originally Posted by sambavan

What is your DB version? There is a bug in 10g where the updates would not happen on the remote database through the DBLINK when you fire an instead of trigger.

---

9206. thanks for the info!