我可以通过T-SQL脚本创建MS SQL Server代理作业和计划吗? [英] Can I create MS SQL Server agent jobs and schedules via T-SQL scripts?

问题描述

我有一个远程客户端，需要正常工作才能在一夜之间运行.
他不具备通过MSSQL Management Studio进行交互式设置的技术知识，但是他可以通过查询"窗口运行脚本.

我无法远程访问他的系统.

是否可以向他发送T-SQL脚本或命令行脚本来设置作业(运行存储的proc)和相关的计划?

感谢您的帮助.
James

I have a remote client that needs a regular job to be run overnight.
He doesn''t have the technical knowledge to set it up interactively via MSSQL Management studio, but he can run scripts through Query window.

I have not remote access to his system.

Is it possible to send him either a T-SQL script or a command line script to set up a job (running a stored proc) and an associated schedule?

Thanks for any help.
James

推荐答案

您需要执行三个sp调用:
sp_add_job-向sql代理添加作业
sp_add_jobstep-向您的作业添加命令步骤
sp_add_jobschedule-为您的工作设定时间计划

You need to do three sp calls:
sp_add_job - to add a job to sql agent
sp_add_jobstep - to add a command step to your job
sp_add_jobschedule - to set a timing plan for your job

DECLARE @jobId BINARY(16)

--Add job
EXEC msdb.dbo.sp_add_job @job_name=N''Job Name'', @job_id = @jobId OUTPUT

--Add step to job
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''Do SQL Stuff'',
            @step_id=1,
            @subsystem=N''TSQL'',
            @command=N''SELECT ''''Hello, I am a query'''';'',
            @database_name=N''DB_Name'',
            @flags=0

--Add schedule to job
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N''Mon-Fri 6:00AM to 7:00PM, every hour'',
            @freq_type=8,
            @freq_interval=62,
            @freq_subday_type=8,
            @freq_subday_interval=1,
            @freq_relative_interval=0,
            @freq_recurrence_factor=1,
            @active_start_date=20100302,
            @active_end_date=99991231,
            @active_start_time=60000,
            @active_end_time=190000

请参阅来自Microsoft的sp_add_xx帮助:
http://msdn.microsoft.com/en-us/library/ms182079.aspx [ ^ ]

侯赛因·塔赫里(Hossein Taheri)

See sp_add_xx helps from microsoft:
http://msdn.microsoft.com/en-us/library/ms182079.aspx[^]

Hossein Taheri

侯赛因
非常感谢您的快速回复.
我知道这会很简单，但是在任何地方都找不到.

您的信息将对我有很大帮助.

我检查了您的网站(HTS)，您似乎有很多经验，但看起来还很年轻.

祝你好运，再次感谢.

詹姆斯
([e-mail])

Hi Hossein
Thanks so much for your fast response.
I knew it would be something simple, but I couldn''t find it anywhere.

Your information will help me very much.

I checked out your website (HTS) and you seem to have so much experience and yet look so young.

Good luck and thanks again.

James
([e-mail])

这篇关于我可以通过T-SQL脚本创建MS SQL Server代理作业和计划吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！