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' 的远程登录或链接登录"，所以干脆直接用上边的一条语句解决问题了。