Mssql->serverlink[oracle]

Requirements Description: SQL Server database connection Oracle database condition preparation: SQL Server database, SQL Server 2008R2 Oracle database, Oracle 12.2.0.1.0 Oracle client, Ora The CLE 11g client or Oracle 12c client must have an Oracle Client (ODBC driver) installed on the SQL Server database host and configure the Tnsnames.ora connection using linked server or row Set function linked server mode, mainly using SP function package to configure and connect Oracle database (other databases are also applicable)

Grammar
sp_addlinkedserver[@server =] 'Server' [, [@srvproduct =] 'Product_Name'][, [@provider =] 'provider_name'][, [@datasrc =] 'Data_source'][, [@location =] ' Location'][, [@provstr =] 'provider_string'][, [@catalog =] 'Catalog'] Provider selectable value "SQLNCLI|SQLOLEDB " -SQL Server, "oraoledb.oracle" -Oracle, "Msdasql" -POSTGRESQL, "Microsoft.Jet.OLEDB.4.0】 -EXCEL

Example:

--Create a link aliasexecMaster.dbo.sp_addlinkedserver@server = 'Oraclelink',--Specify the alias of the linked database@srvproduct = 'Oracle',--products linked to the object database@provider = 'oraoledb.oracle',--Drive@datasrc = 'HMDB0'--to access the server, Oracle needs to provide an alias for the Tnsnames.ora configurationGo  --log in to the linked databaseexecMaster.dbo.sp_addlinkedsrvlogin@rmtsrvname = 'Oraclelink',--Specify the database link alias you want to log on to@locallogin = NULL,--logon name of the local server, direct access to Windows Server Group members@useself = 'false',--Turn off local login, True value using local account authentication, Rmtuser and Rmtpassword parameters are ignored@rmtuser = ' dbMonitor',--Login Database user name@rmtpassword = ' xxxxxx'--Login PasswordGo --Querying Oracle Database tests--the format of the database must be a link alias. Oracle user name. Table nameSelect *  fromOraclelink. Dbmonitor. unique_t;Go  --View current list of all linksexecsp_linkedservers--Remove linked serverExecSp_droplinkedsrvlogin Oraclelink,NULL --Delete the login account of the linked serverExecSp_dropserver Oraclelink--delete a linked database alias

Rowset function mode first create a linked database alias in the graphical interface

　　　　　　

　　　　　　

Test success

　　　　　　

The rowset function returns an available alternative to the object referenced in the Transact-SQL statement OPENQUERY syntax: OPENQUERY (linked_server, ' query ') SELECT * from OPENQUERY (oraclelink, ' SELECT * from unique_t ');

INSERT   INSERT OPENQUERY(Oraclesvr,'SELECT name from Joe.titles')VALUES('Newtitle');UPDATE   UPDATE OPENQUERY(Oraclesvr,'SELECT name from joe.titles WHERE ID = 101')SETName= 'Adifferentname';DELETE   DELETE OPENQUERY(Oraclesvr,'SELECT name from joe.titles WHERE name ="'Newtitle" ");

Reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/rowset-functions-transact-sql?view= sql-server-2017https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/ sp-addlinkedserver-transact-sql?view=sql-server-2017https://www.cnblogs.com/chinhr/archive/2009/03/10/1408033. Html

Mssql->serverlink[oracle]