SQL Agetnt定时备份与定时执行存储过程

计划备份：

DECLARE @strPath NVARCHAR(200)
set @strPath = convert(NVARCHAR(19),getdate(),23)
set @strPath = REPLACE(@strPath, ':' , '.')
set @strPath = 'E:\Backup\' + @strPath +'备份'+ '.bak'
BACKUP DATABASE [jewelry] TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

 

计划存储过程：

EXEC HRAttendsum_update;

exec HRAttend_UpFromFlow_absent;
exec HRAttend_UpFromFlow_AttCard;
exec HRAttend_UpFromFlow_business;
exec HRAttend_UpFromFlow_mobile;
exec HRAttend_UpFromFlow_overtime;
exec HRAttend_UpFromFlow;

sql存储过程：

USE [HRData]
GO
/****** Object:  StoredProcedure [dbo].[HRAttendsum_insert]    Script Date: 08/25/2022 10:23:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--每天0点执行当天数据的写入
ALTER proc [dbo].[HRAttendsum_insert]
 as    
-- if(@FDate ='')
 declare @FDate varchar(10)
   set @FDate=Convert(nvarchar(10),GETDATE(),23)
 insert into HRAttendsum (userid,username,Fdate,department,seclevel)
 select loginid,lastname,@FDate,departmentid,a.seclevel from EDATA.dbo.hrmresource
 a left join  HRAttendsum hh on hh.userid=a.loginid and hh.Fdate=@FDate
 where hh.userid is null and (loginid like '8888%' or loginid='8000')
 
 --更新用户级别
 update a set  a.seclevel=b.seclevel from HRAttendsum a 
 left join EDATA.dbo.hrmresource b on b.loginid=a.userid
 --更新日期类别
update dbo.HRAttendsum set day_type=bb.daytype from HRAttendsum
left join (
select a.fdate,a.day_type,
 case when b.IsWork=1 and HolidayType=0 then 0
   when b.IsWork=0 and HolidayType=0 then 1
    when b.IsWork=0 and HolidayType=1 then 2
   else 3 End AS 'daytype'
  from HRAttendsum a
 left join dbo.HRWorkDay b on b.FDate=a.FDate)
  bb on bb.FDate=HRAttendsum.Fdate
 
--select * from   HRAttendsum set seclevel=0    Fdate=Convert(nvarchar(10),GETDATE(),23)
 --更新日期类别
 update HRAttendsum set remarks='节假日' from HRAttendsum 
where Day_Type=0 and EndTime is null and StartTime is null 
and Fdate>'2016-12-31'