Data exchange between SqlServer and Oracle databases

1. Connect to the server
Graphical interface: Open the Enterprise Manager of SQLSERVER and expand the corresponding DATABASE. Under Security, there is a project linked to the server. Right-click to add
Note: You must have the administrator privilege.
Before proceeding, you must set the oracle client on your computer and use TNSPING to connect to your TNS.
Command line:
Add the connection server sp_addmediaserver 'mylinkserver', 'productname, 'msdaora ', 'mix01'
The parameters are the connection server name (required) Product Name connection type (required) Data Source (required and TNS name)
It is not enough to add the user name and password for logging on to ORACLE.
1. If the SQL statements contain the same user account and password as those in ORACLE, if SysLogin is used
Sp_add1_srvlogin 'mylinkserver', true, 'syslogin'
2. The Administrator account of Windows 2000 maps to the scott user account of Oracle, whose password is tiger:
Sp_add1_srvlogin 'mylinkserver', false, 'w2000as \ admin', 'Scott ', 'tiger'
3. Allow all SQL Server login accounts to log on to Oracle Server as scott with the password tiger:
Sp_add1_srvlogin 'mylinkserver', false, NULL, 'Scott ', 'tiger'

In this case, SQLSERVER is connected to ORACLE. How can I use it?
Assume that the user in ORACLE is IQCM and the data table is GRADE.
The linked table format is:Server name.Database Name.Owner name.Object Name.
1. Query: SELECT * FROM MyLinkServer... IQCM. GRADE
2. INSERT: INSERT MyLinkServer .. IQCM. GRADE (deptno, dname, loc) values (2, NULL, NULL)
Note that you do not need to include INTO keywords. Instead of using DEAULT keywords, you must pass the actual field value.
3. UPDATE: UPDATE MyLinkServer .. IQCM. GRADE set deptno = 3 WHERE deptno = 2
This is not special.

Notes

Before you effectively set the linked Server, you must understand the differences in data types between Oracle and SQL Server. If you store historical data in Oracle and SQL Server at the same time, you must note that the date data type of Oracle can be stored on January 1, January 1, 4712 before the epoch, however, the datetime data type stored in SQL Server starts from January 1, January 1, 1753 AD. In addition, Oracle date data types can be stored to 0.01 seconds, while SQL Server datetime data types can be stored to 0.001 seconds. For more information about the SQL Server datetime data type, see the work of Kalen Delaney, Inside SQL Server. For more information about Oracle data types, go to the http://technet.oracle.com. You can get the documents of Oracle after free registration on this website.

In some cases, you may need to query data from multiple databases, or even from another database server! This will be challenging when other databases are Oracle. Microsoft itself does not intend to provide such customer services for Oracle database users, and Oracle does not provide support for SQL Server users. However, the linked Server allows you to easily access the Oracle database information from SQL Server. Now you know how to configure and use the Oracle connection Server. You can access the Oracle database in real time in a comfortable and comfortable SQL Server environment!