scheduled job

[middletree]

Just rand across this article:

http://www.aspfaq.com/show.asp?id=2143, which led to this article on using a
SQL Server job: http://www.aspfaq.com/show.asp?id=2403

Here's my question: I have an intranet app which tracks tickets for tech
support. The boss wants it so that, depending on the severity of the ticket,
we go into the DB and check every 15 minutes or so to see if the ticket is
being handled. If no activity is detected in an accepted amount of time,
then an email gets sent automatically to certain managers,

The first article cited above gives 4 options, and the last two aren't
possibilities in my case. I'm leaning toward option 2, which is the SQL
job, but will take advice.

Anyone here know of a better solution?

I should add that stored procedures are not an option. Long story.

 

[Aaron Bertrand - MVP]

Anyone here know of a better solution?

That kind of depends on why some of the solutions are not options, doesn't
it?

I should add that stored procedures are not an option. Long story.

Oh, please expand. I love to hear the excuses for using ad hoc SQL. And
show anyone who is forbidding stored procedures, this article:
http://www.aspfaq.com/2201

 

[Ray at]

Stored procedures aren't an option but scheduling jobs is? That's odd.

I'd go with the SQL solution, personally. Either that, or I'd just buy
www.helpstar.com and be done with it. It's one of the few non-MS
applications that I don't bitch about every single time I use it. It's not
bad, and it has served my department well for many years. But, I regret to
say that we're dropping it in favor of an AS/400 based solution.

Ray at work

 

[middletree]

That kind of depends on why some of the solutions are not options, doesn't
it?

Option 3 starts with, "If it's a high-traffic site", and this app does not
fit that IF condition.
Option 4 is to have the web page open 24/7 using META refresh. Um, do I
need to explain why this isn't gonna happen?

Oh, please expand. I love to hear the excuses for using ad hoc SQL. And
show anyone who is forbidding stored procedures, this article:
http://www.aspfaq.com/2201

I offer no excuses. I can't use SP, period. I have tried to convince the
owner of the database, but he ain't budging. It's his Intranet, and my
ticket tracking app simply does a piggy-back onto that. I need to use his
database because customer and employee info is already in there, and no
point in having it in two places, so I work around his rules. Even if I
wanted to create my won DB, my boss has already decreed that I use the
existing intranet one. He's the boss. I got kids to feed. I do what I'm
told.

So I have some permissions on the DB, but not a lot. If I need to create a
new field, for example, I have to email the guy who owns it, and he gets it
done in a day or two. Can't do anything about it.

Now, having established that, I was going to write an ASP page which would
check these things, then send the emails as needed, and then have some other
program, maybe a custom-built exe, which would call this ASP file every 15
minutes. Then I read the article, and thought, maybe this VBScript thing
woudl be better, or maybe a SQL job would be better (If I can talk the
Intranet guy into it).

 

[middletree]

Stored procedures aren't an option but scheduling jobs is? That's odd.

Read my answer to Aaron. Yes, it's odd, but it's what I'm working with.
Beats being unemployed.

And no, there's no guarantee that I will get approval to get the job thing
going. Just wnated input from you guys before I went to ask.

I'd go with the SQL solution, personally. Either that, or I'd just buy
www.helpstar.com and be done with it.

I don't have the option of spending money.

 

[Ray at]

Now, having established that, I was going to write an ASP page which would
check these things, then send the emails as needed, and then have some other
program, maybe a custom-built exe, which would call this ASP file every 15
minutes. Then I read the article, and thought, maybe this VBScript thing
woudl be better, or maybe a SQL job would be better (If I can talk the
Intranet guy into it).

Now knowing your server dilemma, I'd go with a dedicated task machine.
There were a bunch of various tasks that I had running on different
machines, and it became a bit of a mess. So, I took an old Compaq EVO that
was laying around set it up as a task "server." It runs all my scheduled
tasks for me. No one else touches the machine, and it just sits on my desk
with some other computers. I don't back it up or anything. I just document
the task schedules and keep backup copies of the .bats, .exes, .vbss, etc.
on a server that is backed up. So, I would go with the .vbs solution on
another machine that you control, if that is an option.

Ray at work

 

[Aaron Bertrand - MVP]

I don't have the option of spending money.

I know it's not exactly a seller's market, but surely these people must
realize that they're making it almost impossible for you to get anything
done? Wait one or two days for someone to add a column to a table? Be
forbidden from using stored procedures, or creating your own replicated
database? Spend valuable engineering time applying learning curves to a
home remedy, instead of buying a relatively cheap off-the-shelf solution?

Ugh, I'd be shopping around.

 

[Aaron Bertrand - MVP]

I offer no excuses. I can't use SP, period. I have tried to convince the

owner of the database, but he ain't budging. It's his Intranet, and my
ticket tracking app simply does a piggy-back onto that. I need to use his
database because customer and employee info is already in there, and no
point in having it in two places, so I work around his rules. Even if I
wanted to create my won DB, my boss has already decreed that I use the
existing intranet one. He's the boss. I got kids to feed. I do what I'm
told.

So I have some permissions on the DB, but not a lot. If I need to create a
new field, for example, I have to email the guy who owns it, and he gets it
done in a day or two.

If anybody saw 24 last night, this guy totally reminds me of that jerk who
freaked out because Claudia touched his file system.

A

 

[Ray at]

If anybody saw 24 last night, this guy totally reminds me of that jerk who
freaked out because Claudia touched his file system.

Last night was opening night for the NBA. Come on now.

Ray at work

 

[middletree]

Well, after getting laid off a couple times, including right before my twins
were born, I'm thinking that this isn't enough to make me want to shop
around. Besides, I'm not a full-fledged ASP guy. I'm doing tech support for
our product, and doing this ticket-tracking app has been a side project. (I
had some ASP experience in previous jobs, but not extensive)

So your opinions are valid, but this situation is what it is, and this app I
wrote is now in use, and is being used to enforce some company policies.
Now, back to the original question: Do you agree with Ray about having a
separate machine, or with my original idea that, given these contraints, my
best option is to go for option 2 in your article, or another option?

 

[Bob Barrows]

So your opinions are valid, but this situation is what it is, and
this app I wrote is now in use, and is being used to enforce some
company policies. Now, back to the original question: Do you agree
with Ray about having a separate machine, or with my original idea
that, given these contraints, my best option is to go for option 2 in
your article, or another option?

You have my vote for the SQL Agent job. My only reservation is that the
pointy-haired boss who won't permit you to create stored procedures may also
have a phobia about letting you create scheduled jobs. If so, then you need
to go back to option 1, using Ray's idea for a separate machine if that's
feasible.

Bob Barrows

 

[middletree]

Thanks. For clarification, the guy who owns the intranet and accompanying
database is not my boss.
My boss has said that he wants me to make this work, and work with the
existing Intranet db, but he's not the one who owns it. He's a good boss,
but the other guy is in another dept.