Problem in Accessing SQL Server View from Oracle using tg4masql(ORACLE TRANSPARENT GATEWAY CONNECTIVITY)

• Lovkesh Anand

  SSC Rookie

  Points: 37

  More actions

  December 13, 2005 at 3:28 am

  #111026

  Dear All,

  I am using the oracle transparent gateway connectivity with sql server using tg4msql,as far as

  settings are concerned those were set and Connectivity is working Fine, and getting the response

  from that server.

  here is description what I done as:-There is a View on Sqlserver which is Join of 6 tables and have

  data around 1 million in 2 tables and 0.5 million in rest of the tables, as the Query for creating

  sqlserver view is given below:

  CREATE view Account_anila_test as

  SELECT ......(around 50 Columns)

  FROM GE_Init.dbo.Person INNER JOIN

  GE_Init.dbo.Konto ON GE_Init.dbo.Person.Person_ID =

  GE_Init.dbo.Konto.Person_ID INNER JOIN

  GE_Init.dbo.Produkt ON GE_Init.dbo.Konto.Produkt_ID =

  GE_Init.dbo.Produkt.Produkt_ID LEFT OUTER JOIN

  GE_Init.dbo.CRMKonto ON GE_Init.dbo.Konto.Konto_ID =

  GE_Init.dbo.CRMKonto.Konto_ID LEFT OUTER JOIN

  GE_Init.dbo.calcKontoOBSdt ON GE_Init.dbo.Konto.Konto_ID =

  GE_Init.dbo.calcKontoOBSdt.Konto_ID LEFT OUTER JOIN

  GE_Init.dbo.calcKonto ON GE_Init.dbo.Konto.Konto_ID =

  GE_Init.dbo.calcKonto.Konto_ID

  WHERE (GE_Init.dbo.Konto.SlettetKonto = 0) AND (GE_Init.dbo.CRMKonto.MarkertForSletting = 0

  OR GE_Init.dbo.CRMKonto.MarkertForSletting IS NULL)

  Now,I made a table in oracle adjacent to sqlserver View and Inserted data as:

  INSERT INTO ACCOUNT_TEST11(.....)

  SELECT .............

  FROM erson@dbl_getgc1">Person@dbl_getgc1 a INNER JOIN

  Konto@dbl_getgc1 b ON a."Person_ID" = b."Person_ID" INNER JOIN

  rodukt@dbl_getgc1">Produkt@dbl_getgc1 c ON b."Produkt_ID" = c."Produkt_ID" LEFT OUTER JOIN

  CRMKonto@dbl_getgc1 d ON b."Konto_ID" = d."Konto_ID" LEFT OUTER JOIN

  calcKontoOBSdt@dbl_getgc1 e ON b."Konto_ID" = e."Konto_ID" LEFT OUTER JOIN

  calcKonto@dbl_getgc1 f ON b."Konto_ID" = f."Konto_ID"

  WHERE (b."SlettetKonto" = 0) AND (d."MarkertForSletting" = 0 OR

  d."MarkertForSletting" IS NULL);

  This insert Satement worked fine,but when I inserted from the view already created in SQLSERVER as

  :-

  INSERT INTO ACCOUNT_TEST11(.....)

  SELECT .............

  FROM Account_anila_test@dbl_getgc1;

  This gave a following error:-

  "WEB_30_SUM", "WEB_360_COUNT", "WEB_360_SUM", "WEB_90_COUNT", "WEB_90_SUM"

  *

  ERROR at line 48:

  ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

  [Transparent gateway for MSSQL]

  ORA-02063: preceding 2 lines from DBL_GETGC1

  Elapsed: 00:01:01.04

  after this I set The paramater "HS_FDS_TRACE_LEVEL=ON" in INIt file of tg4msql Folder then in the

  trace File of tg4msql folder following error comes:-

  (0)

  (0) [Microsoft][ODBC SQL Server Driver]Timeout expired (SQL State: S1T00; SQL

  (0) Code: 0)

  (0)

  To resolve this issue I Changed the Remote Query Timeout=0(unlimited) through enterprise manager>serverGroup>Properties>connection Tab, but this dosen't worked.

  If any one has Faced the similar kind of problem and Can help me,Please let me Know where I am doing Wrong and How this error can be ractified.

  Waiting For Reply ASAP.

  Regards

  Lovkesh

   

   

   

   

   

   

   

   

   

   

   

• Ian Yates

  SSCoach

  Points: 19738

  More actions

  December 13, 2005 at 4:21 am

  #609163

  Please don't cross post - answer questions in this thread instead..

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=145&messageid=243743

  Helps keep everything together