sqlserver数据库作业调度
--查询作业 SELECT [jop].[job_id] AS '作业唯一标识符' ,[jop].[name] AS '作业名称' ,[dp].[name] AS '作业创建者' ,[cat].[name] AS '作业类别' ,[jop].[description] AS '作业描述' , CASE [jop].[enabled] WHEN 1 THEN '是' WHEN 0 THEN '否' END AS '是否启用' ,[jop].[date_created] AS '作业创建日期' ,[jop].[date_modified] AS '作业最后修改日期' ,[sv].[name] AS '作业运行服务器名称' ,[step].[step_id] AS '作业起始步骤' ,[step].[step_name] AS '步骤名称' , CASE WHEN [sch].[schedule_uid] IS NULL THEN '否' ELSE '是' END AS '是否分布式作业' ,[sch].[schedule_uid] AS '作业计划的唯一标识符' ,[sch].[name] AS '作业计划的用户定义名称' , CASE [jop].[delete_level] WHEN 0 THEN '不删除' WHEN 1 THEN '成功后删除' WHEN 2 THEN '失败后删除' WHEN 3 THEN '完成后删除' END AS '作业完成删除选项' FROM [msdb].[dbo].[sysjobs] AS [jop] LEFT JOIN [msdb].[sys].[servers] AS [sv] ON [jop].[originating_server_id] = [sv].[server_id] LEFT JOIN [msdb].[dbo].[syscategories] AS [cat] ON [jop].[category_id] = [cat].[category_id] LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [step] ON [jop].[job_id] = [step].[job_id] AND [jop].[start_step_id] = [step].[step_id] LEFT JOIN [msdb].[sys].[database_principals] AS [dp] ON [jop].[owner_sid] = [dp].[sid] LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [jsch] ON [jop].[job_id] = [jsch].[job_id] LEFT JOIN [msdb].[dbo].[sysschedules] AS [sch] ON [jsch].[schedule_id] = [sch].[schedule_id] ORDER BY [jop].[name]
SELECT * FROM msdb.dbo.sysjobs --存储将由 SQL Server 代理执行的各个预定作业的信息 SELECT * FROM msdb.dbo.sysjobschedules --包含将由 SQL Server 代理执行的作业的计划信息 SELECT * FROM msdb.dbo.sysjobactivity; --记录当前 SQL Server 代理作业活动和状态 SELECT * FROM msdb.dbo.sysjobservers --存储特定作业与一个或多个目标服务器的关联或关系 SELECT * FROM msdb.dbo.sysjobsteps; --包含 SQL Server 代理要执行的作业中的各个步骤的信息 SELECT * FROM msdb.dbo.sysjobstepslogs; --包含所有 SQL Server 代理作业步骤的作业步骤日志 SELECT * FROM msdb.dbo.sysjobs_view; -- SELECT * FROM msdb.dbo.sysjobhistory --包含有关 SQL Server 代理执行预定作业的信息 SELECT * FROM msdb.dbo.syscategories --包含由 SQL Server Management Studio 用来组织作业、警报和操作员的类别
SELECT [jop].[job_id] AS '作业唯一标识符' ,[jop].[name] AS '作业名称' ,[jop].[description] AS '作业描述' FROM [msdb].[dbo].[sysjobs] AS [jop]
========================================================
----111 --包含 SQL Server 代理要执行的作业中的各个步骤的信息 SELECT command,* FROM msdb.dbo.sysjobsteps where job_id='A96A87A6-3B11-4A03-97AE-8541B05FAE0A'
========================================================
--BaiduWX签收订单超过15天自动已完成 update wxtrade set status='TRADE_FINISH' where tradeid in (select tradeid from wxtrade where status='TRADE_BUYER_SIGNED' and DateDiff(Day,SignTime,GetDate())>=15 ) UPDATE WxTradePercent SET Status=1,UpdateTime=GetDate() WHERE tradeid in (select tradeid from wxtrade where status='TRADE_BUYER_SIGNED' and DateDiff(Day,SignTime,GetDate())>=15 )
========================================================
--修改命令 update msdb.dbo.sysjobsteps set command=N'update wxtrade set status=''TRADE_FINISH'' where tradeid in (select tradeid from wxtrade where status=''TRADE_BUYER_SIGNED'' and DateDiff(Day,SignTime,GetDate())>=15 ) UPDATE WxTradePercent SET Status=1,UpdateTime=GetDate() WHERE tradeid in (select tradeid from wxtrade where status=''TRADE_BUYER_SIGNED'' and DateDiff(Day,SignTime,GetDate())>=15 )' where job_id='A96A87A6-3B11-4A03-97AE-8541B05FAE0A'
========================================================
--BaiduWX订单超过15天自动签收 SELECT command,* FROM msdb.dbo.sysjobsteps where job_id='940B7324-C796-48D9-A783-8B31932E3DB3' update wxtrade set status='TRADE_BUYER_SIGNED' where tradeid in (select tradeid from wxtrade where status='WAIT_BUYER_CONFIRM_GOODS' and DateDiff(Day,PayTime,GetDate())>=15 ) select count(1) from WxTrade where tradeid in (select tradeid from wxtrade where status='WAIT_BUYER_CONFIRM_GOODS' and DateDiff(Day,PayTime,GetDate())>=15 )
========================================================
--修改命令 update msdb.dbo.sysjobsteps set command=N'update wxtrade set status=''TRADE_BUYER_SIGNED'' where tradeid in (select tradeid from wxtrade where status=''WAIT_BUYER_CONFIRM_GOODS'' and DateDiff(Day,PayTime,GetDate())>=15 ) ' where job_id='940B7324-C796-48D9-A783-8B31932E3DB3'
