4

I'm trying to connect to an Oracle 10g database using the built in Microsoft ODBC for Oracle driver. I want to use an dnsless connection, so I grab my connection string from www.connectionstrings.com.

Ideally I won't have to setup a DNS entry or an Oracle TNS entry, and I may be mistaken, but I thought the above would do that for me. I'm getting the following:

ERROR [NA000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-12514:
TNS:listener does not currently know of service requested in connect
descriptor

ERROR [IM006] [Microsoft][ODBC Driver Manager]
Driver's SQLSetConnectAttr failed

ERROR [01000] [Microsoft][ODBC Driver Manager] 
The driver doesn't support the version of ODBC behavior that the
application requested (see SQLSetEnvAttr)."

As far as I can tell online, other people have used the Microsoft ODBC for Oracle driver to connect to 10g, but maybe I'm missing something. I'm connecting from a vb.net application by the way.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Gaidin
  • 1,381
  • 3
  • 13
  • 19

3 Answers3

9

With 10g, you can use the EZCONNECT feature. To connect to an Oracle instance named ORCL on the server myServer, the connection string would look like this :

Driver={Microsoft ODBC for Oracle};Server=myServer:1521/ORCL;Uid=myUsername;Pwd=myPassword;
Mac
  • 8,191
  • 4
  • 40
  • 51
  • @Mac : I am getting same error when connecting via VBA. I am using cn.open method for connecting. How do i use your code in VBA ? – logan Jun 26 '12 at 08:39
2

I'm not sure how kosher it is to answer your own question, but I found a connection string that is more what I'm looking for:

"Driver={Microsoft ODBC for Oracle}; " & _
                                 "CONNECTSTRING=(DESCRIPTION=" & _
                                 "(ADDRESS=(PROTOCOL=TCP)" & _
                                 "(HOST=myserver)(PORT=1521))" & _
                                 "(CONNECT_DATA=(SERVICE_NAME=servicename))); " & _
                                 "uid=username;pwd=password;
Gaidin
  • 1,381
  • 3
  • 13
  • 19
0

You need to add an entry in your tnsnames.ora file for the service to which you want to connect. Alternatively, you can go to the gui interface for that file at

Start->Programs->Oracle->Configuration and Migration Tools->Net Configuration Assistant

and set up your oracle connection there. Then, ODBC will be able to resolve the service name.

JosephStyons
  • 57,317
  • 63
  • 160
  • 234
  • So you're saying the tnsnames.ora is required, and you can't get around it? Does that mean that the oracle driver install is required too, or will the Microsoft ODBC driver look in the tnsnames.ora file? – Gaidin Apr 23 '09 at 15:18
  • Yes, the ODBC is just a redirection of the oracle calls through a common Microsoft interface. You still have to install oracle drivers and set up the oracle connection that odbc will call for you. The advantage of using ODBC can be that your apps have a more consistent interface when looking at oracle, ms access, sql server, etc. – JosephStyons Apr 23 '09 at 15:29
  • @JosephStyons : I am able to connect oralce via SQL developer. but when i try connecting via VBA, it says [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed . How to resolve this ? – logan Jun 26 '12 at 08:44