SQL Server Distributed Query

Connect to the default instance of SQL server:

exec sp_addlinkedserver @ServerName, N'SQL Server'

 

Used to connect to a specified instance:

EXEC sp_addlinkedserver @server='SQL2005', @srvproduct='', @provider='SQLNCLI', @datasrc='DBhere' 

 

@ Server machine name

@ Provider (Use SQLNCLI and SQL Server will redirect to the latest version of SQL Server Native Client OLE DB Provider .)

@ Datasrc Is the name of the data source as interpreted by the ole db provider

Http://msdn.microsoft.com/en-us/library/ms190479.aspx.

English: http://msdn.microsoft.com/zh-cn/library/ms190479.aspx

Then execute

 

exec sp_addlinkedsrvlogin         @rmtsrvname = @ServerName       , @useself = 'false'       , @rmtuser = @RmtUserName       , @rmtpassword = @RmtPasswd

Log On

You can perform a Distributed Query after the query is successful.

-- 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
Insert opendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password'). database. dbo. Table Name
Select * from local table

Also refer to http://blog.csdn.net/iwteih/archive/2009/09/14/4551118.aspx