473,699 Members | 3,103 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Oracle connection problems; Odbc call failed

Hello all. I just installed Oracle 10g developer tools on a machine
running XP Pro and Office XP. Before this I had just the Oracle 9
client installed. I the previous configuration, I was able to access
any of the Oracle tables on another machine but now I am having
problems. Unfortunately, I don't remember the correct syntax for the
ODBC connect string and I am hoping that is my whole problem.

I am trying to connect to an Oracle 9 database on a VMS server. Here's
the info:

VMS machine:
-IP Address 192.168.0.250
-Database global name: db1.mcbill

PC:
-IP Address: 192.168.0.202
-Win XP Pro
-Office XP

When I run the linked table manager and then type username, password
and server I get an Odbc call failed error. Here is what I tried:

username: scott
password: tiger

For the server, I've tried the server's IP address, db1, db1.mcbill,
db********@192. 168.0.250, etc.

Can anyone help?

BTW, I know this is not a connectivity issue as I can connect from this
PC using Oracle forms, SQLPlus and Toad.

Here is my tnsnames.ora file:

# TNSNAMES.ORA Network Configuration File:
c:\oracle\ora10 g\NETWORK\ADMIN \tnsnames.ora
# Generated by Oracle configuration tools.

DB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.250)( PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db1.mcbill)
)
)

EXTPROC_CONNECT ION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PLSExtProc)
)
)

Thanks.
Bill

Nov 13 '05 #1
9 17024
There are some connection string examples here:

http://www.connectionstrings.com/
http://www.able-consulting.com/MDAC/...BC_DSNLess.htm

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
mc******@yahoo. com wrote:
Hello all. I just installed Oracle 10g developer tools on a machine
running XP Pro and Office XP. Before this I had just the Oracle 9
client installed. I the previous configuration, I was able to access
any of the Oracle tables on another machine but now I am having
problems. Unfortunately, I don't remember the correct syntax for the
ODBC connect string and I am hoping that is my whole problem.

I am trying to connect to an Oracle 9 database on a VMS server. Here's
the info:

VMS machine:
-IP Address 192.168.0.250
-Database global name: db1.mcbill

PC:
-IP Address: 192.168.0.202
-Win XP Pro
-Office XP

When I run the linked table manager and then type username, password
and server I get an Odbc call failed error. Here is what I tried:

username: scott
password: tiger

For the server, I've tried the server's IP address, db1, db1.mcbill,
db********@192. 168.0.250, etc.

Can anyone help?

BTW, I know this is not a connectivity issue as I can connect from this
PC using Oracle forms, SQLPlus and Toad.

Here is my tnsnames.ora file:

# TNSNAMES.ORA Network Configuration File:
c:\oracle\ora10 g\NETWORK\ADMIN \tnsnames.ora
# Generated by Oracle configuration tools.

DB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.250)( PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db1.mcbill)
)
)

EXTPROC_CONNECT ION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PLSExtProc)
)
)

Thanks.
Bill

Nov 13 '05 #2
mc******@yahoo. com wrote:
Hello all. I just installed Oracle 10g developer tools on a machine
running XP Pro and Office XP. Before this I had just the Oracle 9
client installed. I the previous configuration, I was able to access
any of the Oracle tables on another machine but now I am having
problems. Unfortunately, I don't remember the correct syntax for the
ODBC connect string and I am hoping that is my whole problem.


Are you using Access? Why are you worrying about constructing the
connect string? The linked table or ODBC DSN connection dialog for pass
through queries does this for you.

Does the DSN or DSNs you're using still work on other client PCs? You
did say SqlPlus worked, so what about deleting the DSN on this machine
and reconstructing it. Next try connecting to the DSN in Access again.

Have the 10g tools replaced the Oracle ODBC driver (I assume you're not
using the MS one) with a later version that might not be compatible with
the server installation? ODBC drivers in Oracle don't have a lot of
problem between versions, but I have had instances where updating a
driver on a client machine did away with some problems.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #3
Don't use DAO/ODBC, use ADO and the Oracle OLEDB provider which you
download from Oracle. If you *must* use ODBC, make sure you're
configuring the Microsoft ODBC for Oracle version 2.573.xxx something.
Here is a sample ADO module for a dsnless connection (from a VB
project):
~~~~~~~~~~~~~ BEGIN CODE
Option Explicit
'
' public connection objects
Public conn As ADODB.Connectio n ' connection object
Public rsA As ADODB.Recordset ' static recordset object (no
updates)
Public rsB As ADODB.Recordset ' dynamic recordset object
' both recordset objects use optimistic locking
' and client side cursors
'
Public Function OpenConn(strUse r As String, strPwd As String, strSvc
As String)
' open db connection
Set conn = New ADODB.Connectio n
'
conn.Provider = ADODB_PROVIDER ' = OraOLEDB.Oracle
conn.Open strSvc, strUser, strPwd
'
End Function
'
Public Function OpenRsA(connLiv e As ADODB.Connectio n, strSQL As
String)
' open recordset A, static
Set rsA = New ADODB.Recordset
'
rsA.CursorLocat ion = adUseClient
rsA.Open strSQL, connLive, adOpenStatic, adLockOptimisti c
'
End Function
'
Public Function OpenRsB(connLiv e As ADODB.Connectio n, strSQL As
String)
' open recordset B, dynamic
Set rsB = New ADODB.Recordset
'
rsB.CursorLocat ion = adUseClient
rsB.Open strSQL, connLive, adOpenDynamic, adLockOptimisti c
'
End Function
'
Public Function ShutConnections ()
' close the connection and
' disinstantiate the connection objects
'
Set rsA = Nothing
Set rsB = Nothing
'
If Not conn Is Nothing Then
conn.Close
End If
'
Set conn = Nothing
'
End Function
'
~~~~~~~~~~~~~ END CODE
On 24 Mar 2005 03:22:24 -0800, mc******@yahoo. com wrote:
Hello all. I just installed Oracle 10g developer tools on a machine
running XP Pro and Office XP. Before this I had just the Oracle 9
client installed. I the previous configuration, I was able to access
any of the Oracle tables on another machine but now I am having
problems. Unfortunately, I don't remember the correct syntax for the
ODBC connect string and I am hoping that is my whole problem.

I am trying to connect to an Oracle 9 database on a VMS server. Here's
the info:

VMS machine:
-IP Address 192.168.0.250
-Database global name: db1.mcbill

PC:
-IP Address: 192.168.0.202
-Win XP Pro
-Office XP

When I run the linked table manager and then type username, password
and server I get an Odbc call failed error. Here is what I tried:

username: scott
password: tiger

For the server, I've tried the server's IP address, db1, db1.mcbill,
db********@192 .168.0.250, etc.

Can anyone help?

BTW, I know this is not a connectivity issue as I can connect from this
PC using Oracle forms, SQLPlus and Toad.

Here is my tnsnames.ora file:

# TNSNAMES.ORA Network Configuration File:
c:\oracle\ora1 0g\NETWORK\ADMI N\tnsnames.ora
# Generated by Oracle configuration tools.

DB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.250)( PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db1.mcbill)
)
)

EXTPROC_CONNEC TION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PLSExtProc)
)
)

Thanks.
Bill


Nov 13 '05 #4
Elaine wrote:
Don't use DAO/ODBC, use ADO and the Oracle OLEDB provider which you
download from Oracle. If you *must* use ODBC, make sure you're
configuring the Microsoft ODBC for Oracle version 2.573.xxx something.


At the risk of hijacking the thread, I'd really appreciate any thoughts
on the following, related to Oracle/ADO.

I tried using ADO when I first went to A2003 last Fall, after many happy
years with A97 (sniff!) 8)

All of my Access 97 FE apps on Oracle databases use pass through queries
and my code constructs and modifies the Oracle SQL for these as
required. I use the pass through query objects in Access to act as row
sources for list and combo boxes and for record sources for forms, be
they datasheet or form view.

I could not figure out how to do this sort of thing with ADO. All I
could do with ADO was generate recordset objects.

Can you do the sort of thing I'm describing with ADO?

Thanks very much for any comment.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #5
Tim Marshall wrote:
Elaine wrote:
Don't use DAO/ODBC, use ADO and the Oracle OLEDB provider which you
download from Oracle. If you *must* use ODBC, make sure you're
configuring the Microsoft ODBC for Oracle version 2.573.xxx something.

At the risk of hijacking the thread, I'd really appreciate any thoughts
on the following, related to Oracle/ADO.

I tried using ADO when I first went to A2003 last Fall, after many happy
years with A97 (sniff!) 8)

All of my Access 97 FE apps on Oracle databases use pass through queries
and my code constructs and modifies the Oracle SQL for these as
required. I use the pass through query objects in Access to act as row
sources for list and combo boxes and for record sources for forms, be
they datasheet or form view.

I could not figure out how to do this sort of thing with ADO. All I
could do with ADO was generate recordset objects.

Can you do the sort of thing I'm describing with ADO?


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're talking about an .adp file, right? I use Views for the
ComboBoxes, ListBoxes. If the Combo/List box is dynamic (criteria
changes based on user input) I use stored procedures. When the criteria
changes I use VBA to change the RowSource:

RowSource: exec sp_name param1, param2, 'param3', etc.

For forms I use Views. If the user wants to change the criteria I just
change the form's filter.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkNd2IechKq OuFEgEQKW7wCeJv IYz9KeiY5GRJ43g EoaJOUHIioAnRyQ
g1KrtnWnPv5Djs1 1YaIZOhAZ
=o1sH
-----END PGP SIGNATURE-----
Nov 13 '05 #6
MGFoster wrote:
Tim Marshall wrote:
Elaine wrote:
Don't use DAO/ODBC, use ADO and the Oracle OLEDB provider which you
All of my Access 97 FE apps on Oracle databases use pass through
queries and my code constructs and modifies the Oracle SQL for these
as required. I use the pass through query objects in Access to act as
row sources for list and combo boxes and for record sources for forms,
be they datasheet or form view.

You're talking about an .adp file, right?


Nope, a "normal" mdb. I'm not sure what an .adp is to be honest and
ignorant... 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #7
Tim Marshall wrote:
MGFoster wrote:
Tim Marshall wrote:
Elaine wrote:

Don't use DAO/ODBC, use ADO and the Oracle OLEDB provider which you
All of my Access 97 FE apps on Oracle databases use pass through
queries and my code constructs and modifies the Oracle SQL for these
as required. I use the pass through query objects in Access to act
as row sources for list and combo boxes and for record sources for
forms, be they datasheet or form view.


You're talking about an .adp file, right?

Nope, a "normal" mdb. I'm not sure what an .adp is to be honest and
ignorant... 8)


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In that case, just forget ADO and continue what you'd been doing in
Acc97 in Acc2003. Hey, if it ain't broke, don't fix it. ;-)

An .adp is an Access project, which uses an OleDB connection to a
provider (aka DB engine), usually MS SQL Server.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkN4kIechKq OuFEgEQIFGgCgtv JZCn16ulH0QJngH y31n+FGAT4AoM0C
tOFtpNLEwNnppHY vl00WmbfL
=den/
-----END PGP SIGNATURE-----
Nov 13 '05 #8
Thanks for the info. However, maybe I used the wrong terminology. When
I referred to the connect string, I meant the string that I would type
into the "Server:" field when the popup login window for "Microsoft
ODBC for Oracle Connect" would appear.

In the list of data sources for the linked tables, the only reference
to Oracle that I saw was the ODBC Connection for Oracle. Should I be
using something else?

Bill

Nov 13 '05 #9
Tim,

You can use ADO to do anything which DAO does, but it's different.
Look at the samples, and search the web for some other clear samples.
Oracle's otn.oracle.com has some decent code samples. Go there.

Couple of things:
1) Even though you *can* mix ADO and DAO, I wouldn't use them in
the same procedures - really gets ugly

2) Get a book to guide you. May be out of print, but the OReilly
title _ADO: ActiveX Data Objects_ is pretty good for walking you
through it. Or maybe the _Access Cookbook_ by Ken Getz.

ADO lets you move forward and backward through recordsets, define
recordsets on the fly, insert, update or delete rows, in short,
everything you can do with DAO, plus some other cool stuff you can't
do with DAO.

Frankly, I am using Java more these days, but much hard to do it with
GUIs than Access is.

HTH,
Elaine

On Thu, 24 Mar 2005 17:04:38 -0330, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtherium> wrote:
Elaine wrote:
Don't use DAO/ODBC, use ADO and the Oracle OLEDB provider which you
download from Oracle. If you *must* use ODBC, make sure you're
configuring the Microsoft ODBC for Oracle version 2.573.xxx something.


At the risk of hijacking the thread, I'd really appreciate any thoughts
on the following, related to Oracle/ADO.

I tried using ADO when I first went to A2003 last Fall, after many happy
years with A97 (sniff!) 8)

All of my Access 97 FE apps on Oracle databases use pass through queries
and my code constructs and modifies the Oracle SQL for these as
required. I use the pass through query objects in Access to act as row
sources for list and combo boxes and for record sources for forms, be
they datasheet or form view.

I could not figure out how to do this sort of thing with ADO. All I
could do with ADO was generate recordset objects.

Can you do the sort of thing I'm describing with ADO?

Thanks very much for any comment.


Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
102538
by: SerGioGio | last post by:
Hello, I am going nuts. I am trying to connect to my local ORACLE instance using ODBC. It used to work few weeks ago, but it fails now. Connection with: - SQL*plus: connection works! - DataDirect 5.0 Oracle Wire protocol (3rd party ODBC driver): connection works when I hit the driver's "test connection"
4
16837
by: Tig | last post by:
Hi all. I have a need to connect to an Oracle 7.3.3.5 database. I have a user who successfully connects to it with her Oracle 7.3 client. I have an Oracle 9.2 client installed on my machine. I had her send me her tnsnames.ora from her PC. The entry I wanted was as follows (some letters replaced with xxx for security reasons):
3
4075
by: premmehrotra | last post by:
I am using Access 2000 and Oracle 9.2.0.x on a Windows 2000. I have setup Oracle 9.2 ODBC Driver (I have not yet figured how to set Microsoft's Oracle ODBC driver). I am exporting a table from Access to Oracle and I get following error: ODBC Call Failed: ORA-12571: TNS Packet Failure Error I see table and its indexes created in Oracle. However, there are no
0
3023
by: totierne | last post by:
comp.databases.ms-access, I want to know how to use Oracle views with session variables in Access. The parameterised views in access, are migrated to views with per session variables. The open questions: How to display a resultset
3
10776
by: Andrew McGregor | last post by:
Hi, I am trying to get a VB.NET application to connect to a local Oracle 9i Lite database. What is the correct form for a connect string? cn = New Microsoft.Data.Odbc.OdbcConnection("dsn=POLite;Trusted_Connection=yes;User Id=SYSTEM;Password=MANAGER")
2
9947
by: Crazy Cat | last post by:
Hi all, I am having trouble getting linked Oracle 9 server in MS SQL Server 2005 Express to work properly. My machine is running Windows XP. The Microsoft and Oracle OLE DB Providers have problems dealing with Oracle's Numeric Data Type, so I decided to use Microsoft's OLE DB for ODBC Provider and an Oracle ODBC source. When using the Microsoft ODBC for Oracle Driver in my ODBC source I have inconsistent behavior. Sometimes my queries...
2
10259
by: Ben | last post by:
Hi, I have a problem connecting to Oracle using and ODBC connection in a ASP.Net web page. The TNS Names works fine because when I create a DSN it works, and it works in SQL Plus. Here are the error I get depending on the connection string: Using the right server (Oracle 9i), and the Native Oracle Driver
6
17630
by: BillCo | last post by:
I've reached the limit of my knowledge here and I'm starting to go mad - any help would be greatfully recieved!!!! I'm having a strange problem with making pass through queries to an oracle db with a permament connection string. - if i create the passthrough query by writing the sql into the sql builder window and selecting the connection details on opening the query then everything works fine
16
9184
by: network-admin | last post by:
We have Problems with Access query on Oracle 10g Database with ODBC Connection. The Query_1 is such as select * from xtable where ycolumn <"S" Result = ODBC Faild ----------------------------------------------------
0
8623
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9053
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8894
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7776
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5879
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4390
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4636
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3071
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2015
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.