| Database Link Failing with ORA-28500 [message #496981] |
Thu, 03 March 2011 07:49  |
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 #498748 is a reply to message #496981] |
Thu, 10 March 2011 23:39  |
 |
Michel Cadot
Messages: 68641
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
