Fix Failure result due to no records found

[Connie]

I have a query that takes blobs that have been created within the last
day out of sql server and places them on a disk drive. The query is a
stored proc which runs in a DTS job. The job shows failure when there
are no pdf's created in the last day, how do I correct this??
Here is my stored proc code:
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 = 'MILNPPRODSQL',

@login = 'sa',

@password = '<sa password>',

@dbname = '<db name>',

@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

[Erland Sommarskog]

Connie (cs*****@rwbaird.com) writes:

I have a query that takes blobs that have been created within the last
day out of sql server and places them on a disk drive. The query is a
stored proc which runs in a DTS job. The job shows failure when there
are no pdf's created in the last day, how do I correct this??
Here is my stored proc code:

Yeah, we've seen your stored procedure a couple of times now. :-) But
what about the error message? Does the procedure produce an error? Don't
you get that later on when you try to move the stuff?

Anyway, post the error message and the code that produces it. Then you
may get more than guesses in return.

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

[MC]

I dont know where exactly do you get an error and what the error is but in
general you could issue count(*) from the table (same select as in a cursor,
only put count instead of id). Then you can set variable value with the
results of the count. After that, its just handling the package depending on
the var value....
MC
"Connie" <cs*****@rwbaird.comwrote in message
news:11**********************@u30g2000hsc.googlegr oups.com...

>I have a query that takes blobs that have been created within the last
day out of sql server and places them on a disk drive. The query is a
stored proc which runs in a DTS job. The job shows failure when there
are no pdf's created in the last day, how do I correct this??
Here is my stored proc code:
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 = 'MILNPPRODSQL',

@login = 'sa',

@password = '<sa password>',

@dbname = '<db name>',

@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

[Connie]

On May 8, 1:00 am, "MC" <[e-mail]:

I dont know where exactly do you get an error and what the error is but in
general you could issue count(*) from the table (same select as in a cursor,
only put count instead of id). Then you can set variable value with the
results of the count. After that, its just handling the package depending on
the var value....

MC

"Connie" <[e-mail] in message

news:11**********************@u30g2000hsc.googlegr oups.com...

I have a query that takes blobs that have been created within the last
day out of sql server and places them on a disk drive. The query is a
stored proc which runs in a DTS job. The job shows failure when there
are no pdf's created in the last day, how do I correct this??
Here is my stored proc code:
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 = 'MILNPPRODSQL',

@login = 'sa',

@password = '<sa password>',

@dbname = '<db name>',

@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- Hide quoted text -

- Show quoted text -

Sorry here is more detail. Last week we had determined that on step 2
I was experiencing a failure due to loss of connection to our Pegasus
server. This time I got an error on step 1 which is the code above
and here is the error message:
Executed as user: US\svcsqlserver. ...Move /Y F:\NPPDFs\*.* \\Mil-
Pegasus-01\Optical\NaviplanOptical001\The filename, directory name, or
volume label syntax is incorrect.DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSCreateProcessTask_1 DTSRun OnError:
DTSStep_DTSCreateProcessTask_1, Error = -2147220330 (80040496)
Error string: CreateProcessTask 'DTSTask_DTSCreateProcessTask_1':
Process returned code 1, which does not match the specified
SuccessReturnCode of 0. Error source: Microsoft Data
Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 4900 Error Detail Records:
Error: -2147220330 (80040496); Provider Error: 0 (0) Error
string: CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process
returned code 1, which does not match the specified SuccessReturnCode
of 0. Erro... Process Exit Code 1. The step failed.

Basically the stored proc is runs

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)

and there are no blobs (PDF's) returned by the select above so the
procedure fails as there are no PDF's to move to (SET @file_name = 'F:
\NPPDFs\'+cast(@pk as varchar(10))+'.pdf') @file_name.

Does that help (or make better sense)

Thanks much :)

[Erland Sommarskog]

Connie (cs*****@rwbaird.com) writes:

Sorry here is more detail. Last week we had determined that on step 2
I was experiencing a failure due to loss of connection to our Pegasus
server. This time I got an error on step 1 which is the code above
and here is the error message:
Executed as user: US\svcsqlserver. ...Move /Y F:\NPPDFs\*.* \\Mil-
Pegasus-01\Optical\NaviplanOptical001\The filename, directory name, or
volume label syntax is incorrect.DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
...
Basically the stored proc is runs

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)

and there are no blobs (PDF's) returned by the select above so the
procedure fails as there are no PDF's to move to (SET @file_name = 'F:
\NPPDFs\'+cast(@pk as varchar(10))+'.pdf') @file_name.

I can't see that your stored procedure attempts to perform a MOVE
command, so the code for the procedure does not matter. Apparently
you have something that invokes MOVE in the DTS job. I guess you need
to add a check somewhere in your DTS job so that it does not try to
move any files that aren't there.

Since I don't know what your DTS job looks like, I can't really suggest
how that should be done. Actually, since I have no experience of DTS,
I would probably not be able to anyway.
--
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