018_异地数据操作
018_异地数据操作
--【分布式查询】异地数据库的异种数据操作
/*
一、分布式查询定义：
MSSQlServer所谓的分布式查询(Distributed Query)是能够访问存放在同一部计算机或不同计算机上的SQL Server或不同种类的数据源, 需要根据数据源的类别来使用相对应的接口.从而实现在服务器跨域或跨服务器访问.
二、执行机制：
client（客户端）向服务器请求数据，此时先检查 memcache server(缓冲服务器)（本地）是否有此数据，没有的话就会根据相关数据源的接口调用异地数据，然后更新缓存服务器MemCache Server上数据，保持数据更新同步, 同时向客户端Client直接返回数据
三、MSSQL SERVER 支持的分布式查询的方法：
<A>使用添加链接服务器方式(Add Link Server)
适用于用于长期的，数据量大的持久性操作
<B>使用特定名称及特定数据源来直接指定(Add Host Names)
适用于临时的信息获取，不适用于大批量的数据提取，有性能瓶颈
*/
--【Add Link Server】配置链接服务器*************************************************************************
/*
1.直接调用链接服务器
2.通过openquery()方法调用链接服务器
*/
--1.直接调用链接服务器-----------------------------------------------------
--①.与ms sql的链接----------------------
--创建链接服务器的方法：
EXEC sp_addlinkedserver @server='test_linked', --被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='172.16.0.214' --要访问的服务器
--创建链接服务器上远程登录之间的映射
EXEC sp_addlinkedsrvlogin
'test_linked', --被访问的服务器别名
'false', --为FALSE 时，用于连接到 rmtsrvname 的远程登录名
NULL,
'fpf', --帐号
'214fzgj' --密码
--操作数据库
SELECT * FROM [test_linked].chisdb_brjm_mz.dbo.yp_dict
INSERT into #test SELECT * FROM [test_linked].chisdb_brjm_mz.dbo.yp_dict
--②.与excel的链接----------------------
--建立连接服务器 【Excel 2007及其以上版本】
EXEC sp_addlinkedserver
'test_linked_excel', --要创建的链接服务器名称
'ex', --产品名称
--'Microsoft.Jet.OLEDB.4.0', --OLE DB 字符 32位可用
'Microsoft.ACE.OLEDB.12.0',--OLE DB 字符 64位可用，需搭配【AccessDatabaseEngine_X64.exe】
'f:\test_linked_excel.xlsx' , --数据源
null,
'Excel 12.0' --OLE DB 提供程序特定的连接字符串
----创建链接服务器上远程登录之间的映射
--链接服务器默认设置为用登陆的上下文进行
--现在我们修改为连接链接服务器不需使用任何登录或密码
exec sp_addlinkedsrvlogin 'test_linked_excel','false'
go
EXEC SP_droplinkedsrvlogin @locallogin=null,@rmtsrvname='test_linked_excel'
EXEC sp_dropserver @server='test_linked_excel'
--查询数据
select * from test_linked_excel...sheet1$
--③.Oracle版本----------------------------
--建立连接服务器
EXEC sp_addlinkedserver
'o', --要创建的链接服务器名称
'Oracle', --产品名称
'MSDAORA', --OLE DB 字符
'acc' --数据源
--创建链接服务器上远程登录之间的映射
EXEC sp_addlinkedsrvlogin
'o',
'false',
NULL,
'F02M185', --Oracle服务器的登陆用户名
'f02m185185' --Oracle服务器的登陆密码
--查询数据
--格式：LinkServer..Oracle用户名.表名
--注意用大写，因为在Oracle的数据字典中为大写
select * from o..F02M185.AI
--2.通过openquery()方法调用链接服务器-----------------------------------------------------
--通过openquery()方法调用链接服务器，同样适用于各种异地异种数据的导入与导出，下面只列举与ms sql的链接
--与ms sql的链接----------------------
--创建链接服务器的方法：
EXEC sp_addlinkedserver @server='test_openquery', --被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='172.16.0.214' --要访问的服务器
--创建链接服务器上远程登录之间的映射
EXEC sp_addlinkedsrvlogin
'test_openquery', --被访问的服务器别名
'false', --为FALSE 时，用于连接到 rmtsrvname 的远程登录名
NULL,
'fpf', --帐号
'214fzgj' --密码
--操作数据库
SELECT * FROM OPENQUERY(test_openquery,'SELECT * from chisdb_brjm_mz.dbo.yp_dict')
INSERT into #test SELECT * FROM OPENQUERY(test_openquery,'SELECT * from chisdb_brjm_mz.dbo.yp_dict')
--【Add Host Names】不配置链接服务器*************************************************************************
/*
SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource'的访问，因为此组件已作为此服务器安全配置的一部分而被关闭
*/
--启用Ad Hoc Distributed Queries的方法，执行下面的查询语句就可以了：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE
--使用完毕后，记得一定要要关闭它，因为这是一个安全隐患,切记执行下面的SQL语句
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
不配置链接服务器而对异地数据库进行数据操作的方法：
/*
1.OPENDATASOURCE() 无法在连接时指定查询使用起来稍显笨拙
2.OPENROWSET() 使用简单而且支持在连接时制定查询语句使用很灵活
3.openrowset(BULK) 主要用于库中表与本地文件的数据交互，见上一篇
4.bulk insert 主要用于库中表与本地文件的数据交互，见上一篇
5.bcp 主要用于库中表与本地文件的数据交互，见上一篇
*/
--1.OPENDATASOURCE()-----------------------------------------------------
--opendatasource使用SQLNCLI的一些例子
select * from opendatasource('SQLNCLI','172.16.0.214;fpf;214fzgj;').chisdb_brjm_mz.dbo.yp_dict as t
select * from opendatasource('SQLNCLI','Server=(local);UID=sa;PWD=***;DataBase=TB').TB.dbo.school as t
--opendatasource使用OLEDB的例子
select * from opendatasource('SQLOLEDB','Server=(local);Trusted_Connection=yes;').TB.dbo.school as t
--opendatasource其他使用
insert opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').TB.dbo.school(name) values('ghjkl')/*要不要where都一样，插入一行*/
update opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').TB.dbo.school set name='kkkkkk'
delete from opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').TB.dbo.school where id=1
--2.openrowset()-----------------------------------------------------
--是一种使用 OLE DB 连接并访问远程数据的一次性的临时方法。对于较频繁引用 OLE DB 数据源的情况，应该改为使用链接服务器.
--①从ip为 [172.16.0.214] 的目标数据库上上传数据到本地数据库中【必须保证网络可通】【库与库】
--在本地数据库中创建目标数据表
CREATE TABLE #test_liwz(charge_code NVARCHAR(10))
--将目标库中的数据插入到本地表
insert into #test_liwz
select * from openrowset('SQLOLEDB','172.16.0.214';'fpf';'214fzgj','SELECT charge_code FROM chisdb_bjrm_zy.dbo.yp_dict')
--②查询本地数据库的数据（无意义）
select * from openrowset('SQLOLEDB','localhost\liwz';'sa';'123',TEST.dbo.test) as t
select * from openrowset('SQLOLEDB','Server=localhost\liwz;Trusted_Connection=yes;','SELECT * FROM TEST.dbo.test') as t
select * from openrowset('SQLOLEDB','Server=localhost\liwz;PWD=123;UID=sa;Trusted_Connection=no;','SELECT * FROM TEST.dbo.test') as t
--openrowset使用OLEDB的一些例子
select * from openrowset('SQLOLEDB','Server=(local);PWD=***;UID=sa;','select * from TB.dbo.school') as t
select * from openrowset('SQLOLEDB','Server=(local);PWD=***;UID=sa;',TB.dbo.school) as t
select * from openrowset('SQLOLEDB','Server=(local);Trusted_Connection=yes;',TB.dbo.school) as t
select * from openrowset('SQLOLEDB','(local)';'sa';'***','select * from TB.dbo.school') as t
select * from openrowset('SQLOLEDB','(local)';'sa';'***',TB.dbo.school) as t
select * from openrowset('SQLOLEDB','(local)';'sa';'***','select school.id as id1,people.id as id2 from TB.dbo.school inner join TB.dbo.people on school.id=people.id') as t
--openrowset使用SQLNCLI的一些例子(SQLNCLI在SqlServer2005以上才能使用)
select * from openrowset('SQLNCLI','(local)';'sa';'***','select * from TB.dbo.school') as t
select * from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select * from TB.dbo.school') as t
select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;','select * from TB.dbo.school') as t
select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;',TB.dbo.school) as t
select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;DataBase=TB','select * from dbo.school') as t
--openrowset 还可用于对远程表进行insert，update，delete操作，但不能使用truncate table
insert openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from TB.dbo.school where id=1') values('ghjkl')/*要不要where都一样，插入一行*/
update openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from TB.dbo.school where id=1') set name='kkkkkk'
delete from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from TB.dbo.school where id=1')
bcp 数据表与本地文件的导入导出交互
--参考博文：
http://www.cnblogs.com/OpenCoder/archive/2010/03/18/1689321.html
http://www.cnblogs.com/chenkai/archive/2010/09/09/1822305.html
--1.SQLOLEDB 和 SQLNCLI 的区别
/*
SQLOLEDB = Microsoft OLE DB Provider for SQL Server
SQLNCLI=Microsoft SQL Native Client OLE DB
sqloledb 一般在sql2000及以上版本都可以
而sqlncli 一般只在sql2005及以上版本
*/
--2.如果ms sql是64位，且访问接口中没有【Microsoft.ACE.OLEDB.12.0】接口，那么上述ms sql 与 excel 的数据调用会出现错误。
--解决方法:安装【AccessDatabaseEngine_X64.exe】，安装后，访问接口中会自动添加【Microsoft.ACE.OLEDB.12.0】接口。
--如果还不行，可以试试执行下列代码：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--允许在进程中使用ACE.OLEDB.12
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
--允许动态参数
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
--3.SQL SERVER 的登陆原理
/*
SQL Server 2000 在连接验证之前，用户先用Windows NT或2000的组帐号在客户端成功登录至SQL Server 2000服务器，方能建立一个信任连接(Trusted connection)
SQL Server从信任连接属性中获取用户的帐号信息，将其与Windows已定义的帐号信息匹配和分析，如果正确就连接成功，并将此Windows帐号作为连接至SQL Server 2000的用户ID。
*/
--4.Trusted_Connection=yes; 意味着连接将采用信任连接方式
--5.sp_addlinkedserver的详细解释
http://msdn.microsoft.com/zh-cn/library/ms190479.aspx
--6.下表显示为能通过 OLE DB 访问数据源而建立链接服务器的方法。对于特定的数据源，可以使用多种方法为其设置链接服务器；该表中可能有多行适用于一种数据源类型。该表还显示了用于设置链接服务器的 sp_addlinkedserver 参数值。
远程 OLE DB 数据源 OLE DB 访问接口 provider_name data_source
【SQL Server】 Microsoft SQL Server Native Client OLE DB 访问接口
【SQL Server】 Microsoft SQL Server Native Client OLE DB 访问接口 【SQLNCLI 【SQL Server 的网络名称（用于默认实例）
【SQL Server】 Microsoft SQL Server Native Client OLE DB 访问接口 【SQLNCLI 【servername\instancename（用于特定实例）
【Access/Jet】 Microsoft OLE DB Provider for Jet 【Microsoft.Jet.OLEDB.4.0 【Jet 数据库文件的完整路径
【ODBC 数据源】 Microsoft OLE DB Provider for ODBC 【MSDASQL 【ODBC 数据源的系统 DSN
【ODBC 数据源】 Microsoft OLE DB Provider for ODBC 【MSDASQL
【文件系统】 Microsoft OLE DB Provider for Indexing Service 【MSIDXS 【索引服务目录名称
【IBM DB2 数据库】 Microsoft OLE DB Provider for DB2 【DB2OLEDB
【Microsoft Excel 电子表格】 Microsoft OLE DB Provider for Jet 【Microsoft.Jet.OLEDB.4.0 【Excel 文件的完整路径
【Oracle】 Microsoft OLE DB Provider for Oracle 【MSDAORA 【用于 Oracle 数据库的 SQL*Net 别名
【Oracle，版本 8 及更高版本】 Oracle Provider for OLE DB 【OraOLEDB.Oracle 【用于 Oracle 数据库的别名
