Sp_addmediaserver Summary (Oracle, SQL Server, access, Excel)

//// // 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