sqlserver作业执行不正常
我用sqlserver语句写了一个脚本，如下所示。在查询分析器能正常获得“未登录人员信息”和“登录人员信息”。但把脚本引入作业后，只能得到“未登录人员信息”，不能得到“登录人员信息”。请问是什么原因？ declare @messages nvarchar(max)
declare @deptname nvarchar(50)
declare @position_name nvarchar(50)
declare @username nvarchar(max)
declare @role_id int
declare @rs int
set @messages='未登录人员信息:
'
declare depts cursor for
select wdl.deptname,wdl.position_name,left(username_set,len(username_set)-1) username,rs
from
( -- wdl
select system_depts.name deptname,hhh.* from
(-- hhh
select dept_id,position_name,
(select username+',' from
(-- username_set
select users.id,users.username username,position.name position_name,depts.name deptname ,position.dept_id
from system_positionmember member left join system_users users on users.id=member.user_id
left join system_position position on member.position_id=position.id
left join system_depts depts on users.dept_id=depts.id
where users.id not in (
select login_user from checkwork_report where login_date = convert(char(10),getdate()-1,120))
and depts.superior<>0
) tb
where position_name=tb1.position_name and dept_id=tb1.dept_id for xml path('')) username_set,count(*) rs
from
(--tb1
select users.id,users.username username,position.name position_name,depts.name deptname,position.dept_id
from system_positionmember member left join system_users users on users.id=member.user_id
left join system_position position on member.position_id=position.id
left join system_depts depts on users.dept_id=depts.id
where users.id not in (
select login_user from checkwork_report where login_date = convert(char(10),getdate()-1,120))
and depts.superior<>0) tb1
group by position_name,dept_id
) hhh
--select * from system_depts
left join system_depts on hhh.dept_id=system_depts.id
) wdl
open depts
fetch next from depts into @deptname,@position_name,@username,@rs
while (@@fetch_status=0)
begin
set @messages=@messages+ @deptname+'('+@position_name+')'+' : '+@username+'。共计'+cast(@rs as nvarchar(10))+'人未登录。'+'
'
fetch next from depts into @deptname,@position_name,@username,@rs
end
--select @messages yy
--关闭游标
close depts
--撤销游标
deallocate depts
---登录人员信息
set @messages='
'+@messages+'已登录人员信息:'+'
'
declare depts cursor for
select wdl.deptname,wdl.position_name,left(username_set,len(username_set)-1) username,rs
from
( -- wdl
select system_depts.name deptname,hhh.* from
(-- hhh
select dept_id,position_name,
(select username+',' from
(-- username_set
select users.id,users.username username,position.name position_name,depts.name deptname ,position.dept_id
from system_positionmember member left join system_users users on users.id=member.user_id
left join system_position position on member.position_id=position.id
left join system_depts depts on users.dept_id=depts.id
where users.id in (
select login_user from checkwork_report where login_date = convert(char(10),getdate()-1,120))
and depts.superior<>0 ) tb
where position_name=tb1.position_name and dept_id=tb1.dept_id for xml path('')) username_set,count(*) rs
from
(--tb1
select users.id,users.username username,position.name position_name,depts.name deptname,position.dept_id
from system_positionmember member left join system_users users on users.id=member.user_id
left join system_position position on member.position_id=position.id
left join system_depts depts on users.dept_id=depts.id
where users.id in (
select login_user from checkwork_report where login_date = convert(char(10),getdate()-1,120))
and depts.superior<>0) tb1
group by position_name,dept_id
) hhh
left join system_depts on hhh.dept_id=system_depts.id
) wdl
open depts
fetch next from depts into @deptname,@position_name,@username,@rs
while (@@fetch_status=0)
begin
set @messages=@messages+ @deptname+'('+@position_name+')'+' : '+@username+'。共计'+cast(@rs as nvarchar(10))+'人登录。'+'
'
fetch next from depts into @deptname,@position_name,@username,@rs
end
--关闭游标
close depts
--撤销游标
deallocate depts
declare roles cursor for
select id from system_users users right join system_user_role user_role on
users.id=user_role.user_id where user_role.role_id=1
open roles
fetch next from roles into @role_id
while (@@fetch_status=0)
begin
insert into messages(user_id,fromuser_id,source_id,type,entity_class,entity_id,url,pri,ssid,createdate,
title,content,isread,isReceiverreaded,readdate,isreply,istransmit,sms,email,im,from_id,attachs,eas,status)
select @role_id,2,1,1,null,null,null,null,null,getdate(),
convert(nvarchar(10),getdate()-1,120)+' :登录信息',@messages,
1,1,getdate(),null,null,9,0,0,0,'',0,1
fetch next from roles into @role_id
end
close roles
--撤销游标
deallocate roles
