数据仓库之抽取数据：通过链接服务器获取Access中的数据

 

在做数据仓库时，最重要的就是ETL的开发，而在ETL开发中的第一步，就是要从原OLTP系统中抽取数据到过渡区中，再对这个过渡区中的数据进行转换，最后把经过处理的干净的数据加载到数据仓库中。

 

目标数据库是sql server，现在要从 Access中抽取数据。

1、开启即席查询

--修改高级参数
sp_configure 'show advanced options',1
go
--允许即席分布式查询
sp_configure 'Ad Hoc Distributed Queries',1
go
--如果配置的值不在合理范围（在最小值最大值范围内），那么可以强制覆盖
reconfigure with override  
go

2、建立链接服务器

exec sp_addlinkedserver 
    @server ='ACCESSsrv', 
    @srvproduct ='Access', 
    @provider = 'Microsoft.Jet.OLEDB.4.0', 
    @datasrc = 'c:\t.mdb'

3、查询数据

SELECT * 
FROM [ACCESSsrv]...wc

（1）若要访问不安全的 Access 数据库，
尝试访问Access数据库的SQL Server登录名,
应将登录映射定义为用户名Admin，且不带密码: 
 
exec sp_addlinkedsrvlogin 
    @rmtsrvname = 'ACCESSsrv',
    @userself   = false,
    @locallogin = 'Joe', 
    @rmtuser = 'Admin',
    @rmtpassword = NULL

（2）若要访问安全的Access数据库，请使用注册表编辑器配置注册表，
以使用Access所用的正确的工作组信息文件。
使用注册表编辑器,
把Access使用工作组信息文件的完整路径名添加到下面注册表项：
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB
配置完注册表项后，创建本地登录名到Access登录名的登录名映射：

  EXEC sp_addlinkedsrvlogin 
      'ACCESSsrv', 
      false,
      'Joe',
      'AccessUser',   --用户名
      '034fhd99dl'    --密码

 

4、使用12.0库查询access数据

SELECT *
FROM 
OPENROWSET('microsoft.ace.oledb.12.0', --库名称
           'C:\t.accdb';'admin';'',    --'路径和文件名称';'用户名';'密码'
           e);                         --表名

5、使用4.0库查询access数据

SELECT *
FROM 
OPENROWSET('microsoft.jet.oledb.4.0',  --库名称
           'C:\t.mdb';'admin';'',      --'路径和文件名称';'用户名';'密码'
           'select * from wc');        --表名