在TSQL中访问远程数据库有三种方式：

1.OPENROWSET

2.OPENDATASOURCE

3.LinkServer

在使用openrowset/opendatasource前搜先要启用Ad Hoc Distributed Queries服务，因为这个服务不安全所以SqlServer默认是关闭的.

开启Ad Hoc Distributed Queries：

exec sp_configure 'show advanced options',1reconfigureexec sp_configure 'Ad Hoc Distributed Queries',1reconfigure

 

使用LinkServer:

--============================================--添加LinkServerEXEC master.dbo.sp_addlinkedserver @server = N'ServerIP,ServerPort', @srvproduct=N'SQL Server'GO--============================================--添加LinkServer的登陆账户EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'ServerIP,ServerPort', @locallogin = NULL , @useself = N'False', @rmtuser = N'UserName', @rmtpassword = N'UserPsw'GO--============================================--使用LinkServer访问表SELECT * FROM [ServerIP,ServerPort].[DB1].[dbo].[TB1]GO--===========================================--使用LinkServer来创建表或执行存储过程EXEC('USE DB1;CREATE TABLE TB1(    ID INT)') AT [ServerIP,ServerPort]GO--===========================================--删除LinkServer EXEC master.dbo.sp_dropserver @server=N'ServerIP,ServerPort', @droplogins='droplogins'GO

 

 

 

使用OPENROWSET

SELECT T.*FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',     'SELECT * FROM DB1.dbo.TB1') AS T;

 

 

使用OPENDATASOURCE

SELECT *FROM OPENDATASOURCE('SQLNCLI',    'Data Source=London\Payroll;Integrated Security=SSPI')    .DB1.dbo.TB1