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

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!
dejandejanovicAsked:
Who is Participating?
 
sarabandeCommented:
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
 
dejandejanovicAuthor Commented:
Forgot to mention:
- I have also used Orace BI ODBC Client tool, and was also successfull connect.
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
CodeCruiserCommented:
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
 
dejandejanovicAuthor Commented:
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
 
dejandejanovicAuthor Commented:
@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
 
sarabandeCommented:
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
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
dejandejanovic can you share the extract error while you do the config using the above post i.e 39961480
0
 
dejandejanovicAuthor Commented:
Sure, will do today, latest tomorrow. I was busy this days, and could not review last messages.
0
 
dejandejanovicAuthor Commented:
@praveenckp:
Error:
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
0
 
sarabandeCommented:
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
 
dejandejanovicAuthor Commented:
Well, I have already post 39961480, a picture of ODBC driver under. You can check it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.