471,089 Members | 1,088 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,089 software developers and data experts.

DTS Job Reporting Failures

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:\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.

May 4 '07 #1
7 4153
Are you logging the dts

May 4 '07 #2
On May 4, 9:18 am, rshivara...@gmail.com wrote:
Are you logging the dts
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.

May 4 '07 #3
On May 4, 9:23 am, Connie <csaw...@rwbaird.comwrote:
On May 4, 9:18 am, rshivara...@gmail.com wrote:
Are you logging the dts

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.
Where does this log to?? I cannot find a log on the server??

May 4 '07 #4
On May 4, 10:33 am, Connie <csaw...@rwbaird.comwrote:
On May 4, 9:23 am, Connie <csaw...@rwbaird.comwrote:
On May 4, 9:18 am, rshivara...@gmail.com wrote:
Are you logging the dts
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.

Where does this log to?? I cannot find a log on the server??
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.

May 4 '07 #5
On May 4, 10:42 am, rshivara...@gmail.com wrote:
On May 4, 10:33 am, Connie <csaw...@rwbaird.comwrote:
On May 4, 9:23 am, Connie <csaw...@rwbaird.comwrote:
On May 4, 9:18 am, rshivara...@gmail.com wrote:
Are you logging the dts
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.
Where does this log to?? I cannot find a log on the server??

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.
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??

May 4 '07 #6
Connie (cs*****@rwbaird.com) writes:
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.
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, es****@sommarskog.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
May 4 '07 #7
On May 4, 4:40 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
Connie (csaw...@rwbaird.com) writes:
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.

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, esq...@sommarskog.se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -

- Show quoted text -
Thanks Erland I was leaning towards a network issue of some sort I
appreciate the verification.

May 4 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Remy Blank | last post: by
reply views Thread by Jason Heyes | last post: by
reply views Thread by db2sysc | last post: by
3 posts views Thread by jez123456 | last post: by
8 posts views Thread by Woody Splawn | last post: by
158 posts views Thread by jacob navia | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.