SQL 2000 - DTS - Oracle

[drStealth]

Hello everyone, we have been puzzled long enough, now we are submitting to this group to solicit ideas.

We have an Oracle database with a couple of tables which we would like to export to SQL 2000. We set up a DTS, use ODBC to connect to Oracle, and ran the program. It worked. Great. Then, we schedule it. It fails. The job re-runs overnight. It works. Next night, it fails. and so on...

We reset it last Friday, and it bombed on Friday and Saturday but it ran perfectly on Sunday. We can't figure out why (1) the DTS works manually every time, but (2) will fail at times but work at other times when scheduled. We got similar results using OLE instead of ODBC.

We have other DTS applications (not from Oracle) which are working great. Are we missing anything in the set up? Is there something inherent to Oracle we need to know? Any and all thoughts are welcome. Thanks. drStealth

 

[RickCole]

Are you getting any error messages at all which might be of use to us???

Rick.

 

[drStealth]

Rick (and everyone),

Yes there is an error message in the job statement. It is:

Error: -2147467259[80004005]
Error string: [Microsoft][ODBC Driver Manager] Driver's SQLSet ConnectAttr Failed
Process Exit Code 3

These are the last lines of the job when it fails. This same job has failed twice and has run perfectly twice.

Our network people say there have been no changes made to the network or servers that may account for the problem.
We have suspected a problem with the ODBC driver but we don't think that's it because we are getting the same results using an OLE driver. Security / privileges may be an issue, but we are using an account with administrator privileges. Besides, why would the same DTS fail at times but run at other times if there were an issue with privileges?

Any thoughts would be appreciated. Thanks. drStealth

 

[RickCole]

Not sure if this could have anything to do with it but might be worth a look:

--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft ODBC for Oracle (Build 2.573.2927), version 2.5
Microsoft OLE DB Provider for Oracle, versions 2.0, 2.1, 2.5
Microsoft Data Access Components versions 2.1 SP2, 2.5, 2.6, 2.7

--------------------------------------------------------------------------------


SYMPTOMS
When you use aggregate functions like DECODE() on Oracle views derieved from tables with non-null columns, NULL values can be returned. Attempting to query the value of the column or trying to perform a MoveFirst causes the following error message:

Run-time error '-2147467259 (80004005)': The data provider or other service returned an E_FAIL status.
NOTE: This behavior occurs on Oracle 8.x servers, and not on Oracle 7.3.x servers.



CAUSE
This behavior occurs as a result of a problem in Oracle's client software. A description of the table from Oracle shows that a NULL value is not "OK" for the aggregated column, where the result value is NULL for the view.



RESOLUTION
To work around this behavior, use a server-side cursor.



MORE INFORMATION
Both the Microsoft OLE DB provider for Oracle and the Microsoft Open Database Connectivity (ODBC) for the Oracle driver (from MDAC 2.1.4202.3 or later) are used to reproduce the behavior. This behavior only occurs when you use a client-side cursor, and is not encountered when you use server-side cursors (for example, the NULL value is returned correctly).

 

[drStealth]

Rick, thanks for your input. I will run it by our Oracle guy and see what he has to say. Thanks for your time and effort to help us out. drStealth