Agent job fails, why?

SQL Server 2005 RTM:
I have created a job to copy a database from another server, but the job
fails when I try to run it.
This is the only information I can see in the Job History:
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
Emailed,Operator Net sent,Operator Paged,Retries Attempted
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
last step to run was step 1
(CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
as user: SAFARI\Administrator. The package execution failed. The step
failed.,00:00:31,0,0,,,,0
This doesn't help me to understand why the job fails.
How can I find the "Real" error log and get a decent clue on why the job
failed?
OlavSpecify an output file for the job step and see if you get any root error messages there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav Tollefsen" <x@.y.com> wrote in message news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
> SQL Server 2005 RTM:
> I have created a job to copy a database from another server, but the job fails when I try to run
> it.
> This is the only information I can see in the Job History:
> Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql
> Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The last step to run was
> step 1 (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
> as user: SAFARI\Administrator. The package execution failed. The step failed.,00:00:31,0,0,,,,0
> This doesn't help me to understand why the job fails.
> How can I find the "Real" error log and get a decent clue on why the job failed?
> Olav
>|||I have created a new SSIS package using the Import / Export Wizard and
theese are the errors I get when I run it:
...
Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Ad hoc updates to system catalogs are not
allowed.".
Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The "input "OLE DB Destination Input" (29)" failed because
error code 0xC020907B occurred, and the error row disposition on "input "OLE
DB Destination Input" (29)" specifies failure on error. An error occurred on
the specified object of the specified component.
Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline:
The ProcessInput method on component "OLE DB Destination" (2) failed with
error code 0xC0209029. The identified component returned an error from the
ProcessInput method. The error is specific to the component, but the error
is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline:
Thread "WorkThread0" has exited with error code 0xC0209029.
Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The final commit for the data insertion has started.
Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The final commit for the data insertion has ended.
Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: "component "OLE DB Destination" (2)" wrote 0 rows.
Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
Task failed: Execute Inner Package
Task failed: CompensatingSql
SSIS package "Package1.dtsx" finished: Failure.
...
Seems like the root error message is "Ad hoc updates to system catalogs are
not allowed.".
This doesn't make me very much wiser!
Does anyone have a clueon what this means?
Olav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
> Specify an output file for the job step and see if you get any root error
> messages there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Olav Tollefsen" <x@.y.com> wrote in message
> news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
>> SQL Server 2005 RTM:
>> I have created a job to copy a database from another server, but the job
>> fails when I try to run it.
>> This is the only information I can see in the Job History:
>> Date,Source,Severity,Step ID,Server,Job Name,Step
>> Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
>> Emailed,Operator Net sent,Operator Paged,Retries Attempted
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
>> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
>> last step to run was step 1
>> (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
>> as user: SAFARI\Administrator. The package execution failed. The step
>> failed.,00:00:31,0,0,,,,0
>> This doesn't help me to understand why the job fails.
>> How can I find the "Real" error log and get a decent clue on why the job
>> failed?
>> Olav
>|||I'm no DTS person, but it looks line you try to execute some code that tries to modify the system
tables on 2005 (which isn't possible). If this isn't the case, I suggest you post to the DTS group.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Olav Tollefsen" <x@.y.com> wrote in message news:uaSLdqw4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I have created a new SSIS package using the Import / Export Wizard and theese are the errors I get
>when I run it:
> ...
> Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Prepare for
> Execute phase is beginning.
> Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Pre-Execute phase
> is beginning.
> Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Execute phase is
> beginning.
> Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: An OLE DB
> error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005
> Description: "Ad hoc updates to system catalogs are not allowed.".
> Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The "input
> "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row
> disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error
> occurred on the specified object of the specified component.
> Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: The ProcessInput method
> on component "OLE DB Destination" (2) failed with error code 0xC0209029. The identified component
> returned an error from the ProcessInput method. The error is specific to the component, but the
> error is fatal and will cause the Data Flow task to stop running.
> Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Thread "WorkThread0"
> has exited with error code 0xC0209029.
> Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Post Execute
> phase is beginning.
> Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The
> final commit for the data insertion has started.
> Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The
> final commit for the data insertion has ended.
> Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Cleanup phase is
> beginning.
> Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: "component "OLE
> DB Destination" (2)" wrote 0 rows.
> Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
> Task failed: Execute Inner Package
> Task failed: CompensatingSql
> SSIS package "Package1.dtsx" finished: Failure.
> ...
> Seems like the root error message is "Ad hoc updates to system catalogs are not allowed.".
> This doesn't make me very much wiser!
> Does anyone have a clueon what this means?
> Olav
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
>> Specify an output file for the job step and see if you get any root error messages there.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Olav Tollefsen" <x@.y.com> wrote in message news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
>> SQL Server 2005 RTM:
>> I have created a job to copy a database from another server, but the job fails when I try to run
>> it.
>> This is the only information I can see in the Job History:
>> Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql
>> Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
>> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The last step to run was
>> step 1 (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
>> as user: SAFARI\Administrator. The package execution failed. The step failed.,00:00:31,0,0,,,,0
>> This doesn't help me to understand why the job fails.
>> How can I find the "Real" error log and get a decent clue on why the job failed?
>> Olav
>>
>