可视化操作的具体步骤:
数据库服务器-》企业管理器-》管理-》SQL Server 代理-》作业-》新建作业。
具体输入看提示即可,微软的软件是典型的傻瓜式操作。
命令行操作步骤:
打开查询分析器,输入如下命令:
EXEC sp_add_job @job_name = '作业名字'
EXEC sp_add_jobstep @job_name = '作业名字',
@step_name = '步骤名子',
@subsystem = 'TSQL',
@command = 'EXEC 库名..过程名',
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔
EXEC sp_add_jobschedule @job_name = '作业名字',
@name = '作业调度名字',
@freq_type = 4, -- 每天
@freq_interval = 26, --间隔
@active_start_time = 10000 --开始时间
EXEC sp_add_job @job_name = 'test '
EXEC sp_add_jobstep @job_name = 'test ',
@step_name = 'test_1 ',
@subsystem = 'TSQL ',
@command = 'update yourtable set yourcol=0 ', /*这是你每次想执行的东西*/
@retry_attempts = 5,
@retry_interval = 5
EXEC sp_add_jobschedule @job_name = 'test ',
@name = 'Schedule_test ',
@freq_type = 16, -- daily
@freq_interval = 26,
@active_start_time = 10000
--每月执行的作业
exec p_createjob @jobname='mm',@sql='select * from syscolumns',@freqtype='month'
--每周执行的作业
exec p_createjob @jobname='ww',@sql='select * from syscolumns',@freqtype='week'
--每日执行的作业
exec p_createjob @jobname='a',@sql='select * from syscolumns'
--每日执行的作业,每天隔4小时重复的作业
exec p_createjob @jobname='b',@sql='select * from syscolumns',@fsinterval=4
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_createjob]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_createjob]
GO
create proc p_createjob
@jobname varchar(100),--作业名称
@sql varchar(8000),--要执行的命令
@dbname sysname='',--默认为当前的数据库名
@freqtype varchar(6)='day',--时间周期,month 月,week 周,day 日
@fsinterval int=1,--相对于每日的重复次数
@time int=170000--开始执行时间,对于重复执行的作业,将从0点到23:59分
as
if isnull(@dbname,'')='' set @dbname=db_name()
--创建作业
exec msdb..sp_add_job @job_name=@jobname
--创建作业步骤
exec msdb..sp_add_jobstep @job_name=@jobname,
@step_name = '数据处理',
@subsystem = 'TSQL',
@database_name=@dbname,
@command = @sql,
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔
--创建调度
declare @ftype int,@fstype int,@ffactor int
select @ftype=case @freqtype when 'day' then 4
when 'week' then 8
when 'month' then 16 end
,@fstype=case @fsinterval when 1 then 0 else 8 end
if @fsinterval<>1 set @time=0
set @ffactor=case @freqtype when 'day' then 0 else 1 end
EXEC msdb..sp_add_jobschedule @job_name=@jobname,
@name = '时间安排',
@freq_type=@ftype , --每天,8 每周,16 每月
@freq_interval=1,--重复执行次数
@freq_subday_type=@fstype,--是否重复执行
@freq_subday_interval=@fsinterval, --重复周期
@freq_recurrence_factor=@ffactor,
@active_start_time=@time --下午17:00:00分执行
-- 添加目标服务器
EXEC msdb.dbo.sp_add_jobserver
@job_name = @jobname ,
@server_name = N'(local)'
go
分享到:
相关推荐
sql server job 定时任务 纯碎数据库客户端定时任务
sql Server Job 新建 很详细的
自动备份sqlserver JOB 用脚本,内附PS1文件及对应的调用批处理文件,您只需要解压缩修改路径及服务器名称即可用使用
该工具使用SMO类库,可以同时显示多个SQL Server实例上作业的当前状态,程序运行所在的Windows系统必须安装SQL Server 2012。
SQLServer定时作业job的设置方法.doc
SQLServer定时作业job的设置方法
SQL Server 2005的设置如下: 1.运行SQL Server Surface Area Configuration->Surface Area Configuration for features->选择Database Mail->选择Enable Database mail stored procedures 2.打开SQL Management ...
XXL-JOB是一个轻量级分布式任务调度平台,其核心设计目标是开发迅速、学习简单、轻量级、易扩展。现已开放源代码并接入多家公司线上产品线,开箱即用. ...xxl-job-admin 版本为2.0.2 SqlServer版本为2012
sqlserver新建job时报c001f011错的解决
在上一篇文章中已经分析了SQL SERVER中关于邮件的基础配置,本篇将利用此功能对多台Server的跑批Job进行监控。 本篇实现 1、每天检查服务器中的SQL Server跑批Job的运行状态,如果跑批失败,则发邮件告诉...
利用SQL Server 2000中的定时功能job,实现自动清理数据表table
一个强悍的监控SQL Server Job运行状况的存储过程. 有详细的文档和源代码。很值得学习
最近服务器执行收缩日志文件大小的job老是报错,小编抽时间把此问题的解决方案整理了一下,特此分享到脚本之家平台,供大家学习
Automate your job using SQL Server Agent to leverage the power of Maintenance Plans to deliver real, proactive solutions to common issues. Schedule common tasks such as backups and index rebuilds to ...
SQL Server 2012 Reporting Services Blueprints is intended to be just that, a series of Blueprints – things that work straight out of the box and that mirror what a real job requires real report ...
Improve your ability to develop, manage, and troubleshoot SQL Server solutions by learning how different components work “under the hood,” and how they communicate with each other. The detailed ...
SQL Server administration used to just be the job of a database administrator (DBA), but as SQL Server proliferates throughout smaller companies, many developers have begun to act as administrators ...
Improve your ability to develop, manage, and troubleshoot SQL Server solutions by learning how different components work “under the hood,” and how they communicate with each other. The detailed ...
This book helps you build a strong foundation to get you comfortable using PowerShell with SQL Server, empowering you to create more complex scripts for your day-to-day job。