"Go" SQL Server access to Oracle through linked servers!

Original: http://blog.sina.com.cn/s/blog_614b6f210100t80r.html

Objective:
1, do not experience the wind and rain, how can see the rainbow.
2, make full use of Baidu, Google and other search engines to find data and integrate analysis!
3, nothing is difficult undone!

The origin of this article: the author in the study of SQL Server linked to Oracle and the use of distributed transactions, the emergence of a variety of ' failures, anomalies ', through the ' Baidu, Google ' and other search engines to solve the problem without fruit, but also found that there are a large number of ' Comrade-in-arms ' sacrifice in this way, so this article lists the link server appeared various problems (I experience) and solutions, hope that the vast number of comrades in arms can be obtained through this article solution and inspiration.

Here's where to go:

I. Create a SQL Server linked service (SQL Server link Oracle)

First SQL Server link Oracle can have two access interfaces:

"Msdaora" and "OraOLEDB.Oracle"

1. The "MSDAORA" Access interface is provided by Microsoft OLE DB Provider for Oracle and is not recommended for linking with this interface. A linked server established through this provider will report this error when querying an Oracle table (with data type CLOB, blob fields)

“
The OLE DB provider "Msdaora" of the linked server "" returned the message "An Oracle error has occurred, but error information could not be retrieved from Oracle." "。
The OLE DB provider "Msdaora" of the linked server "" returned the message "data type not supported. "。
Msg 7321, Level 16, State 2, line 1th
An error occurred while preparing to execute the query "select * from Sys_message" for the OLE DB provider "Msdaora" of the linked server "". ”

The OLE DB provider "Msdaora" of the linked server "Link2oracle" provides inconsistent metadata for the column. Object "" CMCC "." The column "isopened" of the Os2_gis_cell "" (Compile-time ordinal 20) has a 130 "DBTYPE" at compile time, but there is an error at run time.

2. The "OraOLEDB.Oracle" Access interface is provided by Oracle's Oracle Probider for OLE DB driver. It solves the problem of inconsistent two database types. And if you need to use a distributed transaction, you must use it to create a linked server. The following article will be described in detail.
Before creating, in SQL Server, link servers--access interface->oraoledb.oracle-> Right-click Properties, check "Allow InProcess" (in Chinese: allows in-process)
This step is to enable the OraOLEDB.Oracle interface we have chosen to open the execute operation. If not set, the following error will be reported:
"Unable to initialize the data source object for the OLE DB provider" OraOLEDB.Oracle "of the linked server" null "

To create the code for SQL Server linked servers using the Oracle Probider for OLE DB driver:

1:--Establish database linked server

2:exec sp_addlinkedserver

3: @server =n ' Oraclepolice ',--the linked server alias to create

4: @srvproduct =n ' Oracle ',--product name

5: @provider =n ' oraoledb.oracle ',--ole DB driver name

6: @datasrc =n ' ORCL '--Data source Oracle "ora10g" network "admin" Tnsnames.ora view

7:

8:exec sp_addlinkedsrvlogin

9: ' Oraclepolice ',--established linked server name

Ten: ' false ',--fixed */

11:null,--to use this linked server for each user logging on to SQL Server, write the user name */

: ' TESTDB ',--account (Oracle)

: ' TESTDB123 '--Password

14:

15:exec sp_serveroption ' Oraclepolice ', ' rpc out ', ' true '

16:exec sp_serveroption ' Oraclepolice ', ' rpc ', ' true '

17:--These two are open rpc,rpc out, default is False, open can support remote change distributed transaction. (If distributed transaction operations have to be set)

This allows us to build a linked server that can already query the Oracle database. , there are two ways of querying.
1.SELECT * from Oraclepolice. TESTDB. Table_test

(linked server alias : Oracle name. Oracle table, note using uppercase)

this way can be operated, the advantage is simple to use, at a glance, and the disadvantage is poor performance , querying the big Data scale is slow. The efficiency is too poor.

2.select * FROM OPENQUERY (oraclepolice, ' select * from table_test ')
After testing, this kind of query is almost as fast as in Oralce. And we can use OPENQUERY () as a table. For example
Insert INTO OPENQUERY (Oraclepolice, ' SELECT * from Table_test ')

Values ();--inserting data into an Oracle data table
SELECT * FROM OPENQUERY (oraclepolice, ' SELECT * from Table_test ')

where Test_name= ' (Test_name is a field in the Table_test table)
It is recommended that this query method.

Ii. invoking Oracle stored procedures via SQL Server linked servers
A test stored procedure is written in the Oracle database, requiring an incoming parameter and an outgoing parameter.
Here is the code to create and invoke this stored procedure:

1:--Create stored procedures in Oracle

2:create or Replace procedure A_test_nq

3: (

4:namecode VARCHAR2,

5:namevalue out VARCHAR2

6:)

7:is

8:C VARCHAR2 (200);

9:begin

10:c:= ";

11:select Name_value into C from Alink_server_test WHERE Name_code=namecode;

12:namevalue:=c;

13:end;

14:

:------------------------------------------------

16:--the evil dividing line

:------------------------------------------------

18:

:--Call a stored procedure in SQL Server

20:declare @namecode varchar (200)--Incoming parameters

21:declare @namevalue varchar (5000)--Outgoing parameters

22:

23:set @namecode = ' 01 '

24:--Call the stored procedure

25:exec (' Begin A_TEST_PRC (?,?); End; ', @namecode, @namevalue output) at Oraclepolice

Here to pay attention to the outgoing parameters, be sure to add output, otherwise it will error.

"Link Server" Oraclepolice "OLE DB provider" OraOLEDB.Oracle "returned the message" Ora-06502:pl/sql: Numeric or value error: String buffer too small
ORA-06512: In the Sacon. A_test_nq ", line 11
ORA-06512: On line 1 ".
Msg 7215, Level 17, State 1, line 6th
Unable to execute statement on remote server ' Oraclepolice '. ”

Iii. implementing distributed transactions through SQL Server linked servers

Many of your peers lie down in this step, as this step is cumbersome to configure and SQL Server, Oracle database version, even OS version and so on, get rid of drowsiness, and I'll step through the configuration.

1, must first install the good

Oracle Services for Microsoft Transaction Server

(version is consistent with your Oracle version)

It is part of the ODAC because Oracle itself is not supported by distributed transactions, which are opened with Microsoft's transactional services through this drive. I think most of the majority of people who configured MSDTC and parsed the name will still report " unable to start the distributed service " because this driver is not installed.

Msg 7391, Level 16, State 2, line 5th
The operation could not be performed because the OLE DB provider "OraOLEDB.Oracle" of the linked server "Oracletest" could not start the distributed transaction.

2. The linked service must be created with the OraOLEDB.Oracle provider provided by the Oracle Probider for OLE DB driver . (This may be due to the fact that the provider provided by Microsoft does not have an Oracle transaction mechanism, so it can only be accessed through Oracle)

3, MSDTC settings.
Open Administrative Tools-Component Services, open Component Services-computers, and right-click on my Computer. In the MSDTC tab, click on the "Security Configuration" button and make the following settings in the Security Configuration window:
-- Check "Network DTC Access";
Check "Allow remote client" "Allow remote Management" in client management;
--Select "Allow Inbound" "Allow Outbound" "Do not require authentication" in the transaction management communication;
--Guaranteed DTC login account: NT AUTHORITY\NetworkService;

4, both sides to start the MSDTC service
The MSDTC service provides a distributed transaction service, and if you want to use distributed transactions in the database, you must

start MSDTC on both sides of the participating server (distributed Transaction Coordinator ) service.

5. Open both sides 135 ports
The MSDTC service relies on the RPC (Remote Procedure call (RPC)) service, RPC uses the 135 port to ensure that the RPC service starts, and if the server has a firewall, ensure that port 135 is not blocked by the firewall .

6. Add the SET XACT_ABORT on statement before the transaction starts
For most OLE DB providers, including SQL Server, you must set the Xact_abort in the data modification statement in implicit or display affairs to on. The only situation where this option is not required is when the provider supports nested transactions.

7. Server name resolution
When you create a linked server @server or @datasrc is set to the SQL Server server name, name resolution is required, which is to resolve the server name to an IP address.

Add a record to the "C:\WINDOWS\system32\drivers\etc\hosts" file:

xxx.xxx.xxx.xxx Server Name

8. Name resolution on the remote server
The participating servers of a distributed transaction are required to access each other, and the server that initiates the query depends on the machine

name or IP to find the remote server, the remote server also looks for the originating server, remote the server locates the server by initiating the machine name of the server, so make sure the remote server can The originating server is accessed by the machine name of the originating server. In general, two servers in the same network segment machine name can be good parsing, but also does not guarantee that all can be very good analysis, so the more insurance approach is:
In the remote server, add a record in the "C:\WINDOWS\system32\drivers\etc\hosts" File :
xxx.xxx.xxx.xxx initiating the server name

Through the above some of the column operation should be almost, the operation of the period you will be crazy, sometimes crash, sometimes go, the only advice I have is: the trial and exploration to adhere to the end, because the author has confirmed that SQL Server to Oracle through the link to the use of distributed transactions.

1:--Distributed transaction testing

2:set Xact_abort on

3:begin Tran

4:

5:insert into OpenQuery (Oraclepolice, ' SELECT * from Alink_server_test ') VALUES (' 456000 ', ' testtest ', ');

6:

7:if @ @ERROR <>0

8:

9:rollback Tran;

10:else

11:

12:commit TRAN;

13:--Statement execution succeeded

I am inspired by the following Web site, thanks to the original author.
Http://blog.csdn.net/akuoma/archive/2010/01/29/5269496.aspx
http://mlj04.blog.163.com/blog/static/5448108220101855248191/
Http://www.chinaitpower.com/A/2004-06-13/83416.html
Http://www.cnblogs.com/huanghai223/archive/2010/12/28/1919073.html

"Go" SQL Server access to Oracle through linked servers!