Intermittent Connection Issues With Linked Oracle Server

We have a linked Oracle server connected to our production SQL Server and it
works for the most part. However, we get the following intermittent message
when running a query from query analyzer:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12545: Connect failed because target
host or object does not exist
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Retrying the same query usually runs without error.
Any ideas on why these intermittent failures occur?
Larry Menzin
American Techsystems Corp.
DId you try to tnsping the oracle Server ?
Its a commandline utility brought by oracle and can be used with the syntac
tnsping [TnsnameoftheOracleServer].
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
Newsbeitrag news:5486E119-278B-4D2D-81E2-2B8621C61AE8@.microsoft.com...
> We have a linked Oracle server connected to our production SQL Server and
> it
> works for the most part. However, we get the following intermittent
> message
> when running a query from query analyzer:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: ORA-12545: Connect failed because
> target
> host or object does not exist
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> Retrying the same query usually runs without error.
> Any ideas on why these intermittent failures occur?
> --
> Larry Menzin
> American Techsystems Corp.
|||Tnsping is fine. I can connect via SQLPlus about 2 out of three times. Could
this be a time out issue? It is not a configuration issue since if I retry
the query a few times it works. Perhaps the connection latency over the WAN
causes the problem?
Larry Menzin
American Techsystems Corp.
"Jens Sü?meyer" wrote:

> DId you try to tnsping the oracle Server ?
> Its a commandline utility brought by oracle and can be used with the syntac
> tnsping [TnsnameoftheOracleServer].
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:5486E119-278B-4D2D-81E2-2B8621C61AE8@.microsoft.com...
>
>
|||OK, you didnt say that this is a WAN connected Oracle System. YOur
description really sounds like a timeout failure. Perhaps if there is a
running connection you should consider increasing the expire_time in
sqlnet.ora
http://www.adp-gmbh.ch/ora/network/sqlnet_ora.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
Newsbeitrag news:1D782E3B-B6C5-4912-B83E-A81A6B1CF873@.microsoft.com...[vbcol=seagreen]
> Tnsping is fine. I can connect via SQLPlus about 2 out of three times.
> Could
> this be a time out issue? It is not a configuration issue since if I retry
> the query a few times it works. Perhaps the connection latency over the
> WAN
> causes the problem?
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Jens Smeyer" wrote:
|||I believe it is an initial connection problem. When SQL Server uses linked
servers does the connection persist? Or does SQL Server make a new connection
every time an OPENQUERY is executed against Oracle? If I connect via SQLPlus
and leave it, the SQL> prompt remains and the connection is persistent. It
seems like SQL Server reconnects with Oracle after a set time period.
Larry Menzin
American Techsystems Corp.
"Jens Sü?meyer" wrote:

> OK, you didn′t say that this is a WAN connected Oracle System. YOur
> description really sounds like a timeout failure. Perhaps if there is a
> running connection you should consider increasing the expire_time in
> sqlnet.ora
> http://www.adp-gmbh.ch/ora/network/sqlnet_ora.html
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:1D782E3B-B6C5-4912-B83E-A81A6B1CF873@.microsoft.com...
>
>