Data operations between databases on different servers

Version 1 -- create the linked server execsp_add1_serveritsv, SQLOLEDB, remote server name or IP address execsp_add1_srvloginitsv, false, null, user name, password -- Query example select * fromITSV. database Name. dbo. table name -- import example select *

Version 1 -- create the connection server exec sp_add+server 'itsv', '', 'sqlodb', 'remote server name or IP address 'exec sp_add+srvlogin 'itsv', 'false', null, 'username', 'Password' -- Query example select * from ITSV. database Name. dbo. table name -- import example select *

Version 1

-- Create a linked server
Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'
Exec sp_add1_srvlogin 'itsv', 'false', null, 'username', 'Password'

-- Query example
Select * from ITSV. Database Name. dbo. Table Name

-- Import example
Select * into table from ITSV. Database Name. dbo. Table Name

-- Delete the linked server when it is no longer in use
Exec sp_dropserver 'itsv', 'droplogins'

-- Connect to remote/LAN data (openrowset/openquery/opendatasource)
-- 1. openrowset

-- Query example
Select * from openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table name)

-- Generate a local table
Select * into table from openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table name)

-- Import a local table to a remote table
Insert openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table name)
Select * from local table

-- Update local table
Update B
Set B. Column A = a. Column
From openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table Name) as a inner join local Table B
On a. column1 = B. column1

-- Create a connection for openquery usage

-- First create a connection to create a linked server
Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'
-- Query
Select *
FROM openquery (ITSV, 'select * FROM database. dbo. Table name ')
-- Import a local table to a remote table
Insert openquery (ITSV, 'select * FROM database. dbo. Table name ')
Select * from local table
-- Update local table
Update B
Set B. Column B = a. Column B
FROM openquery (ITSV, 'select * FROM database. dbo. Table name') as
Inner join local table B on a. Column A = B. Column

-- 3. opendatasource/openrowset
SELECT *
FROM opendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password'). test. dbo. roy_ta
-- Import a local table to a remote table

Version 2

-- Data operations between databases on different servers
--*************************************** **************************************** *****
1. -- create a linked server
Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'
Exec sp_add1_srvlogin 'itsv', 'false', null, 'username', 'Password'

2. Start the MSDTC Service of the two servers
MSDTC provides the Distributed Transaction service. To use Distributed transactions in the database, you must start the MSDTC (Distributed Transaction Coordinator) service on the servers of both parties involved.

3. open port 135 of both parties
The MSDTC service depends on the Remote Procedure Call (RPC) service. RPC uses port 135 to ensure that the RPC service is started. In US space, if the server has a firewall and a Hong Kong Vm, ensure that port 135 is not blocked by the firewall.
Run the "telnet IP 135" command to test whether the peer port is open to the outside world. You can also use Port scanning software (such as Advanced Port scanning) to scan the Port to determine whether the Port is open.

4. -- to create a trigger
Create trigger t_test on test
For insert, update, delete
As
-- Add the following two sentences to the Hong Kong server. Otherwise, the system will prompt that the new transaction cannot be registered with the specified transaction processor.
Set xact_abort on
Begin distributed tran
Delete from openrowset ('sqloledb', 'xz '; 'sa'; '', test. dbo. test)
Where id in (select id from deleted)
Insert into openrowset ('sqloledb', 'xz '; 'sa'; '', test. dbo. test)
Select * from inserted
Commit tran

-- Query example
Select * from ITSV. Database Name. dbo. Table Name

-- Import example
Select * into table from ITSV. Database Name. dbo. Table Name

-- Delete the linked server when it is no longer in use
Exec sp_dropserver 'itsv', 'droplogins'