Home » RDBMS Server » Networking and Gateways » ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] (Oracle 11g R2)
ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595125] Sat, 07 September 2013 06:11 Go to next message
Manoj.Gupta.91
Messages: 194
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 #595126 is a reply to message #595125] Sat, 07 September 2013 06:41 Go to previous messageGo to next message
John Watson
Messages: 4693
Registered: January 2010
Location: Global Village
Senior Member
Please use [code] tags. You know how to do it.

Why are you using VALUES in your IDENTIFIED BY clause?
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 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
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 #595175 is a reply to message #595173] Mon, 09 September 2013 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Manoj.Gupta.91 wrote on Mon, 09 September 2013 07:05
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.


1/ you didn't answer to John"s question
2/ no difference with Oracle one, just read Database SQL Reference
3/ There is no need to completely quote your original post; if you think it will give you more answers you are fooling yourself, it is the opposite that will happen.

Regards
Michel

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 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
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 #595183 is a reply to message #595180] Mon, 09 September 2013 01:24 Go to previous messageGo to next message
John Watson
Messages: 4693
Registered: January 2010
Location: Global Village
Senior Member
The VALUES syntax is not documented and functions only in very special circumstances, which do not include links to SQL Server. Just follow the docs, and your link may work.
Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595186 is a reply to message #595180] Mon, 09 September 2013 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I don't know how to create DB Link to SQL Server. Can you please help me in that ?


Quote:
2/ no difference with Oracle one, just read Database SQL Reference


Regards
Michel
Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595187 is a reply to message #595183] Mon, 09 September 2013 01:56 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi John,

I'm not using VALUES syntax. I'm creating DB link as below but still it gives me same error.

CREATE PUBLIC DATABASE LINK DBLNK_1 CONNECT TO "manoj" IDENTIFIED BY "mypassword" USING 'SQLDB' ;


Do I need to make any configuration at SQL Server DB?

Thanks & Regards
Manoj
Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595188 is a reply to message #595187] Mon, 09 September 2013 02:03 Go to previous messageGo to next message
John Watson
Messages: 4693
Registered: January 2010
Location: Global Village
Senior Member
You mean you still get an ora-28500 when you use the link? In that case, it woud seem probable that your SQL Server username is not lowercase manoj or the password is not lowercase "password". Windows people often get confused about case sensitivity.
Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595189 is a reply to message #595187] Mon, 09 September 2013 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
{01000}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}


As we don't know what you actually did we can't answer.

Regards
Michel
Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595190 is a reply to message #595188] Mon, 09 September 2013 02:15 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi John,

I checked both (user name and password) are in lower case.

Regards
Manoj
Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595193 is a reply to message #595190] Mon, 09 September 2013 02:49 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2449
Registered: May 2013
Location: World Wide on the Web
Senior Member
Is the Database link created successfully and you receive the error while you execute the query using dblink?
Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595194 is a reply to message #595193] Mon, 09 September 2013 03:01 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi,

DB link gets created successfully. I face error when I'm selecting using DB Link.

Thanks & Regards
Manoj
Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595195 is a reply to message #595194] Mon, 09 September 2013 03:13 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2449
Registered: May 2013
Location: World Wide on the Web
Senior Member
Manoj, instead of select *, can you try with keeping all column names.
Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595196 is a reply to message #595194] Mon, 09 September 2013 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Drop the database link, recreate it and select, and SHOW us the session, the WHOLE one.
It MUST be a SQL*Plus session.

Regards
Michel
Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595198 is a reply to message #595194] Mon, 09 September 2013 03:18 Go to previous messageGo to next message
John Watson
Messages: 4693
Registered: January 2010
Location: Global Village
Senior Member
Manoj.Gupta.91 wrote on Mon, 09 September 2013 09:01
Hi,

DB link gets created successfully. I face error when I'm selecting using DB Link.

Thanks & Regards
Manoj
Manoj, perhaps you should Google "I face error" and see if you get any useful hits. Or you could try sdomething a bit more precise?
Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595204 is a reply to message #595198] Mon, 09 September 2013 05:48 Go to previous messageGo to next message
sidhu.bhim
Messages: 3
Registered: September 2013
Location: India
Junior Member
Hi Manoj,

Can you please share init<SID>.ora file contents of dg4msql gateway you have installed.
Those will be lying in directory $ORACLE_HOME/dg4msql/admin

Regards
Bhim
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 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
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 #595212 is a reply to message #595210] Mon, 09 September 2013 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 09 September 2013 10:17
Drop the database link, recreate it and select, and SHOW us the session, the WHOLE one.
It MUST be a SQL*Plus session.

Regards
Michel

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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2449
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

Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595215 is a reply to message #595213] Mon, 09 September 2013 07:58 Go to previous messageGo to next message
sidhu.bhim
Messages: 3
Registered: September 2013
Location: India
Junior Member
Hi Manoj,

Please try below and let me know result.
HS_FDS_CONNECT_INFO=[SERVER IP]: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


Regards
Bhim
icon7.gif  Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #595228 is a reply to message #595215] Mon, 09 September 2013 08:53 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi Bhim,

I tried with these settings and it worked Smile

ThankYou very much for your help and support.

Regards
Manoj
Re: ORA-28500 : While selecting SQL Server data in Oracle [HSODBC] [message #605667 is a reply to message #595228] Mon, 13 January 2014 04:45 Go to previous message
shreya92
Messages: 2
Registered: November 2013
Location: mumbai
Junior Member
Thanks for sharing the information! It was useful and it worked very fast !
Thanks a lot !
Previous Topic: Listener
Next Topic: JBoss Connection in oracle db 10g
Goto Forum:
  


Current Time: Sat Oct 25 14:45:59 CDT 2014

Total time taken to generate the page: 0.09311 seconds