客户原本是Windows Server2003+SQL Server2000,现升级到Windows Server 2008+SQL Server2005,这几天也跑得没问题,但今天却发现,我挂在代理服务下面的两个作业没有执行,以前在老服务器上(Windows Server2003+SQL Server2000)是没有问题的,我把代理服务作业的代码拿到查询分析器里面执行也没问题,但当我选择作业右击“Star Job”的时候,却说执行失败:错误代码如下:
日期 2010/06/29 16:40:45
日志 作业历史记录 (Import_AttCardData(HTL))步骤 ID 0
服务器 HTL-KSHR
作业名称 Import_AttCardData(HTL)
步骤名称 (作业结果)
持续时间 00:00:01
SQL 严重性 0
SQL 消息 ID 0
已通过电子邮件通知的操作员
已通过网络发送通知的操作员
已通过寻呼通知的操作员
重试次数 0消息
The job failed. The Job was invoked by User sa. The last step to run was step 1 (1).作业的内容为:
GO
insert into EveryDayCardDataRecord
SELECT (ltrim(rtrim(Wgdtwno))+'_'+ltrim(rtrim(cast(Wgdtwdt as varchar)))+'_'+Wgdttim) as CardTxt,
cast(left(cast(wgdtwdt as varchar),4)+'/'+substring(cast(wgdtwdt as varchar),5,2)+'/'+right(cast(wgdtwdt as varchar),2) as smalldatetime) as YYMMDD,
Wgdttim as CardTime,Wgdtwno as EmpNo
from opendatasource('sqloledb','server=10.10.1.43;uid=hello;pwd=hello;database=HTL').HTL.dbo.hrswgdt
where Wgdtwdt between convert(varchar(8),getdate()-3,112) and convert(varchar(8),getdate(),112)
GO
insert into AttCardData(CardTxt,YYMMDD,CardTime,EmpNo)
select cast(el_id as varchar)+'_'+CardTxt,YYMMDD,CardTIme,ltrim(rtrim(EmpNo))
from EveryDayCardDataRecord
where CardTxt not in(select substring(CardTxt,charindex('_',CardTxt)+1,len(CardTxt)) from AttCardData where YYMMDD between convert(varchar(8),getdate()-3,112) and convert(varchar(8),getdate(),112))
GO
update a set a.EmpID=b.EmpID
from AttCardData a left outer join PerEmployee b
on a.EmpNo=b.EmpNo where a.EmpID is null
GO
Drop table EveryDayCardDataRecord
GO
CREATE TABLE [EveryDayCardDataRecord](
[el_id] [int] identity(1,1) primary key NOT NULL,
[CardTxt] [varchar](50) NULL,
[YYMMDD] smalldatetime NULL,
[CardTime] [varchar](5) NULL,
[EmpNo] [Varchar](20) NULL,
)GO此作业在SQL Server2005查询分析器里面执行是可以的,但到了作业里面却报错,请高手帮小弟分析一下问题出在哪里,多谢谢!!!!!
日期 2010/06/29 16:40:45
日志 作业历史记录 (Import_AttCardData(HTL))步骤 ID 0
服务器 HTL-KSHR
作业名称 Import_AttCardData(HTL)
步骤名称 (作业结果)
持续时间 00:00:01
SQL 严重性 0
SQL 消息 ID 0
已通过电子邮件通知的操作员
已通过网络发送通知的操作员
已通过寻呼通知的操作员
重试次数 0消息
The job failed. The Job was invoked by User sa. The last step to run was step 1 (1).作业的内容为:
GO
insert into EveryDayCardDataRecord
SELECT (ltrim(rtrim(Wgdtwno))+'_'+ltrim(rtrim(cast(Wgdtwdt as varchar)))+'_'+Wgdttim) as CardTxt,
cast(left(cast(wgdtwdt as varchar),4)+'/'+substring(cast(wgdtwdt as varchar),5,2)+'/'+right(cast(wgdtwdt as varchar),2) as smalldatetime) as YYMMDD,
Wgdttim as CardTime,Wgdtwno as EmpNo
from opendatasource('sqloledb','server=10.10.1.43;uid=hello;pwd=hello;database=HTL').HTL.dbo.hrswgdt
where Wgdtwdt between convert(varchar(8),getdate()-3,112) and convert(varchar(8),getdate(),112)
GO
insert into AttCardData(CardTxt,YYMMDD,CardTime,EmpNo)
select cast(el_id as varchar)+'_'+CardTxt,YYMMDD,CardTIme,ltrim(rtrim(EmpNo))
from EveryDayCardDataRecord
where CardTxt not in(select substring(CardTxt,charindex('_',CardTxt)+1,len(CardTxt)) from AttCardData where YYMMDD between convert(varchar(8),getdate()-3,112) and convert(varchar(8),getdate(),112))
GO
update a set a.EmpID=b.EmpID
from AttCardData a left outer join PerEmployee b
on a.EmpNo=b.EmpNo where a.EmpID is null
GO
Drop table EveryDayCardDataRecord
GO
CREATE TABLE [EveryDayCardDataRecord](
[el_id] [int] identity(1,1) primary key NOT NULL,
[CardTxt] [varchar](50) NULL,
[YYMMDD] smalldatetime NULL,
[CardTime] [varchar](5) NULL,
[EmpNo] [Varchar](20) NULL,
)GO此作业在SQL Server2005查询分析器里面执行是可以的,但到了作业里面却报错,请高手帮小弟分析一下问题出在哪里,多谢谢!!!!!
解决方案 »
- 请教SQL语句
- 关于数据库备份
- 更新日期格式
- 莫名其妙的问题,请高手解答!
- 如何在数据库中给一个字段赋默认值
- 關於 OPENROWSET 的兩個錯誤請教大家,謝謝!!
- txlicenhe(马可@李)、txlicenhe(马可@李)、 myflok(阿棋)、大力、ms44(ms44)进来接分!!!
- 每个软件都有快捷, 请问sql 的快捷在哪里定义? 大侠你知道哪些?
- 再请教高手:那么又如何分别取mdf和log 的use & free 大小呢???救命啊!!
- 各位大虾,中午好!菜鸟的问题,恳请指教,有关数据库全文检索和模糊查询的,挺急的
- sql 分组后取每组按时间排序的前三条记录写法
- 问一个数据库效率的问题
看下你的代理是用什么账户启动的!
是不是默认WINDOWS启动的。建议用sa启动。再执行试试看!