I am working on an auction web application.
All auctions have an end date. At the end of that time winners are to be determined based on an algorithm.
I am trying to find out a way to determine the winners right after the end datetime.
I could schedule an agent do the job with an interval. But I like to start the process right after the end of the end datetime.
Is there any way to accomplish this?
SQL Server has something called Service Broker. I think you could schedule a job to run every few seconds just to identify which reocrds need to have the algorithm computed and send them to the service broker for processing. Processing could incude calcluating the winner, notifying the winner and the person who put the item up for auction etc. Read up on Service BRoker in Bookds Online, I think it would be very handy for what you need to do.
Schedule an agent job to fire once a tiny bit after the auction end date.
Your best choice might be a .Net app that checks for closing auctions periodically, but you could also use a job that runs every 5 minutes, checks for any auctions ending in the next five minutes, and fires off the WinnerAlgorithm proc with a WAITFOR DELAY modifier. The job will need to be able to fire off multiple, asynchronous jobs to capture every auction ending in that 5-minute time frame:
SELECT ID FROM Auction WHERE AlgorithmIsSet = 0 and AuctionEnd < DATEADD(minute, 5, GETDATE()); -- to pull the ones you need to process
UPDATE Auction SET AlgorithmIsSet = 1 WHERE ID = 12345; -- to mark that this one is taken care of.
WAITFOR DELAY "00:04:23"; -- the exact time until the auction ends - you'll need to construct this from DATEDIFF(second, AuctionEnd, GETDATE())
EXEC WinnerAlgorithm; -- after the wait time, the auction is closed and the winner determined.
This lets the process run as soon as the auction closes. I would have the job running fairly regularly, like every five minutes, and if the agent service stops running or the machine is rebooted that first SELECT statement will pull anything not already processed. As long as you time stamp the bids you can always reconstruct the winner after the fact.
The winner is determined when the auction ends. Your question implies that you want to do something when the auction ends, e.g. notify the winner.
The application that inserts bids into the database, or perhaps a Windows service, can maintain a timer set to expire when the next auction ends. It wakes, processes all completed auctions, determines the next auction expiration and sets the timer again.
WAIT for a period of time or until a specific date/time. That doesn't really help you solve the proposed problem. You still need a separate application to manage a timer that expires when the "next" auction ends. Depending on what you want to do at that time, a Windows service may be the appropriate type of application. (Note that many website hosting services do not allow you to run custom Windows services.)