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 me
ssages 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...[vb
col=seagreen]
> SQL Server 2005 RTM:
> I have created a job to copy a database from another server, but the job f
ails 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,Messa
ge,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_SQLSERVER20
05_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_SQLSERVER20
05_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_
0_Step,,Executed
> as user: SAFARI\Administrator. The package execution failed. The step fai
led.,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 f
ailed?
> Olav
>[/vbcol]|||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 beca
use
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.Pipeli
ne:
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.Pipeli
ne:
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...
>|||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...[vbc
ol=seagreen]
>I have created a new SSIS package using the Import / Export Wizard and thee
se are the errors I get
>when I run it:
> ...
> Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Prepare for
> Execute phase is beginning.
> Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Pre-Execute phase
> is beginning.
> Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Execute phase is
> beginning.
> Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB D
estination [2]: An OLE DB
> error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hres
ult: 0x80004005
> Description: "Ad hoc updates to system catalogs are not allowed.".
> Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB D
estination [2]: The "input
> "OLE DB Destination Input" (29)" failed because error code 0xC020907B occu
rred, and the error row
> disposition on "input "OLE DB Destination Input" (29)" specifies failure o
n error. An error
> occurred on the specified object of the specified component.
> Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipe
line: The ProcessInput method
> on component "OLE DB Destination" (2) failed with error code 0xC0209029. T
he identified component
> returned an error from the ProcessInput method. The error is specific to t
he 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.Pipe
line: Thread "WorkThread0"
> has exited with error code 0xC0209029.
> Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Post Execute
> phase is beginning.
> Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OL
E DB Destination [2]: The
> final commit for the data insertion has started.
> Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OL
E DB Destination [2]: The
> final commit for the data insertion has ended.
> Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Cleanup phase is
> beginning.
> Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.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 ar
e 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 i
n message
> news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
>[/vbcol]