Implementing cross-Library queries in Oracle&sqlserver

One, connect another SQL Server library data in SQL Server

In SQL, you can create a linked server when you want to query a data table in another database in a local library:

EXEC master.dbo.sp_addlinkedserver @server = N ' aliases ', @srvproduct =n ' library name ', @provider =n ' SQLOLEDB ', @datasrc =n ' server address '

EXEC Master.dbo.sp_addlinkedsrvlogin @rmtsrvname = n ' Alias ', @locallogin = NULL, @useself = N ' False ', @rmtuser = N ' username ', @rmt Password = N ' password '

Once created, you can use the select * from alias. Library name. dbo. Table name "to query.

Or you can create it manually:

Ii. Connecting another Oracle library data in Oracle

In Oracle, it's also similar, when you want to connect to another library, you need to create a connection like this:

CREATE DATABASE link alias connect to Mode name (user name) identified by "password" using ' TNS name ';

Note: The TNS name in this is the TNS name of the other library you need to connect to, and it must be the TNS name configured on the server side of the library you are currently connected to.

Once created, we can also access: "SELECT * from table name @ alias"

If you are using the PL/SQL development tool, we can also create it directly in the tool:

Third, connect Oracle data in SQL Server

Similarly, you create a database connection, and we connect to the database in OLE DB:

EXEC master.dbo.sp_addlinkedserver @server = N ' aliases ', @srvproduct =n ' library name ', @provider =n ' Msdaora ', @datasrc =n ' TNS name '

EXEC Master.dbo.sp_addlinkedsrvlogin @rmtsrvname = n ' Alias ', @locallogin = NULL, @useself = N ' False ', @rmtuser = n ' pattern name ', @rmt Password = N ' password '

Note: The TNS name inside this is the TNS name configured on the SQL Server side, not locally on the client.

Once created, use the "select * from OPENQUERY (alias, ' select * from schema name. Table name" to execute the query.)

To connect to other types of databases, in fact the way is similar, as long as the corresponding provider to connect.

Iv. connecting SQL Server data in Oracle

Connecting to SQL Server in Oracle is similar, creating a dblink, but the problem is that in creating Dblink, the TNS name used is connected to Oracle and is not configured to connect to SQL Server.

The idea is to use a transparent gateway in Oracle, first in Oracle's installation name, Oracle Net Services and Oracle Transparent gateways, and select Oracle under this key Transparent Gateway for Microsoft SQL Server.

Configure the transparent gateway, edit%oracle_home%/tg4msql/admin/init%oracle_sid%.ora, which contains the configuration information for the TG for SQL Server, where%oracle_sid% is the "SID" for TG, The default is Tg4msql. Modify the lines in the file hs_fds_connect_info= "Server=sql server address; database= Library name".

Then create the listener: Edit the%oracle_home%/network/admin/listener.ora and edit the sid_list of the corresponding listener:

Sid_list_listener=
(sid_list=
(sid_desc=
(sid_name=%oracle_sid%)
(oracle_home=oracle_home_directory)
(Program=tg4msql)
)
)

Where%oracle_sid% is the SID set in the second cloth, the default value is Tg4msql. You need to restart listener after modifying the Listener.ora file for the changes to take effect.

Finally, you can configure the TNS name, if you modify the Tnsname.ora file directly, the added format is:

TNS name =
(description=
(Address= (PROTOCOL=TCP) (Host=sqlserver) (port=1521))
(Connect_data= (sid= Gateway ID))
(Hs=ok))

This way, after the TNS name, you can create a DB Links and then query in the same way as before.

Implementing cross-Library queries in Oracle&sqlserver