sqlserver2008连接oracle11g

最新推荐文章于 2021-04-06 08:10:22 发布

wutian_713

最新推荐文章于 2021-04-06 08:10:22 发布

阅读量902

点赞数

分类专栏： Database

Database 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Configuring Connection fromSQL Server to Oracle using Linked Server

SQLServer Linked Servers technology allows you to access non-SQLServer databases from a SQL Server database using OLE DB providers.

You can connect to SQLServer and execute commands against OLE DB data sources on remoteservers.

OLE DB Provider forOracle

To create a linked server, you need an OLE DB provider and OLE DB data source.

Microsoft OLE DB Provider for Oracle (MSDAORA)

Microsoft provides OLE DB Provider for Oracle withSQLServer installation but you also need to install Oracle Client software and configure connection to Oracle. The name of this provider is MSDAORA.

Oracle OLE DB Provider (OraOLEDB. Oracle)

OLE DB provider provided by Oracle that also requiresOracle Client software. The name of this provider isOraOLEDB.Oracle.

CreatingLinked Server

Use sp_addlinkedserver procedure to create thelinked server, specifyingMSDAORA as provider_name, and the SQL*Net alias name for the Oracle database instance as data_ source.

   sp_addlinkedserver @server = 'ORASRV', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'ORCL'

@server specifies the server name that will be used in the SQL statements.

@datasrc is an alias from tnsnames.ora file (%ORACLE_NOME%\network\admin):

    ORCL =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = dbsrv)(PORT = 1521))
       )
       (CONNECT_DATA =
         (SERVICE_NAME = orcl.domain.com) ) )

Then use sp_addlinkedsrvlogin to create a login toOracle:

   sp_addlinkedsrvlogin @rmtsrvname = 'ORASRV', @useself = 'False', @locallogin = NULL, 
       @rmtuser = 'SCOTT', @rmtpassword ='TIGER'

After you have created the linked server and login, you can query an Oracle table fromSQLServer:

   SELECT * FROM ORASRV..SCOTT.DEPT

Note. If the table and column names were created in Oracle without quoted identifiers, use them in uppercase. Otherwise you will receive “The table either does not exist or the current user does not have permissions on that table” error.

CreatingLinked Server using OraOLEDB.Oracle Provider

You can also create a linked server using Oracle OLE DB provider.

Use sp_addlinkedserver procedure and specifyOraOLEDB.Oracle as provider_name, and theSQL*Net alias name for theOracle database instance as data_ source.

   sp_addlinkedserver @server = 'ORASRV2', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle', 
       @datasrc = 'ORCL'

@server specifies the server name that will be used in the SQL statements. @datasrc is an alias fromtnsnames.ora file (%ORACLE_NOME%\network\admin).

Then use sp_addlinkedsrvlogin to create a login toOracle:

   sp_addlinkedsrvlogin @rmtsrvname = 'ORASRV2', @useself = 'False', @locallogin = NULL, 
       @rmtuser = 'SCOTT', @rmtpassword ='TIGER'

Note that before you can use Oracle OLE DB provider, you have to setAllow inprocess option (SSMS→Instance→Server Objects→LinkedServer→Providers→OraOLEDB.Oracle) andrestart the SQLServer instance.

通过linkserver访问数据库的SQL语法

a) 使用T-SQL语法:

SELECT * FROM OraTest.ERP.BAS_ITEM_CLASS

注意在，SQL查询分析器中输入SQL语句时注意中文的全角半角切换方式！

b) 使用PLSQL语法：
SELECT * FROM openquery(OraTest,'SELECT * FROM OraTest.ERP.BAS_ITEM_CLASS ')

c)第二种访问方式比第一种约快50％；第二种访问方式跟直连ORACLE的速度相当；第一种访问方式可能会导致一些意外错误，如：
该表不存在，或者当前用户没有访问该表的权限等等一些信息。

d)如果需要访问的column中使用没有精度的数据类型，这两种查询方式都可能会报错，这是ORACLE的BUG，无法修正，只能通过查询语句的特殊处理规避这一问题：
OLE DB 提供程序 'OraOLEDB.Oracle' 为列提供的元数据不一致。执行时更改了元数据信息。