工作中遇到这样的情况,需要在更新表为多(位于服务器ServerA 172.16.8.100中的库数据库)同时更新表b(位于服务器ServerB 172.16.8.101中的库DatabaseB)。
为多表b与结构相同,但数据数量不一定相同,应为有可能TableC也在更新表b。由于数据更新不频繁,为简单起见想到使用了触发器Tirgger。记录一下遇到的一些问题:
<强> 1。访问异地数据库
在ServerA中创建指向ServerB上的链接服务器,并做好账号映射.addlinkedserver存储过程创建一个链接服务器,参数详情参见官方文档。第1个参数LNK_ServerA是自定义的名称,第2参数产品名称,如果是SQL Server不用提供;第3个参数是驱动类型;第4个参数是数据源,这里写SQL Server服务器地址
sp_addlinkedserver,,
配置链接服务器后,默认使用同一本地账号登陆远程数据库,如果账号有不同,还需要进行账号映射.sp_addlinkedsrvlogin参数详情参见官方文档。第1个参数同上;第2个参数假即使用后面参数提供的用户密码登陆;第3个参数零使所有本地账号都可以使用后面的用户密码来登陆链接服务器,如果第3个参数设置为一个本地SQL Server登陆用户名,那么只有这个用户才可以使用远程账号登陆链接服务器;最后两个是登录远程服务器的用户和密码。
sp_addlinkedsrvlogin,,,,
如果要删除以上配置可以如下
sp_droplinkedsrvlogin, sp_dropserver,
上面的配置在SQL Server Management Studio管理器里服务器对象下LinkedServers可以查询到,如果一切链接正常,可以直接打开链接服务器上的库表
值得注意的是以上两个存储过程不能出现在触发器代码中,而是事先在服务器ServerA中运行完成配置,否则触发器隐式事务的要求会报错”过程的系统。sp_addlinkedserver无法在一个事务执行。”
<强> 2。配置分布式事务
SQL Server的触发器是隐式使用事务的,链接服务器是远程服务器,需要在本地服务器和远程服务器之间开启分布式事务处理,否则会报“伙伴事务管理器禁用支持远程/网络事务”的错误。我在ServerA和ServerB中都开启分布式事务协调器,并进行适当配置,以支持分布式事务.ServerA和ServerB上都是Windows Server 2012 R2,其他版本服务器类似。
(1)首先在服务。msc中确认分布式事务协调器已经开启,其他版本的服务器不一定默认安装,需要安装窗户特性的方式先进行该特性的安装。
(2)在服务器管理工具管理工具中找到组件服务,在当地DTC中属性安全选项卡中配置如下,打开相关安全设置,完成后会重启服务,也有文档称需要重启服务器,但是至少2012 R2不用。
(3)配置防火墙,入站和出站都打开
<强> 3。数据库字段文本,ntext的处理
业务中为多表中有一个内容字段是ntext类型,同步到表b时需要对内容做一些替换处理。对于文本和ntext类型是一个过时的类型,微软官方建议用(N) VARCHAR (MAX)替换,可查阅这里。今后设计时可以考虑,这里我们考虑对ntext进行处理。
但是在触发器中,插入和删除表都是不允许对文本/ntext/图像类型进行处理的,这里我们采用一个曲线救国的办法,从数据库中把记录读取到临时表中,然后通过textptr和patindex函数和updatetext命令完成字符串替换处理
<>之前(tempdb . .sysobjects id(#为多temp_tablea id (),(), (),, (内容),(),(内容) # temp_tablea.Content (内容)特别注意以上代码对于文本类型处理中文时会出问题,由于文本存储unicode的数据,patidex会将中文字符解释为1个字符,而updatetext命令却将中文字符解释为2个字符. sql Server 2005以上版本可以这样做替换:
