SQLServer2008 同步Access数据库
SQLServer2008 同步Access数据库
环境：
windows2008R2服务器+winSqlServer2008R2
1.服务器的操作系统是否为X64位的操作系统，如果是，安装的office必须也是X64位的，要不然无法显示
2.要使用数据库的OpenDataSource函数，需要开启Ad Hoc Distributed Queries。
启用：
exec sp_configure "show advanced options",1
reconfigure
exec sp_configure "Ad Hoc Distributed Queries",1
reconfigure
使用完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure "Ad Hoc Distributed Queries",0
reconfigure
exec sp_configure "show advanced options",0
reconfigure
3.--建立连接服务器
EXEC sp_addlinkedserver'my_test_link_server','Access2010','Microsoft.ACE.OLEDB.12.0','D:\Program Files\Att\Data\att2000.mdb'
GO
--创建链接服务器登录映射
EXEC sp_addlinkedsrvlogin'my_test_link_server',false,'sa','admin',NULL
GO
--同步数据
delete from att.dbo.CHECKINOUT
GO
INSERT INTO att.dbo.CHECKINOUT(USERID,CHECKTIME,CHECKTYPE,VERIFYCODE,SENSORID,Memoinfo,WorkCode,sn,UserExtFmt)
SELECT CHECKINOUT.USERID,CHECKINOUT.CHECKTIME,CHECKINOUT.CHECKTYPE,CHECKINOUT.VERIFYCODE,CHECKINOUT.SENSORID,CHECKINOUT.Memoinfo,CHECKINOUT.WorkCode,CHECKINOUT.sn,CHECKINOUT.UserExtFmt
FROM [my_test_link_server]...[CHECKINOUT]
order by CHECKINOUT.id
GO
--查询同步后的数据总数
SELECT * FROM [my_test_link_server]...[CHECKINOUT]
order by CHECKINOUT.id
GO
--同步数据
delete from att.dbo.USERINFO
GO
INSERT INTO att.dbo.USERINFO(USERID,Badgenumber,SSN,Name,Gender,TITLE,PAGER,BIRTHDAY,HIREDDAY,street,CITY,STATE,ZIP,OPHONE,FPHONE,VERIFICATIONMETHOD,DEFAULTDEPTID
,SECURITYFLAGS,ATT,INLATE,OUTEARLY,OVERTIME,SEP,HOLIDAY,MINZU,PASSWORD,LUNCHDURATION,PHOTO,mverifypass,Notes,privilege
,InheritDeptSch,InheritDeptSchClass,AutoSchPlan,MinAutoSchInterval,RegisterOT,InheritDeptRule,EMPRIVILEGE,CardNo,FaceGroup,AccGroup,UseAccGroupTZ,VerifyCode
,Expires,ValidCount,ValidTimeBegin,ValidTimeEnd,TimeZone1,TimeZone2,TimeZone3,IDCardNo,IDCardValidTime)
SELECT USERINFO.USERID,USERINFO.Badgenumber,USERINFO.SSN,USERINFO.Name,USERINFO.Gender,USERINFO.TITLE,USERINFO.PAGER,USERINFO.BIRTHDAY,USERINFO.HIREDDAY,USERINFO.street,USERINFO.CITY,USERINFO.STATE,USERINFO.ZIP,USERINFO.OPHONE,USERINFO.FPHONE,USERINFO.VERIFICATIONMETHOD,USERINFO.DEFAULTDEPTID
,USERINFO.SECURITYFLAGS,USERINFO.ATT,USERINFO.INLATE,USERINFO.OUTEARLY,USERINFO.OVERTIME,USERINFO.SEP,USERINFO.HOLIDAY,USERINFO.MINZU,USERINFO.PASSWORD,USERINFO.LUNCHDURATION,USERINFO.PHOTO,USERINFO.mverifypass,USERINFO.Notes,USERINFO.privilege
,USERINFO.InheritDeptSch,USERINFO.InheritDeptSchClass,USERINFO.AutoSchPlan,USERINFO.MinAutoSchInterval,USERINFO.RegisterOT,USERINFO.InheritDeptRule,USERINFO.EMPRIVILEGE,USERINFO.CardNo,USERINFO.FaceGroup,USERINFO.AccGroup,USERINFO.UseAccGroupTZ,USERINFO.VerifyCode
,USERINFO.Expires,USERINFO.ValidCount,USERINFO.ValidTimeBegin,USERINFO.ValidTimeEnd,USERINFO.TimeZone1,USERINFO.TimeZone2,USERINFO.TimeZone3,USERINFO.IDCardNo,USERINFO.IDCardValidTime
FROM [my_test_link_server]...[USERINFO]
GO
--查询同步后的数据总数
SELECT * FROM [my_test_link_server]...[USERINFO]
GO
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
建立链接服务器:
exec sp_addlinkedserver 'test ', ' ','SQLOLEDB','192.18.1.3'
说明：上述语句中test为创建的链接服务器的名称，为了在本地服务器的数据库中使用，192.163.1.3为远程数据库服务器的ip地址。
建立链接服务器登录：
exec sp_addlinkedsrvlogin 'test','false',null,'sa','pass'
说明：第一个参数为刚刚建立的链接服务器的名称，最后的sa,pass为远程数据库的登录名与密码。
建立完链接服务器后，可以在本地服务器中操作链接服务器上的数据
select * from test.NorthWind.dbo.product;
说明：查询链接服务器上NorthWind数据库中product表中的信息
不需要时，可以删除链接服务器。
删除链接服务器：
execsp_dropserver'test','droplogins'
说明：第一个参数为要删除的链接服务器的名称，第二个参数最好直接传'droplogins'这样会一次性删除到链接服务器与链接服务器的所有登录。我开始填的是sa，提示我说，“为过程 'sys.sp_dropserver' 指定的参数或选项无效。”，调用exec sp_droplinkedsrvlogin 'test','sa'
后再执行sp_dropserver存储过程，会提示"仍有对服务器 'test' 的远程登录或链接登录"，所以干脆直接用上边的一条语句解决问题了。
