SQLServer2008同步Access数据库

SQLServer2008 同步Access数据库

环境：

windows2008R2服务器+winSqlServer2008R2

1.服务器的操作系统是否为X64位的操作系统，如果是，安装的office必须也是X64位的，要不然无法显示

2.要使用数据库的OpenDataSource函数，需要开启Ad Hoc Distributed Queries。

启用：

exec sp_configure &＃8220;show advanced options&＃8221;,1

reconfigure

exec sp_configure &＃8220;Ad Hoc Distributed Queries&＃8221;,1

reconfigure

使用完成后，关闭Ad Hoc Distributed Queries：

exec sp_configure &＃8220;Ad Hoc Distributed Queries&＃8221;,0

reconfigure

exec sp_configure &＃8220;show advanced options&＃8221;,0

reconfigure

3.&＃8211;建立连接服务器

EXEC sp_addlinkedserver&＃8217;my_test_link_server&＃8217;,&＃8217;Access2010&＃8242;,&＃8217;Microsoft.ACE.OLEDB.12.0&＃8242;,&＃8217;D:\Program Files\Att\Data\att2000.mdb&＃8217;

GO

&＃8211;创建链接服务器登录映射

EXEC sp_addlinkedsrvlogin&＃8217;my_test_link_server&＃8217;,false,&＃8217;sa&＃8217;,&＃8217;admin&＃8217;,NULL

GO

&＃8211;同步数据

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]&＃8230;[CHECKINOUT]

order by CHECKINOUT.id

GO

&＃8211;查询同步后的数据总数

SELECT * FROM [my_test_link_server]&＃8230;[CHECKINOUT]

order by CHECKINOUT.id

GO

&＃8211;同步数据

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]&＃8230;[USERINFO]

GO

&＃8211;查询同步后的数据总数

SELECT * FROM [my_test_link_server]&＃8230;[USERINFO]

GO

&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;

&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;

建立链接服务器:

exec sp_addlinkedserver &＃8216;test &＃8216;, &＃8216; &＃8216;,&＃8217;SQLOLEDB&＃8217;,&＃8217;192.18.1.3&＃8242;

说明：上述语句中test为创建的链接服务器的名称，为了在本地服务器的数据库中使用，192.163.1.3为远程数据库服务器的ip地址。

建立链接服务器登录：

exec sp_addlinkedsrvlogin &＃8216;test&＃8217;,&＃8217;false&＃8217;,null,&＃8217;sa&＃8217;,&＃8217;pass&＃8217;

说明：第一个参数为刚刚建立的链接服务器的名称，最后的sa,pass为远程数据库的登录名与密码。

建立完链接服务器后，可以在本地服务器中操作链接服务器上的数据

select * from test.NorthWind.dbo.product;

说明：查询链接服务器上NorthWind数据库中product表中的信息

不需要时，可以删除链接服务器。

删除链接服务器：

execsp_dropserver&＃8217;test&＃8217;,&＃8217;droplogins&＃8217;

说明：第一个参数为要删除的链接服务器的名称，第二个参数最好直接传&＃8217;droplogins&＃8217;这样会一次性删除到链接服务器与链接服务器的所有登录。我开始填的是sa，提示我说，“为过程 &＃8216;sys.sp_dropserver&＃8217; 指定的参数或选项无效。”，调用exec sp_droplinkedsrvlogin &＃8216;test&＃8217;,&＃8217;sa&＃8217;

后再执行sp_dropserver存储过程，会提示&＃8221;仍有对服务器 &＃8216;test&＃8217; 的远程登录或链接登录&＃8221;，所以干脆直接用上边的一条语句解决问题了。