How can I schedule a job to run a SQL query daily?

Question

I need to know how to make a SQL query run daily using a SQL Server Agent job, with minimum required configuration settings.

Tarboosh

Here is solution for express edition: https://mcmap.net/q/152701/-how-to-create-jobs-in-sql-server-express-edition – Pseudonymous

Answer 1

1. Expand the SQL Server Agent node and right click the Jobs node in SQL Server Agent and select 'New Job'

2. In the 'New Job' window enter the name of the job and a description on the 'General' tab.

3. Select 'Steps' on the left hand side of the window and click 'New' at the bottom.

4. In the 'Steps' window enter a step name and select the database you want the query to run against.

5. Paste in the T-SQL command you want to run into the Command window and click 'OK'.

6. Click on the 'Schedule' menu on the left of the New Job window and enter the schedule information (e.g. daily and a time).

7. Click 'OK' - and that should be it.

(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)

Siderolite

how to do that on sql server express ? does agent come with sql server express with advanced services' ?' – Armillda

It goes without saying that depending on who you log on as, you may not be able to see the SQL Server Agent node at all... Not everyone logs on as sa. Further (rather dry) info here.. msdn.microsoft.com/en-us/library/ms188283.aspx – Labourer

if SQL Server Agent node is not expandable with a label "Agent XPs disabled", run this code sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Agent XPs', 1; GO RECONFIGURE GO Explanation is under this link: msdn.microsoft.com/en-us/library/ms178127.aspx – Hagfish

Maybe using scripting BAT, CMD, Powershell for do it programmatically – Vladamir

Answer 2

I made an animated GIF of the steps in the accepted answer. This is from MSSQL Server 2012

Veliavelick

Is it must to login with sa? I am not login with sa and i did not see sql server agent. I think i dont have enough rights to see it. – Lightening

To see the SQL Server Agent area in the menu tree, the user you're logged in as needs correct permissions on the MSDB database (MSDB is a built-in database that SSMS uses for things like permissions). From the main tree explorer go to Security > Logins > your-username > right click > properties > user mappings > check msdb > then below check SQLAgentOperatorRole – Veliavelick

Answer 3

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.

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

Catharine

Answer 4

Using T-SQL: My job is executing stored procedure. You can easy change @command to run your 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 ;

Invective

Answer 5

if You want daily backup // following sql script store in 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 Triggers tab Select New .

Button Select Daily Radio button

click Ok Button

then click Action tab Select New.

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)

-- 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"

then click ok button

the Script will execute on time which you select on Trigger tab on daily basis

enjoy it.............

Underquote

Answer 6

Here's a sample code:

Exec sp_add_schedule
    @schedule_name = N'SchedulName' 
    @freq_type = 1
    @active_start_time = 08300

Scenery

adding an explanation of your code in your answer is usually a good idea (even if it's apparent to you). – Carri

This answer doesn't contain enough explanation. Without enough explanation readers can't understand this answer. – Lubra