Driver's SQLSetConnectAttr failed

[eb24]

I have some DTS packages that ALL fail when scheduled as jobs. I KNOW for a fact that it is not a permissions issue like the majority of DTS Job Failures out there. The specific DTS Jobs that are failing all have one thing in common: they all access different Oracle sources.

I made sure that the Oracle client tools are installed ont he SQL Server 2000 already and also the ODBC connections are there, but I am still receiving the following error:

Error = -2147467259 (80004005)
Error string: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0
Error: -2147467259 (80004005); Provider Error: 12154 (2F7A)
Error string: [Microsoft][ODBC driver for Oracle][Oracle]ORA-12154: TNS:cou... Process Exit Code 1. The step failed.

I am seeking assistance here because I have tried and searched all over the place for the solution (MS Site, Tek-Tips, and other forums), but no luck. Can anyone please tell me what is occurring here? It seems to be that the ODBC connections are not set on that machine, correct? Or is it something else? Just to make sure, I reconfigured those ODBC connections and the machine and then tried the Jobs again but they failed! Should I have restarted SQL Server after these simple ODBC changes?

I would greatly appreciate any help.

 

[marksham]

Hello,
By any chance did you find a resolution to this problem. I am experiencing the same issues and am enjoying no success in finding an answer. Any help you can give would be great.
Thanks

 

[eb24]

It's been a while since this happened to me, but if I do recall correctly, the solution was super easy. I believe the problem was that the TNSNAMES.ORA file did not have the entries I needed.

Hope this helps.

 

[marksham]

Thanks for the response. I did see some other articles that refer to the TNSNAMES.ORA file. However, on the server I am running this on, there is only one TNSNAMES.ORA and it has the correct settings. When I run the DTS Package from a command prompt it runs 100% OK, it's only in the scheduler it fails (very frustrating). I have since caved in and am using Windows scheduler to run it.
It may be that security is my problem, the interaction between the SQL and Oracle servers may have some Windows authenication thrown in for good measure, which alas is outside of my control.
Thanks again for your help.

 

[eb24]

When I was confronted with the aforementioned problem, I did plenty of research and I remember reading some documentation (presentation I believe) from some Microsoft people when a DTS package runs fine on a local machine, but fails via the Server when scheduled, there is a 99% chance that it is a permissions problem. Make sure the TNSNAMES.ORA file is an exact duplicate of the one hosted on your local host.

Hope this helps.