| Author |
Topic |
|
missMac
Posting Yak Master
124 Posts |
 Posted - 2008-10-17 : 15:11:48
|
| are there any downsides to jobs ?As you have downsides to cursors and triggers ?Thanks |
|
|
ForkandBeard
Starting Member
10 Posts |
Posted - 2008-10-17 : 17:09:46
|
| I always found the SQL agent/ jobs to be very robust.Go on. Create as many as you want.What are you thinking of using them for?Many ThanksMitchellwww.forkandbeard.co.uk |
 |
|
|
missMac
Posting Yak Master
124 Posts |
|
|
ForkandBeard
Starting Member
10 Posts |
Posted - 2008-10-17 : 17:51:18
|
| Ok, I think this is tough to advise based on the information provided, but I think...You need a single job running.This job will run every five minutes and execute several processes by doing something like the following.SELECT [Events] FROM tblEvents WHERE [Run at] <= getDATE()You would then take each one of these records and run the SP (Event).If certain SPs are taking 20-30 mins then you need to be either pro-actively running these SP's by storing the data so when the events are called you are merely retreiving the data (this could be acheived with another job; your hourly dataStaging job) or seriously look into streamlining the SPs so they run quicker, first place to look might be indexes.But why not just plug the user's actions direct into the SP they need to be running. The MS SQL engine will handle the multiple concurrent transactions? Many ThanksMitchellwww.forkandbeard.co.uk |
|
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 2008-10-17 : 18:04:29
|
| Ok.Am still reading through your advice. One minBut, Question. Can a job have more than one job step ? |
|
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 2008-10-17 : 18:05:36
|
| and can you alter a job at runtime ?Ie, if a job is scheduled to run at midnight UK time, can you alter it to run before then ? |
|
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 2008-10-17 : 18:08:56
|
quote:
Originally posted by ForkandBeard
But why not just plug the user's actions direct into the SP they need to be running. The MS SQL engine will handle the multiple concurrent transactions?
The user actions are coming from a website, and should be scheduled to run at a particular time. So i cant plug it directly into the SP. If not the web page times out |
|
|
|
ForkandBeard
Starting Member
10 Posts |
Posted - 2008-10-17 : 18:29:10
|
| Oh yeah.A job can have loads of steps but they get executed sequentially.I know I'm not providing much of an alternative but tailoring jobs at runtime is not really a good practice. Think of a job as a means to maintain your database not as a means for implementing system logic. Jobs are used for say, updating a record column's 'status' from processing to failed when the record's 'sent' column occured six hours ago, or re-building an index every morning at 6 o'oclock when you're users aren't active. I think I'm understanding your situation better. You have a heap of processing to do on your server at intervals specified by your users.A single job isn't going to do this for you because jobs run in a single batch.I would create a WindowsService on your server. This could then call a single SP on your DataBase to return a dataset of SPs to execute. Your WindowsService could then run each of these SPs in a seperate thread.Many ThanksMitchellwww.forkandbeard.co.uk |
|
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 2008-10-17 : 18:48:58
|
| Thanks Mitchell,Finally, finally its better explained. But running a windows service, wont this be outside the DB ?does MS SQL Have any appliction as an auto run timer ? as you have in VB ? We were thinking of breaking down the jobs to categoriessmall, medium and large. |
|
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 2008-10-17 : 18:57:30
|
| Were are windows services ? Under administrative tools >> services ?And can they run as timers ? autorun ? |
|
|
|
ForkandBeard
Starting Member
10 Posts |
Posted - 2008-10-18 : 06:49:43
|
| Yes, WindowsServices are found under admin tools>>services. You can write one in VB. It will run outside of the DB and SQL, but can call SPs on the DB (or any DB you have the credentials for) it's really simple, just create a SQLClient object and pass in a connection string, create a SQL command object with the SP and then execute it.The WindowsService will run as a background process continuously.You can create as many of these as you want, but you could, and should, implement your system with just the one WindowsService.Many ThanksMitchellwww.forkandbeard.co.uk |
|
|
|
missMac
Posting Yak Master
124 Posts |
|
|
ForkandBeard
Starting Member
10 Posts |
Posted - 2008-10-18 : 07:11:10
|
| Cool,Glad you're gettting somewhere.Many ThanksMitchellwww.forkandbeard.co.uk |
|
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 2008-10-18 : 07:18:08
|
| Yes, thank you. Am quite excited about it. I got this book on SQL 2005 Admin's companion and printed a few articles of the internet.Its been so helpful and most of all thanks to you and everyone who took time to advice accordingly, and thanks to Spriti1. For his blogI guess the service broker is the best option.Thank youxxx |
|
|
|
|
are there any downsides to jobs ?
Thank youITs based on this post