sqlserver2008连接oracle11g

Configuring Connection fromSQLServer to Oracle usingLinked Server

SQLServerLinked Servers technology allows you to access non-SQLServer databases from a SQLServer 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 installOracle Client software and configure connection toOracle. 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.

CreatingLinkedServer

Use sp_addlinkedserver procedure to create thelinkedserver, specifyingMSDAORA as provider_name, and theSQL*Net alias name for theOracle 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.

CreatingLinkedServer using OraOLEDB.Oracle Provider

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

Use sp_addlinkedserver procedure and specifyOraOLEDB.Oracle asprovider_name, and theSQL*Net alias name for theOracle database instance asdata_ 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' 为列提供的元数据不一致。执行时更改了元数据信息。