I have a job that has 4 steps, all of which have to run. They are all T-SQL
scripts. How can I get the job to report failure if step 2 fails but step 4
doesn't?Stephanie wrote:
> I have a job that has 4 steps, all of which have to run. They are
> all T-SQL scripts. How can I get the job to report failure if step 2
> fails but step 4 doesn't?
Are you saying you want the job to continue should one of the steps fail
or are you saying you want all the steps to run regardless of success
and have the job report failure if any of the steps fail. If the latter,
then how will you resolve running the failed step?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I want all steps to run regardless of the status of a particular step but I
want failure of the job as a whole to be based on failure of any one of the
steps. If a step fails, I will worry about that later. But I'd like all
steps to run no matter what and I'd like to get an alert if any of the steps
fail as opposed to just the last step.
"David Gugick" wrote:
> Stephanie wrote:
> Are you saying you want the job to continue should one of the steps fail
> or are you saying you want all the steps to run regardless of success
> and have the job report failure if any of the steps fail. If the latter,
> then how will you resolve running the failed step?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Stephanie wrote:
> I want all steps to run regardless of the status of a particular step
> but I want failure of the job as a whole to be based on failure of
> any one of the steps. If a step fails, I will worry about that
> later. But I'd like all steps to run no matter what and I'd like to
> get an alert if any of the steps fail as opposed to just the last
> step.
You can check the "Append output to step history" for each step. In the
morning, examine the job history and show step detail. If there were any
failed steps, you'll see them there. Make sure each step is created to
proceed to the next step on failure or success.
select
j.name,
h.*
From
msdb..sysjobs j inner join
msdb..sysjobhistory h
On
j.job_id = h.job_id
and
j.name = N'<job_name>'
and
h.sql_message_id > 0
Order By
j.name,
h.run_date,
h.run_time
-- OR
Exec msdb..sp_help_jobhistory @.job_name = N'<job_name>', @.run_status = 0
David Gugick
Quest Software
www.imceda.com
www.quest.comsql
Showing posts with label failure. Show all posts
Showing posts with label failure. Show all posts
Friday, March 23, 2012
job retry
I have a step in a job that is set to retry on failure. Last night we made a change to a store procedure before the step failed. The retry started about 20 minutes after the update of the stored procedure. Will SQL Server use the updated version stored procedure on the retry?Yes, it will. Once you update a sproc, any subsequent calls to that procedure after it has been changed will invoke the new procedure version.|||Thank you.
Job reports failure, but bak file created
The view history of a job created from a maintenance plan reports that it
failed, and the log says that it failed in the backup operation because
"A nonrecoverable I/O error occurred on file
"\\\\BIGPETE\\E\\data\\Backups\\Daily\\MCR_SFPMS\\MCR_SFPMS_backup_200707110001.bak:" 64(The specified network name is no longer available.).
And yet MCR_SFPMS_backup_200707110001.bak does exist, and moreover, it was a
valid backup, as I just confirmed by restoring it to a test database. So why
the error?
Also, this does not happen consistently. The reporting of this apparent
non-error has occured twice out of the last five runs.Does the backup process include a Verify? IF so the error is most likely
occurring on the verify process which is done after the backup is complete.
Backup & Verify is an intensive operation and if you are backing up across
the network there is plenty of time for the network to drop packets etc.
Sounds like you have an unstable network or networks equipment.
--
Andrew J. Kelly SQL MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:AF302871-2972-420D-A932-408F42EA1EA9@.microsoft.com...
> The view history of a job created from a maintenance plan reports that it
> failed, and the log says that it failed in the backup operation because
> "A nonrecoverable I/O error occurred on file
> "\\\\BIGPETE\\E\\data\\Backups\\Daily\\MCR_SFPMS\\MCR_SFPMS_backup_200707110001.bak:"
> 64(The specified network name is no longer available.).
> And yet MCR_SFPMS_backup_200707110001.bak does exist, and moreover, it was
> a
> valid backup, as I just confirmed by restoring it to a test database. So
> why
> the error?
> Also, this does not happen consistently. The reporting of this apparent
> non-error has occured twice out of the last five runs.|||On Jul 11, 9:16 am, Bev Kaufman <BevKauf...@.discussions.microsoft.com>
wrote:
> The view history of a job created from a maintenance plan reports that it
> failed, and the log says that it failed in the backup operation because
> "A nonrecoverable I/O error occurred on file
> "\\\\BIGPETE\\E\\data\\Backups\\Daily\\MCR_SFPMS\\MCR_SFPMS_backup_200707=11=AD0001.bak:" 64(The specified network name is no longer available.).
> And yet MCR_SFPMS_backup_200707110001.bak does exist, and moreover, it wa=s a
> valid backup, as I just confirmed by restoring it to a test database. So= why
> the error?
> Also, this does not happen consistently. The reporting of this apparent
> non-error has occured twice out of the last five runs.
Are you utilizing a third party backup tool? We use RedGate in our
SQL Server 2000 (SP3a - don't ask) environment and have encountered
this on a daily basis. When we used normal backup procedures, no
errors were reported. I have the same issue though, but figured it
might be resolved with a newer service pack. The only thing I can
think of is possibly disk contention of you are utilizing a SAN
environment, but not sure.
Maybe if we put our heads together, we can figure it out. I haven't
put a whole lot of effort into it once I realized that the backups are
working properly (as verified by the restores). I know, terrible, but
sometimes other fires are hotter :)
failed, and the log says that it failed in the backup operation because
"A nonrecoverable I/O error occurred on file
"\\\\BIGPETE\\E\\data\\Backups\\Daily\\MCR_SFPMS\\MCR_SFPMS_backup_200707110001.bak:" 64(The specified network name is no longer available.).
And yet MCR_SFPMS_backup_200707110001.bak does exist, and moreover, it was a
valid backup, as I just confirmed by restoring it to a test database. So why
the error?
Also, this does not happen consistently. The reporting of this apparent
non-error has occured twice out of the last five runs.Does the backup process include a Verify? IF so the error is most likely
occurring on the verify process which is done after the backup is complete.
Backup & Verify is an intensive operation and if you are backing up across
the network there is plenty of time for the network to drop packets etc.
Sounds like you have an unstable network or networks equipment.
--
Andrew J. Kelly SQL MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:AF302871-2972-420D-A932-408F42EA1EA9@.microsoft.com...
> The view history of a job created from a maintenance plan reports that it
> failed, and the log says that it failed in the backup operation because
> "A nonrecoverable I/O error occurred on file
> "\\\\BIGPETE\\E\\data\\Backups\\Daily\\MCR_SFPMS\\MCR_SFPMS_backup_200707110001.bak:"
> 64(The specified network name is no longer available.).
> And yet MCR_SFPMS_backup_200707110001.bak does exist, and moreover, it was
> a
> valid backup, as I just confirmed by restoring it to a test database. So
> why
> the error?
> Also, this does not happen consistently. The reporting of this apparent
> non-error has occured twice out of the last five runs.|||On Jul 11, 9:16 am, Bev Kaufman <BevKauf...@.discussions.microsoft.com>
wrote:
> The view history of a job created from a maintenance plan reports that it
> failed, and the log says that it failed in the backup operation because
> "A nonrecoverable I/O error occurred on file
> "\\\\BIGPETE\\E\\data\\Backups\\Daily\\MCR_SFPMS\\MCR_SFPMS_backup_200707=11=AD0001.bak:" 64(The specified network name is no longer available.).
> And yet MCR_SFPMS_backup_200707110001.bak does exist, and moreover, it wa=s a
> valid backup, as I just confirmed by restoring it to a test database. So= why
> the error?
> Also, this does not happen consistently. The reporting of this apparent
> non-error has occured twice out of the last five runs.
Are you utilizing a third party backup tool? We use RedGate in our
SQL Server 2000 (SP3a - don't ask) environment and have encountered
this on a daily basis. When we used normal backup procedures, no
errors were reported. I have the same issue though, but figured it
might be resolved with a newer service pack. The only thing I can
think of is possibly disk contention of you are utilizing a SAN
environment, but not sure.
Maybe if we put our heads together, we can figure it out. I haven't
put a whole lot of effort into it once I realized that the backups are
working properly (as verified by the restores). I know, terrible, but
sometimes other fires are hotter :)
Monday, March 19, 2012
JOB Failure!
Hi,
I've a question related to JOB failure in the DTS package.
The following is the error from the LOG file.Could some one help me in this
regard,as how i can find the easy way out.
LOG FILE ERROR:
--
Execution Started: 2/24/2005 11:34:06 PM
Execution Completed: 2/25/2005 12:17:56 AM
Total Execution Time: 2629.937 seconds
Package Steps execution information:
Step 'DTSStep_DTSActiveScriptTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft XML Extensions to SQL Server
Error Description: [DBNETLIB][ConnectionRead (recv()).]General netwo
rk
error. Check your network documentation.
Thanks,
ShyamShyam,
This usually means that there was an actual network problem. A switch or
router may have out of service, a cable disconnected, etc.
RLF
"Shyam" <Shyam@.discussions.microsoft.com> wrote in message
news:25D54BB1-7605-4654-ADBE-7C08CF3901B8@.microsoft.com...
> Hi,
> I've a question related to JOB failure in the DTS package.
> The following is the error from the LOG file.Could some one help me in
> this
> regard,as how i can find the easy way out.
> LOG FILE ERROR:
> --
> Execution Started: 2/24/2005 11:34:06 PM
> Execution Completed: 2/25/2005 12:17:56 AM
> Total Execution Time: 2629.937 seconds
> Package Steps execution information:
> Step 'DTSStep_DTSActiveScriptTask_1' failed
> Step Error Source: Microsoft Data Transformation Services (DTS) Package
> Step Error Description:Error Code: 0
> Error Source= Microsoft XML Extensions to SQL Server
> Error Description: [DBNETLIB][ConnectionRead (recv()).]General net
work
> error. Check your network documentation.
> Thanks,
> Shyam
>
I've a question related to JOB failure in the DTS package.
The following is the error from the LOG file.Could some one help me in this
regard,as how i can find the easy way out.
LOG FILE ERROR:
--
Execution Started: 2/24/2005 11:34:06 PM
Execution Completed: 2/25/2005 12:17:56 AM
Total Execution Time: 2629.937 seconds
Package Steps execution information:
Step 'DTSStep_DTSActiveScriptTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft XML Extensions to SQL Server
Error Description: [DBNETLIB][ConnectionRead (recv()).]General netwo
rk
error. Check your network documentation.
Thanks,
ShyamShyam,
This usually means that there was an actual network problem. A switch or
router may have out of service, a cable disconnected, etc.
RLF
"Shyam" <Shyam@.discussions.microsoft.com> wrote in message
news:25D54BB1-7605-4654-ADBE-7C08CF3901B8@.microsoft.com...
> Hi,
> I've a question related to JOB failure in the DTS package.
> The following is the error from the LOG file.Could some one help me in
> this
> regard,as how i can find the easy way out.
> LOG FILE ERROR:
> --
> Execution Started: 2/24/2005 11:34:06 PM
> Execution Completed: 2/25/2005 12:17:56 AM
> Total Execution Time: 2629.937 seconds
> Package Steps execution information:
> Step 'DTSStep_DTSActiveScriptTask_1' failed
> Step Error Source: Microsoft Data Transformation Services (DTS) Package
> Step Error Description:Error Code: 0
> Error Source= Microsoft XML Extensions to SQL Server
> Error Description: [DBNETLIB][ConnectionRead (recv()).]General net
work
> error. Check your network documentation.
> Thanks,
> Shyam
>
JOB Failure!
Hi,
I've a question related to JOB failure in the DTS package.
The following is the error from the LOG file.Could some one help me in this
regard,as how i can find the easy way out.
LOG FILE ERROR:
--
Execution Started: 2/24/2005 11:34:06 PM
Execution Completed: 2/25/2005 12:17:56 AM
Total Execution Time: 2629.937 seconds
Package Steps execution information:
Step 'DTSStep_DTSActiveScriptTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft XML Extensions to SQL Server
Error Description: [DBNETLIB][ConnectionRead (recv()).]General network
error. Check your network documentation.
Thanks,
ShyamShyam,
This usually means that there was an actual network problem. A switch or
router may have out of service, a cable disconnected, etc.
RLF
"Shyam" <Shyam@.discussions.microsoft.com> wrote in message
news:25D54BB1-7605-4654-ADBE-7C08CF3901B8@.microsoft.com...
> Hi,
> I've a question related to JOB failure in the DTS package.
> The following is the error from the LOG file.Could some one help me in
> this
> regard,as how i can find the easy way out.
> LOG FILE ERROR:
> --
> Execution Started: 2/24/2005 11:34:06 PM
> Execution Completed: 2/25/2005 12:17:56 AM
> Total Execution Time: 2629.937 seconds
> Package Steps execution information:
> Step 'DTSStep_DTSActiveScriptTask_1' failed
> Step Error Source: Microsoft Data Transformation Services (DTS) Package
> Step Error Description:Error Code: 0
> Error Source= Microsoft XML Extensions to SQL Server
> Error Description: [DBNETLIB][ConnectionRead (recv()).]General network
> error. Check your network documentation.
> Thanks,
> Shyam
>
I've a question related to JOB failure in the DTS package.
The following is the error from the LOG file.Could some one help me in this
regard,as how i can find the easy way out.
LOG FILE ERROR:
--
Execution Started: 2/24/2005 11:34:06 PM
Execution Completed: 2/25/2005 12:17:56 AM
Total Execution Time: 2629.937 seconds
Package Steps execution information:
Step 'DTSStep_DTSActiveScriptTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft XML Extensions to SQL Server
Error Description: [DBNETLIB][ConnectionRead (recv()).]General network
error. Check your network documentation.
Thanks,
ShyamShyam,
This usually means that there was an actual network problem. A switch or
router may have out of service, a cable disconnected, etc.
RLF
"Shyam" <Shyam@.discussions.microsoft.com> wrote in message
news:25D54BB1-7605-4654-ADBE-7C08CF3901B8@.microsoft.com...
> Hi,
> I've a question related to JOB failure in the DTS package.
> The following is the error from the LOG file.Could some one help me in
> this
> regard,as how i can find the easy way out.
> LOG FILE ERROR:
> --
> Execution Started: 2/24/2005 11:34:06 PM
> Execution Completed: 2/25/2005 12:17:56 AM
> Total Execution Time: 2629.937 seconds
> Package Steps execution information:
> Step 'DTSStep_DTSActiveScriptTask_1' failed
> Step Error Source: Microsoft Data Transformation Services (DTS) Package
> Step Error Description:Error Code: 0
> Error Source= Microsoft XML Extensions to SQL Server
> Error Description: [DBNETLIB][ConnectionRead (recv()).]General network
> error. Check your network documentation.
> Thanks,
> Shyam
>
JOB Failure!
Hi,
I've a question related to JOB failure in the DTS package.
The following is the error from the LOG file.Could some one help me in this
regard,as how i can find the easy way out.
LOG FILE ERROR:
Execution Started: 2/24/2005 11:34:06 PM
Execution Completed: 2/25/2005 12:17:56 AM
Total Execution Time: 2629.937 seconds
Package Steps execution information:
Step 'DTSStep_DTSActiveScriptTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft XML Extensions to SQL Server
Error Description: [DBNETLIB][ConnectionRead (recv()).]General network
error. Check your network documentation.
Thanks,
Shyam
Shyam,
This usually means that there was an actual network problem. A switch or
router may have out of service, a cable disconnected, etc.
RLF
"Shyam" <Shyam@.discussions.microsoft.com> wrote in message
news:25D54BB1-7605-4654-ADBE-7C08CF3901B8@.microsoft.com...
> Hi,
> I've a question related to JOB failure in the DTS package.
> The following is the error from the LOG file.Could some one help me in
> this
> regard,as how i can find the easy way out.
> LOG FILE ERROR:
> --
> Execution Started: 2/24/2005 11:34:06 PM
> Execution Completed: 2/25/2005 12:17:56 AM
> Total Execution Time: 2629.937 seconds
> Package Steps execution information:
> Step 'DTSStep_DTSActiveScriptTask_1' failed
> Step Error Source: Microsoft Data Transformation Services (DTS) Package
> Step Error Description:Error Code: 0
> Error Source= Microsoft XML Extensions to SQL Server
> Error Description: [DBNETLIB][ConnectionRead (recv()).]General network
> error. Check your network documentation.
> Thanks,
> Shyam
>
I've a question related to JOB failure in the DTS package.
The following is the error from the LOG file.Could some one help me in this
regard,as how i can find the easy way out.
LOG FILE ERROR:
Execution Started: 2/24/2005 11:34:06 PM
Execution Completed: 2/25/2005 12:17:56 AM
Total Execution Time: 2629.937 seconds
Package Steps execution information:
Step 'DTSStep_DTSActiveScriptTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft XML Extensions to SQL Server
Error Description: [DBNETLIB][ConnectionRead (recv()).]General network
error. Check your network documentation.
Thanks,
Shyam
Shyam,
This usually means that there was an actual network problem. A switch or
router may have out of service, a cable disconnected, etc.
RLF
"Shyam" <Shyam@.discussions.microsoft.com> wrote in message
news:25D54BB1-7605-4654-ADBE-7C08CF3901B8@.microsoft.com...
> Hi,
> I've a question related to JOB failure in the DTS package.
> The following is the error from the LOG file.Could some one help me in
> this
> regard,as how i can find the easy way out.
> LOG FILE ERROR:
> --
> Execution Started: 2/24/2005 11:34:06 PM
> Execution Completed: 2/25/2005 12:17:56 AM
> Total Execution Time: 2629.937 seconds
> Package Steps execution information:
> Step 'DTSStep_DTSActiveScriptTask_1' failed
> Step Error Source: Microsoft Data Transformation Services (DTS) Package
> Step Error Description:Error Code: 0
> Error Source= Microsoft XML Extensions to SQL Server
> Error Description: [DBNETLIB][ConnectionRead (recv()).]General network
> error. Check your network documentation.
> Thanks,
> Shyam
>
Job failure notifications for non-administrative users
Some of my power users create their own SQL Agent jobs. All well and
good. However, I don't wish to give them System Administrator
privileges, and when they create their jobs, the check box to "e-mail
operator" in the job notifications tab is greyed out. Is this a
feature? I'm having difficulty finding documentation on it, or even any
mention, where a distinction is made between administrators and
standard users.
Other than setting up the notifications myself after the jobs are
created, is there any way around this issue? This is on SQL 2000 EE,
service pack 3a, running on Windows 2K Advanced Server.
Thanks,
Steve
Hi Steve,
A distinction is made between administrators and standard users?
This depends of as configured the Services SQL Server Agent, the account is
"Local System" or is a administrator account o User Email Account?.
If is "Local System", this can you problem.
Hermilson Tinoco
"Steve" wrote:
> Some of my power users create their own SQL Agent jobs. All well and
> good. However, I don't wish to give them System Administrator
> privileges, and when they create their jobs, the check box to "e-mail
> operator" in the job notifications tab is greyed out. Is this a
> feature? I'm having difficulty finding documentation on it, or even any
> mention, where a distinction is made between administrators and
> standard users.
> Other than setting up the notifications myself after the jobs are
> created, is there any way around this issue? This is on SQL 2000 EE,
> service pack 3a, running on Windows 2K Advanced Server.
> Thanks,
> Steve
>
|||SQL Server Agent is running under the same account as the SQL Server, a
domain account set up to be a local administrator.
Note that as an administrator I can add notifications to both my own
jobs and my users' jobs; it's just the users that can't add
notifications to their own jobs.
Steve
good. However, I don't wish to give them System Administrator
privileges, and when they create their jobs, the check box to "e-mail
operator" in the job notifications tab is greyed out. Is this a
feature? I'm having difficulty finding documentation on it, or even any
mention, where a distinction is made between administrators and
standard users.
Other than setting up the notifications myself after the jobs are
created, is there any way around this issue? This is on SQL 2000 EE,
service pack 3a, running on Windows 2K Advanced Server.
Thanks,
Steve
Hi Steve,
A distinction is made between administrators and standard users?
This depends of as configured the Services SQL Server Agent, the account is
"Local System" or is a administrator account o User Email Account?.
If is "Local System", this can you problem.
Hermilson Tinoco
"Steve" wrote:
> Some of my power users create their own SQL Agent jobs. All well and
> good. However, I don't wish to give them System Administrator
> privileges, and when they create their jobs, the check box to "e-mail
> operator" in the job notifications tab is greyed out. Is this a
> feature? I'm having difficulty finding documentation on it, or even any
> mention, where a distinction is made between administrators and
> standard users.
> Other than setting up the notifications myself after the jobs are
> created, is there any way around this issue? This is on SQL 2000 EE,
> service pack 3a, running on Windows 2K Advanced Server.
> Thanks,
> Steve
>
|||SQL Server Agent is running under the same account as the SQL Server, a
domain account set up to be a local administrator.
Note that as an administrator I can add notifications to both my own
jobs and my users' jobs; it's just the users that can't add
notifications to their own jobs.
Steve
Job failure notifications for non-administrative users
Some of my power users create their own SQL Agent jobs. All well and
good. However, I don't wish to give them System Administrator
privileges, and when they create their jobs, the check box to "e-mail
operator" in the job notifications tab is greyed out. Is this a
feature? I'm having difficulty finding documentation on it, or even any
mention, where a distinction is made between administrators and
standard users.
Other than setting up the notifications myself after the jobs are
created, is there any way around this issue? This is on SQL 2000 EE,
service pack 3a, running on Windows 2K Advanced Server.
Thanks,
SteveHi Steve,
A distinction is made between administrators and standard users'
This depends of as configured the Services SQL Server Agent, the account is
"Local System" or is a administrator account o User Email Account?.
If is "Local System", this can you problem.
Hermilson Tinoco
"Steve" wrote:
> Some of my power users create their own SQL Agent jobs. All well and
> good. However, I don't wish to give them System Administrator
> privileges, and when they create their jobs, the check box to "e-mail
> operator" in the job notifications tab is greyed out. Is this a
> feature? I'm having difficulty finding documentation on it, or even any
> mention, where a distinction is made between administrators and
> standard users.
> Other than setting up the notifications myself after the jobs are
> created, is there any way around this issue? This is on SQL 2000 EE,
> service pack 3a, running on Windows 2K Advanced Server.
> Thanks,
> Steve
>|||SQL Server Agent is running under the same account as the SQL Server, a
domain account set up to be a local administrator.
Note that as an administrator I can add notifications to both my own
jobs and my users' jobs; it's just the users that can't add
notifications to their own jobs.
Steve
good. However, I don't wish to give them System Administrator
privileges, and when they create their jobs, the check box to "e-mail
operator" in the job notifications tab is greyed out. Is this a
feature? I'm having difficulty finding documentation on it, or even any
mention, where a distinction is made between administrators and
standard users.
Other than setting up the notifications myself after the jobs are
created, is there any way around this issue? This is on SQL 2000 EE,
service pack 3a, running on Windows 2K Advanced Server.
Thanks,
SteveHi Steve,
A distinction is made between administrators and standard users'
This depends of as configured the Services SQL Server Agent, the account is
"Local System" or is a administrator account o User Email Account?.
If is "Local System", this can you problem.
Hermilson Tinoco
"Steve" wrote:
> Some of my power users create their own SQL Agent jobs. All well and
> good. However, I don't wish to give them System Administrator
> privileges, and when they create their jobs, the check box to "e-mail
> operator" in the job notifications tab is greyed out. Is this a
> feature? I'm having difficulty finding documentation on it, or even any
> mention, where a distinction is made between administrators and
> standard users.
> Other than setting up the notifications myself after the jobs are
> created, is there any way around this issue? This is on SQL 2000 EE,
> service pack 3a, running on Windows 2K Advanced Server.
> Thanks,
> Steve
>|||SQL Server Agent is running under the same account as the SQL Server, a
domain account set up to be a local administrator.
Note that as an administrator I can add notifications to both my own
jobs and my users' jobs; it's just the users that can't add
notifications to their own jobs.
Steve
Job failure notification
Hello,
I created a job that will execute two dts packages at night. Both back up
different databases from the remote server to the local server, so they are
not dependent on each other. Also, if the first one fails, the job steps to
the second one. However, I would like to be notified if either/both of them
fail (and know which one failed).
Can I do this or should I have two separate jobs. If possible, I would
rather get an email saying DTS Package 1 failed versus having to look at the
each log file to determine which one failed.
--
Thanks in advance,
StevenWhy are you using DTS to do backups ?
You can have a job fire that will backup databases. You would have a job
per server you wanted to backup. You can specify the location of the backup
file using UNC. When you say backup do you actually mean data transfer ?
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Steven K" <sckeels@.lucent.com> wrote in message
news:uspA2dlRDHA.2116@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I created a job that will execute two dts packages at night. Both back up
> different databases from the remote server to the local server, so they
are
> not dependent on each other. Also, if the first one fails, the job steps
to
> the second one. However, I would like to be notified if either/both of
them
> fail (and know which one failed).
> Can I do this or should I have two separate jobs. If possible, I would
> rather get an email saying DTS Package 1 failed versus having to look at
the
> each log file to determine which one failed.
> --
> Thanks in advance,
> Steven
>|||Sorry Allan,
What I meant to write was data transfer.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:OtAVkklRDHA.3132@.tk2msftngp13.phx.gbl...
> Why are you using DTS to do backups ?
> You can have a job fire that will backup databases. You would have a job
> per server you wanted to backup. You can specify the location of the
backup
> file using UNC. When you say backup do you actually mean data transfer ?
> --
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Steven K" <sckeels@.lucent.com> wrote in message
> news:uspA2dlRDHA.2116@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I created a job that will execute two dts packages at night. Both back
up
> > different databases from the remote server to the local server, so they
> are
> > not dependent on each other. Also, if the first one fails, the job
steps
> to
> > the second one. However, I would like to be notified if either/both of
> them
> > fail (and know which one failed).
> >
> > Can I do this or should I have two separate jobs. If possible, I would
> > rather get an email saying DTS Package 1 failed versus having to look at
> the
> > each log file to determine which one failed.
> >
> > --
> > Thanks in advance,
> >
> > Steven
> >
> >
>
I created a job that will execute two dts packages at night. Both back up
different databases from the remote server to the local server, so they are
not dependent on each other. Also, if the first one fails, the job steps to
the second one. However, I would like to be notified if either/both of them
fail (and know which one failed).
Can I do this or should I have two separate jobs. If possible, I would
rather get an email saying DTS Package 1 failed versus having to look at the
each log file to determine which one failed.
--
Thanks in advance,
StevenWhy are you using DTS to do backups ?
You can have a job fire that will backup databases. You would have a job
per server you wanted to backup. You can specify the location of the backup
file using UNC. When you say backup do you actually mean data transfer ?
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Steven K" <sckeels@.lucent.com> wrote in message
news:uspA2dlRDHA.2116@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I created a job that will execute two dts packages at night. Both back up
> different databases from the remote server to the local server, so they
are
> not dependent on each other. Also, if the first one fails, the job steps
to
> the second one. However, I would like to be notified if either/both of
them
> fail (and know which one failed).
> Can I do this or should I have two separate jobs. If possible, I would
> rather get an email saying DTS Package 1 failed versus having to look at
the
> each log file to determine which one failed.
> --
> Thanks in advance,
> Steven
>|||Sorry Allan,
What I meant to write was data transfer.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:OtAVkklRDHA.3132@.tk2msftngp13.phx.gbl...
> Why are you using DTS to do backups ?
> You can have a job fire that will backup databases. You would have a job
> per server you wanted to backup. You can specify the location of the
backup
> file using UNC. When you say backup do you actually mean data transfer ?
> --
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Steven K" <sckeels@.lucent.com> wrote in message
> news:uspA2dlRDHA.2116@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I created a job that will execute two dts packages at night. Both back
up
> > different databases from the remote server to the local server, so they
> are
> > not dependent on each other. Also, if the first one fails, the job
steps
> to
> > the second one. However, I would like to be notified if either/both of
> them
> > fail (and know which one failed).
> >
> > Can I do this or should I have two separate jobs. If possible, I would
> > rather get an email saying DTS Package 1 failed versus having to look at
> the
> > each log file to determine which one failed.
> >
> > --
> > Thanks in advance,
> >
> > Steven
> >
> >
>
Job Failure External Log
Someone in this newsgroup has suggested turning on External Log to see the
job failure information as follow:
Job's properties, choose the step, click 'Edit' then choose 'Advanced' tab.
There would be 'Output file' field. Check if it is set and if it is, then
look at the file.
For a scheduled job failure, I find that there is no external log is
enabled. I would like to know where is the best place to put the external
log ? I don't want to mix it with other logs.
ThanksI would put it with the other logs, but name it differently or put it in a
sub-directory underneath the logs directory. There it will easier to find.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:OtHNrfpZFHA.464@.TK2MSFTNGP15.phx.gbl...
> Someone in this newsgroup has suggested turning on External Log to see the
> job failure information as follow:
> Job's properties, choose the step, click 'Edit' then choose 'Advanced'
tab.
> There would be 'Output file' field. Check if it is set and if it is, then
> look at the file.
> For a scheduled job failure, I find that there is no external log is
> enabled. I would like to know where is the best place to put the external
> log ? I don't want to mix it with other logs.
> Thanks
>
job failure information as follow:
Job's properties, choose the step, click 'Edit' then choose 'Advanced' tab.
There would be 'Output file' field. Check if it is set and if it is, then
look at the file.
For a scheduled job failure, I find that there is no external log is
enabled. I would like to know where is the best place to put the external
log ? I don't want to mix it with other logs.
ThanksI would put it with the other logs, but name it differently or put it in a
sub-directory underneath the logs directory. There it will easier to find.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:OtHNrfpZFHA.464@.TK2MSFTNGP15.phx.gbl...
> Someone in this newsgroup has suggested turning on External Log to see the
> job failure information as follow:
> Job's properties, choose the step, click 'Edit' then choose 'Advanced'
tab.
> There would be 'Output file' field. Check if it is set and if it is, then
> look at the file.
> For a scheduled job failure, I find that there is no external log is
> enabled. I would like to know where is the best place to put the external
> log ? I don't want to mix it with other logs.
> Thanks
>
Job Failure External Log
Someone in this newsgroup has suggested turning on External Log to see the
job failure information as follow:
Job's properties, choose the step, click 'Edit' then choose 'Advanced' tab.
There would be 'Output file' field. Check if it is set and if it is, then
look at the file.
For a scheduled job failure, I find that there is no external log is
enabled. I would like to know where is the best place to put the external
log ? I don't want to mix it with other logs.
ThanksI would put it with the other logs, but name it differently or put it in a
sub-directory underneath the logs directory. There it will easier to find.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:OtHNrfpZFHA.464@.TK2MSFTNGP15.phx.gbl...
> Someone in this newsgroup has suggested turning on External Log to see the
> job failure information as follow:
> Job's properties, choose the step, click 'Edit' then choose 'Advanced'
tab.
> There would be 'Output file' field. Check if it is set and if it is, then
> look at the file.
> For a scheduled job failure, I find that there is no external log is
> enabled. I would like to know where is the best place to put the external
> log ? I don't want to mix it with other logs.
> Thanks
>
job failure information as follow:
Job's properties, choose the step, click 'Edit' then choose 'Advanced' tab.
There would be 'Output file' field. Check if it is set and if it is, then
look at the file.
For a scheduled job failure, I find that there is no external log is
enabled. I would like to know where is the best place to put the external
log ? I don't want to mix it with other logs.
ThanksI would put it with the other logs, but name it differently or put it in a
sub-directory underneath the logs directory. There it will easier to find.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:OtHNrfpZFHA.464@.TK2MSFTNGP15.phx.gbl...
> Someone in this newsgroup has suggested turning on External Log to see the
> job failure information as follow:
> Job's properties, choose the step, click 'Edit' then choose 'Advanced'
tab.
> There would be 'Output file' field. Check if it is set and if it is, then
> look at the file.
> For a scheduled job failure, I find that there is no external log is
> enabled. I would like to know where is the best place to put the external
> log ? I don't want to mix it with other logs.
> Thanks
>
Job Failure External Log
Someone in this newsgroup has suggested turning on External Log to see the
job failure information as follow:
Job's properties, choose the step, click 'Edit' then choose 'Advanced' tab.
There would be 'Output file' field. Check if it is set and if it is, then
look at the file.
For a scheduled job failure, I find that there is no external log is
enabled. I would like to know where is the best place to put the external
log ? I don't want to mix it with other logs.
Thanks
I would put it with the other logs, but name it differently or put it in a
sub-directory underneath the logs directory. There it will easier to find.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:OtHNrfpZFHA.464@.TK2MSFTNGP15.phx.gbl...
> Someone in this newsgroup has suggested turning on External Log to see the
> job failure information as follow:
> Job's properties, choose the step, click 'Edit' then choose 'Advanced'
tab.
> There would be 'Output file' field. Check if it is set and if it is, then
> look at the file.
> For a scheduled job failure, I find that there is no external log is
> enabled. I would like to know where is the best place to put the external
> log ? I don't want to mix it with other logs.
> Thanks
>
job failure information as follow:
Job's properties, choose the step, click 'Edit' then choose 'Advanced' tab.
There would be 'Output file' field. Check if it is set and if it is, then
look at the file.
For a scheduled job failure, I find that there is no external log is
enabled. I would like to know where is the best place to put the external
log ? I don't want to mix it with other logs.
Thanks
I would put it with the other logs, but name it differently or put it in a
sub-directory underneath the logs directory. There it will easier to find.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:OtHNrfpZFHA.464@.TK2MSFTNGP15.phx.gbl...
> Someone in this newsgroup has suggested turning on External Log to see the
> job failure information as follow:
> Job's properties, choose the step, click 'Edit' then choose 'Advanced'
tab.
> There would be 'Output file' field. Check if it is set and if it is, then
> look at the file.
> For a scheduled job failure, I find that there is no external log is
> enabled. I would like to know where is the best place to put the external
> log ? I don't want to mix it with other logs.
> Thanks
>
Job Failure emails not being sent in SQL 2005
Hi I am trying to configure notification emails on my sql2005 server for SQL
agent job failures. I have configured a mail profile in database mail and I
can send test emails which are received correctly.
I have configured the notification to email me when the job completes but
this is not working and I do not receive any notifications. When I look at
the job history
it gives me the message below after the normal job completion information.
NOTE: Failed to notify 'Email User' via email.
Any ideas as to want i can do to fix this problem.
Cheers
James
On Jan 28, 5:23 am, "Mark Yudkin" <DoNotContac...@.boingboing.org>
wrote:
> The most likely cause is that the userid running thejobis not permitted to
> sendemail. Check out the authorizations on your mailserverand add the
> necessary permissions.
> "James Matthews" <JamesMatth...@.discussions.microsoft.com> wrote in message
> news:30E73785-3D3D-4829-AB4A-3DB82F5E7E4F@.microsoft.com...
>
>
>
> - Show quoted text -
I encountered the same problem and fixed it by restarting the SQL
SERVER AGENT in Management Studio.
agent job failures. I have configured a mail profile in database mail and I
can send test emails which are received correctly.
I have configured the notification to email me when the job completes but
this is not working and I do not receive any notifications. When I look at
the job history
it gives me the message below after the normal job completion information.
NOTE: Failed to notify 'Email User' via email.
Any ideas as to want i can do to fix this problem.
Cheers
James
On Jan 28, 5:23 am, "Mark Yudkin" <DoNotContac...@.boingboing.org>
wrote:
> The most likely cause is that the userid running thejobis not permitted to
> sendemail. Check out the authorizations on your mailserverand add the
> necessary permissions.
> "James Matthews" <JamesMatth...@.discussions.microsoft.com> wrote in message
> news:30E73785-3D3D-4829-AB4A-3DB82F5E7E4F@.microsoft.com...
>
>
>
> - Show quoted text -
I encountered the same problem and fixed it by restarting the SQL
SERVER AGENT in Management Studio.
Job Failure emails not being sent in SQL 2005
Hi I am trying to configure notification emails on my sql2005 server for SQL
agent job failures. I have configured a mail profile in database mail and I
can send test emails which are received correctly.
I have configured the notification to email me when the job completes but
this is not working and I do not receive any notifications. When I look at
the job history
it gives me the message below after the normal job completion information.
NOTE: Failed to notify 'Email User' via email.
Any ideas as to want i can do to fix this problem.
Cheers
JamesThe most likely cause is that the userid running the job is not permitted to
send email. Check out the authorizations on your mail server and add the
necessary permissions.
"James Matthews" <JamesMatthews@.discussions.microsoft.com> wrote in message
news:30E73785-3D3D-4829-AB4A-3DB82F5E7E4F@.microsoft.com...
> Hi I am trying to configure notification emails on my sql2005 server for
> SQL
> agent job failures. I have configured a mail profile in database mail and
> I
> can send test emails which are received correctly.
> I have configured the notification to email me when the job completes but
> this is not working and I do not receive any notifications. When I look at
> the job history
> it gives me the message below after the normal job completion information.
> NOTE: Failed to notify 'Email User' via email.
> Any ideas as to want i can do to fix this problem.
> Cheers
> James
>|||On Jan 28, 5:23 am, "Mark Yudkin" <DoNotContac...@.boingboing.org>
wrote:
> The most likely cause is that the userid running thejobis not permitted to
> sendemail. Check out the authorizations on your mailserverand add the
> necessary permissions.
> "James Matthews" <JamesMatth...@.discussions.microsoft.com> wrote in messag
e
> news:30E73785-3D3D-4829-AB4A-3DB82F5E7E4F@.microsoft.com...
>
>
>
>
>
> - Show quoted text -
I encountered the same problem and fixed it by restarting the SQL
SERVER AGENT in Management Studio.
agent job failures. I have configured a mail profile in database mail and I
can send test emails which are received correctly.
I have configured the notification to email me when the job completes but
this is not working and I do not receive any notifications. When I look at
the job history
it gives me the message below after the normal job completion information.
NOTE: Failed to notify 'Email User' via email.
Any ideas as to want i can do to fix this problem.
Cheers
JamesThe most likely cause is that the userid running the job is not permitted to
send email. Check out the authorizations on your mail server and add the
necessary permissions.
"James Matthews" <JamesMatthews@.discussions.microsoft.com> wrote in message
news:30E73785-3D3D-4829-AB4A-3DB82F5E7E4F@.microsoft.com...
> Hi I am trying to configure notification emails on my sql2005 server for
> SQL
> agent job failures. I have configured a mail profile in database mail and
> I
> can send test emails which are received correctly.
> I have configured the notification to email me when the job completes but
> this is not working and I do not receive any notifications. When I look at
> the job history
> it gives me the message below after the normal job completion information.
> NOTE: Failed to notify 'Email User' via email.
> Any ideas as to want i can do to fix this problem.
> Cheers
> James
>|||On Jan 28, 5:23 am, "Mark Yudkin" <DoNotContac...@.boingboing.org>
wrote:
> The most likely cause is that the userid running thejobis not permitted to
> sendemail. Check out the authorizations on your mailserverand add the
> necessary permissions.
> "James Matthews" <JamesMatth...@.discussions.microsoft.com> wrote in messag
e
> news:30E73785-3D3D-4829-AB4A-3DB82F5E7E4F@.microsoft.com...
>
>
>
>
>
> - Show quoted text -
I encountered the same problem and fixed it by restarting the SQL
SERVER AGENT in Management Studio.
Job Failure emails not being sent in SQL 2005
Hi I am trying to configure notification emails on my sql2005 server for SQL
agent job failures. I have configured a mail profile in database mail and I
can send test emails which are received correctly.
I have configured the notification to email me when the job completes but
this is not working and I do not receive any notifications. When I look at
the job history
it gives me the message below after the normal job completion information.
NOTE: Failed to notify 'Email User' via email.
Any ideas as to want i can do to fix this problem.
Cheers
JamesThe most likely cause is that the userid running the job is not permitted to
send email. Check out the authorizations on your mail server and add the
necessary permissions.
"James Matthews" <JamesMatthews@.discussions.microsoft.com> wrote in message
news:30E73785-3D3D-4829-AB4A-3DB82F5E7E4F@.microsoft.com...
> Hi I am trying to configure notification emails on my sql2005 server for
> SQL
> agent job failures. I have configured a mail profile in database mail and
> I
> can send test emails which are received correctly.
> I have configured the notification to email me when the job completes but
> this is not working and I do not receive any notifications. When I look at
> the job history
> it gives me the message below after the normal job completion information.
> NOTE: Failed to notify 'Email User' via email.
> Any ideas as to want i can do to fix this problem.
> Cheers
> James
>|||On Jan 28, 5:23 am, "Mark Yudkin" <DoNotContac...@.boingboing.org>
wrote:
> The most likely cause is that the userid running thejobis not permitted to
> sendemail. Check out the authorizations on your mailserverand add the
> necessary permissions.
> "James Matthews" <JamesMatth...@.discussions.microsoft.com> wrote in message
> news:30E73785-3D3D-4829-AB4A-3DB82F5E7E4F@.microsoft.com...
>
> > Hi I am trying to configurenotificationemails on my sql2005serverfor
> >SQL
> > agentjobfailures. I have configured a mail profile in database mail and
> > I
> > can send test emails which are received correctly.
> > I have configured thenotificationtoemailme when thejobcompletes but
> > this is not working and I do not receive any notifications. When I look at
> > thejobhistory
> > it gives me the message below after the normaljobcompletion information.
> > NOTE: Failed to notify 'EmailUser' viaemail.
> > Any ideas as to want i can do to fix this problem.
> > Cheers
> > James- Hide quoted text -
> - Show quoted text -
I encountered the same problem and fixed it by restarting the SQL
SERVER AGENT in Management Studio.
agent job failures. I have configured a mail profile in database mail and I
can send test emails which are received correctly.
I have configured the notification to email me when the job completes but
this is not working and I do not receive any notifications. When I look at
the job history
it gives me the message below after the normal job completion information.
NOTE: Failed to notify 'Email User' via email.
Any ideas as to want i can do to fix this problem.
Cheers
JamesThe most likely cause is that the userid running the job is not permitted to
send email. Check out the authorizations on your mail server and add the
necessary permissions.
"James Matthews" <JamesMatthews@.discussions.microsoft.com> wrote in message
news:30E73785-3D3D-4829-AB4A-3DB82F5E7E4F@.microsoft.com...
> Hi I am trying to configure notification emails on my sql2005 server for
> SQL
> agent job failures. I have configured a mail profile in database mail and
> I
> can send test emails which are received correctly.
> I have configured the notification to email me when the job completes but
> this is not working and I do not receive any notifications. When I look at
> the job history
> it gives me the message below after the normal job completion information.
> NOTE: Failed to notify 'Email User' via email.
> Any ideas as to want i can do to fix this problem.
> Cheers
> James
>|||On Jan 28, 5:23 am, "Mark Yudkin" <DoNotContac...@.boingboing.org>
wrote:
> The most likely cause is that the userid running thejobis not permitted to
> sendemail. Check out the authorizations on your mailserverand add the
> necessary permissions.
> "James Matthews" <JamesMatth...@.discussions.microsoft.com> wrote in message
> news:30E73785-3D3D-4829-AB4A-3DB82F5E7E4F@.microsoft.com...
>
> > Hi I am trying to configurenotificationemails on my sql2005serverfor
> >SQL
> > agentjobfailures. I have configured a mail profile in database mail and
> > I
> > can send test emails which are received correctly.
> > I have configured thenotificationtoemailme when thejobcompletes but
> > this is not working and I do not receive any notifications. When I look at
> > thejobhistory
> > it gives me the message below after the normaljobcompletion information.
> > NOTE: Failed to notify 'EmailUser' viaemail.
> > Any ideas as to want i can do to fix this problem.
> > Cheers
> > James- Hide quoted text -
> - Show quoted text -
I encountered the same problem and fixed it by restarting the SQL
SERVER AGENT in Management Studio.
Job failure cannot easily be traced
sql server ver 8... Microsoft Windows 2000
A copy of a database is made using sp_attach sp_detach and the
transaction log backup scheduled for that hour fails ...makes sense !!
There has been subsequent failures at the same time at the application
log but not at the database view history.
No idea what is going on here .....
Your ideas will be more than appreciated
Vince"Vincento Harris" <wumutek@.yahoo.com> wrote in message
news:2fa13ee7.0307110428.6b0d66a@.posting.google.com...
> sql server ver 8... Microsoft Windows 2000
> A copy of a database is made using sp_attach sp_detach and the
> transaction log backup scheduled for that hour fails ...makes sense !!
> There has been subsequent failures at the same time at the application
> log but not at the database view history.
> No idea what is going on here .....
>
> Your ideas will be more than appreciated
If you are detaching, copying and re-attaching the database, the source
database will be unavailable during that time. Possibly contributing to your
job failures.
A far better technique would involve running BACKUP and RESTORE, in that way
the source db does not have to be made unavailable.
Steve
A copy of a database is made using sp_attach sp_detach and the
transaction log backup scheduled for that hour fails ...makes sense !!
There has been subsequent failures at the same time at the application
log but not at the database view history.
No idea what is going on here .....
Your ideas will be more than appreciated
Vince"Vincento Harris" <wumutek@.yahoo.com> wrote in message
news:2fa13ee7.0307110428.6b0d66a@.posting.google.com...
> sql server ver 8... Microsoft Windows 2000
> A copy of a database is made using sp_attach sp_detach and the
> transaction log backup scheduled for that hour fails ...makes sense !!
> There has been subsequent failures at the same time at the application
> log but not at the database view history.
> No idea what is going on here .....
>
> Your ideas will be more than appreciated
If you are detaching, copying and re-attaching the database, the source
database will be unavailable during that time. Possibly contributing to your
job failures.
A far better technique would involve running BACKUP and RESTORE, in that way
the source db does not have to be made unavailable.
Steve
Job failure > could this be corruption
There's this job that has been around for over 3 years it runs a DTS
package, and at the beginning of each year the DTS package it modified to
change a table name to the previous year. This year the most peculiar thing
is happening. The DTS package has been changed so that the new table name
is dbo.[Sales_2006], and when run from LOCAL PACKAGES, it runs fine. But
when the scheduled JOB runs at 5 or 6 am (I've tried many things) it fails.
HERE IS PART OF THE message pulled from HISTORY >>> Executed as user:
SQLSA. ...OnStart: Drop table [dbo].[Sales_2003] Step DTSRun OnFinish:
Drop table [dbo].[Sales_2003]
There is more But you get the point, The Job will not accept the new
changes, and continues to refer to the new table as sales_2003. I have;
deleted the job, saved the DTS package to a new name and scheduled that, RUN
DBCC CHECKTABLE (sysdtspackages), deleted many old versions of the DTS
package. I am a bit concerned that there may be a BIG problem, but at this
point I am not sure what to do next. I need to know what someone else
thinks this could be, what should be done to pin-point and resolve this.
Thanks!!
My first guess would be the package versions...I would try
to open the package, make the changes, and then do a save as
for the package. Then schedule the job to run the new
package. Are you referencing package version in your DTS run
command? My other guess is that you are using the Schedule
Package option from the package to create the job and dtsrun
command. Schedule it yourself, write your own dtsrun command
and reference the package name.
-Sue
On Mon, 12 Feb 2007 13:29:18 -0600, "WANNABE" <breichenbach
AT istate DOT com> wrote:
>There's this job that has been around for over 3 years it runs a DTS
>package, and at the beginning of each year the DTS package it modified to
>change a table name to the previous year. This year the most peculiar thing
>is happening. The DTS package has been changed so that the new table name
>is dbo.[Sales_2006], and when run from LOCAL PACKAGES, it runs fine. But
>when the scheduled JOB runs at 5 or 6 am (I've tried many things) it fails.
>HERE IS PART OF THE message pulled from HISTORY >>> Executed as user:
>SQLSA. ...OnStart: Drop table [dbo].[Sales_2003] Step DTSRun OnFinish:
>Drop table [dbo].[Sales_2003]
>There is more But you get the point, The Job will not accept the new
>changes, and continues to refer to the new table as sales_2003. I have;
>deleted the job, saved the DTS package to a new name and scheduled that, RUN
>DBCC CHECKTABLE (sysdtspackages), deleted many old versions of the DTS
>package. I am a bit concerned that there may be a BIG problem, but at this
>point I am not sure what to do next. I need to know what someone else
>thinks this could be, what should be done to pin-point and resolve this.
>Thanks!!
>
package, and at the beginning of each year the DTS package it modified to
change a table name to the previous year. This year the most peculiar thing
is happening. The DTS package has been changed so that the new table name
is dbo.[Sales_2006], and when run from LOCAL PACKAGES, it runs fine. But
when the scheduled JOB runs at 5 or 6 am (I've tried many things) it fails.
HERE IS PART OF THE message pulled from HISTORY >>> Executed as user:
SQLSA. ...OnStart: Drop table [dbo].[Sales_2003] Step DTSRun OnFinish:
Drop table [dbo].[Sales_2003]
There is more But you get the point, The Job will not accept the new
changes, and continues to refer to the new table as sales_2003. I have;
deleted the job, saved the DTS package to a new name and scheduled that, RUN
DBCC CHECKTABLE (sysdtspackages), deleted many old versions of the DTS
package. I am a bit concerned that there may be a BIG problem, but at this
point I am not sure what to do next. I need to know what someone else
thinks this could be, what should be done to pin-point and resolve this.
Thanks!!
My first guess would be the package versions...I would try
to open the package, make the changes, and then do a save as
for the package. Then schedule the job to run the new
package. Are you referencing package version in your DTS run
command? My other guess is that you are using the Schedule
Package option from the package to create the job and dtsrun
command. Schedule it yourself, write your own dtsrun command
and reference the package name.
-Sue
On Mon, 12 Feb 2007 13:29:18 -0600, "WANNABE" <breichenbach
AT istate DOT com> wrote:
>There's this job that has been around for over 3 years it runs a DTS
>package, and at the beginning of each year the DTS package it modified to
>change a table name to the previous year. This year the most peculiar thing
>is happening. The DTS package has been changed so that the new table name
>is dbo.[Sales_2006], and when run from LOCAL PACKAGES, it runs fine. But
>when the scheduled JOB runs at 5 or 6 am (I've tried many things) it fails.
>HERE IS PART OF THE message pulled from HISTORY >>> Executed as user:
>SQLSA. ...OnStart: Drop table [dbo].[Sales_2003] Step DTSRun OnFinish:
>Drop table [dbo].[Sales_2003]
>There is more But you get the point, The Job will not accept the new
>changes, and continues to refer to the new table as sales_2003. I have;
>deleted the job, saved the DTS package to a new name and scheduled that, RUN
>DBCC CHECKTABLE (sysdtspackages), deleted many old versions of the DTS
>package. I am a bit concerned that there may be a BIG problem, but at this
>point I am not sure what to do next. I need to know what someone else
>thinks this could be, what should be done to pin-point and resolve this.
>Thanks!!
>
Job failure > could this be corruption
There's this job that has been around for over 3 years it runs a DTS
package, and at the beginning of each year the DTS package it modified to
change a table name to the previous year. This year the most peculiar thing
is happening. The DTS package has been changed so that the new table name
is dbo.[Sales_2006], and when run from LOCAL PACKAGES, it runs fine. But
when the scheduled JOB runs at 5 or 6 am (I've tried many things) it fails.
HERE IS PART OF THE message pulled from HISTORY >> Executed as user:
SQLSA. ...OnStart: Drop table [dbo].[Sales_2003] Step DTSRun OnFinish:
Drop table [dbo].[Sales_2003]
There is more But you get the point, The Job will not accept the new
changes, and continues to refer to the new table as sales_2003. I have;
deleted the job, saved the DTS package to a new name and scheduled that, RUN
DBCC CHECKTABLE (sysdtspackages), deleted many old versions of the DTS
package. I am a bit concerned that there may be a BIG problem, but at this
point I am not sure what to do next. I need to know what someone else
thinks this could be, what should be done to pin-point and resolve this.
Thanks!!My first guess would be the package versions...I would try
to open the package, make the changes, and then do a save as
for the package. Then schedule the job to run the new
package. Are you referencing package version in your DTS run
command? My other guess is that you are using the Schedule
Package option from the package to create the job and dtsrun
command. Schedule it yourself, write your own dtsrun command
and reference the package name.
-Sue
On Mon, 12 Feb 2007 13:29:18 -0600, "WANNABE" <breichenbach
AT istate DOT com> wrote:
>There's this job that has been around for over 3 years it runs a DTS
>package, and at the beginning of each year the DTS package it modified to
>change a table name to the previous year. This year the most peculiar thing
>is happening. The DTS package has been changed so that the new table name
>is dbo.[Sales_2006], and when run from LOCAL PACKAGES, it runs fine. But
>when the scheduled JOB runs at 5 or 6 am (I've tried many things) it fails.
>HERE IS PART OF THE message pulled from HISTORY >> Executed as user:
>SQLSA. ...OnStart: Drop table [dbo].[Sales_2003] Step DTSRun OnFinish:
>Drop table [dbo].[Sales_2003]
>There is more But you get the point, The Job will not accept the new
>changes, and continues to refer to the new table as sales_2003. I have;
>deleted the job, saved the DTS package to a new name and scheduled that, RUN
>DBCC CHECKTABLE (sysdtspackages), deleted many old versions of the DTS
>package. I am a bit concerned that there may be a BIG problem, but at this
>point I am not sure what to do next. I need to know what someone else
>thinks this could be, what should be done to pin-point and resolve this.
>Thanks!!
>
package, and at the beginning of each year the DTS package it modified to
change a table name to the previous year. This year the most peculiar thing
is happening. The DTS package has been changed so that the new table name
is dbo.[Sales_2006], and when run from LOCAL PACKAGES, it runs fine. But
when the scheduled JOB runs at 5 or 6 am (I've tried many things) it fails.
HERE IS PART OF THE message pulled from HISTORY >> Executed as user:
SQLSA. ...OnStart: Drop table [dbo].[Sales_2003] Step DTSRun OnFinish:
Drop table [dbo].[Sales_2003]
There is more But you get the point, The Job will not accept the new
changes, and continues to refer to the new table as sales_2003. I have;
deleted the job, saved the DTS package to a new name and scheduled that, RUN
DBCC CHECKTABLE (sysdtspackages), deleted many old versions of the DTS
package. I am a bit concerned that there may be a BIG problem, but at this
point I am not sure what to do next. I need to know what someone else
thinks this could be, what should be done to pin-point and resolve this.
Thanks!!My first guess would be the package versions...I would try
to open the package, make the changes, and then do a save as
for the package. Then schedule the job to run the new
package. Are you referencing package version in your DTS run
command? My other guess is that you are using the Schedule
Package option from the package to create the job and dtsrun
command. Schedule it yourself, write your own dtsrun command
and reference the package name.
-Sue
On Mon, 12 Feb 2007 13:29:18 -0600, "WANNABE" <breichenbach
AT istate DOT com> wrote:
>There's this job that has been around for over 3 years it runs a DTS
>package, and at the beginning of each year the DTS package it modified to
>change a table name to the previous year. This year the most peculiar thing
>is happening. The DTS package has been changed so that the new table name
>is dbo.[Sales_2006], and when run from LOCAL PACKAGES, it runs fine. But
>when the scheduled JOB runs at 5 or 6 am (I've tried many things) it fails.
>HERE IS PART OF THE message pulled from HISTORY >> Executed as user:
>SQLSA. ...OnStart: Drop table [dbo].[Sales_2003] Step DTSRun OnFinish:
>Drop table [dbo].[Sales_2003]
>There is more But you get the point, The Job will not accept the new
>changes, and continues to refer to the new table as sales_2003. I have;
>deleted the job, saved the DTS package to a new name and scheduled that, RUN
>DBCC CHECKTABLE (sysdtspackages), deleted many old versions of the DTS
>package. I am a bit concerned that there may be a BIG problem, but at this
>point I am not sure what to do next. I need to know what someone else
>thinks this could be, what should be done to pin-point and resolve this.
>Thanks!!
>
Job failure > could this be corruption
There's this job that has been around for over 3 years it runs a DTS
package, and at the beginning of each year the DTS package it modified to
change a table name to the previous year. This year the most peculiar thing
is happening. The DTS package has been changed so that the new table name
is dbo.[Sales_2006], and when run from LOCAL PACKAGES, it runs fine. Bu
t
when the scheduled JOB runs at 5 or 6 am (I've tried many things) it fails.
HERE IS PART OF THE message pulled from HISTORY >>> Executed as user:
SQLSA. ...OnStart: Drop table [dbo].[Sales_2003] Step DTSRun OnFi
nish:
Drop table [dbo].[Sales_2003]
There is more But you get the point, The Job will not accept the new
changes, and continues to refer to the new table as sales_2003. I have;
deleted the job, saved the DTS package to a new name and scheduled that, RUN
DBCC CHECKTABLE (sysdtspackages), deleted many old versions of the DTS
package. I am a bit concerned that there may be a BIG problem, but at this
point I am not sure what to do next. I need to know what someone else
thinks this could be, what should be done to pin-point and resolve this.
Thanks!!My first guess would be the package versions...I would try
to open the package, make the changes, and then do a save as
for the package. Then schedule the job to run the new
package. Are you referencing package version in your DTS run
command? My other guess is that you are using the Schedule
Package option from the package to create the job and dtsrun
command. Schedule it yourself, write your own dtsrun command
and reference the package name.
-Sue
On Mon, 12 Feb 2007 13:29:18 -0600, "WANNABE" <breichenbach
AT istate DOT com> wrote:
>There's this job that has been around for over 3 years it runs a DTS
>package, and at the beginning of each year the DTS package it modified to
>change a table name to the previous year. This year the most peculiar thin
g
>is happening. The DTS package has been changed so that the new table name
>is dbo.[Sales_2006], and when run from LOCAL PACKAGES, it runs fine. B
ut
>when the scheduled JOB runs at 5 or 6 am (I've tried many things) it fails.
>HERE IS PART OF THE message pulled from HISTORY >>> Executed as user:
>SQLSA. ...OnStart: Drop table [dbo].[Sales_2003] Step DTSRun OnF
inish:
>Drop table [dbo].[Sales_2003]
>There is more But you get the point, The Job will not accept the new
>changes, and continues to refer to the new table as sales_2003. I have;
>deleted the job, saved the DTS package to a new name and scheduled that, RU
N
>DBCC CHECKTABLE (sysdtspackages), deleted many old versions of the DTS
>package. I am a bit concerned that there may be a BIG problem, but at this
>point I am not sure what to do next. I need to know what someone else
>thinks this could be, what should be done to pin-point and resolve this.
>Thanks!!
>
package, and at the beginning of each year the DTS package it modified to
change a table name to the previous year. This year the most peculiar thing
is happening. The DTS package has been changed so that the new table name
is dbo.[Sales_2006], and when run from LOCAL PACKAGES, it runs fine. Bu
t
when the scheduled JOB runs at 5 or 6 am (I've tried many things) it fails.
HERE IS PART OF THE message pulled from HISTORY >>> Executed as user:
SQLSA. ...OnStart: Drop table [dbo].[Sales_2003] Step DTSRun OnFi
nish:
Drop table [dbo].[Sales_2003]
There is more But you get the point, The Job will not accept the new
changes, and continues to refer to the new table as sales_2003. I have;
deleted the job, saved the DTS package to a new name and scheduled that, RUN
DBCC CHECKTABLE (sysdtspackages), deleted many old versions of the DTS
package. I am a bit concerned that there may be a BIG problem, but at this
point I am not sure what to do next. I need to know what someone else
thinks this could be, what should be done to pin-point and resolve this.
Thanks!!My first guess would be the package versions...I would try
to open the package, make the changes, and then do a save as
for the package. Then schedule the job to run the new
package. Are you referencing package version in your DTS run
command? My other guess is that you are using the Schedule
Package option from the package to create the job and dtsrun
command. Schedule it yourself, write your own dtsrun command
and reference the package name.
-Sue
On Mon, 12 Feb 2007 13:29:18 -0600, "WANNABE" <breichenbach
AT istate DOT com> wrote:
>There's this job that has been around for over 3 years it runs a DTS
>package, and at the beginning of each year the DTS package it modified to
>change a table name to the previous year. This year the most peculiar thin
g
>is happening. The DTS package has been changed so that the new table name
>is dbo.[Sales_2006], and when run from LOCAL PACKAGES, it runs fine. B
ut
>when the scheduled JOB runs at 5 or 6 am (I've tried many things) it fails.
>HERE IS PART OF THE message pulled from HISTORY >>> Executed as user:
>SQLSA. ...OnStart: Drop table [dbo].[Sales_2003] Step DTSRun OnF
inish:
>Drop table [dbo].[Sales_2003]
>There is more But you get the point, The Job will not accept the new
>changes, and continues to refer to the new table as sales_2003. I have;
>deleted the job, saved the DTS package to a new name and scheduled that, RU
N
>DBCC CHECKTABLE (sysdtspackages), deleted many old versions of the DTS
>package. I am a bit concerned that there may be a BIG problem, but at this
>point I am not sure what to do next. I need to know what someone else
>thinks this could be, what should be done to pin-point and resolve this.
>Thanks!!
>
Job failure - error msg attached
I have a maintenance job that is failing with the
following error message:
The job failed. Unable to determine if the owner %OWNER%
of job %JOB% has server access (reason: The log for
database 'tempdb' is not available. [SQLSTATE HY000]
(Error 9001)).
Any ideas how to resolve this one? I've looked in the KB
and BOL, and I see entries for the error and the reason
in parentheses, but not with the two together. Was
thinking just cycling the instance might resolve the
issue, but was hoping to avoid doing this. Never seen
this one before, so any help would be appreciated.
Cheers.
All I can think is no DC available for authentication? I suspect the =
reason in brackets to be misleading, if tempdb had lost it's log you =
would be getting many other problems!)
Mike John
"knives" <anonymous@.discussions.microsoft.com> wrote in message =
news:1e30b01c454a9$cf5029c0$a001280a@.phx.gbl...
> I have a maintenance job that is failing with the=20
> following error message:
>=20
> The job failed. Unable to determine if the owner %OWNER%=20
> of job %JOB% has server access (reason: The log for=20
> database 'tempdb' is not available. [SQLSTATE HY000]=20
> (Error 9001)).
>=20
> Any ideas how to resolve this one? I've looked in the KB=20
> and BOL, and I see entries for the error and the reason=20
> in parentheses, but not with the two together. Was=20
> thinking just cycling the instance might resolve the=20
> issue, but was hoping to avoid doing this. Never seen=20
> this one before, so any help would be appreciated.
>=20
> Cheers.
|||I'd start by setting the job owner to sa.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"knives" <anonymous@.discussions.microsoft.com> wrote in message
news:1e30b01c454a9$cf5029c0$a001280a@.phx.gbl...
> I have a maintenance job that is failing with the
> following error message:
> The job failed. Unable to determine if the owner %OWNER%
> of job %JOB% has server access (reason: The log for
> database 'tempdb' is not available. [SQLSTATE HY000]
> (Error 9001)).
> Any ideas how to resolve this one? I've looked in the KB
> and BOL, and I see entries for the error and the reason
> in parentheses, but not with the two together. Was
> thinking just cycling the instance might resolve the
> issue, but was hoping to avoid doing this. Never seen
> this one before, so any help would be appreciated.
> Cheers.
following error message:
The job failed. Unable to determine if the owner %OWNER%
of job %JOB% has server access (reason: The log for
database 'tempdb' is not available. [SQLSTATE HY000]
(Error 9001)).
Any ideas how to resolve this one? I've looked in the KB
and BOL, and I see entries for the error and the reason
in parentheses, but not with the two together. Was
thinking just cycling the instance might resolve the
issue, but was hoping to avoid doing this. Never seen
this one before, so any help would be appreciated.
Cheers.
All I can think is no DC available for authentication? I suspect the =
reason in brackets to be misleading, if tempdb had lost it's log you =
would be getting many other problems!)
Mike John
"knives" <anonymous@.discussions.microsoft.com> wrote in message =
news:1e30b01c454a9$cf5029c0$a001280a@.phx.gbl...
> I have a maintenance job that is failing with the=20
> following error message:
>=20
> The job failed. Unable to determine if the owner %OWNER%=20
> of job %JOB% has server access (reason: The log for=20
> database 'tempdb' is not available. [SQLSTATE HY000]=20
> (Error 9001)).
>=20
> Any ideas how to resolve this one? I've looked in the KB=20
> and BOL, and I see entries for the error and the reason=20
> in parentheses, but not with the two together. Was=20
> thinking just cycling the instance might resolve the=20
> issue, but was hoping to avoid doing this. Never seen=20
> this one before, so any help would be appreciated.
>=20
> Cheers.
|||I'd start by setting the job owner to sa.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"knives" <anonymous@.discussions.microsoft.com> wrote in message
news:1e30b01c454a9$cf5029c0$a001280a@.phx.gbl...
> I have a maintenance job that is failing with the
> following error message:
> The job failed. Unable to determine if the owner %OWNER%
> of job %JOB% has server access (reason: The log for
> database 'tempdb' is not available. [SQLSTATE HY000]
> (Error 9001)).
> Any ideas how to resolve this one? I've looked in the KB
> and BOL, and I see entries for the error and the reason
> in parentheses, but not with the two together. Was
> thinking just cycling the instance might resolve the
> issue, but was hoping to avoid doing this. Never seen
> this one before, so any help would be appreciated.
> Cheers.
Subscribe to:
Posts (Atom)