--启用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]