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_DTSExe cuteSQLTask_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_identifi er 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_cmdS hell @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\'+ca st(@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(D TSStep_DTSCreat eProcessTask_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\Navi planOptical001\
I am getting the following info on failure:
Executed as user: US\svcsqlserver . ...Move /Y F:\NPPDFs\*.* \\Mil-
Pegasus-01\Optical\Navi planOptical001\ F:\NPPDFs\8562. pdfF:\NPPDFs
\8830.pdfF:\NPP DFs\8869.pdfF:\ NPPDFs\8955.pdf F:\NPPDFs\8961. pdfF:
\NPPDFs\8968.pd fF:\NPPDFs\9019 .pdfF:\NPPDFs\9 023.pdfF:\NPPDF s
\9024.pdfF:\NPP DFs\9025.pdfF:\ NPPDFs\9027.pdf F:\NPPDFs\9028. pdfF:
\NPPDFs\9031.pd fF:\NPPDFs\9034 .pdfF:\NPPDFs\9 036.pdfF:\NPPDF s
\9041.pdfF:\NPP DFs\9042.pdfF:\ NPPDFs\9043.pdf F:\NPPDFs\9044. pdfF:
\NPPDFs\9047.pd fF:\NPPDFs\9055 .pdfF:\NPPDFs\9 056.pdfF:\NPPDF s
\9057.pdfF:\NPP DFs\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:\NP PDFs\9070.pdfF:
\NPPDFs\9073.pd fF:\NPPDFs\9077 .pdfF:\NPPDFs\9 079.pdfF:\NPPDF s
\9081.pdf 29 file(s) moved. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExec uteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExec uteSQLTask_1 DTSRun OnStart:
DTSStep_DTSCrea te... 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.
the job is actually completing successfully. The job only has a
couple steps. Step 1 (DTSStep_DTSExe cuteSQLTask_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_identifi er 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_cmdS hell @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)
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\'+ca st(@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(D TSStep_DTSCreat eProcessTask_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\Navi planOptical001\
I am getting the following info on failure:
Executed as user: US\svcsqlserver . ...Move /Y F:\NPPDFs\*.* \\Mil-
Pegasus-01\Optical\Navi planOptical001\ F:\NPPDFs\8562. pdfF:\NPPDFs
\8830.pdfF:\NPP DFs\8869.pdfF:\ NPPDFs\8955.pdf F:\NPPDFs\8961. pdfF:
\NPPDFs\8968.pd fF:\NPPDFs\9019 .pdfF:\NPPDFs\9 023.pdfF:\NPPDF s
\9024.pdfF:\NPP DFs\9025.pdfF:\ NPPDFs\9027.pdf F:\NPPDFs\9028. pdfF:
\NPPDFs\9031.pd fF:\NPPDFs\9034 .pdfF:\NPPDFs\9 036.pdfF:\NPPDF s
\9041.pdfF:\NPP DFs\9042.pdfF:\ NPPDFs\9043.pdf F:\NPPDFs\9044. pdfF:
\NPPDFs\9047.pd fF:\NPPDFs\9055 .pdfF:\NPPDFs\9 056.pdfF:\NPPDF s
\9057.pdfF:\NPP DFs\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:\NP PDFs\9070.pdfF:
\NPPDFs\9073.pd fF:\NPPDFs\9077 .pdfF:\NPPDFs\9 079.pdfF:\NPPDF s
\9081.pdf 29 file(s) moved. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExec uteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExec uteSQLTask_1 DTSRun OnStart:
DTSStep_DTSCrea te... 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.
Comment