OraFAQ Forum: Networking and Gateways » Network Connection via ODBC

Home » RDBMS Server » Networking and Gateways » Network Connection via ODBC

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Network Connection via ODBC [message #210167]

Tue, 19 December 2006 15:12

jmontgomery
Messages: 9
Registered: December 2006

Junior Member

I have a 9i installation that works fine on the local machine with a Visual Studio 2005 application. The connection string is:

"Provider=MSDAORA;DSN=Oracle9i;UID=scott;PWD=tiger;Data Source=corvette"

When I attempt to run the application from a networked machine I get an execption saying:

"ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

When I attempt to setup a DSN on the client machine I get an error saying:

Oracle client & networking components are not installed

I believe I need to install these components, but don't know how.

Can I use the existing 9i installation to install these components? If so, please give me some guidance.

TIA,

Joe

Report message to a moderator

Re: Network Connection via ODBC [message #210182 is a reply to message #210167]

Tue, 19 December 2006 16:08

ebrian
Messages: 2794
Registered: April 2006

Senior Member

If you are on at least 9.2.0, you can consider the Oracle Instant Client. If not, you'll need to install the ODBC components from the Oracle client CD.

Report message to a moderator

Re: Network Connection via ODBC [message #210353 is a reply to message #210182]

Wed, 20 December 2006 08:27

jmontgomery
Messages: 9
Registered: December 2006

Junior Member

Instant client looked like the easy answer -- I do have 9.2 -- so
I downloaded it & unzipped. Ended up with 6 dlls & 2 jar files.
What do I do with these? There are no istallation instructions.

Anyway, tried to use the 9.2 CD to install client & networking components, but get the error msg that 9.2 & vista are not compatible.

Bottom line, any help will be appreciated. TIA

Report message to a moderator

Re: Network Connection via ODBC [message #210363 is a reply to message #210353]

Wed, 20 December 2006 08:38

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Here are the install instructions that were listed on the Instant Client page:

Installation Instructions

Installation Steps:

1. Download the appropriate Instant Client packages for your platform. All installations REQUIRE the Basic or Basic Lite package.

2. Unzip the packages into a single directory such as "instantclient".

3. Set the library loading path in your environment to the directory in Step 2 ("instantclient"). On many UNIX platforms, LD_LIBRARY_PATH is the appropriate environment variable. On Windows, PATH should be used.

4. Start your application and enjoy.

Report message to a moderator

Re: Network Connection via ODBC [message #210437 is a reply to message #210363]

Wed, 20 December 2006 17:04

jmontgomery
Messages: 9
Registered: December 2006

Junior Member

Tnx for the instructions. I set path & am now able to configure a DSN called Oracle with the server name of corvette -- the machine where Oracle is hosted. (The DSN is located on a networked machine & I am sure the network conn is good.)

TIA for any further help.

Below is the pertinent info:

The connection string is:

string connection = "Driver={Microsoft ODBC for Oracle};DSN=Oracle;Uid=scott;Pwd=tiger";

A different error is now generated:

ERROR [NA000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-12560: TNS:protocol adapter error
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).

File Contents on machine corvette are:

# LISTENER.ORA Network Configuration File: C:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Corvette)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = Oracle1)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = Oracle1)
)
)

# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLE1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = corvette)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = Oracle1)
)
)

INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Corvette)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

Report message to a moderator

Re: Network Connection via ODBC [message #210442 is a reply to message #210437]

Wed, 20 December 2006 18:57

ebrian
Messages: 2794
Registered: April 2006

Senior Member

A tnsnames.ora file will be needed for the instant client side too. Here is the Oracle ODBC Instant Client Release Notes which should provide additional details not provided on the Oracle Instant client page itself.

HTH

Report message to a moderator

Re: Network Connection via ODBC [message #210680 is a reply to message #210442]

Thu, 21 December 2006 14:22

jmontgomery
Messages: 9
Registered: December 2006

Junior Member

I read the release notes & set TNS_ADMIN to c:\oracle\instantclient where all the files are located on the client machine. Copied the tnsnames.ora from the server into the same directory. (See contents of tnasnames.ora in prior post)

Still no connection. The error msg is still 12560.

Does the tnsnames.ora file need any changes when placed on the client side?

TIA for any help,

Report message to a moderator

Re: Network Connection via ODBC [message #210684 is a reply to message #210680]

Thu, 21 December 2006 15:06

ebrian
Messages: 2794
Registered: April 2006

Senior Member

How did you set the PATH and TNS_ADMIN variables ?

You can try to enable tracing for ODBC and try to make the connection again. This may provide additional details.

Report message to a moderator

Re: Network Connection via ODBC [message #210688 is a reply to message #210684]

Thu, 21 December 2006 16:06

jmontgomery
Messages: 9
Registered: December 2006

Junior Member

I set PATH & TNS_ADMIN via control panel -> system -> environment variables. For path, I added c:\oracle\instantclient
to the end of prior path settings. For TNS_ADMIN, I added the variable pointing c:\oracle\instantclient.

I turned on tracing. Below is the log file.

Although I don't know how to read this, the notations
about login-timeout & invalid string length are something
I would guess are not good. However, I'm not sure what to
do about either. Does ODBC support a connection timeout
parameter?

TIA for any help,

*******************************************

connect.vshost 9a8-ea4 ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 00000000
SQLHANDLE * 013E5518

connect.vshost 9a8-ea4 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 00000000
SQLHANDLE * 0x013E5518 ( 0x05997ed0)

connect.vshost 9a8-ea4 ENTER SQLSetEnvAttr
SQLHENV 05997ED0
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 0x00000003
SQLINTEGER -6

connect.vshost 9a8-ea4 EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
SQLHENV 05997ED0
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 0x00000003 (BADMEM)
SQLINTEGER -6

connect.vshost 9a8-ea4 ENTER SQLSetEnvAttr
SQLHENV 05997ED0
SQLINTEGER 201 <unknown>
SQLPOINTER [Unknown attribute 201]
SQLINTEGER -6

connect.vshost 9a8-ea4 EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
SQLHENV 05997ED0
SQLINTEGER 201 <unknown>
SQLPOINTER [Unknown attribute 201]
SQLINTEGER -6

connect.vshost 9a8-ea4 ENTER SQLAllocHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 05997ED0
SQLHANDLE * 013E55A8

connect.vshost 9a8-ea4 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 05997ED0
SQLHANDLE * 0x013E55A8 ( 0x05997f48)

connect.vshost 9a8-ea4 ENTER SQLSetConnectAttrW
SQLHDBC 05997F48
SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>
SQLPOINTER 0x0000000F
SQLINTEGER -5

connect.vshost 9a8-ea4 EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS)
SQLHDBC 05997F48
SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>
SQLPOINTER 0x0000000F (BADMEM)
SQLINTEGER -5

connect.vshost 9a8-ea4 ENTER SQLDriverConnectW
HDBC 05997F48
HWND 00000000
WCHAR * 0x6DD3C630 [ -3] <Invalid string length!>
SWORD -3
WCHAR * 0x6DD3C630
SWORD -3
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>

connect.vshost 9a8-ea4 EXIT SQLDriverConnectW with return code -1 (SQL_ERROR)
HDBC 05997F48
HWND 00000000
WCHAR * 0x6DD3C630 [ -3] <Invalid string length!>
SWORD -3
WCHAR * 0x6DD3C630
SWORD -3
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>

DIAG [NA000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-12560: TNS:protocol adapter error (12560)

DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed (0)

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

connect.vshost 9a8-ea4 ENTER SQLGetDiagRecW
SQLSMALLINT 2
SQLHANDLE 05997F48
SQLSMALLINT 1
SQLWCHAR * 0x05555DA0 (NYI)
SQLINTEGER * 0x0527E2EC
SQLWCHAR * 0x05555598 (NYI)
SQLSMALLINT 1024
SQLSMALLINT * 0x0527E2E8

connect.vshost 9a8-ea4 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2
SQLHANDLE 05997F48
SQLSMALLINT 1
SQLWCHAR * 0x05555DA0 (NYI)
SQLINTEGER * 0x0527E2EC (12560)
SQLWCHAR * 0x05555598 (NYI)
SQLSMALLINT 1024
SQLSMALLINT * 0x0527E2E8 (80)

connect.vshost 9a8-ea4 ENTER SQLGetDiagRecW
SQLSMALLINT 2
SQLHANDLE 05997F48
SQLSMALLINT 2
SQLWCHAR * 0x05555DA0 (NYI)
SQLINTEGER * 0x0527E2EC
SQLWCHAR * 0x05555598 (NYI)
SQLSMALLINT 1024
SQLSMALLINT * 0x0527E2E8

connect.vshost 9a8-ea4 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2
SQLHANDLE 05997F48
SQLSMALLINT 2
SQLWCHAR * 0x05555DA0 (NYI)
SQLINTEGER * 0x0527E2EC (0)
SQLWCHAR * 0x05555598 (NYI)
SQLSMALLINT 1024
SQLSMALLINT * 0x0527E2E8 (66)

connect.vshost 9a8-ea4 ENTER SQLGetDiagRecW
SQLSMALLINT 2
SQLHANDLE 05997F48
SQLSMALLINT 3
SQLWCHAR * 0x05555DA0 (NYI)
SQLINTEGER * 0x0527E2EC
SQLWCHAR * 0x05555598 (NYI)
SQLSMALLINT 1024
SQLSMALLINT * 0x0527E2E8

connect.vshost 9a8-ea4 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2
SQLHANDLE 05997F48
SQLSMALLINT 3
SQLWCHAR * 0x05555DA0 (NYI)
SQLINTEGER * 0x0527E2EC (0)
SQLWCHAR * 0x05555598 (NYI)
SQLSMALLINT 1024
SQLSMALLINT * 0x0527E2E8 (140)

connect.vshost 9a8-ea4 ENTER SQLGetDiagRecW
SQLSMALLINT 2
SQLHANDLE 05997F48
SQLSMALLINT 4
SQLWCHAR * 0x05555DA0 (NYI)
SQLINTEGER * 0x0527E2EC
SQLWCHAR * 0x05555598 (NYI)
SQLSMALLINT 1024
SQLSMALLINT * 0x0527E2E8

connect.vshost 9a8-ea4 EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)
SQLSMALLINT 2
SQLHANDLE 05997F48
SQLSMALLINT 4
SQLWCHAR * 0x05555DA0 (NYI)
SQLINTEGER * 0x0527E2EC
SQLWCHAR * 0x05555598 (NYI)
SQLSMALLINT 1024
SQLSMALLINT * 0x0527E2E8

connect.vshost 9a8-c1c ENTER SQLFreeHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 05997F48

connect.vshost 9a8-c1c EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 05997F48

connect.vshost 9a8-c1c ENTER SQLFreeHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 05997ED0

connect.vshost 9a8-c1c EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 05997ED0

Report message to a moderator

Re: Network Connection via ODBC [message #210691 is a reply to message #210688]

Thu, 21 December 2006 16:21

ebrian
Messages: 2794
Registered: April 2006

Senior Member

As a quick test, before we dig too deep into the trace file. Can you try to put the Oracle path as the first element in your PATH statement and then retry the connection ?

Report message to a moderator

Re: Network Connection via ODBC [message #210692 is a reply to message #210691]

Thu, 21 December 2006 16:42

jmontgomery
Messages: 9
Registered: December 2006

Junior Member

Tried moving the oracle path to the beginning of PATH, but with
the same error msg.

Tnx,

Report message to a moderator

Re: Network Connection via ODBC [message #210696 is a reply to message #210692]

Thu, 21 December 2006 17:42

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Couple things:

1. Did you happen to reboot after you made the PATH changes?
(if you pull up a command prompt and type PATH, do you see the correct path listed?)

2. Do you see another ORACLE ODBC as a "data source" when you click on Add under System DSN in the ODBC Administrator?

Report message to a moderator

Re: Network Connection via ODBC [message #210698 is a reply to message #210696]

Thu, 21 December 2006 18:07

jmontgomery
Messages: 9
Registered: December 2006

Junior Member

I did check path in a cmd prompt window -- it was good -- I did
reboot. Still same 12560 error.

The only reference to Oracle as an OBDC data source is the one
named -- Microsoft ODBC for Oracle. I was expecting to see a second reference to Oracle after running obdc_install.exe, but
there is none.

Report message to a moderator

Re: Network Connection via ODBC [message #210837 is a reply to message #210698]

Fri, 22 December 2006 10:54

jmontgomery
Messages: 9
Registered: December 2006

Junior Member

Problem found & now success.

The connection string needs to contain a parameter:

server=Oracle1

The correct string is:

string connection = "Driver={Microsoft ODBC for Oracle};server=Oracle1;Uid=scott;Pwd=tiger";

Oracle1 is defined in the tnsnames.ora file that resides in c:\oracle\instantclient. This file is just a copy of the same file from the server.

Thanks for all the help

Report message to a moderator

Re: Network Connection via ODBC [message #210848 is a reply to message #210837]

Fri, 22 December 2006 11:30

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Glad to see you got it resolved.

It's strange though that you aren't seeing the Oracle Instant Client version of the ODBC in the data source window. It appears that you are using the Microsoft ODBC version to connect to Oracle. Did you install the Instant Client Basic or Basic Lite ? The Basic Lite seems to have issues. When I used the Basic version, the install populated the data source window with the Oracle Instant Client ODBC version.

Nevertheless, as long as the Microsoft version serves your purpose, you should be ok.

[Updated on: Fri, 22 December 2006 17:03]

Report message to a moderator

Re: Network Connection via ODBC [message #210869 is a reply to message #210848]

Fri, 22 December 2006 15:21

jmontgomery
Messages: 9
Registered: December 2006

Junior Member

I did install the lite version. Sometime during the hoildays I'll install the basic version & look for the Oracle driver. Would rather use the Oracle version.

Tnx again for the help

Report message to a moderator

Previous Topic:	Calling a SQL SERVER procedure from Oracle heterogeneous service
Next Topic:	UNICODE CHARACTER SET AND CONNECTIVITY

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 12:15:30 CDT 2024