Member Avatar for sfener06

i need something like a function to do a specific job which work in a specific time.Like every first day of the year i want to sum all the members account amount to my tbl_information table's column. How can i do that?

  • 3 Contributors
  • 2 Replies
  • 77 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by kvprajapati

Dani AI

Generated

wanted a job that runs yearly (every Jan 1) to sum member account balances into tbl_information. correctly pointed toward SQL Server Agent and mentioned triggers/stored procedures/functions. Important clarifications: triggers are event-driven (fire on DML) and are not appropriate for calendar-based scheduling; SQL Server user-defined functions cannot perform DML that updates other tables, so they are unsuitable for this task. A stored procedure is the right database-side unit to compute the aggregate and persist the result.

Recommended workflow: implement a stored procedure that computes the SUM and either updates a column or—preferably—inserts a timestamped snapshot row into a history table. Wrap the work in a transaction, use TRY/CATCH for error handling, and write run details to a job_log table. On full SQL Server schedule the procedure with SQL Server Agent to run once yearly at midnight on Jan 1. On SQL Server Express (no Agent) schedule a Windows Task that calls sqlcmd to execute the procedure, for example:

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC MyDatabase.dbo.SumMemberAccounts"

Operational cautions: run during low-activity windows to reduce blocking; make the procedure idempotent so reruns do not double-count (use SET col = (SELECT SUM(...)) or insert snapshot rows rather than accumulating); grant the job an account with the minimal required permissions; add robust logging/alerts so failures are noticed; and fully test the process on a non-production copy before applying to production.

Recommended Answers

All 2 Replies

Member Avatar for nalply

When you open SQL Server Management Studio, you see SQL Server Agent. You can define jobs in the SQL Server Agent to be executed regularly at specified times.

Member Avatar for kvprajapati

Of course you may do so; Learn sql triggers,stored procedures, and functions.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.