创建远程连接服务器

--启用Ad Hoc Distributed Queries

EXEC sys.sp_configure 'show advanced options',1 reconfigure

EXEC sys.sp_configure 'Ad Hoc Distributed Queries',1 reconfigure

--创建一个链接的服务器

EXEC sys.sp_addlinkedserver 'ITSV','','SQLOLEDB','192.168.0.13\MSSQLSERVER08R2'

EXEC sys.sp_addlinkedsrvlogin 'ITSV','false',NULL,'sa','sa2008r2'

 

declare @cardID nvarchar(50);

select TOP 1 @cardID=cardID from ITSV.[pubs].[dbo].[bank];

select TOP 1 * from ITSV.[pubs].[dbo].[bank];

print @cardID;

--关闭Ad Hoc Distributed Queries

EXEC sys.sp_configure 'Ad Hoc Distributed Queries',0 reconfigure

EXEC sys.sp_configure 'show advanced options',0 reconfigure

--以后不再使用时，删除链接服务器

EXEC sys.sp_dropserver 'ITSV','droplogins'

--启用Ad Hoc Distributed Queries

EXEC sys.sp_configure 'show advanced options',1 reconfigure

EXEC sys.sp_configure 'Ad Hoc Distributed Queries',1 reconfigure

--OPENROWSET

SELECT TOP 1 * FROM OPENROWSET('SQLOLEDB','192.168.0.13\MSSQLSERVER08R2';'sa';'sa2008r2',[pubs].[dbo].[bank])

--OPENQUERY

EXEC sys.sp_addlinkedserver 'ITSV','','SQLOLEDB','192.168.0.13\MSSQLSERVER08R2'

EXEC sys.sp_addlinkedsrvlogin 'ITSV','false',NULL,'sa','sa2008r2'

SELECT TOP 1 * FROM OPENQUERY(ITSV,'select TOP 1 * FROM [pubs].[dbo].[bank]')

--OPENDATASOURCE

SELECT TOP 1 * FROM OPENDATASOURCE('SQLOLEDB','Data Source=192.168.0.13\MSSQLSERVER08R2;

User ID=sa;Password=sa2008r2').[pubs].[dbo].[bank]