Post reply

To automatically change the value of a column if a particular date time is reached

  • September 7, 2017 at 9:27 pm

    #1958671

    poornima.s_pdi - Tuesday, September 5, 2017 2:19 AM

    Are you  asking me to use view or storedprocedure? If it is a view, the actual data of the table is not affected.
    I am also trying to run a storedprocedure  continuously but don't know how to do?
    I never used Windows scheduler before.
    How to write the update loop with 1 minute delay and have it running all the time using stored procedure?

    You have a bunch of choices, Poornima.  First, here's a link to how to use Windows Task Scheduler.  It would probably be best if you did it through "the Wizard".  To be honest, I've not had to use it or SQL Express in over a decade so thought it would be better to provide you with a link rather than personal instruction.
    https://technet.microsoft.com/en-us/library/cc748993(v=ws.11).aspx

    You would need to call "SQLCMD" to run some SQL on a scheduled basis.  Here's a link to a tutorial for that...
    https://technet.microsoft.com/en-us/library/ms170207%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

    The script that you'd need to run is the one  gvoshol 73146 wrote in his post above.  That script is the one that looks like this...


     UPDATE table --<----<<< LOOK! You need to change this to your real table name.
        SET Status = '2'
      WHERE ModifiedDate >= GETDATE()
        AND Status <> '2'  -- why update it again, if it was already set?
    ;

    The catch here is that in order to avoid having a using name and password in clear text in the Windows Task Scheduler, you need to create an Active Directory user and give that user a login in SQL Express along with the appropriate privs to update just that one table.  That way, you can use a "trusted connection" in both the Windows Task Scheduler and the call to SQLCMD.  If you don't know how to do these things, let us know and we'll try to help.  Give a shot with the help of Yabingooglehoo though.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 7, 2017 at 9:35 pm

    #1958672

    gvoshol 73146 - Tuesday, September 5, 2017 5:47 AM

    Tables don't "automatically update" by themselves.  (Egad, would that be a mess if they did!)  You need something to make the table update.

    Actually, they CAN be self-updating to one extent or another.  You can use a trigger to update the table, although I don't believe that would be the good thing to do for this problem because a trigger won't fire on just a SELECT.  You can also use a "computed column", which could be made to work in this case with a little trickery because of GETDATE().  It wouldn't be a persisted compute column because of GETDATE() and so it couldn't be indexed (well, unless you know the trick with a function that looks at a view with a GETDATE() in it but that trick would seriously backfire because of the return value of GETDATE() changing constantly).  A non-persisted computed column would certainly do the job, though, although performance would suffer during a lookup.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 8, 2017 at 4:02 am

    #1958693

    Hi Jeff,

    I got a Idea from your posts.
    Trigger is not suited for this . As trigger is activated during insert, delete and update commands.
    Mostly will use Windows Task scheduler to carry out the task which runs for every minute.
    Thanks for your valuable information and guidance.
    Thanks a lot.

    Regards,
    Poornima

  • September 8, 2017 at 9:48 am

    #1958752

    You're welcome but it's not just me.  A lot of good people on this thread made similar suggestions.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 10, 2017 at 7:50 pm

    #1958879

    anthony.green - Monday, September 4, 2017 4:10 AM

    If you need to automate anything with SQL Express that you would normally do with the SQL Agent, you need to use the Windows Task Scheduler.

    Is this a job though and not the need for a trigger perhaps, a bit of background on the task at hand may be beneficial.  Eg on insert of a row that is 6:13pm set status = 2 can be done without the need for a job/task schedule.

    Hi anthony.green,
    Thanks for your reply.
    Windows Task scheduler is the only way that i can use for this.
    Thanks a lot.

    Regards,
    Poornima

  • September 10, 2017 at 7:52 pm

    #1958880

    Thanks for all.
    Those suggested and guided me in the right direction to use windows Task scheduler.

    Regards,
    Poornima

  • September 10, 2017 at 9:43 pm

    #1958889

    poornima.s_pdi - Sunday, September 10, 2017 7:52 PM

    Thanks for all.
    Those suggested and guided me in the right direction to use windows Task scheduler.

    Regards,
    Poornima

    I still think it would be better to simply interrogate the dates rather than to try to maintain a temporally dependent status column.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply