Posted by Connie on 05/04/07 14:02

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
>= pv.update_time and pr.create_time > (Getdate()-1)

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:\NPPDFs\8961.pdfF:
\NPPDFs\8968.pdfF:\NPPDFs\9019.pdfF:\NPPDFs\9023.pdfF:\NPPDFs
\9024.pdfF:\NPPDFs\9025.pdfF:\NPPDFs\9027.pdfF:\NPPDFs\9028.pdfF:
\NPPDFs\9031.pdfF:\NPPDFs\9034.pdfF:\NPPDFs\9036.pdfF:\NPPDFs
\9041.pdfF:\NPPDFs\9042.pdfF:\NPPDFs\9043.pdfF:\NPPDFs\9044.pdfF:
\NPPDFs\9047.pdfF:\NPPDFs\9055.pdfF:\NPPDFs\9056.pdfF:\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.pdfF:\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.

[Back to original message]