Job fails, but can't find what's wrong

[Amy]

I have a job transforming a foxpro database into a sql
server database. When I run it on our production SQL
Server, it fails; when we run it on a user's local SQL
Server, it runs with no errors. The job FTP's a file,
unzips it, then executes a transformation task. We've
had problems with the agent's user not having access to
the Foxpro drivers (which I still don't think I've
resolved), but the weird thing is the job actually starts
running now, and fails near the end. So it obviously is
at least finding the Foxpro drivers now, because we can
see the table is getting loaded. But the load fails
before all of the rows are loaded, and this is the only
error message I get in the "details" section of the
history:

Executed as user: GLOBUSANDCOSMOS\SQL. ...p -d
y:\tech\cmsdataWarning: TZ environment variable not
found, cannot use UTC times!! Archive:
y:/tech/glb_exp.zip inflating:
y:/tech/cmsdata/EXPORT.DBF inflating:
y:/tech/cmsdata/notes.dbf inflating:
y:/tech/cmsdata/notes.fpt C:\WINDOWS\system32>del
y:\tech\glb_exp.zipDTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSFTPTask_1
DTSRun OnFinish: DTSStep_DTSFTPTask_1 DTSRun OnStart:
DTSStep_DTSCreateProcessTask_1 DTSRun OnFinish:
DTSStep_DTSCreateProcessTask_1 DTSRun OnStart:
DTSStep_DTSCreateProcessTask_2 DTSRun OnFinish:
DTSStep_DTSCreateProcessTask_2 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_3 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_3; 1000 Rows have been
transformed or copied... Process Exit Code 1. The step
failed.

Does anyone know where I can get a more detailed
description of what is happening? Also, what kind of
security do I need to set up to make sure the SQL Server
Agent user has access to them? Thanks.

Amy

[Allan Mitchell]

The job step history which is what we see here is not very useful as it is
truncated before we get to the meat of the matter.
Have the package itself and the Datapump tasks log to text files ++ if you
are calling the package in the job through a CmdExec step then look at the
advanced tab of the step and specify an output file.

When things go bent in a job but not through EM then you can usually figure
things out by reading this article.

http://support.microsoft.com/?kbid=269074
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know

Post by Amy
I have a job transforming a foxpro database into a sql
server database. When I run it on our production SQL
Server, it fails; when we run it on a user's local SQL
Server, it runs with no errors. The job FTP's a file,
unzips it, then executes a transformation task. We've
had problems with the agent's user not having access to
the Foxpro drivers (which I still don't think I've
resolved), but the weird thing is the job actually starts
running now, and fails near the end. So it obviously is
at least finding the Foxpro drivers now, because we can
see the table is getting loaded. But the load fails
before all of the rows are loaded, and this is the only
error message I get in the "details" section of the
Executed as user: GLOBUSANDCOSMOS\SQL. ...p -d
y:\tech\cmsdataWarning: TZ environment variable not
y:/tech/cmsdata/notes.fpt C:\WINDOWS\system32>del
Executing... DTSRun OnStart: DTSStep_DTSFTPTask_1
DTSStep_DTSDataPumpTask_3; 1000 Rows have been
transformed or copied... Process Exit Code 1. The step
failed.
Does anyone know where I can get a more detailed
description of what is happening? Also, what kind of
security do I need to set up to make sure the SQL Server
Agent user has access to them? Thanks.
Amy

[Amy]

Getting the error log worked, thanks! But now this is what it says:
DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147467259 (80004005)

Error string: Error at Destination for Row number 1186601. Errors
encountered so far in this task: 1.
Error source: DTS Data Pump
Help file:
Help context: 0

Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 60 (3C)
Error string: ROW-00060: Internal error: [dainsert,16]
Error source: OraOLEDB
Help file:
Help context: 0

DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147213206 (8004206A)
Error string: The number of failing rows exceeds the maximum specified.
Error source: Microsoft Data Transformation Services (DTS) Data Pump
Help file: sqldts80.hlp
Help context: 0

Any ideas on what THAT means? Thanks so much for the help...

Post by Allan Mitchell
The job step history which is what we see here is not very useful as it is
truncated before we get to the meat of the matter.
Have the package itself and the Datapump tasks log to text files ++ if you
are calling the package in the job through a CmdExec step then look at the
advanced tab of the step and specify an output file.
When things go bent in a job but not through EM then you can usually figure
things out by reading this article.
http://support.microsoft.com/?kbid=269074
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know

Post by Amy
I have a job transforming a foxpro database into a sql
server database. When I run it on our production SQL
Server, it fails; when we run it on a user's local SQL
Server, it runs with no errors. The job FTP's a file,
unzips it, then executes a transformation task. We've
had problems with the agent's user not having access to
the Foxpro drivers (which I still don't think I've
resolved), but the weird thing is the job actually starts
running now, and fails near the end. So it obviously is
at least finding the Foxpro drivers now, because we can
see the table is getting loaded. But the load fails
before all of the rows are loaded, and this is the only
error message I get in the "details" section of the
Executed as user: GLOBUSANDCOSMOS\SQL. ...p -d
y:\tech\cmsdataWarning: TZ environment variable not
y:/tech/cmsdata/notes.fpt C:\WINDOWS\system32>del
Executing... DTSRun OnStart: DTSStep_DTSFTPTask_1
DTSStep_DTSDataPumpTask_3; 1000 Rows have been
transformed or copied... Process Exit Code 1. The step
failed.
Does anyone know where I can get a more detailed
description of what is happening? Also, what kind of
security do I need to set up to make sure the SQL Server
Agent user has access to them? Thanks.
Amy

[Allan Mitchell]

80004005 is a general exception and usually relates to permissions but that
is probably not the case here. What I think we may have is a problem with
the driver and a row of data.

On the last tab of the datapump in 2000 there is the option to throw bad
rows out to a text file.

Also set allowable bad rows to some high figure as well.
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know

Post by Amy
DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147467259 (80004005)
Error string: Error at Destination for Row number 1186601. Errors
encountered so far in this task: 1.
Error source: DTS Data Pump
Help context: 0
Error: -2147467259 (80004005); Provider Error: 60 (3C)
Error string: ROW-00060: Internal error: [dainsert,16]
Error source: OraOLEDB
Help context: 0
DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147213206 (8004206A)
Error string: The number of failing rows exceeds the maximum specified.
Error source: Microsoft Data Transformation Services (DTS) Data Pump
Help file: sqldts80.hlp
Help context: 0
Any ideas on what THAT means? Thanks so much for the help...

Post by Allan Mitchell
The job step history which is what we see here is not very useful as it is
truncated before we get to the meat of the matter.
Have the package itself and the Datapump tasks log to text files ++ if you
are calling the package in the job through a CmdExec step then look at the
advanced tab of the step and specify an output file.
When things go bent in a job but not through EM then you can usually figure
things out by reading this article.
http://support.microsoft.com/?kbid=269074
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know

Post by Amy
I have a job transforming a foxpro database into a sql
server database. When I run it on our production SQL
Server, it fails; when we run it on a user's local SQL
Server, it runs with no errors. The job FTP's a file,
unzips it, then executes a transformation task. We've
had problems with the agent's user not having access to
the Foxpro drivers (which I still don't think I've
resolved), but the weird thing is the job actually starts
running now, and fails near the end. So it obviously is
at least finding the Foxpro drivers now, because we can
see the table is getting loaded. But the load fails
before all of the rows are loaded, and this is the only
error message I get in the "details" section of the
Executed as user: GLOBUSANDCOSMOS\SQL. ...p -d
y:\tech\cmsdataWarning: TZ environment variable not
y:/tech/cmsdata/notes.fpt C:\WINDOWS\system32>del
Executing... DTSRun OnStart: DTSStep_DTSFTPTask_1
DTSStep_DTSDataPumpTask_3; 1000 Rows have been
transformed or copied... Process Exit Code 1. The step
failed.
Does anyone know where I can get a more detailed
description of what is happening? Also, what kind of
security do I need to set up to make sure the SQL Server
Agent user has access to them? Thanks.
Amy

[Viji]

Thanks... This helped me a lot.. :) i never knew that such a log file exists before seeing this blog... :

From http://www.developmentnow.com/g/103_2004_8_0_0_397217/Job-fails-but-cant-find-whats-wrong.ht

Posted via DevelopmentNow.com Group
http://www.developmentnow.com/g/