//// // SQL Server ///////// ///////////////////
Exec sp_droplinkedsrvlogin dbvip, null
Exec sp_dropserver dbvip
Exec sp_addmediaserver
@ Server = 'dbvip ',--Access Server alias
@ Srvproduct = '',
@ Provider = 'sqloledb ',
@ Datasrc = 'server2 '--Server to be accessed
Exec sp_add1_srvlogin
'Dbvip ',--Access Server alias
'False ',
Null,
'Sa ',--Account
'Thankyoubob '--Password
Select * From dbvip. Pubs. DBO. Orders
////////// //////////////////
YesSQL ServerServer InstallationOracleClient;
Exec sp_droplinkedsrvlogin demo, null
Exec sp_dropserver demo
Go
Exec sp_addmediaserver
@ Server = 'Demo ',--Alias of the linked server to be created
@ Srvproduct = 'oracle ',--Product Name
@ Provider = 'msdaora ', -- OLE DBCharacter
@ Datasrc = 'servicename '--Data SourceOracle "ora92" network "admin" tnsnames. oraView
Exec sp_add1_srvlogin
'Demo ',--Name of the linked server
'False ',--Fixed*/
Null ,--For each loginSQL ServerUser using this link server, otherwise write the user name*/
'Userid ',--Account
'Password'-Password
Go
Select * from demo... userid. Table
--Note that uppercase is used, becauseOracleThe data dictionary is in uppercase.
The database format must be the name of the linked server.. OracleUser Name.Table Name.
Select * From openquery (demo, 'select * From tbdemo ')
Update openquery (demo, 'select ID from tbdemo where id = 100 ')
Set Name = 'hello ';
Insert openquery (demo, 'select ID from tbdemo ')
Values ('hello ');
Delete openquery (demo, 'select ID from tbdemo where name = 'Hello ''');
In this caseSQL ServerYou can accessOracle.
The linked server can be seen in the Enterprise Manager..
////////// //////////////////
--Establish a connection Server
Exec sp_addmediaserver
'Ai ',--Name of the linked server to be created
'Access ',--Product Name
'Microsoft. Jet. oledb.4.0 ', -- OLE DBCharacter
'D: "testdb" DB. mdb '--Data Source--Drive letter:"Path"File Name--""Network Name"Share Name"File Name(Online version)
Go
--Create a ing between remote logins on the linked server
--The linked server is set to use the login context by default.
--Now we can change it to a connection server without any logon or password.
Exec sp_add1_srvlogin 'ai', 'false'
Go
Select * from AI... mytable
/// // Excel ////////// //////////////////
--Establish a connection Server
Exec sp_addmediaserver
'Ai _ Ex ',--Name of the linked server to be created
'X ',--Product Name
'Microsoft. Jet. oledb.4.0 ', -- OLE DBCharacter
'D: "testdb" mybook.xls ',--Data Source--Drive letter:"Path"File Name--""Network Name"Share Name"File Name(Online version)
Null,
'Excel 5.0 '-- OLE DBProvideProgramSpecific connection string
Go
----Create a ing between remote logins on the linked server
--The linked server is set to use the login context by default.
--Now we can change it to a connection server without any logon or password.
Exec sp_add1_srvlogin 'ai _ ex', 'false'
Go
--Query data
Select * From ai_ex... sheet3 $
///////////////////// Ms SQL ///////// ///////////////////
--Establish a connection Server
Exec sp_addmediaserver
'Ai _ mssql ',--Name of the linked server to be created
'Ms ',--Product Name
'Sqlodb', -- OLE DBCharacter
'192. 204.111.111, 100 '--Data Source
Go
--Create a ing between remote logins on the linked server
Exec sp_add1_srvlogin
'Ai _ mssql ',
'False ',
Null,
'Hangzhou ',--Login username of the remote server
'Fish2231 '--Logon password of the remote server
Go
--Query data
Select * From ai_mssql.pubs.dbo.jobs
--There is also a simpler method
--This method is enabled by default after the linked server is created.RPCOf
--Establish a connection Server
Exec sp_addmediaserver
'192. 204.111.111, 123 ',--Name of the linked server to be created
'SQL Server '--The data source is used as the name.
Go
--Create a ing between remote logins on the linked server
Exec sp_add1_srvlogin
'1970. 204.111.111, 123 ',
'False ',
Null,
'Hangzhou ',--Login username of the remote server
'Fish2231 '--Logon password of the remote server
Go
--Query data
Select * from [218.204.253.131, 3342] .pubs.dbo.jobs
Go