I have a DTS Job that is reporting failures but it looks to me as if
the job is actually completing successfully. The job only has a
couple steps. Step 1 (DTSStep_DTSExecuteSQLTask_1) Execute SQL Task,
runs a stored procedure to export blobs (pdf files) out of sql server
and onto the local machine.
Here is the code in the storedprocedure called sp_PDFExport
CREATE PROCEDURE [dbo].[sp_PDFExport] AS
begin
set quoted_identifier off

declare @pk int

declare @where_clause varchar(100)

declare @file_name varchar (50)

declare @debug varchar (50)

Declare @cmd varchar (50)

--debug

/*if @Debug = 1
print @cmd
exec Master..xp_cmdShell @cmd */


-- begin cursor

DECLARE LOOKUP CURSOR FOR select pr.[id]
from plan_report pr, plan_version pv
where pv.plan_id = pr.plan_id and pv.status = '30' and pr.create_time
OPEN LOOKUP

FETCH NEXT FROM LOOKUP INTO @pk


-- Loop through the list

WHILE @@FETCH_STATUS = 0

BEGIN


SET @where_clause = 'Where' + '[ID]' + '=' + cast(@pk as
varchar(10))


SET @file_name = 'F:\NPPDFs\'+cast(@pk as varchar(10))+'.pdf'

exec sp_textcopy @srvname = '<Server Name is here>',

@login = 'sa',

@password = '<sa password here>',

@dbname = '<database name here>',

@tbname = 'Plan_Report',

@colname = 'document',

@filename = @file_name,

@whereclause = @where_clause,

@direction = 'o' -- 'o' for output, 'i' for input

-- loop cursor

SET @pk = NULL

SET @where_clause = NULL

SET @file_name = NULL

FETCH NEXT FROM LOOKUP INTO @pk


END


-- cleanup

CLOSE LOOKUP

DEALLOCATE LOOKUP

end
GO

Then on success of this step I run the following Execute Process Task:
F:\NPMove.bat(DTSStep_DTSCreateProcessTask_1) which runs a batch file
command to move the PDF's from the local machine to our optical
storage. Here is the batch file command:

CD F:
Move /Y F:\NPPDFs\*.* \\Mil-Pegasus-01\Optical\NaviplanOptical001\

I am getting the following info on failure:
Executed as user: US\svcsqlserver. ...Move /Y F:\NPPDFs\*.* \\Mil-
Pegasus-01\Optical\NaviplanOptical001\F:\NPPDFs\8562.pdfF: \NPPDFs
\8830.pdfF:\NPPDFs\8869.pdfF:\NPPDFs\8955.pdfF:\NP PDFs\8961.pdfF:
\NPPDFs\8968.pdfF:\NPPDFs\9019.pdfF:\NPPDFs\9023.p dfF:\NPPDFs
\9024.pdfF:\NPPDFs\9025.pdfF:\NPPDFs\9027.pdfF:\NP PDFs\9028.pdfF:
\NPPDFs\9031.pdfF:\NPPDFs\9034.pdfF:\NPPDFs\9036.p dfF:\NPPDFs
\9041.pdfF:\NPPDFs\9042.pdfF:\NPPDFs\9043.pdfF:\NP PDFs\9044.pdfF:
\NPPDFs\9047.pdfF:\NPPDFs\9055.pdfF:\NPPDFs\9056.p dfF:\NPPDFs
\9057.pdfF:\NPPDFs\9058.pdfThe specified network name is no longer
available.The specified network name is no longer available.The
specified network name is no longer available.F:\NPPDFs\9070.pdfF:
\NPPDFs\9073.pdfF:\NPPDFs\9077.pdfF:\NPPDFs\9079.p dfF:\NPPDFs
\9081.pdf 29 file(s) moved. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSCreate... Process Exit Code 1. The step failed.

When I check our optical storage I see the pdf's above have been moved
there, so my issues is what is actually failing? If anyone has any
ideas I would greatly appreciate it If step 1 is failing then the
above PDF files would not be there to move so it must be succeeding,
I am just not sure what else to look at.

Are you logging the dts

On May 4, 9:18 am, rshivara... (AT) gmail (DOT) com wrote:
yes in the package properties I have logging checked and I am logging
to the sql server that this process is running on milnpprodsql, using
the sa login and password.

On May 4, 9:23 am, Connie <csaw... (AT) rwbaird (DOT) com> wrote:
Where does this log to?? I cannot find a log on the server??

On May 4, 10:33 am, Connie <csaw... (AT) rwbaird (DOT) com> wrote:
go to the dtspackage in enterprise manager\Data transformation Services
\Local Packages and right click the dts and you can find the Package
logs in it.

On May 4, 10:42 am, rshivara... (AT) gmail (DOT) com wrote:
Duh I'm sorry I know those were there but I just didn't make the
connection, bad day! The error doesn't really hellp me much



Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:CreateProcessTask
'DTSTask_DTSCreateProcessTask_1': Process returned code 1, which does
not match the specified SuccessReturnCode of 0.
Step Error code: 80040496
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4900

This is the step that is moving the PDF files and I can confirm that
it is completing. I selected workflow properties for this task and on
the options tab Use ActiveX Script is selected, since I am not using
any activex script in this DTS job could that be causing the error??

Connie (csawyer (AT) rwbaird (DOT) com) writes:
I guess it's the "The specified network name is no longer available."
Seems like there was a glitch when the share disappeared for a short
while. If you can verify that all files are where they should be, I guess
you can sleep well.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On May 4, 4:40 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Thanks Erland I was leaning towards a network issue of some sort I
appreciate the verification.