如何安排每天运行 sql 查询的作业？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5471080/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to schedule a job for sql query to run daily?
提问by Bobj-C
I need to know how to make a SQL query run daily using a SQL Server Agent job, with minimum required configuration settings.
我需要知道如何使用 SQL Server 代理作业每天运行 SQL 查询，并使用最少的配置设置。
回答by L-Note
Expand the SQL Server Agent node and right click the Jobs node in SQL Server Agent and select
'New Job'In the
'New Job'window enter the name of the job and a description on the'General'tab.Select
'Steps'on the left hand side of the window and click'New'at the bottom.In the
'Steps'window enter a step name and select the database you want the query to run against.Paste in the T-SQL command you want to run into the Command window and click
'OK'.Click on the
'Schedule'menu on the left of the New Job window and enter the schedule information (e.g. daily and a time).Click
'OK'- and that should be it.
展开 SQL Server Agent 节点并右键单击 SQL Server Agent 中的 Jobs 节点并选择
'New Job'在
'New Job'窗口中输入作业名称和'General'选项卡上的描述。选择
'Steps'窗口左侧的 并单击'New'底部的。在
'Steps'窗口中输入步骤名称，然后选择要运行查询的数据库。将要运行的 T-SQL 命令粘贴到命令窗口中，然后单击
'OK'。单击
'Schedule'“新建作业”窗口左侧的菜单并输入计划信息（例如每天和时间）。单击
'OK'- 应该就是这样。
(There are of course other options you can add - but I would say that is the bare minimum you need to get a job set up and scheduled)
（当然，您还可以添加其他选项 - 但我想说这是设置和安排工作所需的最低限度）
回答by S.Mason
I made an animated GIF of the steps in the accepted answer. This is from MSSQL Server 2012
我制作了已接受答案中步骤的动画 GIF。这是来自 MSSQL Server 2012
回答by Doug Lampe
To do this in t-sql, you can use the following system stored procedures to schedule a daily job. This example schedules daily at 1:00 AM. See Microsoft help for details on syntax of the individual stored procedures and valid range of parameters.
要在 t-sql 中执行此操作，您可以使用以下系统存储过程来安排日常作业。此示例安排在每天凌晨 1:00。有关各个存储过程的语法和有效参数范围的详细信息，请参阅 Microsoft 帮助。
DECLARE @job_name NVARCHAR(128), @description NVARCHAR(512), @owner_login_name NVARCHAR(128), @database_name NVARCHAR(128);
SET @job_name = N'Some Title';
SET @description = N'Periodically do something';
SET @owner_login_name = N'login';
SET @database_name = N'Database_Name';
-- Delete job if it already exists:
IF EXISTS(SELECT job_id FROM msdb.dbo.sysjobs WHERE (name = @job_name))
BEGIN
EXEC msdb.dbo.sp_delete_job
@job_name = @job_name;
END
-- Create the job:
EXEC msdb.dbo.sp_add_job
@job_name=@job_name,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=@description,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=@owner_login_name;
-- Add server:
EXEC msdb.dbo.sp_add_jobserver @job_name=@job_name;
-- Add step to execute SQL:
EXEC msdb.dbo.sp_add_jobstep
@job_name=@job_name,
@step_name=N'Execute SQL',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N'TSQL',
@command=N'EXEC my_stored_procedure; -- OR ANY SQL STATEMENT',
@database_name=@database_name,
@flags=0;
-- Update job to set start step:
EXEC msdb.dbo.sp_update_job
@job_name=@job_name,
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=@description,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=@owner_login_name,
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N'';
-- Schedule job:
EXEC msdb.dbo.sp_add_jobschedule
@job_name=@job_name,
@name=N'Daily',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20170101, --YYYYMMDD
@active_end_date=99991231, --YYYYMMDD (this represents no end date)
@active_start_time=010000, --HHMMSS
@active_end_time=235959; --HHMMSS
回答by efysis
Using T-SQL:
My job is executing stored procedure. You can easy change @commandto run your sql.
使用 T-SQL：我的工作正在执行存储过程。您可以轻松更改@command以运行您的 sql。
EXEC msdb.dbo.sp_add_job
@job_name = N'MakeDailyJob',
@enabled = 1,
@description = N'Procedure execution every day' ;
EXEC msdb.dbo.sp_add_jobstep
@job_name = N'MakeDailyJob',
@step_name = N'Run Procedure',
@subsystem = N'TSQL',
@command = 'exec BackupFromConfig';
EXEC msdb.dbo.sp_add_schedule
@schedule_name = N'Everyday schedule',
@freq_type = 4, -- daily start
@freq_interval = 1,
@active_start_time = '230000' ; -- start time 23:00:00
EXEC msdb.dbo.sp_attach_schedule
@job_name = N'MakeDailyJob',
@schedule_name = N'Everyday schedule' ;
EXEC msdb.dbo.sp_add_jobserver
@job_name = N'MakeDailyJob',
@server_name = @@servername ;
回答by gangster
Here's a sample code:
这是一个示例代码：
Exec sp_add_schedule
@schedule_name = N'SchedulName'
@freq_type = 1
@active_start_time = 08300
回答by pravin Dukare
if You want daily backup // following sql script store in C:\Users\admin\Desktop\DBScript\DBBackUpSQL.sql
如果您想要每日备份 // 以下 sql 脚本存储在 C:\Users\admin\Desktop\DBScript\DBBackUpSQL.sql
DECLARE @pathName NVARCHAR(512),
@databaseName NVARCHAR(512) SET @databaseName = 'Databasename' SET @pathName = 'C:\DBBackup\DBData\DBBackUp' + Convert(varchar(8), GETDATE(), 112) + '_' + Replace((Convert(varchar(8), GETDATE(), 108)),':','-')+ '.bak' BACKUP DATABASE @databaseName TO DISK = @pathName WITH NOFORMAT,
INIT,
NAME = N'',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
open the Task scheduler
打开任务调度程序
create task-> select Triggerstab Select New.
创建任务-> 选择Triggers选项卡 Select New。
Button Select Daily Radio button
按钮 选择每日单选按钮
click OkButton
单击Ok按钮
then click Actiontab Select New.
然后单击Action选项卡选择新建。
Button Put "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"-S ADMIN-PC -i "C:\Users\admin\Desktop\DBScript\DBBackUpSQL.sql"in the program/script text box(make sure Match your files path and Put the double quoted path in start-> search box and if it find then click it and see the backup is there or not)
按钮 Put "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"-S ADMIN-PC -i"C:\Users\admin\Desktop\DBScript\DBBackUpSQL.sql"在程序/脚本文本框中（确保匹配您的文件路径并将双引号路径放在开始-> 搜索框中，如果找到则单击它并查看备份是否存在)
-- the above path may be insted 100 write 90 "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S ADMIN-PC -i"C:\Users\admin\Desktop\DBScript\DBBackUpSQL.sql"
-- 上面的路径可以插入 100 写 90 "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S ADMIN-PC -i"C:\Users\admin\Desktop\DBScript\DBBackUpSQL.sql"
then click ok button
然后点击确定按钮
the Script will execute on time which you select on Trigger tab on daily basis
脚本将按您每天在“触发器”选项卡上选择的时间执行
enjoy it.............
好好享受.............
