March 20, 2006 at 5:07 pm
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
March 21, 2006 at 7:11 am
Are you running it as a manual process or as a scheduled job?
March 21, 2006 at 3:17 pm
I can run it as both, providing it is on the server that functions correctly with the driver (eg. Win 2000 server but not Win 2003 server).
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 as 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?
March 21, 2006 at 3:36 pm
Can you check the permissions for the accounts running the task or transformation on the working machine and make sure you're using the same accounts on the non-working machine? Oracle got fussy with accounts running jobs a while back, and it caused us all sorts of problems.
To bypass it, we had to use commandline dtsrun and windows at scheduler on an allocated machine, instead of using SQL Server Jobs scheduling. You may be having some of the same issues -- the accounts on the new machine don't have sufficient permissions on the Oracle box for Oracle to think it should do the activity.
To find this out took me several months and a lot of hair pulling.
March 21, 2006 at 3:48 pm
Hmmm.. you have got me thinking. I am trying to workout where the permissions come into effect.
We have one account (oracle) eg joeblogs/mypass that is used in the DSN entry on all machines to login to the Oracle RDB box. I thought that would be the only account I would need to worry about assuming I am not trying to schedule the package as a job.... am I correct on that assumption?
Are you suggesting the network account could influence the ability to connect to Oracle RDB? This doesn't make sense though as when I create the DSN entry and use the inbuilt connection tester it connects with a problem on all machines using the joeblogs/mypass credentials.....
March 21, 2006 at 4:22 pm
The problem is that scheduled jobs run under the account that registered the SQL Server, not under the dbo account, not under the account in the connection string, etc. This is what messed me up -- there was no way to even know who registered our SQL Server, and no way to re-register it with an account that our Oracle group would grant permissions to.
It sounds like that machine that works was registered using the joeblogs account and the one that doesn't was registered by somebody else.
This is what ended up working for us. It may or may not work for you.
Please try running the job in this fashion: Create a .bat file with a line in it like:
DTSRUN /S "DVTAP443" /E /N "MyDTS"
where "dvtap443" is the name of your server and "MyDTS" is the package, and the rest is the same. This will force Oracle to use the user data in the connection string rather than the user data in the originating job.
Then, run the bat file. You should get a dos window with one line for each step in your dts package that runs (if it does).
If this works, then you can use Windows AT Scheduler to run the jobs at specific days and times (let me know and I'll give you the dos commands to set up the schedule using system accounts instead of requiring a specific user being logged onto that machine).
March 22, 2006 at 11:59 am
Another thing you might try is a different driver. I normally end up connecting to Oracle databases using "Other (ODBC Data Source)" or "Microsoft ODBC Driver for Oracle". Never had much luck using any version of the Oracle drivers in DTS or linked servers.
March 22, 2006 at 10:21 pm
I have just tried connecting to the database using Access and get the following error - 'ODBC--call failed'. [Oracle][ODBC][Rdb]%RDB-F-BAD_DB_HANDLE, invalid database handle (#-1)'
This is a little more helpful. I have since read the help file (yes that is a good idea!) and here is what it says....
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.
Cheers
April 2, 2006 at 6:53 pm
I downloaded the new driver (released last week) and have found that the 3103 RDB driver resolved my issue. I just needed to remove the existing driver (3102), remove all existing DSN entries and recreate using the new driver.
Cheers
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply