note: I posted under Uri's thread too... Sorry, should've just posted
here...

Hey Allan,

How 'bout some help with this one?

Runs fine when I run manually from EM. When I schedule the job and assign
it the svcSQL account it fails... What has me baffled is that it processes
the datapumptask_1 a little bit(looks like 5000ish rows) and then fails. I
could understand if it just failed to process, but why the partial
processing?

Thanks for any help you might provide!

Travis

Error from Jobs below...
Executed as user: DOMAIN\svcSQL. ...OnStart: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been
transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun
OnProgress: DTSStep_DTSDataPumpTask_1; 2000 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 3000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 3000 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed o... Process
Exit Code 1. The step failed.

I just got a different failure on the last run... It failed at 4000 versus
5000. WEIRD!

TR

Is there no error code there ? Expected would be 80040005

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"REM7600" <rem7600 (AT) hotmail (DOT) com> wrote

WYSIWYG

Event Viewer/Application Logs shows the failure but nothing of use
Nothing of use in the SQL Server Logs either...

Did I stump "The Master"?

:-)

Travis

You are too kind !

Have the package log to text file.


Log in to the server as the account under which the SQL Server Agent is
started and now interactively execute the package

Any more errors ?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"REM7600" <rem7600 (AT) hotmail (DOT) com> wrote

WELL, here's the scoop... I logged into the svcSQL account and tested
manually, most of it worked except that the "SendMail" routine in the
package failed... I had gone into the account and tested the e-mail prior
under support services... BUT... Obviously something was wrong so I copied
the MS EXCHANGE SERVER settings to a profile called Outlook I immediately
started getting the report e-mail to me by the scheduled job!

All I can figure is that the number of lines or amount of text reported in a
job step failure is limited and although it looked like the DataPump was
failing, it was actually the final object SendMail routine that was failing.

Allan can you confirm (or deny) that the job detail is always valid?
Limited rows? characters?

Thanks for the help! Remember, U Da Man!

:-)

Travis

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

In message <ePHGAg8zDHA.2476 (AT) TK2MSFTNGP09 (DOT) phx.gbl>, REM7600
<rem7600 (AT) hotmail (DOT) com> writes
your testing, but the service account on the server obviously had a
different or even no MAPI profile. Mapi isn't great for sever side
processes.


For the full output you need to set a job step log file, or better still
for Dts use the package log.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Glad we helped

showing the results of jobs in EM executes sp_help_jobhistory

It returns in the resultset "message" which is an nvarchar(1024)

so yes the output is curtailed


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"REM7600" <rem7600 (AT) hotmail (DOT) com> wrote

Thanks Guys... I learn a little more each day! :-)

Darren, I'm curious, what do you suggest as a replacement for using the MAPI
sessions?

Travis

Here's one alternative: http://www.sqldev.net/xp/xpsmtp.htm

--

Andrew J. Kelly
SQL Server MVP


"REM7600" <rem7600 (AT) hotmail (DOT) com> wrote