We have a DTS pulling data from Oracle via Link server. The connection
between oracle and SQL seens not very good. Quite often, we will have
following error:

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:OLE DB provider 'MSDAORA' reported an error. The
provider did not give any information about the error. (Microsoft OLE DB
Provider for SQL Server (80040e14): OLE DB error trace [OLE/DB Provider
'MSDAORA' IDBInitialize::Initialize returned 0x80004005: The provider did
not give any information about the error.].)
Step Error code: 80040E14
Step Error Help File:
Step Error Help Context ID:0

Any way to track this problem ?

thanks, Lan

Hello LanLan,

This error code 0x80004005 is a little cryptic in that it covers a multitude
of sins. The most common would be that you do not have permissions to use
the resource or that the resource you requested is no longer there (Network
down or does not exist)

A lot of people I know using data between O and SQL Server use an export
to text file, FTP and either Bulk Insert or SQL*Loader

Allan

Hello Allan,

Since this error occurs intermittently. I would guess it is network problem.
I cannot change the DTS at the moment, no rights. Is there any script to
force / retry / ping the oracle until the connection is okay.

thanks, Lan

"Allan Mitchell" wrote:

Hello LanLan,

You could write an external process to do this but I have seen people advocate
issuing a dummy query against the remote source. No errors + result returned
== destination alive

Allan

Hello Allan,

Do you have such script ? On my test PC, I created a linked server, write
the following script, and then unplug the network cable. Theoretically, the
script should at leas try to loop three times, yet not.

DECLARE
@err int,
@rowcnt int,
@icount int

SET @icount = 0

CONN_LOOP:
IF @icount < 3
BEGIN
SELECT *
into UserLists
from LinkServer.DatabaseName.dbo.UserLists
SELECT @rowcnt=@@rowcount, @err = @@ERROR
PRINT ( 'loop: ' + cast(@ICOUNT as varchar(5)))
SET @icount = @icount + 1
IF (@@ERROR <>0 and @rowcnt <= 0 ) GOTO CONN_LOOP
END
ELSE
BEGIN
GOTO END_Conn
END


GOTO END_Conn

END_Conn:

"Allan Mitchell" wrote: