Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arunv
Starting Member
3 Posts |
 Posted - 2010-08-09 : 09:26:52
|
| Hi, Some one please advice a better solution I have few rows under a category and 5 hours to send messages from selected rows.when a category has 25 - every 12 mins the program should be calledthat is 5 messages per hourwhen there are 5 - every 1 hour the program should be called.when there are 10 - every 30 mins the program should be called and so on.... as the number is dynamic, the firing of program should be dynamic, which is a lil tricky thing ., working on figuring out a way for dynamic calling of program because if we make it occur every 12 mins - even there are 5 messages - it will send all 5 in a single hour - every 12 mins if we make it occur every 30 mins - then 25 messages cannot be finished in single 5 hour span.. |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-08-09 : 09:46:11
|
| What do you mean by "every 12 mins the program should be called"? What kind of program? How is it being called?If you need to run a stored procedure or some kind of sql query at "random" intervals, you might create a job that is run by the sql server agent every 1 minute that checks of this program of yours should be called or not. If it's not supposed to be called at that minute then just do nothing. The next time it runs it checks again and starts the program if the time is correct.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
 |
|
|
arunv
Starting Member
3 Posts |
Posted - 2010-08-09 : 12:36:53
|
| Basically, the time intervals for which a job should be scheduled according to the number of records in a category. When I say program should be called every 12 mins, it is that a job should be fired for every 12 mins e.g., if we have 25 records in a category - each record is sent as an email for every 12 mins thus we can send all 25 records in 5 hours span. but its not 25 recors every time, the number of records in a category can be <= 25 which is a dynamic number. I am struggling to schedule a SQL job agent dynamically -( every 12 mins -5 hrs - 5*60 = 300/25).should I write for every number of records ie for 1, 2, 3... |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-08-10 : 03:43:13
|
| Is it the same job that needs to execute (the same query/procedure with different parameters) for every category? Because if it is then it would be far easier if you change the way you think about this scheduling. Instead of thinking about scheduling a separate job every x minutes, try and think about running several categories at the same time instead. Take this example: Category 1 must run every 4 minutes, Category2 must run every 2 minutes. Then you schedule one single job that runs every minute that is able to process both categories:12:00 -> Process Cat1 and 212:01 -> Do nothing12:02 -> Process Cat212:03 -> Do nothing12:04 -> Process Cat1 and 212:05 -> Do nothing...Will something like this work for you...? Do you understand what I'm saying?- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-08-10 : 03:58:00
|
Here's an example of a query that will return different rows depending on what the time is. If you set the Frequency to be 2 the row will be returned every 2 minutes, if Frequency is 25 it will be returned every 25 minutes:DECLARE @table table ( Category varchar(20), Frequency int ) INSERT INTO @table SELECT 'Category1', 2 UNION ALL SELECT 'Category2', 3--> The number of returned rows depends on what the current time isSELECT DATEPART(minute, GETDATE()), *FROM @tableWHERE DATEDIFF(minute, '2010-01-01 00:00:00', GETDATE()) % Frequency = 0 - LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
|
|
|
|
|
SQL Job Agent help please