SQL Server Distributed query essay (link server (sp_addlinkedserver) and Telnet mapping (sp_addlinkedsrvlogin) using a small summary)

Due to the diversity of business logic, it is often required to query data in different databases in SQL Server, which results in the requirement of distributed query.

Now I will be in the development of several types of queries summarized as follows:

Version 1.access

--Establish a connection server
EXEC sp_addlinkedserver
--The 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 \ filename
--\ \ Network name \ share name \ file name (Network version)
' D:\testdb\db.mdb '
GO

--Create mappings between remote logins on a linked server
--The linked server is set by default to use the login context
--Now we modify to connect the linked server without using any login or password
exec sp_addlinkedsrvlogin ' ai ', ' false '
Go

--Querying data
SELECT * FROM Ai...mytable
Go

Version 2.excel

--Establish a connection server
EXEC sp_addlinkedserver
--The name of the linked server to be created
' AI_EX ',
--Product Name
' Ex ',
--ole DB Character
' Microsoft.Jet.OLEDB.4.0 ',
--Data source
--Format:
--Drive letter: \ path \ filename
--\ \ Network name \ share name \ file name (Network version)
' D:\testdb\mybook.xls ',
Null
--ole DB Provider-specific connection string
' Excel 5.0 '
GO

----Creating mappings between remote logins on a linked server
--The linked server is set by default to use the login context
--Now we modify to connect the linked server without using any login or password
exec sp_addlinkedsrvlogin ' ai_ex ', ' false '
Go

--Querying data
SELECT * FROM AI_EX ... sheet3$
Go

3.ms SQL version

--Establish a connection server
EXEC sp_addlinkedserver
--The name of the linked server to be created
' Ai_mssql ',
--Product Name
' Ms ',
--ole DB Character
' SQLOLEDB ',
--Data source
' 218.204.111.111,3342 '
GO

--Create mappings between remote logins on a linked server
EXEC sp_addlinkedsrvlogin
' Ai_mssql ',
' False ',
Null
--Login user name of remote server
' Zhangzhe ',
--Login password for remote server
' fish2231 '
Go

--Querying data
SELECT * FROM Ai_mssql.pubs.dbo.jobs
Go

--there's an easier way.
--This method is the default open RPC after the linked server is established

--Establish a connection server
EXEC sp_addlinkedserver
--The name of the linked server to be created
--Here is the name of the data source
' 218.204.111.111,3342 ',
' SQL Server '
GO

--Create mappings between remote logins on a linked server
EXEC sp_addlinkedsrvlogin
' 218.204.111.111,3342 ',
' False ',
Null
--Login user name of remote server
' Zhangzhe ',
--Login password for remote server
' fish2231 '
Go

--Querying data
SELECT * FROM [218.204.253.131,3342].pubs.dbo.jobs
Go

Version 4.Oracle

--Establish a connection server
EXEC sp_addlinkedserver
--The name of the linked server to be created
' O ',
--Product Name
' Oracle ',
--ole DB Character
' Msdaora ',
--Data source
' ACC '
GO

--Create mappings between remote logins on a linked server
EXEC sp_addlinkedsrvlogin
' O ',
' False ',
Null
Login user name for--oracle server
' f02m185 ',
Login password for--oracle server
' f02m185185 '
Go

--Querying data
--Format: Linkserver. Oracle user name. Table name
--pay attention to uppercase because it is capitalized in Oracle's data dictionary
SELECT * from O.. f02m185. AI
Go

SQL Server Distributed query essay (link server (sp_addlinkedserver) and Telnet mapping (sp_addlinkedsrvlogin) using a small summary)