Hot To Reset Counter Nightly?

[compweb]

I have a click counter for each record in my link database. How do I make all the counter revert to 0 each night at midnight?
Using SQL server 7, ASP & VBscript.
Please help.

Thanks,
Mitch

 

[bperry]

Sounds like you want to create a job that the SQLServerAgent will wake up and run for you every night. One of the steps (or perhaps the only step) in the job will be an sql Update statement to zero out your counters.

If you haven't worked with jobs or the SQLServerAgent before, then you probably need a DBA book with a good chapter in it on creating/running jobs.

bp

 

[Guest_imported]

It is neither hard to write a sql statement to update the counters nor is it difficult to schedule this with the service agent.

Under management in enterprise manager select jobs.

Right click jobs and select new job.
Give it a name.
Make Step 1 your sql statement that updates the counters.
Give Step 1 a name.
Go to the schedule tab. Make this run every day, once, at 12:00am with no end date.

 

[compweb]

Is it possible to do it without access to the enterprise manager? I'm using a hosting service.

Thanks for the replies,
Mitch

 

[bperry]

Yep, I tried it, works just like you said.

compweb needs to make sure the Agent service is running, which might not be by default. (It wasn't on my system).

 

[bperry]

I think there are some system stored procedures that can do this. Check BOL for syntax details.

sp_add_job
sp_add_jobstep
sp_add_jobschedule