| Oracle for Linux Access SQLserver Through dblink issue [message #662383] |
Fri, 28 April 2017 04:39  |
 |
edwin2017
Messages: 2
Registered: April 2017
|
Junior Member |
|
|
Hi, everybody:
My English is not good, so if my describe have some mistake plese don't care. Ok, let me speak my question.
the test environment is:
windows 64 / sqlserver 2008 64bit
linux32 / oracle 11.2.0.4 32bit
I want access sql server 2008 from oracle in linux server, so I use the freetds and unixODBC. and everything is ok, unless the last step "query data from sqlserver through a dblink". my configuration and test like this:
#/etc/freetds.conf
[global]
tds version = 7.1
dump file = /tmp/freetds.log
timeout = 10
connect timeout = 10
text size = 64512
[mssql2008]
host = 192.168.2.100
port = 1433
tds version = 7.1
client charset = UTF-8
#/etc/odbcinst.ini
# Example driver definitions
[ODBC]
Trace = Yes
TraceFile = /tmp/mssql.log
ForceTrace = Yes
Pooling = No
[SQLServer]
Description = SQLServer
Driver = /usr/local/freetds/lib/libtdsodbc.so
UsageCount = 1
CPTimeout = 5
CPReuse = 5
FileUsage = 1
#odbc.ini
[mssql2008]
Driver=SQLServer
Description=SQLServer
SERVER=192.168.2.100
Database=testdb
Port=1433
TDS_Version=7.1
#The tsql test is ok
isql mssql2008 sa sqlserver -v
SQL> use testdb
SQL> select * from org_mssql_tab2
+------------+------------+-------------------------------+------------+------------------------+
| id1 | id2 | name | age | dtime |
+------------+------------+-------------------------------+------------+------------------------+
| 1 | 1 | wanggang | 30 | 2017-04-28 12:35:30.000|
+------------+------------+-------------------------------+------------+------------------------+
quit
#And the oracle's lsnrctl and tnsnames config like this:
#listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.155)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY=EXTPROC))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/db/oracle/product/11.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = mssql2008)
(ORACLE_HOME = /home/db/oracle/product/11.2.0)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH=/usr/local/unixODBC/lib:/usr/local/freetds/lib:/usr/lib:/home/db/oracle/product/11.2.0/hs/lib)
)
)
ADR_BASE_LISTENER = /home/db/oracle
DIAG_ADR_ENABLED_LISTENER = OFF
DIAG_ADR_ENABLED_MSSQL = OFF
#tnsnames.ora
mssql2008 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.155)(PORT = 1521))
)
(CONNECT_DATA =
(SID = mssql2008)
)
(HS = OK)
)
#The listener status info
oracle@oracle32 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-APR-2017 13:56:59
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.155)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 28-APR-2017 11:17:33
Uptime 0 days 2 hr. 39 min. 25 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/db/oracle/product/11.2.0/network/admin/listener.ora
Listener Log File /home/db/oracle/product/11.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.155)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "edwin" has 1 instance(s).
Instance "edwin", status READY, has 1 handler(s) for this service...
Service "mssql2008" has 1 instance(s).
Instance "mssql2008", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
# the tnsping mssql2008 is ok
tnsping mssql2008
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.155)(PORT = 1521))) (CONNECT_DATA = (SID = mssql2008)) (HS = OK))
OK (0 msec)
#finally, I created a link in oracle, after that I running a qury through the dblink, but it's not working.
create public database link mssql08 connect to "sa" identified by "sqlserver" using 'mssql2008';
select * from "org_mssql_tab2"@mssql;
select * from "org_mssql_tab2"@mssql
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from MSSQL
# the /tmp/mssql.log is nothing record when the error happens. but the log should be loging information when connected to the remote database(such as use isql mssql2008....), so I think when I running the select statement, the request have not arrive to the sql server database. I was searched the large article in net. but I hve not find a useful infomation.
help me please.
[Updated on: Fri, 28 April 2017 04:57] Report message to a moderator |
|
|
|
| Re: Oracle for Linux Access SQLserver Through dblink issue [message #662435 is a reply to message #662383] |
Tue, 02 May 2017 04:06  |
|
edwin2017
Messages: 2
Registered: April 2017
|
Junior Member |
|
|
It's still not working with freetds and unixODBC, so I changed my method. instead of freetds I use the oracle gateway(usually in the install media NO.5), right now, it's working properly. and the configuration step easier than freetds. I will study freetds & unixODDBC later.
Thinks everybody again. thanks for moderator let me publish the article.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
