Remote Query failure Oracle to Non-Oracle

[dbasan]

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.

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?

[srt]

You did not give any database link connection in update statement

[srt]

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

[dbasan]

You did not give any database link connection in update statement

Can you pl. read the thread again?

Trigger has it.

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.

[srt]

Post the trigger

[dbasan]

Post the trigger

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.

[dbtoo]

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

[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.

[dbasan]

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.

[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.