| ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595125] |
Sat, 07 September 2013 06:11  |
Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi
|
Senior Member |
|
|
Hi All,
I'm facing ORA-28500 error while configuring HSODBC to SQL Server.
Below is the complete information related to listener.ora, sqlnet.ora and tnsnames.ora files.
Listener status is showing dg4msql instance successfully started.
I guess I'm facing some issue while creating DB link.
In Oracle 11g Release 11.2.0.1.0 we need to create DB link as below
CREATE DATABASE LINK "DBLNK_ETM_PRODUCTION"
CONNECT TO "USER_NAME" IDENTIFIED BY VALUES 'Password'
USING 'ServerIPAddress:PortNumber/SID';
How to create DB link to SQL Server.
Please help me to resolve this issue.
---------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
---------------------------------------------------------------------------------------------------------------------
$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
#NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
#sqlnet.authentication_services=(NTS)
ADR_BASE = /u01/app/oracle
---------------------------------------------------------------------------------------------------------------------
$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.123)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1)
)
)
SQLDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.123)(PORT = 1521))
)
(CONNECT_DATA =
(SID = dg4msql))
(HS = OK)
)
---------------------------------------------------------------------------------------------------------------------
$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = DB1)
)
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(PROGRAM = dg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.123)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
---------------------------------------------------------------------------------------------------------------------
$lsnrctl - status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 07-SEP-2013 16:27:17
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 06-SEP-2013 20:24:58
Uptime 0 days 20 hr. 2 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/devdb1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.123)(PORT=1521)))
Services Summary...
Service "DB1" has 1 instance(s).
Instance "DB1", status UNKNOWN, has 1 handler(s) for this service...
Service "dg4msql" has 1 instance(s).
Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
---------------------------------------------------------------------------------------------------------------------
CREATE PUBLIC DATABASE LINK DBLNK_1 CONNECT TO "manoj" IDENTIFIED BY "mypassword" USING 'SQLDB' ;
SELECT * FROM MyTable@DBLNK_1 ;
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Driver][libssclient24]General network error. Check your network documentation.
{08001,NativeErr = 11}[Oracle][ODBC SQL Server Driver][libssclient24]ConnectionOpen (Name or service not known()).
{01000}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}
ORA-02063: preceding 2 lines from DBLNK_AVL_CLUSTER2
28500. 00000 - "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.
Error at Line: 5 Column: 20
---------------------------------------------------------------------------------------------------------------------
Thanks & Regards
Manoj
|
|
|
|
|
|
| Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595173 is a reply to message #595126] |
Mon, 09 September 2013 00:05  |
Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi
|
Senior Member |
|
|
John Watson wrote on Sat, 07 September 2013 17:11
Why are you using VALUES in your IDENTIFIED BY clause?
Please help me in creating DB Link.
Do I need to set any parameters in sqlnet.ora ?
Oracle Databse Server :
Linux devdb1 2.6.32-358.el6.x86_64 #1 SMP Tue Jan 29 11:47:41 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
Windows Server 2008 (R2) Enterprise Edition.
Thanks & Regards
Manoj
|
|
|
|
|
|
| Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595180 is a reply to message #595175] |
Mon, 09 September 2013 01:01 |
Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi
|
Senior Member |
|
|
Hi Michel,
I just posted my original post after using code tags as asked by John.
I don't know how to create DB Link to SQL Server. Can you please help me in that ?
Did I miss any configuration settings for HSODBC?
In Oracle 11g R2 DB link should be created as below and I've created one which is working. The only difference is that was created between two oracle databases.
CREATE DATABASE LINK DBLNK_TMP
CONNECT TO "USER_NAME" IDENTIFIED BY VALUES "Password"
USING 'ServerIPAddress:PortNumber/SID' ;
Thanks & Regards
Manoj
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595210 is a reply to message #595204] |
Mon, 09 September 2013 07:17 |
Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi
|
Senior Member |
|
|
Hi Bhim,
$ cat initdg4msql.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[SERVER18]:1433//msqlinst
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_SQLLEN_INTERPRETATION=4
HS_NLS_NCHAR=AMERICAN_AMERICA.US7ASCII
HS_LANGUAGE=AMERICAN_AMERICA.US7ASCII
Thanks & Regards
Manoj
|
|
|
|
|
|
| Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595213 is a reply to message #595210] |
Mon, 09 September 2013 07:31 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
There are couple of suggestions made, please try each of them and as Michel suggested, please show us the complete SQL*Plus session.
I found an OTN forum link where a similar question was answered by adding few HS init parameters, you could once have a look ORA-28500
Another thing, since the connection uses ODBC SQL Server Driver, isn't that the program and sid_name should be "DG4ODBC" instead of "DG4MSQL". I am open to any correction on this.
[EDIT : Provided an OTN forum link to a similar answered question]
[Updated on: Mon, 09 September 2013 07:41] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
