将MS SQL Server数据库连接到Oracle 11g数据库 [英] Connect MS SQL Server database to Oracle 11g database

问题描述

我正在努力创建从Oracle数据库到一个SQL Server的数据库链接.

I am struggling to create a db link from a Oracle Database to one SQL Server.

为此，我添加了以下内容:

For that, I have added the followings:

在文件tnsnames.ora中:

sqlUserConn =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
      (CONNECT_DATA=(SID=sqlUserConn))
      (HS=OK)
    )

在文件listener.ora中:

(SID_DESC=
  (SID_NAME=sqlUserConn)
  (ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\server)
  (PROGRAM=dg4odbc)
)

在文件sqlnet.ora中:

SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) 

在文件initsqlUserConn.ora中:

HS_FDS_CONNECT_INFO = sqlUserConn
HS_FDS_TRACE_LEVEL = OFF

文件tnsnames.ora，sqlnet.ora和listener.ora放在** server \ network \ admin **中，而initsqlUserConn.ora放在** server \ hs \ admin **中

The files tnsnames.ora, sqlnet.ora and listener.ora are placed in **server\network\admin** and initsqlUserConn.ora is placed in **server\hs\admin**

我有以下sql语句:

CREATE DATABASE LINK sqlUserConn CONNECT TO "USER" IDENTIFIED BY "PASSWORD" USING 'sqlUserConn';

然后要测试数据库链接，我正在运行类似于:

And then to test the db link, I am running something similar to:

Select * from table@sqlUserConn

此命令引发以下错误:

ORA-12154:TNS:无法解析指定的连接标识符

ORA-12154: TNS:could not resolve the connect identifier specified

很明显，我做错了..但是仍然不明白..它应该可以正常工作，因为我看到很多用户都这样做了

Obviously, I have done something wrong.. but still I don't get it.. It should work as I have seen a lot of users doing it like this

有人可以为我指出将SQL Server数据库链接到Oracle 11g的正确解决方案吗?

Could someone point me to the correct solution for linking a SQL Server database to Oracle 11g?

推荐答案

首先，请确保您使用的是正确的DSN体系结构(32或64位).如果使用不正确的体系结构，它将引发错误. Windows上的Oracle具有奇怪的行为，因此，命令lsnrctl reload甚至使用lsnrctl stop，然后使用lsnrctl start都无助于刷新放置在指定文件(tnsnames，侦听器等)中的信息.因此，唯一的解决方案是重新引导，然后检查dblink是否有效.

First of all, make sure you are using the correct DSN architecture (32 or 64 bits). It will fire errors if using incorrect architecture. Oracle on Windows has a strange behavior, therefor the command lsnrctl reload or even using lsnrctl stop and then lsnrctl start wont help in refreshing the info placed in the files specified (tnsnames, listener, etc..). So the only solution is to reboot and then check if dblink works.

这篇关于将MS SQL Server数据库连接到Oracle 11g数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！