Drivers SQLSetConnectAttr failed

Question

Drivers SQLSetConnectAttr failed

eb24

SSC Eights!

Points: 823
More actions
March 4, 2004 at 2:55 pm

#359284

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.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gary Andrews SSCommitted Points: 1639 More actions · Answer 1

First, I don't know the answer specifically.

Some ruminations. SQL Server has the ability to communicate (send/receive data) via serveral communications links. I.e. TCP/Ip, Named pipes, etc.

For a given server, you define what communications links that SQL server is going to support. Normally communication links not needed are not supported.

A client is in some ways similiar. Meaning you define on a client what communication links are going to be supported to SQL Server.

Is it possible that there is a mis-match on what communication link is being supported between the client and server?

HTHs

Gary

noeld SSC Guru Points: 96590 More actions · Answer 2

noeld

SSC Guru

Points: 96590

March 4, 2004 at 3:18 pm

#497424

How did you tested the ODBC DSN ?

* Noel

eb24 SSC Eights! Points: 823 More actions · Answer 3

Gary:

I don't think this is the problem since the client (my workstation) has the same Oracle Client Tools and ODBCs (communication links) as the server does. Is this what you mean by 'communication links'? My links are the ODBCs I have set.

Thanks.

zach_john SSCertifiable Points: 5922 More actions · Answer 4

Try doing a link server to the oracle server, see if you can get there from there. Could be a RPC issue

John Zacharkan

eb24 SSC Eights! Points: 823 More actions · Answer 5

noeld:

Excuse my ignorance, but how I can test the ODBC DSNs I have set for Oracle to make sure they do indeed work from the server?

Thanks.

noeld SSC Guru Points: 96590 More actions · Answer 6

1. Make sure they are set up as System DSN not User DSN

2. Create a linked server as expressed above by John Zacharkan

3. see if you can connect

* Noel

jwsanders Valued Member Points: 55 More actions · Answer 7

I am getting the same error message

(Error string: Error Code: 0 Error Source= Microsoft OLE DB Provider for Oracle Error Description: Error while trying to retrieve text for error ORA-12154 Help context: 4500 Error: -2147467259 (80004005);Provider Error: 12154 (2F7A)).

The DTS packages will run when executed from the design window. But not when run as a job. I have defined SYSTEM Data Sources Not User Data Sources.

Thanks

Jim Sanders

dlink SSC Eights! Points: 901 More actions · Answer 8

The data sources need to be set up on the server when run as a job. When running from the designer, the job processes on the local machine (where the designer is running).

HTH

eb24 SSC Eights! Points: 823 More actions · Answer 9

Thanks to everyone that has participated in this discussion in trying to solve my problem. I am a bit astonished as to the lack of documentation on setting linked servers via SQL Server to Oracle sources. I have perused numerous URLs and forums since early yesterday morning, and still can't seem to find a proper solution. I also have 3 MS Press books and one other one on SQL Server and all of these skim the subject of linked servers!

This is what I do know and have done (hopefully it'll help you experts out more in assisting me):

1. On my client PC's EM, I can successfully run DTS jobs that query both of my Oracle sources with no problems.

2. On my PC, with MS Access, I can successfully link to those Oracle sources via ODBC after I installed Oracle Client Tools. This is utilizing the driver Oracle73, version 2.05.03.01, company Oracle Corporation and file SQL32_73.DLL.

3. Once the jobs are scheduled, the jobs fail on the SQL Server because what is listed below (I believe):

4. I recently installed the same Oracle Client Tools and ODBCs onto the SQL Server, but haven't rebooted yet (will do this tonight). Is rebooting necessary for SQL Server to recognize the new Oracle installations? None of my references say to do this, but I read in one place on the web, thus I will try this.

5. On the server's EM, I picked the Linked Servers node and chose New Linked Server with the following settings:

a. Linked Server: DATA1.ORACLE

b. Provider name: Oracle Provider for OLE DB (is this correct??)

c. Product name: Oracle 7.3

d. Data source: I picked the same name that was located in the TNS.ORA file by the SID attribute, e.g. DATA123

e. Provider string: ??? don't know what exactly goes here; I have seen tons of examples, but don't know how to access this data; I have also heard of aliases for this attribute and don't know exactly what to do?

I have tried different combinations of attributes but nothing seems to work. I believe it is the Provider Name I am picking. Will rebooting the system have Oracle73 in the drop-down? How about the Provider String?

I would like to thank everyone who has taken the time to look at this thread and their attempt at assisting me. I greatly appreciate everyone's help.

Thanks!

dlink SSC Eights! Points: 901 More actions · Answer 10

If you're using DTS, you don't need linked servers. The linked servers are used for openquery and direct access SQL statements.

I don't know about the reboot, but it can't hurt.

Are you using the Oracle ODBC connection in the DTS package? If so, you need to configure the ODBC connections on the server the same way as they are configured on your working PC.

Good Luck!