Software:
$ e /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ETMCDB)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = ETMCDB)
# (PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.106)(PORT = 1521))
)
)
Link_Ora =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.106)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ETMCDB)
)
)
..........................................
D:\oracle\product\10.2.0\client_1\BIN>tnsping Link_Ora
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on ..............................
D:\oracle\product\10.2.0\client_1\BIN>sqlplus system/oracle@Link_Ora
SQL*Plus: Release 10.2.0.1.0 - Production on ..............................
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
Start --> Control Panel--> Services: to make sure "Distributed Transaction Coordinator" is starting
3. windows ODBC
Start --> Control Panel --> Computer Management --> ODBC --> Tab (User DNS or System DNS) --> Add (Microsoft ODBC for oracle) --> Name (Same as the name in tnsnames.ora:Link_Ora) --> Server name (Same as the name in tnsnames.ora:Link_Ora)-->Finish
4. DB_Link in SQL Server
In General Tab
Other Data Source
Link: Microsoft OLE DB Provider for Oracle
Product name: Oracle
Data_Source: the Name in ODBC Link_Ora(step 3)
User ID=username;Password=userpasswd or UID=username;PWD=userpasswd (the username / password in Oracle)
In Security Tab:
Or to use SQL:
or
'Link_Ora','false',null,'rmtuser','rmtpassword'
Testing:
SELECT * FROM openquery(OraTest,'SELECT * FROM OraTest.ERP.BAS_ITEM_CLASS ')
Ref:
http://www.cnblogs.com/studyzy/archive/2006/12/08/690307.html
http://space.itpub.net/?uid-7728585-action-viewspace-itemid-470225
