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

User · Answer

Here s a sample code   Exec sp add schedule      schedule name   N SchedulName        freq type   1      active start time   08300

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to sql-server-2008

Examples related to sql-job