DTS Connection Error - Oracle RDB Connection

Hi all,

I am trying very hard to run an existing DTS package.  The error I get is extremely unhelpful and appears as below -

'HResult of 0x8004005 (-2147467259) returned. Unexpected error occured. An error result was returned without an error message'

The package is pulling data from an Oracle RDB system. I have installed the lastest driver from the Oracle web site and have installed it successfully. I have created a DSN entry and have been able to click on the 'Test Connection' with a positive confirmation.

The driver I have installed is 'Oracle ODBC Driver for Rdb, Release 3.1.0.2 for Windows '.

What I don't understand is how it can appear be able to connect directly be accessing the DSN entry, but when I reference the DSN entry in a package it falls over with the message above.

My colleague has managed to get it working on an Win XP desktop and 2000 server machines. I am using the same OS with the same SP 1 without any success. It is also not working on other machines that include another 2000 server and 2003 server.

What am I missing!! Help!

Cheers
Darcy Wright
SuperBootAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nmcdermaidCommented:
Double check that the DSN actually exists.

The ODBC manager will let you go through and create a DSN test it etc. then not actually save it if you have don't have rights.

SO go back into the ODBC manager and ensure that the DSN exists.
SuperBootAuthor Commented:
I have created DSN entries via 'Admin tools->Data Sources'. I have also been able to successfully test the connection with the 'Test Connection' button.

I have also created the same DSN entry via the Connection object in the DTS package with the same successful result.

When I create a new connection in a package, I can choose the 'Oracle RDB Driver', it then provides a list of the DSN entries for that driver, I then select the one I want. That is a far as I get. If I either try and use that connection in an SQL Task or a Transformation it returns with the message I have documented above....

Important Point - It works unders Windows Server 2000 but not Window Server 2003 and mixed results for Windows XP SP1.

Could it have something to do with the version of MDAC? I have tried 2.8 and 2.8sp1 without any luck. Should I try an earlier version?
nmcdermaidCommented:
MDAC is a bit of a mess - even if you installed an older version it probably wouldn't work.

I'm just about out of ideas except maybe you could try to use a UDL (though usually the same thing happens)


1. Create a new text file
2. Rename the extension to UDL
3. Double click it
4. In the first pane, try both the native Oracle OLE DB driver (if it exists) and also try just the ODBC driver, using the DSN you have created
5. When done configuring, press OK
6. In the DTS use a connection of type UDL, and point it at your UDL file.

I'm not particularly confident that it'll work, but it may bring some more enlightening error messages to light.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

SuperBootAuthor Commented:
Thanks for your comments.

The tricky part is I am connecting to an Oracle RDB database not your standart Oracle 8i, 9i or 10g. Until recently I didn't even know it existed - it has a long history and Oracle purchase the product off another company in 1990's. See http://www.oracle.com/technology/products/rdb/htdocs/rdb7/rdb_statement_of_direction.html 

I guess my point is I can really only use the driver Oracle supplied. Having said that I have just tried what you suggested and did get some more information!


When I do a Test from within DTS using the UDL I get the following - 'Test connection failed because of an error in initializing provider. [Microsoft][ODBC Driver Manager] Drivers's SQLSetConnectSttr failed'

I will start investigating this - it is a little more helpful than the other message.

Thanks
nmcdermaidCommented:
Could be a red herring but fingers crossed!

Does the ODBC driver work if, for instance, you try to import data into Excel from it?
nmcdermaidCommented:
Clarification on my last post - open Excel and try to import data into Excel from the ODBC connection (don't use DTS at all) just to see if it works that way.


That link makes it sound fantastic..... if only the ODBC driver worked properly!!!!  ;)
SuperBootAuthor Commented:
I like your thinking, the only issue is I am not sure if I can do this using Excel.

5min later .... I have just tried your suggestion using Access and get the following error - 'ODBC--call failed'. [Oracle][ODBC][Rdb]%RDB-F-BAD_DB_HANDLE, invalid database handle (#-1)'

Interesting, not sure what it all means though... the message is a little more helpful!
SuperBootAuthor Commented:
It looks as though this provides some helpful insight (when in doubt read the help menu!) -

If the error message contains the [Rdb] prefix (as in the second example), it is not an Oracle Rdb Driver error. Note that although the error message contains the [Rdb] prefix, the actual error may be coming from one of several sources:

·      If the error message text starts with the following prefix, you can obtain more information about the error in the file SYS$HELP:rdb_msg.doc on the system on which Oracle Rdb is installed:

%RDB-

Therefore, it does not seem to be a driver error but an issue on the database side. I am waiting to hear back as to what the SYS$HELP:rdb_msg.doc file contains. Will post once I know.
SuperBootAuthor Commented:
I have installed the last oracle RDB Driver 3103 as it had a number of fixes. The Test Connection was one of them, it now displays the problem where previously it didn't, adding to the confusion.

The message is the same - [Oracle][ODBC][Rdb]%RDB-F-BAD_DB_HANDLE, invalid database handle (#-1).

Having read the SYS$HELP:rdb_msg.doc file it basically says the file handle is not what is expected. Going nowhere fast!

Explanation:   (RCI users) You must declare a variable as an explicit
               database handle and set this variable to zero before
               calling RDB_ATTACH_DATABASE or RDB_CREATE_DATABASE.
               You may not modify this variable after its value is set
               by the database system.

               (RDML and SQL users) Your program may have modified the
               value for the database handle (RDML) or authorization
               identifier (SQL) variable that is created by a
               precompiler.  This is unlikely unless you altered the
               language source file output by a precompiler.

User Action:   (RCI users) Make sure your program explicitly sets the
               variable for the database handle to zero before calling
               RDB_ATTACH_DATABASE or RDB_CREATE_DATABASE.

               (RCI, RDML, and SQL users) Make sure that your program
               is not changing the value of the variable for the
               database handle (or authorization identifier for SQL
               users).  If your program has not caused this error,
               please contact your Oracle support representative for
               assistance.
nmcdermaidCommented:
I think that you can safely ignore all that stuff. It has to be a symptom of something else.

Are you an administrator on the computer thats trying to connect?

Can you install Oracle tools and connect OK? (ie PL/SQL)

Anything strange about the database name - spaces or punctuation marks?
SuperBootAuthor Commented:
To answer your questions-

1. Yes, I have admin rights on my machine.
2. I can't even create a DSN entry that connects. I am trying to connect to Oracle RDB not Oracle 8i, 9i or 10g and therefore the tools you mention are not installed, and in my view, would not operate (eg. Client driver does not use TNSNames.ora file to connect - it uses an 'Attach Statement' value set in the DSN entry)
3. The database name, 'Attach Statement' is exactly the same as the Server that does connect successfully eg. ATTACH ‘FILENAME DKA2:[SIS_XXXX_DB.RDB_DB.SHS_MF]SHS_MF_DB’
SuperBootAuthor Commented:
I have found that the 3103 RDB driver did resolve my issue. I just needed to remove the existing driver (3102), remove all existing DSN entries and recreate using the new driver.

Thanks for your help nmcdermaid - I will try and award you some of the points.

Cheers
SuperBootAuthor Commented:
To the Administrator -

I have resolved my issue, however no-one contributed directly to the solution. If possible I would like to award nmcdermaid a proportion of the the points as he/she assisted to some degree. The final solution to the problem has been entered above this entry.

Regards
GranModCommented:
PAQed with points refunded (500)

GranMod
Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.