By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,948 Members | 1,595 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,948 IT Pros & Developers. It's quick & easy.

Problem in Accessing SQL Server Views from Oracle Using TG4MSQL(Transparent gateway Connectivity)

P: n/a
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 Person@dbl_getgc1 a INNER JOIN
Konto@dbl_getgc1 b ON a."Person_ID" = b."Person_ID" INNER JOIN
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)

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

Dec 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
(lo**********@gmail.com) writes:
Now,I made a table in oracle adjacent to sqlserver View and Inserted
data as:
...

This insert Satement worked fine,but when I inserted from the view
already created in SQLSERVER as
Do I understand it correctly that the query for the first INSERT
was a simplified version of the view? The real view appears to use
more tables.
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)


The ODBC SQL Server Driver has a default timeout of 30 seconds, as have
several other client APIs. (Don't ask me why; I think it's stupid.) I
have no idea whether you can control it from Oracle.

If you run a SELECT * from the view, on SQL Server how long time does
it take? You may have to look into tuning the query.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.