Problem in Accessing Sql Server Views from Oracle Using TG4MSQL(Transparent gateway connectivity)
-
Hi All,
Please help me To resolve this Problem,it is as:-
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)
Then to resolve this I set the Remote query timeout =0(unlimited) in sql server through enterprise manager but this doesn'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
-
I would suggest that you wouldn't set remote query timeout at the SQL Server end - try setting it for the oracle gateway instead... Not sure where to set it - never used the oracle gateway (although a potential customer of ours wants us to integrate with their Oracle-based hospital billing system using the oracle transparent gateway - I look forward to seeing the solution to this in case I have the same issue!!
-
Hi,
I tried to find out the parameter related to query timeout in the tg4msql initialization file not been able to get it,as you said You'll follow up with Your Friend regarding the same,if any response please let me know.
Regards
Lovkesh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply