|
|
Posted by Connie on 05/07/07 16:02
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
Navigation:
[Reply to this message]
|