• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3303
  • Last Modified:

How do I deal with ORA-28545 when trying to connect from Oracle to MS-Access?

I have an Oracle Server 11.2.0.2.0 Enterprise Edition installed on a 64-bit Windows Server 2008 R2 Standard. I need to be able to connect from it to MS-Access and have an Oracle stored procedure run DML commands on tables in an Access database.
Following some advice I found on the Net, I did the following steps, all on the Oracle server itself:

1

I created a new System DSN ODBC called PristineDSN with the "Microsoft Access Driver", using the abovementioned MS-Access database. Note that I did not use the one under Start->"Administrative Tools"->"Data Sources (ODBC)" because it did not have the "Microsoft Access Driver". I ran odbcad32.exe under C:\Windows\SysWOW64 instead.

2

I created a file called initPristineDSN.ora under $ORACLE_HOME\hs\admin containing the following three lines:
HS_FDS_CONNECT_INFO = PristineDSN
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SUPPORT_STATISTICS = FALSE

3

I opened the file listener.ora under $ORACLE_HOME\NETWORK\ADMIN and added the following four lines to the SID_LIST_LISTENER entry:
(SID_DESC=
  (SID_NAME=PristineDSN)
  (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
  (PROGRAM=hsodbc))

4

I opened the file tnsnames.ora under $ORACLE_HOME\NETWORK\ADMIN and appended the following six lines:
PRISTINEDSN =
 (DESCRIPTION=
  (ADDRESS_LIST =
   (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (CONNECT_DATA=(SID=PristineDSN))
 (HS=OK))

5

I ran the following two commands from a command prompt in order to stop and then start the listener:
lsnrctl stop
lsnrctl start

At this point I ran TNSPING PRISTINEDSN from a Command Prompt window, and the result was:
TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 05-DEC-2011 13:46:47

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
E:\ORACLE\product\11.2.0\db\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))) (CONNECT_DATA=(SID=PristineDSN)) (HS=OK))
OK (20 msec)

6

I connected to a schema and ran the folowing command to create a database link:
CREATE DATABASE LINK DBL_PRISTINE USING'PRISTINEDSN';

Now, I ran the following command:
SELECT * FROM TBL_PRODUCTS@DBL_PRISTINE;

I received the following output:
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from DBL_PRISTINE


The procedure above worked on a previous machine I had, running Oracle Server 10.2.0.1.0 Enterprise Edition on a 32-bit Windows Server 2003 SP2. What am I doing wrong?
Perhaps there is a simpler way to connect from Oracle 11g to MS-Access?


Note: All instances of $ORACLE_HOME in the steps above have been substituted for E:\ORACLE\product\11.2.0\db which is the value of ORACLE_HOME under the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1 key in the server's registry.
Also, Microsoft Access is not installed on the server; only the Microsoft Access Driver is.
0
netformx
Asked:
netformx
  • 4
  • 3
1 Solution
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
there is some version change......... check this

>>>ORACLE_HOME = D:\oracle\product\10.2.0\db_1<<<

>>E:\ORACLE\product\11.2.0\db\network\admin\sqlnet.ora<<

if it is just a typo error then check the global_name parameter in DB i.e
SQL>show parameter global_name
if it is true set it to false.
SQL>alter system set global_names=false ;

check this doc for more info

http://www.orafaq.com/forum/t/147423/2/

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206

0
 
netformxAuthor Commented:
praveencpk,

This is not a typo. The value of ORACLE_HOME under the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1 key in the server's registry really is E:\ORACLE\product\11.2.0\db and this is also the correct path on the server's hard disk.
Also, the global_names parameter is already set to FALSE.

I have no idea what to do with the two links you provided. If you have any more suggestions, I would be happy to know.
0
 
OP_ZaharinCommented:
"(SID_DESC=
  (SID_NAME=PristineDSN)
  (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
  (PROGRAM=hsodbc))"

- do you have 2 oracle installation on the same machine? if not kindly confirm that you have change the above D:\oracle\product\10.2.0\db_1 to E:\ORACLE\product\11.2.0\db
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.

 
netformxAuthor Commented:
OP_Zaharin (and also praveencpk),

Now I see what you both meant, and stand corrected. (I hold a faulty copy/paste to blame.) Allow me to rewrite the four lines I added to the SID_LIST_LISTENER entry of listener.ora in step #3 above:
(SID_DESC=
 (SID_NAME=PristineDSN)
 (ORACLE_HOME = E:\ORACLE\product\11.2.0\db)
 (PROGRAM=hsodbc))

Open in new window

Sorry for the mixup. Your turn.
0
 
OP_ZaharinCommented:
- did you modify your sqlnet.ora to the following?:
SQLNET.AUTHENTICATION_SERVICES= (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

- after the changes above, restart the listener. if its still not working, i think my team have a similar issue when setting up HS recently. on Oracle 11g, there is no longer hsodbc. instead you have to setup Database gateway for ODBC - dg4odbc. the setup is almost similar except that the (PROGRAM=hsodbc) is changed to (PROGRAM=dgodbc). look further in the Oracle doc for the steps.
 
0
 
OP_ZaharinCommented:
- here is the doc for 11.1. i believe the steps is the same for 11.2:
http://docs.oracle.com/cd/B28359_01/gateways.111/b31043/configodbc.htm
0
 
netformxAuthor Commented:
OP_Zaharin,

Using Database Gateway worked. I had to install Microsoft Access Driver for 64-bit and then spend some more time investigating why it did not work at first, but then I changed your (PROGRAM=dgodbc) into (PROGRAM=dg4odbc) and then it worked.

Thanks for your help.
0
 
OP_ZaharinCommented:
Hi netformx, yeah its typo. PROGRAM=dg4odbc is the correct one. glad it finally work for you.

cheers,
OP
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.

Join & Write a Comment

Featured Post

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.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now