Dynamically Created SQL Job With Query/Code

Question

I'm working on a bidding system.

People bid when bidding time start and when it ends they get the result.

Is it possible to create a SQL job with query?

What I wanted to do is, once the bidding time ends, I want to update every bidders records as to who is the winner or loser.

So, what I have in mind is that, when the administrator set the start date and end date of bidding, I want to dynamically create a schedule job that would automatically run the stored proc that would update every record when the date is equal to end date.

Is it possible?

Another way that I'm thinking is, put a checker on the bidding page that would constantly check with database about bidding time, if it detects bidding has ended, it will automatically execute a stored proc that updates every user record? The thing that I'm scared with this method is that, what if many users are accessing the page, many users will call that stored proc now.

Any suggestions greatly appreciated.

you can do to a procedure within a SQL agent job to run dynamically .. I am not sure about SQL agent capability of running dynamically — Avi, Mar 16 '20 at 03:28
Yes. it is possible to create a sql job using t-sql. Why do you want to create the schedule job when administrator set the start date and end date of bidding. When the administrator sets the date of bidding, can't it also execute the stored procedure as well ? What is the purpose of the schedule job ? — Squirrel, Mar 16 '20 at 03:33
Thanks for the reply. @Avi is it bad if i run set a sql job that runs every second? it will check the bidding end datetime? if date is equal end datetime. it will call for stored proc. — user5397881, Mar 16 '20 at 03:33
yes. it is bad. You are consuming resources. Better to do it within the same process when administrator set the end date — Squirrel, Mar 16 '20 at 03:35
@Squirrel Thanks for the reply. What I want to do is, once the bidding time has ended, it will automatically update all bidders record. Which is why I plan to create the schedule job dynamically. Sorry, if its confusing — user5397881, Mar 16 '20 at 03:39
i know what you want. But why do you need to do this in `schedule job` when you can do it all together when administrator set the end date ? — Squirrel, Mar 16 '20 at 03:40
Schedule job are mean for something you need to do unattended, at a fixed time and / or interval. — Squirrel, Mar 16 '20 at 03:42
@Squirrel, Sorry, but the end date is being set way before the bidding even start. Which is why I need the schedule job. When the administrator register the end date, I want to create a schedule job based on the end date that the administrator pick. So that when the date is equal to end date, it will update the user record. I believe in my-sql you can do it with Event. I'm looking for something similar. — user5397881, Mar 16 '20 at 03:52
ok. does the `end date` includes time as well ? if you then you can schedule the job to run just after midnight — Squirrel, Mar 16 '20 at 03:59
if you want to create the job from `t-sql`, a cheat way is manually create one in `SQL Server Agent` then right click and `script job` to get the full t-sql script for creating that job — Squirrel, Mar 16 '20 at 04:00
@Squirrel, Sorry for the late reply, had to put this project on hold for a while. It does include time. It just dawn on me tho, dynamic job require special permission to be done, so it can't be done dynamically from system unless db admin give an account that has the rights to do it. I'm at wits end. — user5397881, May 20 '20 at 06:22

Atk · Answer 1 · 2020-03-16T04:19:52.130

In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.

--Create job
USE msdb ;  
GO  

EXEC dbo.sp_add_job  
    @job_name = N'Weekly Sales Data Backup' ;  
GO

--Add schedule to your job
EXEC msdb.dbo.sp_add_jobschedule 
        @job_name = N'Weekly Sales Data Backup', -- Job name
        @name = N'Weekly_Sat_2AM',  -- Schedule name
        @freq_type = 8, -- Weekly
        @freq_interval = 64, -- Saturday
        @freq_recurrence_factor = 1, -- every week
        @active_start_time = 20000 -- 2:00 AM

-- creates a job step that uses Transact-SQL  
USE msdb;  
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

Complete sp_add_jobstep list of parameters

sp_add_jobstep [ @job_id = ] job_id | [ @job_name = ] 'job_name'   
     [ , [ @step_id = ] step_id ]   
     { , [ @step_name = ] 'step_name' }   
     [ , [ @subsystem = ] 'subsystem' ]   
     [ , [ @command = ] 'command' ]   
     [ , [ @additional_parameters = ] 'parameters' ]   
          [ , [ @cmdexec_success_code = ] code ]   
     [ , [ @on_success_action = ] success_action ]   
          [ , [ @on_success_step_id = ] success_step_id ]   
          [ , [ @on_fail_action = ] fail_action ]   
          [ , [ @on_fail_step_id = ] fail_step_id ]   
     [ , [ @server = ] 'server' ]   
     [ , [ @database_name = ] 'database' ]   
     [ , [ @database_user_name = ] 'user' ]   
     [ , [ @retry_attempts = ] retry_attempts ]   
     [ , [ @retry_interval = ] retry_interval ]   
     [ , [ @os_run_priority = ] run_priority ]   
     [ , [ @output_file_name = ] 'file_name' ]   
     [ , [ @flags = ] flags ]   
     [ , { [ @proxy_id = ] proxy_id   
         | [ @proxy_name = ] 'proxy_name' } ]

Thanks for this script sir. I'll give it a try, but it just dawn on me that, without required permission, this cannot be done dnyamically -,- — user5397881, May 20 '20 at 06:19

score 1 · Answer 2 · answered Mar 16 '20 at 04:34

I think system informing the bidders, once the bidding time is over, is better approach due to following reasons:

As it is once per bid, instead of multiple calls per bid and not affecting performance of system
As it is system driven, users cannot try to overload system with multiple unnecessary calls.

I would not suggest you go for dynamic job. The reasons are:

Creating a dynamic job for every bid, will make the system to have huge number of jobs and difficult to maintain
You need to have a separate house keeping script to clean up the jobs, once the job is completed

I would suggest a static job with very short frequencies

Even if you are fine for latency of 30 seconds, you can even schedule jobs as frequently as 30 seconds, which will see the completed bids in the past 30 seconds and send an update to bidders. I am not sure, how that can be even reduced.

Read more on job schedule frequency in SO post

Thanks for the response sir. When you said system informing bidders, is it invoke by admin? if it is, that might works. Thanks again sir — user5397881, May 20 '20 at 06:21
Yes. I meant that scheduling static job will check whether bid is over (Endtime is reached) and it will inform the users(update user record). — Venkataraman R, May 20 '20 at 06:32
you are welcome. If it answered your question, mark it as answer. So, it will be helpful for people in future. — Venkataraman R, May 20 '20 at 06:33

Dynamically Created SQL Job With Query/Code

2 Answers2