SQLTeam.com | Weblogs | Forums

Schedule running stored procedure every 3 hours (T-SQL)

tsql
#1

Hi all,

I worked with many databases like Oracle, MySql, Postgres...
And SQL Server as well, long time no see. Also this time I'm under linux and using Azure Data Studio, so no UI to schedule jobs available. So... forgive me for my newbie question below.

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 microsoft doc (can't provide link as I'm fresh here), but it is rather not intuitive and each parameter is not explained in T-SQL.

Please advise.

1 Like

#2

Create a SQL job
Set the time to run every 3 hours
In the step select the db of sproc
Add exec sproc_name

1 Like

#3

See my job script below (I'm about to run it every two hours).

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

I ran it manually and 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 [corrected] (4 days to wait), but why?

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%')

Please advise.

0 Likes

#4

what is todays date where you are living? are you on another planet ? :wink:

0 Likes

#5

Sorry, my bad. It says 2019-04-14 00:00:00.000 (almost 4 days to wait). What did I do wrong in my job script (see above) ?

0 Likes

#6

you have it set to run weekly on Sundays only. 4 days from today = Sunday
change it to run not Weekly but Daily.

0 Likes

#7

Any idea how do I fix it in my script above? I'm under linux and using Azure Data Studio, no access to jobs UI. Thanks anyway...

0 Likes

#8

change from @freq_type=8 to @freq_type=4,

1 Like

#9

Thanks yosiasz.

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.

0 Likes

#10

well it depends where on planet earth you are at this moment and what time it is on your side of the world. what time is your sql server on?

0 Likes

#11

My local time and date:

SELECT SYSDATETIME()

2019-04-10 22:31:46.3549993

But I do not understand why my next scheduled run date is NULL if I do not run job manually. Is it by design?

0 Likes

#12

because it has not run yet

1 Like

#13

So, it is by design.
Thanks for your help, yosiasz.

0 Likes