February 3, 2006 at 3:48 am
Hi,
I'm having some serious issues with some DTS jobs not working. These DTS jobs all pull data out of an Oracle database and pump it into a SQL database.
They are all failing on connecting to the Oracle database. If I use the Oracle Tools (SQL Plus) I can connect to the Oracle database. But if I try to connect to the Oracle database via a DTS package it fails.
If I use the native Oracle OLE DB Provider it works if I execute the package manually, if I try to then schedule the task and it fails running on a schedule. The user used to run the task manually and the user that SQL Server Agent starts under is the same. It fails with the Error TNS No Listener, which is not accurate because the listener is up on the Oracle database (otherwise the manual execution would not work).
If I use the Microsoft OLE DB Provider for Oracle it doesn't work at all.
The generic error message I get in both cases is error number '80004005', this is a stock standard ODBC error code it gives when there is no info on the error.
If anyone can help, it would be much appreciated. We've had 2 Oracle DBA's and myself the SQL DBA working on this and we're stuck...
HELP!!!
February 6, 2006 at 8:00 am
This was removed by the editor as SPAM
February 6, 2006 at 9:56 am
Have you made certain that the Oracle Client is installed on the SQL Server from which the dts package is run -- as well as having an ODBC connection set up on the server?
February 6, 2006 at 11:34 pm
The Oracle client is installed. The issue is with the ODBC drivers. It works using the Oracle drivers, but doesn't work using the Microsoft drivers for Oracle. Which is a pain in the ass when you have 20 plus dts packages setup using the Microsoft ODBC for Oracle....
Ended up resolving the issue with some registry entry changes, one of our Oracle dba's found the solution on the Oracle Metalink support site. Essentially what it looks like is that there were some legacy registry entries pointing the Oracle home directory to a folder that doesn't exist. I'm guessing the Oracle provider doesn't use these entries, but the Microsoft driver does...
Anyway, the problem is resolved, after alot of stressing and sweat...
February 10, 2006 at 7:04 am
Be aware that once Oracle Client is installed on the SQL box then the server needs rebooting before it's fully recognised by SQL. I believe that this is to do with registry entries.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply