?
Solved

DTS Connection Error - Oracle RDB Connection

Posted on 2006-03-20
16
Medium Priority
?
1,748 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:SuperBoot
  • 8
  • 5
14 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16242944
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.
0
 

Author Comment

by:SuperBoot
ID: 16244058
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?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16245059
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.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:SuperBoot
ID: 16252709
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
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16253303
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?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16253317
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!!!!  ;)
0
 

Author Comment

by:SuperBoot
ID: 16263756
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!
0
 

Author Comment

by:SuperBoot
ID: 16265912
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.
0
 

Author Comment

by:SuperBoot
ID: 16338135
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.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16340556
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?
0
 

Author Comment

by:SuperBoot
ID: 16356765
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’
0
 

Author Comment

by:SuperBoot
ID: 16356973
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
0
 

Author Comment

by:SuperBoot
ID: 16473961
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
0
 

Accepted Solution

by:
GranMod earned 0 total points
ID: 16869280
PAQed with points refunded (500)

GranMod
Community Support Moderator
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question