August 16, 2011 at 11:57 am
(DB Forums, SQL Team Crosspost)
Hi Everyone,
I am fairly new with SQL Server and don't know how to approach this problem.
I have an Audit table with an "AuditStatus" and "ScheduleDate" column that contains the day the audit has been scheduled (mm/dd/yyyy format). I also have two tables that contains the calendar quarters and what months are associated with each quarter (Q1:1,2,3; Q2:4,5,6, Q3:7,8,9; Q4:10,11,12).
The audit can be scheduled to a calendar quarter without a schedule date. However, if there is no schedule date, and today's date is two weeks before the assigned quarter begins, the audit status needs to be changed.
For example: Let's say I schedule an audit for Q4 2011 today without a schedule date. Then September 16th 2011 arrives. There is still no schedule date for the audit. SQL Server must somehow automatically update the Audit's status to "Delinquent" because October 1st is the first day of the 4th quarter.
So, is there a way for SQL Server to automatically update the "AuditStatus" column if today's date is two weeks before the beginning of an assigned quarter?
Thank you all for your time and help!!!
August 16, 2011 at 12:03 pm
i think what you want is a schedule job that runs every day.
that job updates rows meeting your criteria for being delinquent.
Lowell
August 16, 2011 at 12:11 pm
Do you know if it's possible with SQL Server 2005 Express since it doesn't have Agent Service?
August 16, 2011 at 12:14 pm
tonyppham (8/16/2011)
Do you know if it's possible with SQL Server 2005 Express since it doesn't have Agent Service?
You can arrange something using the windows scheduler instead, but it's not as easily integrated. You'll have to create a .bat file that uses SQLCMD to perform the scheduled updates.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 16, 2011 at 1:15 pm
Evil Kraig F (8/16/2011)
tonyppham (8/16/2011)
Do you know if it's possible with SQL Server 2005 Express since it doesn't have Agent Service?You can arrange something using the windows scheduler instead, but it's not as easily integrated. You'll have to create a .bat file that uses SQLCMD to perform the scheduled updates.
This is a bit out of the scope of SQL Server but, do you know if I can somehow have Access 2007 perform the task when it's open? What are drawbacks of using this method?
The reason why I ask is because I am not sure if IT would allow me to use windows scheduler with a .bat file or task scheduler with vb script. I would like to know my options before asking them. Thanks!
August 16, 2011 at 1:48 pm
tonyppham (8/16/2011)
Evil Kraig F (8/16/2011)
tonyppham (8/16/2011)
Do you know if it's possible with SQL Server 2005 Express since it doesn't have Agent Service?You can arrange something using the windows scheduler instead, but it's not as easily integrated. You'll have to create a .bat file that uses SQLCMD to perform the scheduled updates.
This is a bit out of the scope of SQL Server but, do you know if I can somehow have Access 2007 perform the task when it's open? What are drawbacks of using this method?
The reason why I ask is because I am not sure if IT would allow me to use windows scheduler with a .bat file or task scheduler with vb script. I would like to know my options before asking them. Thanks!
Erm, I'm sure you could, but Access would have to be open, you'd need a startup macro that would start a piece of VBA that would perform waits and check the time, and when it hit a certain time you could send passthrough queries to perform the necessary task.
The idea of doing it that way makes my spine crawl though... so many things could go wrong.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 16, 2011 at 2:14 pm
Evil Kraig F (8/16/2011)
tonyppham (8/16/2011)
Evil Kraig F (8/16/2011)
tonyppham (8/16/2011)
Do you know if it's possible with SQL Server 2005 Express since it doesn't have Agent Service?You can arrange something using the windows scheduler instead, but it's not as easily integrated. You'll have to create a .bat file that uses SQLCMD to perform the scheduled updates.
This is a bit out of the scope of SQL Server but, do you know if I can somehow have Access 2007 perform the task when it's open? What are drawbacks of using this method?
The reason why I ask is because I am not sure if IT would allow me to use windows scheduler with a .bat file or task scheduler with vb script. I would like to know my options before asking them. Thanks!
Erm, I'm sure you could, but Access would have to be open, you'd need a startup macro that would start a piece of VBA that would perform waits and check the time, and when it hit a certain time you could send passthrough queries to perform the necessary task.
The idea of doing it that way makes my spine crawl though... so many things could go wrong.
What if when Access 2007 is opened, my startup macro executes the passthrough query instead of performing the waits and checking the time? This will, however, execute the passthrough query whenever other users open Access. If the stored procedure just updates the table, could this startup macro significantly affect performance or cause any other problems? I assume that there will be at most four users with Access opened at a time.
August 17, 2011 at 12:28 am
tonyppham (8/16/2011)
What if when Access 2007 is opened, my startup macro executes the passthrough query instead of performing the waits and checking the time? This will, however, execute the passthrough query whenever other users open Access. If the stored procedure just updates the table, could this startup macro significantly affect performance or cause any other problems? I assume that there will be at most four users with Access opened at a time.
This will depend on just how invasive that procedure is. It could cause severe blocking and concurrency issues... it could be blithely ignored. Without knowing the usage of the table, the usage of the users in regards to the data you're affecting, the expected volume of data that's being affected ( at 5000 rows this process will start trying to take a table lock), how the rest of the system integrates...
... I just can't tell you if it'll significantly affect your performance. I can tell you that it's probably better then having an Access DB just sitting off in the dark somewhere waiting to trigger the event... but I don't know for sure. I'd seriously look into using a regular scheduling agent that has logs, events, time controls, etc.
Or possibly getting a single instance of SQL Server Standard up for a single processor license. You don't need much to trigger simple jobs on other instances, just make sure they run the code remotely on your more robust Express servers.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply