Home » RDBMS Server » Networking and Gateways » Database Link Failing with ORA-28500 (11.1.0.7.0 Standard Edition Windows 32-Bit / Windows Server 2008)
Database Link Failing with ORA-28500 [message #496981] Thu, 03 March 2011 07:49 Go to next message
playitsafe
Messages: 19
Registered: January 2009
Location: NJ
Junior Member
Attempting to create Database Link which talks to SQL Server 2008R2 system

Database: 11.1.0.7 Oracle Standard Edition
Server: Windows Server Enterprise, Service Pack 2 (2008)

I do not have the Oracle Gateway product installed, as this was not configured on my working 10G system.

I tried configuring the link with both the SQL Server ODBC Driver (GMSRES) and the
SQL Server Native Client 10.0 (GMSGAS). Returning errors when SQL command is executed.

Everything tests out fine (tnsping, listener configured OK, test connect in Database Link setup). When SQL statement is executed it fails.


Setup:

1. Created ODBC Driver (GMSRES). This tested successfull.
Created SQL Native Driver (GMSGAS). This tested successfull.


2. Created initiGMSRES.ora file C:\Oracle\product\11.1.0\db_1\hs\admin\ folder.

HS_FDS_CONNECT_INFO = GMSGAS
HS_FDS_TRACE_LEVEL = OFF
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P15

HS_FDS_CONNECT_INFO = GMSRES
HS_FDS_TRACE_LEVEL = OFF
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P15

3. Created listener.ora entry:

LISTENERGMS =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=WALL-ALIGN-01.testsystem.com)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))


SID_LIST_LISTENERGMS =
(SID_LIST =
(SID_DESC =
(SID_NAME = GMSGAS)
(ORACLE_HOME = C:\Oracle\product\11.1.0\db_1)
(PROGRAM = dg4odbc)
)
(SID_DESC =
(SID_NAME = GMSRES)
(ORACLE_HOME = C:\Oracle\product\11.1.0\db_1)
(PROGRAM = dg4odbc)
)

)

4. Created tnsnames.ora entry:

TnsGMSRES =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=WALL-ALIGN-01.testsystem.com)(PORT=1522))
(CONNECT_DATA=(SID=GMSRES))
(HS=OK)
)

TnsGMSGAS =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=WALL-ALIGN-01.testsystem.com)(PORT=1522))
(CONNECT_DATA=(SID=GMSGAS))
(HS=OK)
)

5. Started LIstenerGMS
Status Successful for GMSRES
Status successful for GMSGAS

6. tnsping GMSRES returns:

(HS=OK) OK (10 sec)

7. tnsping GMSGAS returns:

(HS=OK) OK (10 sec)

8. Created Database Link in Enterprise Manager.

NAME: RES
Net Service Name: TNSGMSRES
Schema: PUBLIC

Connect As: User fixeduser (GMSADMIN)

Test Result: The Database Link is active.


NAME: GAS
Net Service Name: TNSGMSGAS
Schema: PUBLIC

Connect As: User fixeduser (GMSADMIN)

Test Result: The Database Link is active.


9. SQLPlus test results:

Here are the errors once the link is configured for each setup:

> select count(*) from userpriv@res;

SQL Server (ODBC Driver)
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt.
ORA-02063: preceding 2 lines from <databasename>


> select count(*) from userpriv@gas;

SQL Server Native Client 10.0
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Microsoft][SQL Server Native Client 10.0]Connection is busy with results for another command.
ORA-02063: preceding 2 lines from <databasename>



Re: Database Link Failing with ORA-28500 [message #498721 is a reply to message #496981] Thu, 10 March 2011 21:34 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
I'll certainly have to try out myself.
Look up Making-a-Connection-from-Oracle-to-SQL-Server.htm
make sure to enclose your username and pass in quotes like this:
create database link dblink
connect to "user" identified by "password"
using 'dbname';

and MARS setting for sql2005/2008 (blogs.msdn.com/b/dataaccess/archive/2005/08/02/446894.aspx)


Re: Database Link Failing with ORA-28500 [message #498748 is a reply to message #496981] Thu, 10 March 2011 23:39 Go to previous message
Michel Cadot
Messages: 67466
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
 *Cause:    The cause is explained in the forwarded message.
 *Action:   See the non-Oracle system's documentation of the forwarded
            message.

Regards
Michel
Previous Topic: Listener
Next Topic: Heterogeneous services
Goto Forum:
  


Current Time: Mon Oct 26 10:51:56 CDT 2020