Distributed operations between SQL2000 and Oracle

Distributed operations between SQL2000 and Oracle

The latest project will be cascaded with the data generated by Oracle, marking:

Database:
our project: SQL2000 (independent server -- win2003)
Customer: Oracle 9i (independent server -- win2003)
requirements:
when specific data changes in our project, including addition, update, and deletion. to synchronize data in a database of the customer.
I originally wanted to use a trigger. give up because of maintenance issues. instead, use distributed operations.
process:
first, install the Oracle client on our server (because ole db is used for connection). The installation process is skipped. after you test El, you can directly go to Create " linked server "
" Enterprise Manager "-> select your SQL Server -->" security "-->" linked server ". 1
Figure 1:

 Right-click "link server" --> select "new link server" --> write a name "his" in the pop-up form "link server" (to be used in tsql later) select "other data sources" under "server type"
Select Microsoft ole db provider for Oracle from the drop-down list on the right of "provider name"2
Figure 2:

Enter"Product Name", data source, connection string.These contents must be in the following file
"H: \ oracle \ ora92 \ Network \ admin \ tnsnames. ora"Find, where "H:" Is the Oracle installation disk
The following content is displayed in Notepad:

# Tnsnames. ora Network Configuration File : H: \ oracle \ ora92 \ Network \ admin \ tnsnames. ora
# Generated By Oracle configuration tools.

74   =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host =   192.168 . 0.0 ) (Port =   1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = Recdb)
)
)

 

 

Where "74" is "Product Name", "Data Source" to fill in the content, "connection string": uid = Oracle user; Pwd = Oracle password. All content 3

Then select"Security"Page, select"Use this security context"Input"Remote Logon" (Oracle user name) and "remote logon password" (Oracle user password)And then open the "server options" page to set the "value" below"
Select. 4 for all columns.

After you click "OK", all the configurations are complete. Then you can view the "link server" in "Enterprise Manager", and an additional linked server named "his" is displayed.
The following uses 'tsql 'to operate Oracle tables:

 

/*
Openquery (his, 'select * From his. r_dept ')
The 'his 'is the name of the linked server.
*/
-- Query the data in the r_dept table and list fields.
Select   *  
From   Openquery (His, ' Select * From his. r_dept ' )

--Query the data in the r_dept table and only list the content of the 'dept _ Code' field.
Select * 
From Openquery(His,'Select dept_code from hi. r_dept')

-- Query records with specified conditions
Select   *   From   Openquery (His, ' Select * From his. r_address ' ) Where A. addr_code = ' 3294 '
 
-- Insert a new record
Insert   Into   Openquery (His, ' Select addr_code, address from his. r_address ' )
Values ( ' 3294 ' , ' Test ' )

-- Update a record of a specified condition
Update   Openquery (His, ' Select addr_code, address from his. r_address ' ) Set Address = ' Test '   Where Addr_code = ' 3294 '

--Delete records with specified conditions
Delete FromOpenquery(His,'Select * From his. r_address')WhereAddr_code='3294'

Note: oracle users are required to perform the preceding operations.

it is strongly recommended that you add the "real-time Automatic save" function when editing Article . IE crashes twice when writing content, it was rewritten twice. depressed !!!
(end)