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