Job succeeds manually but fails if scheduled

My client has a number of jobs that are run overnight. We've set them
up to email me when they're completed. Every morning I get in to a
bunch of emails like this:

<quote
JOB RUN:'Tech Pubs Email Notification' was run on 18/03/2006 at
00:00:00
DURATION:0 hours, 0 minutes, 0 seconds
STATUS: Succeeded
MESSAGES:The job succeeded. The Job was invoked by Schedule 10 (Send
Mail). The last step to run was step 1 (Send Mail).
</quote
However, the most important job - the database backup - fails every
time.

<quote>
JOB RUN:'DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1''
was run on 20/03/2006 at 18:00:00
DURATION:0 hours, 0 minutes, 2 seconds
STATUS: Failed
MESSAGES:The job failed. The Job was invoked by Schedule 7 (Schedule
1). The last step to run was step 1 (Step 1).
</quote
What's strange is that the job runs successfully if you kick it off
manually (in EM: right-click and "Start Job")!!! Does anyone have any
idea of why that might be? Where to look for diagnostic information?

TIA

Edwardteddysnips@.hotmail.com wrote:
> My client has a number of jobs that are run overnight. We've set them
> up to email me when they're completed. Every morning I get in to a
> bunch of emails like this:
> <quote>
> JOB RUN:'Tech Pubs Email Notification' was run on 18/03/2006 at
> 00:00:00
> DURATION:0 hours, 0 minutes, 0 seconds
> STATUS: Succeeded
> MESSAGES:The job succeeded. The Job was invoked by Schedule 10 (Send
> Mail). The last step to run was step 1 (Send Mail).
> </quote>
> However, the most important job - the database backup - fails every
> time.
> <quote>
> JOB RUN:'DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1''
> was run on 20/03/2006 at 18:00:00
> DURATION:0 hours, 0 minutes, 2 seconds
> STATUS: Failed
> MESSAGES:The job failed. The Job was invoked by Schedule 7 (Schedule
> 1). The last step to run was step 1 (Step 1).
> </quote>
> What's strange is that the job runs successfully if you kick it off
> manually (in EM: right-click and "Start Job")!!! Does anyone have any
> idea of why that might be? Where to look for diagnostic information?
> TIA
> Edward

Edward,

Have you got the right permissions?

This might sound a bit obvious but its usually the case. You might not
be the owner of the package and the system will only schedule the job
to run if the permissions are correct.

Bryan|||Bryan wrote:
> teddysnips@.hotmail.com wrote:
[...]
>
> Edward,
> Have you got the right permissions?
> This might sound a bit obvious but its usually the case. You might not
> be the owner of the package and the system will only schedule the job
> to run if the permissions are correct.
> Bryan

I would have assumed so, but I'll check. Thanks for the suggestion.

Edward|||Bryan wrote:
[...]
> Edward,
> Have you got the right permissions?
> This might sound a bit obvious but its usually the case. You might not
> be the owner of the package and the system will only schedule the job
> to run if the permissions are correct.

I checked on this today. All the jobs that run correctly have exactly
the same owners/permissions as the job that fails. I can't find any
attribute of the job (apart from what it actually does, obviously) that
distinguishes it from any of the other, successful jobs.

One other thing of which I was not aware. Apparently this job had been
scheduled successfully up until about a week ago, when it began
failing.

Any further thoughts/ideas?

TIA

Edward|||Hi Edward,

To be honest I don't ( I hate admitting defeat though....)

I have encountered this problem in the past especially when picking up
from any previous owners of packages.
One solution I have done is to copy the code from one package and dump
it into a package that is being allowed to run but with a different
owner.
This usually confirmed to myself that the problem must a a Owner /
Permission issue.

Apart from that I'm at a loss to recommend any other course of action.|||(teddysnips@.hotmail.com) writes:
> I checked on this today. All the jobs that run correctly have exactly
> the same owners/permissions as the job that fails. I can't find any
> attribute of the job (apart from what it actually does, obviously) that
> distinguishes it from any of the other, successful jobs.
> One other thing of which I was not aware. Apparently this job had been
> scheduled successfully up until about a week ago, when it began
> failing.
> Any further thoughts/ideas?

Check View History for the job. Don't miss to check View Step History.

Unfortunately, jobs that set up from a maintenance plan does not seem to
write very much useful information, so I'm not really expecting this to
give you anything. (But check nevertheless.) I the maintenance plan has
its own log somewhere, but I don't remember where - or if there was anything
that useful in it.

May you should scrap the plan, and set up the jobs without it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||<teddysnips@.hotmail.com> wrote in message
news:1142932717.825816.151430@.z34g2000cwc.googlegr oups.com...
> My client has a number of jobs that are run overnight. We've set them
> up to email me when they're completed. Every morning I get in to a
> bunch of emails like this:
> <quote>
> JOB RUN: 'Tech Pubs Email Notification' was run on 18/03/2006 at
> 00:00:00
> DURATION: 0 hours, 0 minutes, 0 seconds
> STATUS: Succeeded
> MESSAGES: The job succeeded. The Job was invoked by Schedule 10 (Send
> Mail). The last step to run was step 1 (Send Mail).
> </quote>
> However, the most important job - the database backup - fails every
> time.
> <quote>
> JOB RUN: 'DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1''
> was run on 20/03/2006 at 18:00:00
> DURATION: 0 hours, 0 minutes, 2 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 7 (Schedule
> 1). The last step to run was step 1 (Step 1).
> </quote>
> What's strange is that the job runs successfully if you kick it off
> manually (in EM: right-click and "Start Job")!!! Does anyone have any
> idea of why that might be? Where to look for diagnostic information?
> TIA
> Edward

If it isn't the owner of the job step (sa would be good if sa owns the
database), then check for proper disk
space in the backup destination.

Job scheduler in SQL server

Hi,

I have to genearte an autoamtic email in sql server

could any body guide me for how to schedule job and how to generate automatic email.

Thankyou in Advance.

regards,

Raja.

check this

http://classicasp.aspfaq.com/email/how-do-i-send-e-mail-from-sql-server.html

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 notification

Morning all!

My SQL server email job notifications have stopped working, but mails sent through the xp_sendmail command still work.

The only thing that has changed recently is the SQL server agent account passwords have been changed, these accounts are normal win2000 domain accounts.

Any ideas cos I am fully stumped?Have you tried testing the SQLMail Profile?

Support Services/SQLMail.

Are SQLMail and SQLAgentMail using the same mail profile?|||Already tested the SQL mail profile and that works ok,

How do I check which profile the sqlagentmail is using?|||Originally posted by SQLSlammer
Already tested the SQL mail profile and that works ok,

How do I check which profile the sqlagentmail is using?

Right click on SQL Agent/Properties and check that it is using the same mail profile.

job email

hi
i'm new to sql administration.
the built in sp for email uses a single account to send email.
i have a table (fromEmail, fromName, toEmail, toName, subject,
content). the fromXXX fields are dynamic.
is there another sp that can send mail with these parameters?
how could i do this?
thanx
riyazwhat built in sp did you try?
sp_send_dbmail in sql server 2005 has a @.profile_name argument which is the
name of the profile to send the message from.
hth,
Cristian Babu
<rmanchu@.gmail.com> wrote in message
news:1147241986.934852.156770@.i40g2000cwc.googlegroups.com...
> hi
> i'm new to sql administration.
> the built in sp for email uses a single account to send email.
> i have a table (fromEmail, fromName, toEmail, toName, subject,
> content). the fromXXX fields are dynamic.
> is there another sp that can send mail with these parameters?
> how could i do this?
> thanx
> riyaz
>|||One that I use ,(it's not built in you will have to apply, straightforward)
is XPSMTP on .http://www.sqldev.net/xp/xpsmtp.htm
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
<rmanchu@.gmail.com> wrote in message
news:1147241986.934852.156770@.i40g2000cwc.googlegroups.com...
> hi
> i'm new to sql administration.
> the built in sp for email uses a single account to send email.
> i have a table (fromEmail, fromName, toEmail, toName, subject,
> content). the fromXXX fields are dynamic.
> is there another sp that can send mail with these parameters?
> how could i do this?
> thanx
> riyaz
>|||One that I use ,(it's not built in you will have to apply, straightforward)
is XPSMTP on .http://www.sqldev.net/xp/xpsmtp.htm
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
<rmanchu@.gmail.com> wrote in message
news:1147241986.934852.156770@.i40g2000cwc.googlegroups.com...
> hi
> i'm new to sql administration.
> the built in sp for email uses a single account to send email.
> i have a table (fromEmail, fromName, toEmail, toName, subject,
> content). the fromXXX fields are dynamic.
> is there another sp that can send mail with these parameters?
> how could i do this?
> thanx
> riyaz
>|||Jack Vamvas wrote:
> One that I use ,(it's not built in you will have to apply, straightforward
)
> is XPSMTP on .http://www.sqldev.net/xp/xpsmtp.htm
had a look. seems solid.
but how do i specify the name/email of the recipients? is this
possible? is there a format?
eg: John Doe <jdoe@.trademin.gov.mv>, Jane Doe <email>
thanx
riyaz