Remote Query failure Oracle to Non-Oracle
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Remote Query failure Oracle to Non-Oracle

  1. #1
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650

    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?
    "What is past is PROLOGUE"

  2. #2
    Join Date
    Nov 2003
    Posts
    89
    You did not give any database link connection in update statement

  3. #3
    Join Date
    Nov 2003
    Posts
    89
    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

  4. #4
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    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.

    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.
    "What is past is PROLOGUE"

  5. #5
    Join Date
    Nov 2003
    Posts
    89
    Post the trigger

  6. #6
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    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.
    "What is past is PROLOGUE"

  7. #7
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131
    did you turn on trace on your tg4msql and see what is happening there?

  8. #8
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131
    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.

  9. #9
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    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 past is PROLOGUE"

  10. #10
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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.
    Thanx
    Sam



    Life is a journey, not a destination!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width