Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
4,775 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
ID: 39958697
Forgot to mention:
- I have also used Orace BI ODBC Client tool, and was also successfull connect.
0
 
LVL 12

Assisted Solution

by:Praveen Kumar Chandrashekatr
Praveen Kumar Chandrashekatr earned 400 total points
ID: 39959710
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 400 total points
ID: 39961227
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:dejandejanovic
ID: 39961480
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
ID: 39961512
@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 35

Assisted Solution

by:sarabande
sarabande earned 1200 total points
ID: 40074720
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
 
LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 40080497
dejandejanovic can you share the extract error while you do the config using the above post i.e 39961480
0
 

Author Comment

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

Author Comment

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

Assisted Solution

by:sarabande
sarabande earned 1200 total points
ID: 40083351
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
ID: 40085578
Well, I have already post 39961480, a picture of ODBC driver under. You can check it.
0
 
LVL 35

Accepted Solution

by:
sarabande earned 1200 total points
ID: 40085599
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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ā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

926 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