Solved

Microsoft ODBC for Oracle: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

Posted on 2014-03-27
13
4,241 Views
Last Modified: 2014-05-30
Hello,
I have got an authentification information from my Corp to connect on Oralce database through Oracle. Corp is using special script through website, which automatically download sources directly to odbcad32.exe.
I have start testing two connections:
1. Simple way: through MSAccess >> External Data linking ODBC.
*Result: Successfull. I was just need to pickup ODBC name, and just enter username & password >> and successful got a list of tables.

2. More complex way with visual studio, behind code of WindowsApplication project, either vb.net/c#, with below code, but getting error.
*Error result: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

  Try
            Dim cn As OdbcConnection
            cn = New OdbcConnection("Driver = {Microsoft ODBC for Oracle};Server=Siebel Analytics DIR - Prod;uid=xxxx;pwd=xxxx")
            'Dim myString As String = "Select * from TestTable"
            'Dim cmd As OdbcCommand = New OdbcCommand(myString)
            cn.Open()
            MsgBox("Connected")
            cn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Open in new window


3. I have also try with System.Data.OracleClient reference, but:
*result: obsolete technic

4. After posting this help I have found 3rd party tool, meaning Oracle solution Oracle.DataAccess references.

Please, need someone for help, as I do not have experience with Oracle connection strings.

Thanks in advance for help!
0
Comment
Question by:dejandejanovic
  • 6
  • 3
  • 2
  • +1
13 Comments
 

Author Comment

by:dejandejanovic
Comment Utility
Forgot to mention:
- I have also used Orace BI ODBC Client tool, and was also successfull connect.
0
 
LVL 12

Assisted Solution

by:praveencpk
praveencpk earned 100 total points
Comment Utility
Always in these type of connection there will be a problem of driver version i.e 32 bit or 64 bit drivers. So confirm that you are using the right odbc driver for connection.

here are some of the example on vb.net connection to database using odbc drivers.

http://www.carlprothman.net/Technology/ConnectionStrings/NETDataProviders/tabid/86/Default.aspx

http://msdn.microsoft.com/en-us/library/aa984313(v=vs.71).aspx
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 100 total points
Comment Utility
You said you have got Oracle provider for .NET so why are you using ODBC? Try with ODP and check below link for example of connection string

http://www.connectionstrings.com/oracle-data-provider-for-net-odp-net/
0
 

Author Comment

by:dejandejanovic
Comment Utility
I have try to use below code, linked posted by: praveencpk, but getting errors. Check attached printscreen.

Dim oODBCConnection As OdbcConnection
            Dim sConnString As String = _
                "Driver={Microsoft ODBC for Oracle};" & _
                "Server=Siebel Analytics DIR - Prod;" & _
                "Uid=xxxx;" & _
                "Pwd=xxxx"
            oODBCConnection = New Odbc.OdbcConnection(sConnString)
            oODBCConnection.Open()

Open in new window

Capture.PNG
0
 

Author Comment

by:dejandejanovic
Comment Utility
@CodeCruiser:
I have already try with ODP, but major problem is error:
"ORA-12154: TNS:could not resolve the connect identifier specified."

But, I have also found this connection string standard. But I have not idea what shouldI put for HOST, or PORT?

I have add printscreen of my company ODBC connection, which I already describe in question that I do not have problem connecting using MSAccess, or Oracle Client.
If you have experience, can you show me by printscreen if those information can be entered into connection string?

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));
User Id=myUsername;Password=myPassword;

Open in new window

Capture.PNG
0
 
LVL 32

Assisted Solution

by:sarabande
sarabande earned 300 total points
Comment Utility
ORA-12154: TNS:could not resolve the connect identifier specified.
generally, any oracle connection can be done only with valid TNS entries. these entries are stored in a text file named tnsnames.ora

The tnsnames.ora can be found (normally) below a folder like \oracle\product\10.2.0\client_1\network\admin . the folder was created when you install the oracle client on your windows machine. the way to go is to get an oracle client which fits to the oracle db you want to connect to. if the db is at a local server, you could/should use the client that comes with the server. you also should install the odbc client from the oracle setup if available. in any case when you installed the oracle client you would get an assistant where you could configure the client access and which would write a correct entry to the tnsnames.ora. you also should provide or have environment variables for TNS_ADMIN (path to tnsnames.ora) and ORACLE_HOME (path to oracle client).

see http://kb.tableausoftware.com/articles/knowledgebase/oracle-connection for detailed information how to setup a working environment on windows platform for accessing oracle db.

if you installed an odbc driver from oracle (actually, I never had success when using a microsoft oracle odbc driver), then you should be able to add a new "datasource" using the odbc applet in system settings (or run odbcad32.exe) which then could be used for odbc connection string.

Sara
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 12

Expert Comment

by:praveencpk
Comment Utility
dejandejanovic can you share the extract error while you do the config using the above post i.e 39961480
0
 

Author Comment

by:dejandejanovic
Comment Utility
Sure, will do today, latest tomorrow. I was busy this days, and could not review last messages.
0
 

Author Comment

by:dejandejanovic
Comment Utility
@praveenckp:
Error:
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
0
 
LVL 32

Assisted Solution

by:sarabande
sarabande earned 300 total points
Comment Utility
type WIN+R and enter odbcad32. then define a datasource in the system dsn tab using the oracle odbc driver.

if the datasource is well-defined you could use it in the odbc connection string for connecting to the oracle db.

Sara
0
 

Author Comment

by:dejandejanovic
Comment Utility
Well, I have already post 39961480, a picture of ODBC driver under. You can check it.
0
 
LVL 32

Accepted Solution

by:
sarabande earned 300 total points
Comment Utility
the applet shown in the image is an oracle server applet. as far as I seen from your previous posts you try to use a Microsoft odbc driver for oracle from a client and not from a server.

that hardly fits together.

I strongly would recommend to install an oracle client with oracle odbc driver at your windows client. then use the wizard that came with that client to create a windows datasource for odbc based on the oracle odbc driver. is also would recommend to use a single word without spaces, dashes, slashes, dots as datasource name. if the datasource is made properly, your connect string would need only the datasource name (and username and password if not already configured in the datasource). see

http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_odbc.htm#BABDBDJG

for more details.

note, it is also possible to create a datasource by connect string on the fly but this is much more difficult and success depends on a properly oracle and odbc environment as well.

Sara
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now