locked
T-SQL to schedule running stored procedure every 3 hours RRS feed

Answers

  • Here  you go

    Job name is "Test"

    USE [msdb]
    GO
    DECLARE @jobId BINARY(16)
    EXEC  msdb.dbo.sp_add_job @job_name=N'test', 
    @enabled=1, 
    @notify_level_eventlog=0, 
    @notify_level_email=2, 
    @notify_level_page=2, 
    @delete_level=0, 
    @category_name=N'[Uncategorized (Local)]', 
    @owner_login_name=N'sa', @job_id = @jobId OUTPUT
    select @jobId
    GO
    EXEC msdb.dbo.sp_add_jobserver @job_name=N'test', @server_name = N'SEVERNAME' ---CHNAGE IT
    GO
    USE [msdb]
    GO
    EXEC msdb.dbo.sp_add_jobstep @job_name=N'test', @step_name=N'sp', 
    @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 stored procedure', 
    @database_name=N'master', 
    @flags=0
    GO
    USE [msdb]
    GO
    EXEC msdb.dbo.sp_update_job @job_name=N'test', 
    @enabled=1, 
    @start_step_id=1, 
    @notify_level_eventlog=0, 
    @notify_level_email=2, 
    @notify_level_page=2, 
    @delete_level=0, 
    @description=N'', 
    @category_name=N'[Uncategorized (Local)]', 
    @owner_login_name=N'sa', 
    @notify_email_operator_name=N'', 
    @notify_page_operator_name=N''
    GO
    USE [msdb]
    GO
    DECLARE @schedule_id int
    EXEC msdb.dbo.sp_add_jobschedule @job_name=N'test', @name=N'every 3 hours', 
    @enabled=1, 
    @freq_type=8, 
    @freq_interval=1, 
    @freq_subday_type=8, 
    @freq_subday_interval=3, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20190410, 
    @active_end_date=99991231, 
    @active_start_time=0, 
    @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
    select @schedule_id
    GO


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 10, 2019 11:08 AM
    Answerer
  • Any T-SQL example, please? This time I'm under linux and using Azure Data Studio, so no UI to schedule jobs available.

    yes

    you can use t-sql statements too for the same purpose

    looks like this

    DECLARE @JobID uniqueidentifier

    SELECT @JobID = job_id
    FROM msdb.dbo.sysjobs
    WHERE name = 'your job name'

    DECLARE @schedule_id int EXEC msdb.dbo.sp_add_jobschedule @job_id=@JobID, @name=N'Schedule Name', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=8, @freq_subday_interval=3, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20190410, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959, @schedule_id = @schedule_id OUTPUT select @schedule_id

    for the schedule


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Wednesday, April 10, 2019 11:22 AM
    Answerer
  • Any T-SQL example, please? This time I'm under linux and using Azure Data Studio, so no UI to schedule jobs available.

    Good day marchelloua,

    Well done on using transact-sql over using GUI wizards. This is the best way to go :-)

    I am guessing that the reasons the document which you brought is confusing or as you said "not intuitive" are (1) code is always less intuitive then visual GUI, and (2) This is the middle of the process and not the first step - like open a book in the middle without reading the first half :-)

    The link is only about "Schedule a Job" but first you need to create the job.

    Step 1: Create new job
    Step 2: Add steps to the job
    Step 3: Create (add) new schedule
    Step 4: Attach the schedule to the job
    Step 5: Targets the specified job at the specified server.

    https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-job?view=sql-server-2017

    USE msdb ;  
    GO  
    EXEC dbo.sp_add_job  
        @job_name = N'Weekly Sales Data Backup' ;  
    GO  
    EXEC sp_add_jobstep  
        @job_name = N'Weekly Sales Data Backup',  
        @step_name = N'Set database to read only',  
        @subsystem = N'TSQL',  
        @command = N'ALTER DATABASE SALES SET READ_ONLY',   
        @retry_attempts = 5,  
        @retry_interval = 5 ;  
    GO  
    EXEC dbo.sp_add_schedule  
        @schedule_name = N'RunOnce',  
        @freq_type = 1,  
        @active_start_time = 233000 ;  
    USE msdb ;  
    GO  
    EXEC sp_attach_schedule  
       @job_name = N'Weekly Sales Data Backup',  
       @schedule_name = N'RunOnce';  
    GO  
    EXEC dbo.sp_add_jobserver  
        @job_name = N'Weekly Sales Data Backup';  
    GO

    For more details on each step, simple search Google for the SP name. For example, to understand what sp_add_jobserver do, simply google it and find the right document :-)

     


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, April 10, 2019 11:25 AM
  • Thanks Ronen, I need to clarify that my both client (Azure Data Studio) and server are running under ubuntu/linux: 

    SELECT @@VERSION
    
    Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64) 	Mar 12 2019 19:29:19 	Copyright (C) 2017 Microsoft Corporation	Express Edition (64-bit) on Linux (Ubuntu 18.04.2 LTS)

    Is it still possible to follow your links in my case? 

    Or should I do it using linux cron only?

    You can install SQL Server Agent in ubuntu

    https://www.mssqltips.com/sqlservertip/4817/installing-sql-server-agent-on-ubuntu-server/

    For adding a job

    see

    https://www.mssqltips.com/sqlservertip/4807/how-to-schedule-a-sql-server-agent-job-on-a-linux-based-instance-using-tsql/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Wednesday, April 10, 2019 11:50 AM
    Answerer
  • Hi,

    1. You can connect the server remotely using SSMS on windows if you feel like you better familiar with SSMS

    * Personally I prefer the Azure Data Studio

    2. Once you install the job agent it is the same application which run on windows from the point of view of the user. Queries are basically the same. The documentation is the same and the answer is yes

    There are some limitations regarding what you can use and what is supported (link a bit old but give the idea):
    https://cloudblogs.microsoft.com/sqlserver/2017/03/17/sql-server-on-linux-running-jobs-with-sql-server-agent/


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Wednesday, April 10, 2019 11:58 AM
  • Hi marchelloua,

     

    >>Upd: So, I assume that it is by design. If I did not run job manually yet, then next scheduled run date is NULL.

     

    Yes, you are right. You use tsql just to start the job but not execute the job, so you need to do it manually.When we use ssms to create a job and start the job, the job will be executed first:

     

     

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, April 11, 2019 7:58 AM

All replies

  • Create a job in SSMS  and  clickon the Schedule  option on the left pane 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 10, 2019 10:46 AM
    Answerer
  • Just use Agent Job. 

    Please mark as answer if this post helped you

    Wednesday, April 10, 2019 10:49 AM
  •  Hi all, 

    How do I prepare T-SQL to schedule running stored procedure every 3 hours (every day, starting from now, no expiration date)? 

    Tried to follow this link, but it is rather not intuitive and each parameter is not explained in T-SQL  https://docs.microsoft.com/en-us/sql/ssms/agent/schedule-a-job?view=sql-server-2017

    Just go to SQL Server Agent and add a new job 

    Add a step of type Transact SQL and in the command part add

    EXEC Procedurename paramvalue1,...

    Then attach a schedule for job as shown below

    This will ensure job execute every 3 hours starting from date when its set without any expiration


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 10, 2019 10:51 AM
    Answerer
  • Any T-SQL example, please? This time I'm under linux and using Azure Data Studio, so no UI to schedule jobs available.
    Wednesday, April 10, 2019 10:57 AM
  • Any T-SQL example, please? This time I'm under linux and using Azure Data Studio, so no UI to schedule jobs available.
    Wednesday, April 10, 2019 10:57 AM
  • Any T-SQL example, please? This time I'm under linux and using Azure Data Studio, so no UI to schedule jobs available.
    Wednesday, April 10, 2019 10:57 AM
  • Here  you go

    Job name is "Test"

    USE [msdb]
    GO
    DECLARE @jobId BINARY(16)
    EXEC  msdb.dbo.sp_add_job @job_name=N'test', 
    @enabled=1, 
    @notify_level_eventlog=0, 
    @notify_level_email=2, 
    @notify_level_page=2, 
    @delete_level=0, 
    @category_name=N'[Uncategorized (Local)]', 
    @owner_login_name=N'sa', @job_id = @jobId OUTPUT
    select @jobId
    GO
    EXEC msdb.dbo.sp_add_jobserver @job_name=N'test', @server_name = N'SEVERNAME' ---CHNAGE IT
    GO
    USE [msdb]
    GO
    EXEC msdb.dbo.sp_add_jobstep @job_name=N'test', @step_name=N'sp', 
    @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 stored procedure', 
    @database_name=N'master', 
    @flags=0
    GO
    USE [msdb]
    GO
    EXEC msdb.dbo.sp_update_job @job_name=N'test', 
    @enabled=1, 
    @start_step_id=1, 
    @notify_level_eventlog=0, 
    @notify_level_email=2, 
    @notify_level_page=2, 
    @delete_level=0, 
    @description=N'', 
    @category_name=N'[Uncategorized (Local)]', 
    @owner_login_name=N'sa', 
    @notify_email_operator_name=N'', 
    @notify_page_operator_name=N''
    GO
    USE [msdb]
    GO
    DECLARE @schedule_id int
    EXEC msdb.dbo.sp_add_jobschedule @job_name=N'test', @name=N'every 3 hours', 
    @enabled=1, 
    @freq_type=8, 
    @freq_interval=1, 
    @freq_subday_type=8, 
    @freq_subday_interval=3, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20190410, 
    @active_end_date=99991231, 
    @active_start_time=0, 
    @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
    select @schedule_id
    GO


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 10, 2019 11:08 AM
    Answerer

  • 1-->mssql installed on linux can schedule using windows connection.

    2.--> another method using crontab -->Following the crontab standard

    you have use crone job to schedule.

    https://sqlschool.com/blog/cron-jobs-in-sql-server/

    https://www.tecmint.com/11-cron-scheduling-task-examples-in-linux/

    format:

     +---------------- minute (0 - 59)
     |  +------------- hour (0 - 23)
     |  |  +---------- day of month (1 - 31)
     |  |  |  +------- month (1 - 12)
     |  |  |  |  +---- day of week (0 - 6) (Sunday=0 or 7)
     |  |  |  |  |
     *  *  *  *  *  command to be executed
     Schedule a cron to execute at 3am daily.

    This will be useful for scheduling database backup on a daily basis.

    0 3 * * * /bin/sh backup.sh ---> 

    Any query let me know.


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx






    • Edited by AV111 Wednesday, April 10, 2019 11:38 AM
    Wednesday, April 10, 2019 11:19 AM
  • Any T-SQL example, please? This time I'm under linux and using Azure Data Studio, so no UI to schedule jobs available.

    yes

    you can use t-sql statements too for the same purpose

    looks like this

    DECLARE @JobID uniqueidentifier

    SELECT @JobID = job_id
    FROM msdb.dbo.sysjobs
    WHERE name = 'your job name'

    DECLARE @schedule_id int EXEC msdb.dbo.sp_add_jobschedule @job_id=@JobID, @name=N'Schedule Name', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=8, @freq_subday_interval=3, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20190410, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959, @schedule_id = @schedule_id OUTPUT select @schedule_id

    for the schedule


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Wednesday, April 10, 2019 11:22 AM
    Answerer
  • Any T-SQL example, please? This time I'm under linux and using Azure Data Studio, so no UI to schedule jobs available.

    Good day marchelloua,

    Well done on using transact-sql over using GUI wizards. This is the best way to go :-)

    I am guessing that the reasons the document which you brought is confusing or as you said "not intuitive" are (1) code is always less intuitive then visual GUI, and (2) This is the middle of the process and not the first step - like open a book in the middle without reading the first half :-)

    The link is only about "Schedule a Job" but first you need to create the job.

    Step 1: Create new job
    Step 2: Add steps to the job
    Step 3: Create (add) new schedule
    Step 4: Attach the schedule to the job
    Step 5: Targets the specified job at the specified server.

    https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-job?view=sql-server-2017

    USE msdb ;  
    GO  
    EXEC dbo.sp_add_job  
        @job_name = N'Weekly Sales Data Backup' ;  
    GO  
    EXEC sp_add_jobstep  
        @job_name = N'Weekly Sales Data Backup',  
        @step_name = N'Set database to read only',  
        @subsystem = N'TSQL',  
        @command = N'ALTER DATABASE SALES SET READ_ONLY',   
        @retry_attempts = 5,  
        @retry_interval = 5 ;  
    GO  
    EXEC dbo.sp_add_schedule  
        @schedule_name = N'RunOnce',  
        @freq_type = 1,  
        @active_start_time = 233000 ;  
    USE msdb ;  
    GO  
    EXEC sp_attach_schedule  
       @job_name = N'Weekly Sales Data Backup',  
       @schedule_name = N'RunOnce';  
    GO  
    EXEC dbo.sp_add_jobserver  
        @job_name = N'Weekly Sales Data Backup';  
    GO

    For more details on each step, simple search Google for the SP name. For example, to understand what sp_add_jobserver do, simply google it and find the right document :-)

     


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, April 10, 2019 11:25 AM
  • Thanks Ronen, I need to clarify that my both client (Azure Data Studio) and server are running under ubuntu/linux: 

    SELECT @@VERSION
    
    Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64) 	Mar 12 2019 19:29:19 	Copyright (C) 2017 Microsoft Corporation	Express Edition (64-bit) on Linux (Ubuntu 18.04.2 LTS)

    Is it still possible to follow your links in my case? 

    Or should I do it using linux cron only?

    Wednesday, April 10, 2019 11:43 AM
  • Thanks Ronen, I need to clarify that my both client (Azure Data Studio) and server are running under ubuntu/linux: 

    SELECT @@VERSION
    
    Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64) 	Mar 12 2019 19:29:19 	Copyright (C) 2017 Microsoft Corporation	Express Edition (64-bit) on Linux (Ubuntu 18.04.2 LTS)

    Is it still possible to follow your links in my case? 

    Or should I do it using linux cron only?

    You can install SQL Server Agent in ubuntu

    https://www.mssqltips.com/sqlservertip/4817/installing-sql-server-agent-on-ubuntu-server/

    For adding a job

    see

    https://www.mssqltips.com/sqlservertip/4807/how-to-schedule-a-sql-server-agent-job-on-a-linux-based-instance-using-tsql/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Wednesday, April 10, 2019 11:50 AM
    Answerer
  • Hi,

    1. You can connect the server remotely using SSMS on windows if you feel like you better familiar with SSMS

    * Personally I prefer the Azure Data Studio

    2. Once you install the job agent it is the same application which run on windows from the point of view of the user. Queries are basically the same. The documentation is the same and the answer is yes

    There are some limitations regarding what you can use and what is supported (link a bit old but give the idea):
    https://cloudblogs.microsoft.com/sqlserver/2017/03/17/sql-server-on-linux-running-jobs-with-sql-server-agent/


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Wednesday, April 10, 2019 11:58 AM
  • Ok, now my script is below. When I run it manually then it works fine.

    Exec msdb.dbo.sp_start_job 'backup_tax';
    Go

    But when I check next scheduled run date, it says 2019-04-14 00:00:00.000, but why? Today is 2019-04-10, so it will run only after 4 days?

    How do I check next scheduled run date: 

    SELECT
    sj.name AS jobName
    , ss.name AS scheduleName
    , sja.next_scheduled_run_date
    FROM msdb.dbo.sysjobs sj
    INNER JOIN msdb.dbo.sysjobactivity sja ON sja.job_id = sj.job_id
    INNER JOIN msdb.dbo.sysjobschedules sjs ON sjs.job_id = sja.job_id
    INNER JOIN msdb.dbo.sysschedules ss ON ss.schedule_id = sjs.schedule_id
    WHERE (sj.name LIKE 'backup%')


    My job script:

    USE [tax]
    GO
    DECLARE @jobId BINARY(16)
    EXEC msdb.dbo.sp_add_job @job_name=N'backup_tax', 
    @enabled=1, 
    @notify_level_eventlog=0, 
    @notify_level_email=2, 
    @notify_level_page=2, 
    @delete_level=0, 
    @category_name=N'[Uncategorized (Local)]', 
    @owner_login_name=N'sa', @job_id = @jobId OUTPUT
    select @jobId
    GO
    EXEC msdb.dbo.sp_add_jobserver @job_name=N'backup_tax', @server_name = N'marchelloG' ---CHANGE IT
    GO
    USE [tax]
    GO
    EXEC msdb.dbo.sp_add_jobstep @job_name=N'backup_tax', @step_name=N'sp', 
    @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 BackupDatabase @fullPath = ''/mnt/hdd/tmp/backup_tax.bak''', 
    @database_name=N'tax', 
    @flags=0
    GO
    USE [tax]
    GO
    EXEC msdb.dbo.sp_update_job @job_name=N'backup_tax', 
    @enabled=1, 
    @start_step_id=1, 
    @notify_level_eventlog=0, 
    @notify_level_email=2, 
    @notify_level_page=2, 
    @delete_level=0, 
    @description=N'', 
    @category_name=N'[Uncategorized (Local)]', 
    @owner_login_name=N'sa', 
    @notify_email_operator_name=N'', 
    @notify_page_operator_name=N''
    Go
    USE [tax]
    Go
    DECLARE @schedule_id int
    EXEC msdb.dbo.sp_add_jobschedule @job_name=N'backup_tax', @name=N'every 2 hours', 
    @enabled=1, 
    @freq_type=8, 
    @freq_interval=1, 
    @freq_subday_type=8, 
    @freq_subday_interval=2, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20190410, 
    @active_end_date=99991231, 
    @active_start_time=0, 
    @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
    Select @schedule_id
    Go

    Please advise how do I set it to run every two hours and starting immediately, without waiting 4 days... 

    Thanks

    Wednesday, April 10, 2019 6:19 PM
  • So, I deleted job and re-created it with @freq_type=4.
    Checked next scheduled run date and it was NULL.
    Ran job manually and it worked fine, then checked next scheduled run date and it was 2019-04-11 00:00:00.000
    Basically, I can live with that workaround (run job manually after creating).
    Still, if there is any idea how to do it properly without need to run manually, please advise.

    Upd: So, I assume that it is by design. If I did not run job manually yet, then next scheduled run date is NULL.

    • Edited by marchelloua Wednesday, April 10, 2019 8:00 PM
    Wednesday, April 10, 2019 7:23 PM
  • Hi marchelloua,

     

    >>Upd: So, I assume that it is by design. If I did not run job manually yet, then next scheduled run date is NULL.

     

    Yes, you are right. You use tsql just to start the job but not execute the job, so you need to do it manually.When we use ssms to create a job and start the job, the job will be executed first:

     

     

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, April 11, 2019 7:58 AM