|
|
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.
Navigation:
[Reply to this message]
|