Job Run Time Notifications

Is there a way to send an email alert if a dts job runs
for more than a specific time. For example, I want to
send an alert if a job runs for more than 2 minutes.As the first step of your DTS job you could start another job that simply
waits 2 minutes (waitfor delay '00:02:00') and then checks the execution
status of the DTS job (you could use sp_help_job). It could then alert if
the job was still running (assuming the DTS job wouldn't start again before
the monitor finished). I'm sure there's a slicker way but that would be
relatively simple and robust
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tim Savery" <Tim.Savery@.mbna.com> wrote in message
news:05a701c3a2df$b7115900$a001280a@.phx.gbl...
Is there a way to send an email alert if a dts job runs
for more than a specific time. For example, I want to
send an alert if a job runs for more than 2 minutes.|||To be sure whether the job is still in the current execution, you can
execute msdb..sp_help_job before the wait, and then check execute
sp_help_job and check last_run_date and last_run_time. If the job has been
restarted, these two values would have been changed.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eiBPqZxoDHA.360@.TK2MSFTNGP12.phx.gbl...
> As the first step of your DTS job you could start another job that simply
> waits 2 minutes (waitfor delay '00:02:00') and then checks the execution
> status of the DTS job (you could use sp_help_job). It could then alert if
> the job was still running (assuming the DTS job wouldn't start again
before
> the monitor finished). I'm sure there's a slicker way but that would be
> relatively simple and robust
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Tim Savery" <Tim.Savery@.mbna.com> wrote in message
> news:05a701c3a2df$b7115900$a001280a@.phx.gbl...
> Is there a way to send an email alert if a dts job runs
> for more than a specific time. For example, I want to
> send an alert if a job runs for more than 2 minutes.
>

job access

I thought I had read that in SQL 2k5 we would be able to give a non-sa user
access to specific jobs, but I can't find how to do this. Is it
possible? If so, can someone tell me how?
Thanks, AndreHi
You have to create proxy account. Lookup BOL
"Andre" <nospam@.spam.com> wrote in message
news:%23zuEqCe2HHA.3900@.TK2MSFTNGP02.phx.gbl...
>I thought I had read that in SQL 2k5 we would be able to give a non-sa user
> access to specific jobs, but I can't find how to do this. Is it
> possible? If so, can someone tell me how?
> Thanks, Andre
>
>|||Yes...different levels of access based on different
configuration options of proxies or using the new database
roles in msdb. Check the following topics in books online:
How to: Configure a User to Create and Manage SQL Server
Agent Jobs (SQL Server Management Studio)
SQL Server Agent Fixed Database Roles
Creating SQL Server Agent Proxies
-Sue
On Wed, 8 Aug 2007 10:22:15 -0700, "Andre" <nospam@.spam.com>
wrote:

>I thought I had read that in SQL 2k5 we would be able to give a non-sa user
>access to specific jobs, but I can't find how to do this. Is it
>possible? If so, can someone tell me how?
>Thanks, Andre
>|||Thanks Sue - I'll check this out.
Andre