SQL Server Distributed Query essay (sp_addmediaserver) and remote login ing (sp_addmediasrvlogin) use small summary)

RelatedArticleNavigation

1. SQL server2005 Transact-SQL new weapon learning Summary-Summary
2. Index of flex and fms3 articles
3. Flexair open-source edition-global free multi-person video chat room, free network remote multi-person Video Conferencing System (jointly developed by flex and fms3) <video chat, conference Development Instance 8>

Due to the diversity of business logic, SQL Server often needs to query data in different databases, which leads to the need for distributed query.

Now I will summarize several types of queries encountered in development as follows:

1. Access Version

-- Establish a connection Server
Exec sp_addmediaserver
-- Name of the linked server to be created
'Ai ',
-- Product name
'Access ',
-- Ole db character
'Microsoft. Jet. oledb.4.0 ',
-- Data Source
-- Format:
-- Drive letter: \ path \ file name
-- \ Network Name \ shared name \ file name (online version)
'D: \ testdb \ dB. mdb'
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

-- Query data
Select * from AI... mytable
Go

 

2. Excel version

-- Establish a connection Server
Exec sp_addmediaserver
-- Name of the linked server to be created
'Ai _ Ex ',
-- Product name
'X ',
-- Ole db character
'Microsoft. Jet. oledb.4.0 ',
-- Data Source
-- Format:
-- Drive letter: \ path \ file name
-- \ Network Name \ shared name \ file name (online version)
'D: \ testdb \ mybook.xls ',
Null,
-- Ole db providesProgramSpecific connection string
'Excel 5.0'
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 $
Go

3. ms SQL version

-- Establish a connection Server
Exec sp_addmediaserver
-- Name of the linked server to be created
'Ai _ mssql ',
-- Product name
'Ms ',
-- Ole db character
'Sqlodb ',
-- Data Source
'192. 204.111.111, 123'
Go

-- Create a ing between remote logins on the linked server
Exec sp_add1_srvlogin
'Ai _ mssql ',
'False ',
Null,
-- Login username of the remote server
'Hangzhou ',
-- Logon password of the remote server
'Fish2231'
Go

-- Query data
Select * From ai_mssql.pubs.dbo.jobs
Go

-- A Simpler Method
-- After the connection server is established, RPC is enabled by default.

-- Establish a connection Server
Exec sp_addmediaserver
-- Name of the linked server to be created
-- The data source is used as the name.
'1970. 204.111.111, 123 ',
'SQL Server'
Go

-- Create a ing between remote logins on the linked server
Exec sp_add1_srvlogin
'1970. 204.111.111, 123 ',
'False ',
Null,
-- Login username of the remote server
'Hangzhou ',
-- Logon password of the remote server
'Fish2231'
Go

-- Query data
Select * from [218.204.253.131, 3342] .pubs.dbo.jobs
Go

 

4. Oracle version

-- Establish a connection Server
Exec sp_addmediaserver
-- Name of the linked server to be created
'O ',
-- Product name
'Oracle ',
-- Ole db character
'Msdaora ',
-- Data Source
'Acc'
Go

-- Create a ing between remote logins on the linked server
Exec sp_add1_srvlogin
'O ',
'False ',
Null,
-- Login username of the Oracle server
'F02m185 ',
-- Logon password of the Oracle server
'F02m185185'
Go

-- Query data
-- Format: linkserver.. Oracle username. Table Name
-- Use uppercase letters because the data dictionary in Oracle is uppercase letters.
Select * from o .. f02m185. AI
Go

 

 

Favorites and sharing

Add QQ bookmarks to Baidu souzang {
Function onclick ()
{
Window. Open ('HTTP: // myweb.cn.yahoo.com/popadd.html? Url = '+ encodeuricomponent (document. location. href) + '& Title =' + encodeuricomponent (document. title), 'yahoo ', 'scrollbars = Yes, width = 440, Height = 440, Left = 80, Top = 80, status = Yes, resizable = Yes ');
}
} "> Add to Yahoo favorites

RSS subscribe to me What is RSS?

Dongguan. Net Club

Welcome to join