Needing advice...ColdFusion or SQL2000? 2

[grx21]

I need some advice please. I am developing a small application that captures and tracks data throughout the month. When a new month starts the data needs to be moved from a live table to an archive table. Would you use coldfusion to trigger the month changing event that will move the data from one table to the other or would you use SQL 2000?

Thanks in advance for any tips.

grx21

 

[ecobb]

Since all of the info is kept in the database, I would use the SQL Server to do the monthly transfers. That way you know as long as the database is up and running, it's going to get transferred. You don't have to worry about if the web server is up, network connections to the database, CF scheduled tasks, etc...



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[grx21]

Hi Ecobb! Thanks for the answer. I was thinking of using a SQL Server trigger to do the job. I looked into <cfschedule> and it looks like it would be a pain to execute the task the first day of each month with <cfschedule>

Thanks again,

grx21

 

[philhege]

Hi grx21,

You're headed in the right direction, but what you really want is a scheduled job in the SQL Server Agent. Write a stored procedure that "archives" your old info, then create a job in the SQL Server Agent that calls it. Schedule the job to run whenever you need it (sounds like first-of-every-month).

SQL is really, really good at this sort of task. Your archive job belongs there. Read BOL for more information about the Agent and scheduled jobs.

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[grx21]

Hi philhege, thanks for your reply and your advice. I'm looking into your suggestions.

grx21

 

[imstillatwork]

generaly, if you can do it in cold fusion, and you can do it in the DB server, the db server will do it better.

 

[grx21]

Thanks again to Ecobb and philhege. Your suggestions worked! I appreciate your responses.

grx21