Monday, March 19, 2012

help about sql server job

I created a stored procedure.When i execute the sp in Query Analyzer or in
dts packages, it worked well.But when i scheduled it as job,the job
failed.There are 51 jobs at the server totally.
I also scheduled the sp as job on another server,this time it worked well
too.
Could anyone tell me the reason?Should I set any configurations at the
server?
Below is the sp script,I used linked server in this sp:
CREATE PROCEDURE sp1 AS
declare @.last_upd_tm datetime
set @.last_upd_tm=(select top 1 rspn_upd_tm from TBL_eCardGEMSRpt_load_tm
order by rspn_upd_tm desc)
delete trspn from TBL_eCardGEMS_Response trspn join
linkedserver.eCardGEMsRpt.dbo.Response erspn
on trspn.ResponseID=erspn.ResponseID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMS_Response
select * from linkedserver.eCardGEMsRpt.dbo.Response erspn
where erspn.LastUpdateDate>@.last_upd_tm
delete tkt from TBL_eCardGEMS_Ticket tkt join
linkedserver.eCardGEMsRpt.dbo.Response erspn
on tkt.TicketID=erspn.TicketID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMS_Ticket
select distinct tkt.* from linkedserver.eCardGEMsRpt.dbo.Response erspn
join
FMSQLPRD002.eCardGEMsRpt.dbo.Ticket tkt on
erspn.TicketID=tkt.TicketID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMSRpt_load_tm
select max(lastupdatedate) from linkedserver.eCardGEMsRpt.dbo.Response
GOAaron,
Verify if the database that the job is calling is the correct Database. By
default the database selected is master.
[ ]'s
PEDRO HENRIQUE NUNES - Brasil
"Aaron Huang" <Aapursueron@.hotmail.com> wrote in message
news:bgsh1m$lgj$1@.news01.intel.com...
> I created a stored procedure.When i execute the sp in Query Analyzer or in
> dts packages, it worked well.But when i scheduled it as job,the job
> failed.There are 51 jobs at the server totally.
> I also scheduled the sp as job on another server,this time it worked well
> too.
> Could anyone tell me the reason?Should I set any configurations at the
> server?
> Below is the sp script,I used linked server in this sp:
> CREATE PROCEDURE sp1 AS
> declare @.last_upd_tm datetime
> set @.last_upd_tm=(select top 1 rspn_upd_tm from TBL_eCardGEMSRpt_load_tm
> order by rspn_upd_tm desc)
> delete trspn from TBL_eCardGEMS_Response trspn join
> linkedserver.eCardGEMsRpt.dbo.Response erspn
> on trspn.ResponseID=erspn.ResponseID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMS_Response
> select * from linkedserver.eCardGEMsRpt.dbo.Response erspn
> where erspn.LastUpdateDate>@.last_upd_tm
> delete tkt from TBL_eCardGEMS_Ticket tkt join
> linkedserver.eCardGEMsRpt.dbo.Response erspn
> on tkt.TicketID=erspn.TicketID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMS_Ticket
> select distinct tkt.* from linkedserver.eCardGEMsRpt.dbo.Response erspn
> join
> FMSQLPRD002.eCardGEMsRpt.dbo.Ticket tkt on
> erspn.TicketID=tkt.TicketID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMSRpt_load_tm
> select max(lastupdatedate) from linkedserver.eCardGEMsRpt.dbo.Response
> GO

No comments:

Post a Comment