LinkedServer 的一些知识

SQL?SERVER??linked?server

--创建链结服务器???
??exec???sp_addlinkedserver?????'srv_lnk','','SQLOLEDB','远端服务器名或ip位址'???
??exec???sp_addlinkedsrvlogin???'srv_lnk','false',null,'用户名','密码'???
??exec???sp_addlinkedsrvlogin??'linkserverme','本地用户','连接服务器用户','连接服务器用户密码'?
????
??--查询示例???
??select???*???from???srv_lnk.数据库名.dbo.表名???
????
??--导入示例???
??select???*???into???表???from???srv_lnk.数据库名.dbo.表名???
????
??--处理完成后删除链结服务器???
??exec???sp_dropserver???'srv_lnk','droplogins'???
????
????
??--如果只是临时访问,可以直接用openrowset???
??--查询示例???
??select???*???from???openrowset('SQLOLEDB'???
??,'sql服务器名';'用户名';'密码'???
??,数据库名.dbo.表名)?
-------------------------------------------
sp_linkedservers?
sp_catalogs
sp_indexes

exec???sp_addlinkedserver?????'SerA','192.168.0.1'?
exec???sp_addlinkedsrvlogin??'SerA','sa','sa'??
exec???sp_addlinkedserver?????'SerB','?192.168.0.1'?
exec???sp_addlinkedsrvlogin??'SerB','sa'

sp_linkedservers
sp_catalogs?SerA
sp_indexes?SerA

sp_linkedservers
exec???sp_dropserver???'srv_lnk','droplogins'
??
select???top?3?*???from???SerA.Northwind.dbo.Products?where?ProductID<4
union
select???top?2?*???from???SerB.Northwind.dbo.Products?where?ProductID>4?
-------------------------------------------

SELECT?a.*?
FROM?OPENROWSET('SQLOLEDB',?'KFServer';?
'sa';'sa',
'SELECT?ProductID,?UnitPrice?FROM?Northwind.dbo.Products?ORDER?BY?UnitPrice')
AS?a
GO

SELECT?a.*
FROM?OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:Program?FilesMicrosoft?OfficeOfficeSamplesNorthwind.mdb';
'Admin';'',
订单)
AS?a
GO

USE?Northwind
GO
SELECT?cust.*,ord.*?
FROM?Customers?as?cust?JOIN
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
订单)
AS?ord
On?cust.customerid?=?ord.客户ID
GO

---------------------------
EXEC?sp_addlinkedserver
'KFServer',
'SQL?Server'
GO

select?*?from?master.dbo.sysservers
GO

EXEC?sp_addlinkedsrvlogin
@rmtsrvname?=?'Floyd',
@useself?=?'false',
@locallogin?=?'sa',
@rmtuser?=?'sa',
@rmtpassword?=?'sa'
GO

EXEC?sp_addlinkedsrvlogin
@rmtsrvname?=?'Floyd',
@rmtuser=?'allcustomers'
GO

USE?master
EXEC?sp_serveroption?'Floyd',
'collation?compatible',?true
GO

SELECT?*?FROM?OPENQUERY
(Floyd,?'SELECT?ProductID,ProductName??
FROM?Northwind.dbo.Products')
GO

EXEC?Floyd.master.dbo.sp_linkedservers

SET?XACT_ABORT?ON
BEGIN?DISTRIBUTED?TRANSACTION

...............

COMMIT?TRAN

----------------------------
//Access
EXEC?sp_addlinkedserver?
???@server?=?'ACC',?
???@provider?=?'Microsoft.Jet.OLEDB.4.0',?
???@srvproduct?=?'OLE?DB?Provider?for?Jet',
???@datasrc?=?'C:Northwind.mdb'?

EXEC?sp_addlinkedsrvlogin
@rmtsrvname?=?'ACC',
@rmtuser=?'Admin'

select?*?from?acc...订单 --------------------------

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!