I wrote a Data Synchronization script yesterday and found that the two databases are not on the same server. Later I thought that the database had the function of querying the remote connection database, and I modified the script again. The remote connection database query script is as follows:
-- Connection parameters between servers: @ server writes a name for the server for use below; sqloledb does not need to be changed; @ datasrc is the IP address of the remote database
Exec sp_addlinkedserver @ Server = 'servername', @ provider = 'sqlodb', @ srvproduct = '', @ datasrc = '192. 168.85.20'
-- Server login parameter description: The @ rmtsrvname here should be the same as the @ server above; @ rmtuser Login Server Username; @ rmtpassword Login server password
Exec DBO. sp_add1_srvlogin @ rmtsrvname = n'servername', @ useself = n'false', @ locallogin = NULL, @ rmtuser = n'sa ', @ rmtpassword = n'123'
/* ----------- Abbreviated form ------------------
Exec DBO. sp_addmediaserver 'servername', '', 'sqlodb', '192. 168.85.20'
Exec sp_add1_srvlogin 'servername', 'false', null, 'sa ', '123'
------------------------------------*/
Go
/* ------------ Data operation zone ----------------*/
Select * from [servername]. [database name]. [DBO]. [Table name]
/* -------------- End ---------------------*/
Go
-- Disable server connection
Exec sp_droplinkedsrvlogin 'servername', null
Exec sp_dropserver 'servername'
The stored procedures used are available in "system database master!