After creating a heterogenous database link from Oracle database to MSSQL Server, getting error message “ORA-28500”
SQL>select sysdate from dual@DBLINK_MSSQL;
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Wire Protocol driver]Connection refused. Verify Host
Name and Port Number. {08001}
ORA-02063: preceding 2 lines from MSSQL_INTG
Reason
$ telnet MSSQLDB 1433
MSSQLDB/1433: Name or service not known
$ telnet 10.x.x.x 1433
Trying 10.x.x.x…
Connected to 10.x.x.x (10.x.x.x).
Escape character is ‘^]’.
^]
telnet> logout
^CConnection closed by foreign host.
I am able to telnet to MSSQL database Server using IP address , but cannot telnet to MSSQL server using hostname
SQL>select sysdate from dual@DBLINK_MSSQL;
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Wire Protocol driver]Connection refused. Verify Host
Name and Port Number. {08001}
ORA-02063: preceding 2 lines from MSSQL_INTG
Reason
$ telnet MSSQLDB 1433
MSSQLDB/1433: Name or service not known
$ telnet 10.x.x.x 1433
Trying 10.x.x.x…
Connected to 10.x.x.x (10.x.x.x).
Escape character is ‘^]’.
^]
telnet> logout
^CConnection closed by foreign host.
I am able to telnet to MSSQL database Server using IP address , but cannot telnet to MSSQL server using hostname
Solution
Contact your Network admin to allow access to 1433 using Servername (MSSQLDB)
(or)
Change the below configuration
1. Set Oracle Home to point to your Gateway Oracle HOME
2. Navigate to $ORACLE_HOME/dg4msql/admin
Change below values in initdg4msql.ora
changed HS_FDS_CONNECT_INFO from MSSQLDB to IP address
#
HS_FDS_CONNECT_INFO=10.x.x.x:1433//wfcdb
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
3. Restart the listener in Gateway Oracle HOME
4. Verify the database link is working fine.
SQL>select sysdate from dual@DBLINK_MSSQL;
==>Above Query should return current system date.
It’s a pity you don’t have a donate button! I’d most certainly donate to this brilliant blog! I guess for now i’ll settle for book-marking and adding your RSS feed to my Google account. I look forward to brand new updates and will talk about this site with my Facebook group. Talk soon!
Thank you for your articles. I find them very helpful. Could you help me with something?
very informative articles or reviews at this time.
We stumbled over here coming from a different web page and thought I might check things out. I like what I see so now i’m following you.Look forward to finding out about your web page yet again. http://www.kayswell.com
Aw, this was a very nice post. Taking the time and actual effort to create a great article… but what can I say… I hesitate a whole lot and never seem to get nearly anything done. http://www.kayswell.com
It’s genuinely very difficult in this busy life to listen news on Television, so I just use world wide web for that reason, and take the most up-to-date news. http://www.kayswell.com
Hello my loved one! I wish to say that this post is amazing, great written and include approximately all significant infos. I would like to peer extra posts like this . http://www.kayswell.com
I’m curious to find out what blog system you have been using? I’m experiencing some small security issues with my latest blog and I’d like to find something more safe. Do you have any suggestions? http://www.hairstylesvip.com
I’m extremely impressed with your writing skills as well as with the layout on your weblog. Is this a paid theme or did you customize it yourself? Anyway keep up the excellent quality writing,it is rare to see a nice blog like this one today. http://www.kayswell.com
Ahaa, its fastidious discussion on the topic of this article at this place at this website, I have read all that, so now me also commenting at this place. http://www.kayswell.com