Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: sql server 2000 jobs...

    Hi. i'm new to sql server jobs as i have never used the feature manually before. here is what i want to do but i don't know if it's possible.

    we have a report we use that's accessed from an asp page. currently, the page uses a stored procedure to generate the data. well, as time has progressed, we've added many items to the report. it now takes a good 15-20 seconds to pull the report. what i want to do is run a job that will compile the data for me and just dump that information into a new table. does that make sense?

    can this be done with sql server jobs? thank you.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    sure but the data will never be current.

    why not just see if your query can be tuned. got code?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    well, after looking around, it says i can make a schedule to run it every so often. what do you mean by the data will never be current? i don't want the report to display current data since it's such a hog on resources. i just want to pull the numbers and dump them into a new table. does that make any sense?

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by bla4free
    well, after looking around, it says i can make a schedule to run it every so often. what do you mean by the data will never be current? i don't want the report to display current data since it's such a hog on resources. i just want to pull the numbers and dump them into a new table. does that make any sense?
    it makes sense, as long as you put something like "report processed on such and such a date" so that users know how stale the report is.

    if this latency doesn't matter, then doing it this way has the benefit of being more gentle on the server. the report is processed only once, no matter how many users load it.

    If anyone tries to view the report in the middle of that table being populated though, they might get confused.

  5. #5
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    If anyone tries to view the report in the middle of that table being populated though, they might get confused
    The table will probably be locked, so as long as you don't do an UNCOMMITTED READ (that is, use the WITH (NOLOCK) option), it's no problem They just have to wait untill the job is finished (and the lock is released) before they get the results.

  6. #6
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Bla4free to get back to your original post, yes it can be done. Now if you are planning on creating a bunch of these reports/jobs you might look into MS Reporting Services which allows you to cache a report, and thus it won't be recreated every time.

    Now another way of doing this report is in your asp code or at the end of your job, e-mail the report the intended users. This will put you in power of when the data is updated, and you won't run into problems with users waiting 20 seconds + the time to query the db because of the table lock.

    Good luck,
    Hope this helps

  7. #7
    Join Date
    Jan 2005
    Posts
    165
    Quote Originally Posted by Reghardt
    ...Now another way of doing this report is in your asp code or at the end of your job, e-mail the report the intended users. This will put you in power of when the data is updated, and you won't run into problems with users waiting 20 seconds + the time to query the db because of the table lock.
    i like your idea of emailing the users the report. i never thought of that and i'm sure they would like getting an updated report instead of having to access it. thanks for the tip!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •