1: Perform the stored procedure on another server
Exec openDataSource ('sqloledb', 'data source = remote IP; user ID = sa; password = password'). Database name. dbo. Store
2: Insert the information into the table on another server
Select * Into local library name .. Name from OpenDataSource ('sqloledb', 'data source = remote IP; user ID = sa; password = password'). Database name .dbo. Table name
INSERT local library name .. Name Select * from OpenDataSource ('sqloledb', 'data source = remote IP; user ID = sa; password = password'). library name .dbo. Table name
Or use the Joint Server: EXEC SP_ADDLINKEDSERVER 'alias', '', 'msdasql', null, null, 'driver = {SQL Server}; server = remote name; UID = user; PWD = password;'
Exec sp_addlinkedsrvlogin @ rmtsrvname = 'alias', @ useseelf = 'false', @ locallogin = 'sa', @ rmtuser = 'sa', @ rmtpassword = 'password' Go
(Please pay attention to the above two steps to run at the same time)
Then you can follow the following: SELECT * from the alias. Name .dbo. Table name INSERT library name. Dbo. Table name Select * from alias. Database name .dbo. Table name Select * INTO library name .dbo. New table name from Alias. The name .dbo. Table name Go
3:
If you use the connection server to use the connection server in your transaction.
Distributed transaction: start DTC on both sides
SET XACT_ABORT ONSET ANSI_NULL_DFLT_ON ONSET ANSI_WARNINGS ON
Begin Distributed TransactionSelect * from OpenDataSource ('msdasql', 'driver = {sql server}; server = ip; uid = sa; pwd = password;'). Pubs.dbo.JobsCommit TRAN
