<Doctype HTML public-wcdtd XHTML stricten httpwwwworgtrxhtmldtdxhtml-strictdtd> 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 provider-specific 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
# Database Technology