CODEWITHSUNDEEP
sqlasp-classicdelaydelayed-execution
Levi
Levi

Reputation: 669

How can I make a SQL script run after a certain time without waiting for it?

I'm trying to create a SQL script with a delay.

I could use:

blah blah
WAITFOR DELAY '00:30:00'
blah blah

but using a script like this requires that I sit through the 30 minutes.

Leaving the website will cancel the script.

The problem is that I want to change something in a table, and then change it back automatically 30 minutes later.

The person making the change will leave the webpage, so any kind of client-side script is out of the question.

Nor can I wait for the person to return and make the change if 30 minutes have passed, the change must happen after 30 minutes regardless.

Is there anyway to do this without making a service or any other program on the server?

Using only ASP/SQL programming.

If this is impossible, how do I make a service or program on the server to make this change?

Must be able to start this from the website using ASP.

Upvotes: 1

Views: 1378

Answers (3)

Levi
Levi

Reputation: 669

Figured it out by using SQL Server Agent and SQL procedures.

This is basically how my code is built up now:

Make the temporary change in the table

UPDATE table SET column = 'temp_value' WHERE column = 'normal_value'

Check if the procedure is there, if so, delete it. Create a procedure to revert the changes in the table.

IF EXISTS ( SELECT * 
    FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'myRevertProcedure')
        AND type IN ( N'P', N'PC' ) )
    DROP PROCEDURE myRevertProcedure 

CREATE PROCEDURE myRevertProcedure 
    AS
    BEGIN 
        WAITFOR DELAY '00:30:00'
        UPDATE table SET column = 'normal_value' WHERE column = 'temp_value'
    END

I've created a job in the SQL Server Agent that runs the following:

IF EXISTS ( SELECT * 
    FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'myRevertProcedure')
        AND type IN ( N'P', N'PC' ) )
    BEGIN
        EXEC MyProc
        DROP PROCEDURE myRevertProcedure 
    END

The reason the job does not simply revert the change itself is because the user shall set the delay.

If the delay were allways to be 30 mins, I could've made the job run the following:

IF EXISTS (SELECT * FROM table WHERE column = 'temp_value')
    BEGIN
        WAITFOR DELAY '00:30:00'
        UPDATE table SET column = 'normal_value' WHERE column = 'temp_value'
    END

By doing this, I would not need any procedure.

BTW: The job runs every few seconds.

Upvotes: 0

George Scott
George Scott

Reputation: 21

Personally, although others on this board would never think it is professional... I use website uptime monitoring services to run scripts. For example this one will hit an asp page of your choosing every 30 minutes http://www.serviceuptime.com/free_monitoring.php.

For ease of development I use different monitoring services to load scripts. There are more professional ways of doing so such as making a VBS script and running it through the Task Manager which I do for long running scripts but for simple things like checking a mail queue every so often I just use a monitoring service to load the page often enough to do what I want.

Upvotes: 1

GarethD
GarethD

Reputation: 69759

I personally would not approach the situation this way. I don't know exactly what your data structure is, or why you need to change something for 30 minutes, but I would use a 'Change' table.

So you might have something like

MainTable (ID, Column1, Column2, Column3, Column4);

ChangeTable (ID, Column1, Column2, Column3, Column4, CreatedDateTime);

Whenever you make your change instead of updating your main table you can simply insert the values you would be updating to into the ChangeTable (I'm assuming SQL-Server based on WAITFOR).

I would then make a view like so:

CREATE VIEW dbo.MainView
AS
SELECT  m.ID,
        Column1 = ISNULL(c.Column1, m.Column1),
        Column2 = ISNULL(c.Column2, m.Column2),
        Column3 = ISNULL(c.Column3, m.Column3)
FROM    dbo.MainTable m
        OUTER APPLY 
        (   SELECT  TOP 1 c.Column1, c.Column2, c.Column3
            FROM    dbo.ChangeTable c
            WHERE   c.ID = m.ID
            AND     c.CreatedDate >= DATEADD(MINUTE, -30, GETDATE())
            ORDER BY c.CreatedDate DESC
        ) c;

Then refer to this throughout the website.

If space is an issue you could set up a nightly Job to delete any old entries, e.g. set the following to run at 00:30

DELETE  ChangeTable
WHERE   CreatedDate < CAST(GETDATE() AS DATE);

Upvotes: 3

Related Questions