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

Question

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

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

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

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)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

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)

poornima.s_pdi SSCrazy Points: 2720 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

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)

poornima.s_pdi SSCrazy Points: 2720 More actions · Answer 5

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

poornima.s_pdi SSCrazy Points: 2720 More actions · Answer 6

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

Regards,
Poornima

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

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)