SQL Server link service configuration

A. Link to the SQL Server:

1. Use the ODBC Microsoft ole db Provider

EXEC sp_addmediaserver 'Alias ', '', 'msdasql', NULL, NULL, 'driver = {SQL Server}; SERVER = remote name; UID = user; PWD = password ;'
Go
Exec sp_add1_srvlogin @ rmtsrvname = 'aliased ', @ useself = 'false', @ locallogin = 'sa', @ rmtuser = 'sa ', @ rmtpassword = 'Password'
2. Use SQL Server's Microsoft OLE DB Provider

Exec sp_addlinkedserver @ server = 'Alias ', @ provider = 'sqlodb', @ srvproduct = '', @ datasrc = 'remote server name'
Exec sp_addjavassrvlogin @ rmtsrvname = 'wzb', @ useself = 'false', @ locallogin = 'sa ', @ rmtuser = 'sa', @ rmtpassword = 'Password'

3. operation data
Select * from alias. Database Name. dbo. Table Name
Insert database name. dbo. Table Name select * from alias. Database Name. dbo. Table Name
Select * into database name. dbo. New table name from alias. Database Name. dbo. Table Name
Go

Example 1: Create a linked Server named S1_instance1 on the SQL Server instance, which uses the Microsoft ole db provider of SQL Server.

EXEC sp_addmediaserver @ server = 's1 _ instance1 ', @ srvproduct = '', @ provider = 'sqloledb', datasrc = 's1 \ instance1'

Example 2: Create a linked server
EXEC sp_addmediaserver 'xiaoming', '', 'msdasql ', NULL, NULL, 'driver = {SQL Server}; SERVER = 192.168.0.1; UID = sa; PWD = 123 ;'
Create a connection Server login ing
Exec sp_addmediasrvlogin @ rmtsrvname = 'xiaoming', @ useself = 'false', @ locallogin = 'sa ', @ rmtuser = 'sa', @ rmtpassword = '2016'
Go
-- Query data
Select * from xiaoming. schooladmin. dbo. agent

-- Delete the link Server login ing and link Server:
Exec sp_droplinkedsrvlogin 'xiaoming', 'sa'
Exec sp_dropserver 'xiaoming'

Note:

SET IDENTITY_INSERT [database. [owner.] {table} {ON | OFF}. Therefore, you cannot SET this attribute by connecting to the server.
Into also has this problem: select * into xiaoming. northwind. dbo. tt from xiaoming. northwind. dbo. tt

B. Set the linked server to Access the Access database

Use the Microsoft ole db provider for Jet. In this example, create a linked server named test.

This example assumes that Microsoft Access and the example Northwind database have been installed, and the Northwind database resides in C :\.
USE master
GO
-- To use named parameters:
EXEC sp_addmediaserver @ server = 'test', @ provider = 'Microsoft. Jet. OLEDB.4.0 ', @ srvproduct = 'ole DB Provider for Jet ',
@ Datasrc = 'C: \ Northwind. mdb'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addmediaserver 'test', 'ole DB Provider for Jet ', 'Microsoft. Jet. OLEDB.4.0', 'c: \ Northwind. mdb'
GO
Data Query
Select * from test... table name