Creating Oracle Connection servers in SQL Server

Transferred from the founder of the Emperor: http://www.cnblogs.com/jirglt/archive/2012/06/10/2544025.html
Reference: http://down.51cto.com/data/910072
Creating Oracle Connection servers in SQL Server

The first type: interface operation

(1) Expand Server objects--linked servers--right-click on "New Linked server"

(2) Enter the IP of the linked server

(3) After the link is successful


second type: statement manipulation

NOTE: The Oracle client program needs to be installed in advance to restart the machine. (If you do not have an Oracle client program, you are prompted "Oracle client and network components not found")

EXEC sp_addlinkedserver

@server = ' Province ',--the server alias being accessed

@srvproduct = ' ORACLE ',--sqlserver does not need to be written by default

@provider = ' Msdaora ',--different libraries are not the same, OLE db characters

@datasrc = ' 192.168.88.88/ORCL '--the server to be accessed

GO



EXEC sp_addlinkedsrvlogin

@rmtsrvname = ' Province ',--the server alias being accessed

@useself = ' false ',--fixed so write

@locallogin = ' sa ',--local operation account name

@rmtuser = ' Kobe ',--the user name of the server being accessed

@rmtpassword = ' kobe123 '--the server being accessed with a password

GO



--Parameter explanation:

sp_addlinkedserver: Create a linked server that allows access to distributed, heterogeneous queries against OLE DB data sources.

Sp_addlinkedsrvlogin: Creates or updates a mapping between a login on a local microsoft®sql Server™ instance and a remote login on a linked server.

Now test it, log in to SQL Server locally with account SA, create a new query, enter

SELECT * from OPENQUERY (province, ' select * from T_base_school ')

Like what:

Select Organization_id,organization_name from OPENQUERY (province, ' select * from T_base_organizationtree ')

No problem, you can query the data.



Some common operations:

--Query operation

SELECT * from OPENQUERY (province, ' select * from T_base_duty ')

--delete operation

DELETE from OPENQUERY (province, ' select * from T_base_duty ')

--insert Operation

INSERT into OPENQUERY (province, ' SELECT * from T_base_role ') (role_id,role_name,bureautype_id,is_system,b_use,field_ order,b_del,identity_id) VALUES (10, ' Kobe ', 7,1,0,1,0,2)

--Update operation

UPDATE OPENQUERY (province, ' SELECT * from T_base_role ') SET role_name= ' Kobe ' WHERE role_id=10

Note: The Technique (OPENQUERY (province, ' select * from T_base_duty ') is considered a table)

Creating Oracle Connection servers in SQL Server