--启用Ad Hoc Distributed Queries:
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
select * from opendatasource('SQLOLEDB','Data Source=zengfd;UID=sa;Password=1').ticket11.dbo.t_base
--使用完成后,关闭Ad Hoc Distributed Queries:
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
--------------------------------------------------------------------
链接服务器
exec sp_addlinkedserver 'sql132','','SQLOLEDB','10.98.0.132\sql2005'
exec sp_addlinkedsrvlogin 'sql132','false',null,'sa','fangte2005'
----------------------------------------------------------------
为Customers表创建一个Synonym,叫MyCustomers。 你可以把这个MyCustomers当作一个普通的表,可以对它进行查询,更新,删除和插入。比如
Create Synonym MyCustomers For SqlTest.Northiwind.dbo.Customers
查询: Select * from MyCustomers.
插入: Insert into MyCustomers (CustomersID, CompanyName) values ('Tom', 'MS')
所有的操作,和普通的表没有区别。
相对于Synonym,OpenQuery相对来说,功能就弱了点。其只是在调用其它数据库上的sprocs
select * from openquery([SqlTest], 'exec northwind.dbo.[Customers By City] @param1=N''London''') where CustomerID = 'AROUT'