SQL server Distributed Query

Diversity of business logic
It is often necessary to query data in different databases in SQL server.
Therefore, distributed query is required.

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

I hope the Knowledge mentioned above will prompt you.
Of course, you are welcome to contact us and correct us.

Author: aierong
Blog: http://www.cnblogs.com/aierong
Email: aierong@126.com