Re: Facing problem in update statement...when using Oracle Transparent Gateway

<lovkeshanand_at_gmail.com> schreef in bericht news:1137401065.950671.248510_at_f14g2000cwb.googlegroups.com...
> Dear All,
> I am facing Problem in Update Query when I am Updating
> the Oracle table "ACCOUNT" From the Sql Server Table "Account" using
> DBLINK ("@DBL_NO_GCMS" which itself refers to SQL SERVER Data Base
> Through Oracle Transparent Gateway Connectivity), as When I Fires the
> following Query :-
>
> UPDATE ACCOUNT A
> SET ("ACCOUNT_AGE","ACCOUNT_GROUP")= (SELECT
> B."Account_age",B."Balance"
> FROM Account_at_DBL_NO_GCMS B
> WHERE A."ACCOUNT_KEY"=B."Account_id" AND A."ACCOUNT_NO"= B."Account_no"
> AND A."CUSTOMER_KEY"=B."Customer_id" AND
> A."ACCOUNT_SOCIAL_SECURITY_NUMBER"=B."SSN"
> AND B."ETL_ACTION_FLAG"='U' and B."Account_id" <5)
> WHERE A."ACCOUNT_KEY"<5
>
> it gives the following error:-
>
> ERROR at line 47:
> ORA-28500: connection from ORACLE to a non-Oracle system returned this
> message:
> ORA-02063: preceding line from DBL_NO_GCMS
>
> I am not been able to find out the Cause of error as in trace file,it
> does not giving any cause or error number other than ORA-28500.
>
> as I fired the following Query It worked Fine:-
>
> UPDATE ACCOUNT A
> SET ("ACCOUNT_AGE")= (SELECT B."Account_age"
> FROM Account_at_DBL_NO_GCMS B
> WHERE A."ACCOUNT_KEY"=B."Account_id" AND A."ACCOUNT_NO"= B."Account_no"
> AND A."CUSTOMER_KEY"=B."Customer_id" AND
> A."ACCOUNT_SOCIAL_SECURITY_NUMBER"=B."SSN"
> AND B."ETL_ACTION_FLAG"='U' and B."Account_id" <5)
> WHERE A."ACCOUNT_KEY"<5
> SQL> /
>
> 4 rows updated.
>
> and
>
> UPDATE ACCOUNT A
> SET ("ACCOUNT_AGE","ACCOUNT_GROUP")= (SELECT
> B."Account_age",B."Balance"
> FROM Account_at_DBL_NO_GCMS B
> WHERE A."ACCOUNT_KEY"=B."Account_id" AND A."ACCOUNT_NO"= B."Account_no"
> AND A."CUSTOMER_KEY"=B."Customer_id" AND
> A."ACCOUNT_SOCIAL_SECURITY_NUMBER"=B."SSN"
> AND B."ETL_ACTION_FLAG"='U' and B."Account_id" =1)
> WHERE A."ACCOUNT_KEY"=1
> SQL> /
>
> 1 row updated.
>
> The above two Queries are Working fine.Please Look into the Problem and
> Kindly let me Know Where I am doing Wrong,as It is very Urgent,
>
> Thank you in advance.
>
> Regards
> Lovkesh
>

I have no experience with MS Sql Server, so I can't give you advice on the specific SQL statements.
I Have (limited) experience though with OTG for DB2. IMHO, there are a few things you could try:

1. Does the problematic SQL statement work correctly when directly executed against SQL Server, i.e. not via the Gateway?
2. As you stated you couldn't find any [MS-SQL] error information in a trace file: Check the setting of your Heterogeneous Services Initialization Parameters, more specific the one that determines whether tracing is set for the remote database (it is called something like HS_<????>_TRACE_LEVEL, check the Admin Guide for your specific OTG-platform). It should be enabled by setting it to 'ON'.
3. IIRC, the OTG Admin Guide contains a chapter on Problem Diagnosis. Maybe you will find more hints in there.

HTH.

-- 
Jeroen