C6中休假申请与人事软件的跨服务器数据库整合，已处理。

最新推荐文章于 2012-12-19 10:44:55 发布

tzjly

最新推荐文章于 2012-12-19 10:44:55 发布

阅读量706

点赞数

分类专栏：金和C6 文章标签：服务器数据库 c insert output null

本文链接：https://blog.csdn.net/tzjly/article/details/6729517

版权

金和C6 专栏收录该内容

44 篇文章 1 订阅

订阅专栏

目的，C6中流程结束后在C6表JHHR_Attendance_Leave与人事考勤软件中加班请假登记中同时保存。二个库在不同服务器。

关联logincode与empnum后需处理的问题。另attadd.AddRsn备注字段改大点。

1、 C6中创建一个表、二个TR。

2、触发器跨服务器的处理.

链接服务器"(null)"的 OLE DB 访问接口 "SQLNCLI" 返回了消息 "该伙伴事务管理器已经禁止了它对远程/网络事务的支持。"。
消息 7391，级别 16，状态 2，过程 t_test，第 13 行
无法执行该操作，因为链接服务器 "(null)" 的 OLE DB 访问接口 "SQLNCLI" 无法启动分布式事务。

A：二台服务器的msdtc启用，及组件中安全配置！

----------------------------------------------------------

C6中执行例

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;

create TABLE HYJUserRelations(
UserId varchar(20) primary key not NULL,
AttendanceUserId varchar(50) NULL,
userXingming varchar(50) null
)
go

alter trigger   [dbo].[t_test]   on [dbo].[JHHR_Attendance_Leave]
for insert,update
as
set     XACT_ABORT   on
exec   master..xp_cmdshell   'isql   /S"192.168.10.100"   /U"kyj2008"   /P"kyj2008"   /q"exec   master..xp_cmdshell   ''net   start   msdtc'',no_output"',no_output
exec   master..xp_cmdshell   'net   start   msdtc',no_output
BEGIN   DISTRIBUTED   TRANSACTION
   if(update(App_Flag))
   begin
insert   into   openrowset('sqloledb','192.168.10.100';'kyj2008';'kyj2008',kyj2008.dbo.Attadd)(AddKind,AddDateBeg,AddBeg,AddDateEnd,AddEnd,AddDays,AddHours,CkId,SysFlg,AddCat,VaildFlag)
select
CASE WHEN a.AttLT_Name!='加班' THEN 'LEV' else 'OVT'
END as AddKind, convert(char(10),a.AttL_BeginTime,120) as AddDateBeg,
(datename(Hh,a.AttL_BeginTime)+':'+datename(Mi,a.AttL_BeginTime)) as AddBeg,
convert(char(10),case when a.AttL_LimitType='D' then dateadd(day,a.AttL_Limit,a.AttL_BeginTime)
     when a.AttL_LimitType='H' then dateadd(hh,(convert(decimal(18,2),a.AttL_Limit/8.5)-cast(convert(decimal(18,2),a.AttL_Limit/8.5) as int))*8.5,dateadd(day ,cast(convert(decimal(18,2),a.AttL_Limit/8.5) as int),a.AttL_BeginTime))
     when a.AttL_LimitType='M' then dateadd(mi,(convert(decimal(18,2),a.AttL_Limit/60.0)-cast(convert(decimal(18,2),a.AttL_Limit/60.0) as int))*60,dateadd(hh ,cast(convert(decimal(18,2),a.AttL_Limit/60.0) as int),a.AttL_BeginTime))
end,120) as AddDateEnd ,
(datename(Hh,(case when a.AttL_LimitType='D' then dateadd(day,a.AttL_Limit,a.AttL_BeginTime)
     when a.AttL_LimitType='H' then dateadd(hh,(convert(decimal(18,2),a.AttL_Limit/8.5)-cast(convert(decimal(18,2),a.AttL_Limit/8.5) as int))*8.5,dateadd(day ,cast(convert(decimal(18,2),a.AttL_Limit/8.5) as int),a.AttL_BeginTime))
     when a.AttL_LimitType='M' then dateadd(mi,(convert(decimal(18,2),a.AttL_Limit/60.0)-cast(convert(decimal(18,2),a.AttL_Limit/60.0) as int))*60,dateadd(hh ,cast(convert(decimal(18,2),a.AttL_Limit/60.0) as int),a.AttL_BeginTime))
end))+':'+datename(Mi,(case when a.AttL_LimitType='D' then dateadd(day,a.AttL_Limit,a.AttL_BeginTime)
     when a.AttL_LimitType='H' then dateadd(hh,(convert(decimal(18,2),a.AttL_Limit/8.5)-cast(convert(decimal(18,2),a.AttL_Limit/8.5) as int))*8.5,dateadd(day ,cast(convert(decimal(18,2),a.AttL_Limit/8.5) as int),a.AttL_BeginTime))
     when a.AttL_LimitType='M' then dateadd(mi,(convert(decimal(18,2),a.AttL_Limit/60.0)-cast(convert(decimal(18,2),a.AttL_Limit/60.0) as int))*60,dateadd(hh ,cast(convert(decimal(18,2),a.AttL_Limit/60.0) as int),a.AttL_BeginTime))
end))) as AddEnd,
case when a.AttL_LimitType='D' then convert(decimal(18,2),a.AttL_Limit*1.0)
     when a.AttL_LimitType='H' then convert(decimal(18,2),a.AttL_Limit/8.5)
     when a.AttL_LimitType='M' then convert(decimal(18,2),a.AttL_Limit/60/6.5)
end as AddDays,
case when a.AttL_LimitType='D' then convert(decimal(18,2),a.AttL_Limit*8.5)
     when a.AttL_LimitType='H' then convert(decimal(18,2),a.AttL_Limit*1.0)
     when a.AttL_LimitType='M' then convert(decimal(18,2),a.AttL_Limit*1.0/60)
end as AddHours,1 ,b.AttendanceUserId,'ot1',1
from   inserted a,HYJUserRelations b where a.App_Flag=1 and a.Reg_Code=b.UserId
end
commit   tran
go



insert into HYJUserRelations(AttendanceUserId,userXingming,UserId)
select a.SysFlg,a.empnum,b.UserID from openrowset('sqloledb','192.168.10.100';'kyj2008';'kyj2008',kyj2008.dbo.Employee) a,Users b where a.empnum=b.LoginCode
go

alter trigger t_AddUserLink   on Users
for insert
as
   set     XACT_ABORT   on
exec   master..xp_cmdshell   'isql   /S"192.168.10.100"   /U"kyj2008"   /P"kyj2008"   /q"exec   master..xp_cmdshell   ''net   start   msdtc'',no_output"',no_output
exec   master..xp_cmdshell   'net   start   msdtc',no_output
BEGIN   DISTRIBUTED   TRANSACTION
insert into HYJUserRelations(AttendanceUserId,userXingming,UserId)
select a.SysFlg,a.empnum,b.UserID from openrowset('sqloledb','192.168.10.100';'kyj2008';'kyj2008',kyj2008.dbo.Employee) a,inserted b where a.empnum=b.LoginCode
commit tran
go